[postgis-users] WORKAROUND: Restore of PostgreSQL/PostGIS Database fails with functional Index on ST_Transform

[schild]

Hi all,


Migration of a PostGIS database via pg_restore induces an error, if the database includes a functional index on ST_Transform:


[postgres ~] $ pg_restore --username=postgres --dbname=mydb --verbose  --no-tablespaces  --index=mygeom_etrs89laea_gist "mydb.backup"

=> Error Message (see below)


Workaround:

mydb=# ALTER FUNCTION ST_Transform(geometry, INTEGER) SET search_path=public;


pg_restore: connecting to database for restore
pg_restore: creating INDEX "myschema.mygeom_etrs89laea_gist "


Greetings from Vienna,

Andreas


* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Error Message:

pg_restore: connecting to database for restore
pg_restore: creating INDEX "myschema.mygeom_etrs89laea_gist"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 17844; 1259 443698 INDEX mygeom_etrs89laea_gist                                      postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "spatial_ref_sys" does not exist
LINE 1: SELECT proj4text FROM spatial_ref_sys WHERE srid = 31287 LIM...

                                                            ^
QUERY:  SELECT proj4text FROM spatial_ref_sys WHERE srid = 31287 LIMIT 1
    Command was: CREATE INDEX mygeom_etrs89laea_gist ON mygeom USING gist (public.st_transform(geom, 93035));


* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *


mydb=# SELECT version();

PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.2.0, 64-bit


mydb=# SELECT postgis_full_version();

POSTGIS="2.4.2 r16113" PGSQL="100" GEOS="3.6.2-CAPI-1.10.2 4d2925d6"

PROJ="Rel.4.9.3, 15 August 2016" GDAL="GDAL 2.2.3, released 2017/11/20"

LIBXML="2.9.7" LIBJSON="0.13" LIBPROTOBUF="1.3.0" TOPOLOGY RASTER