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

[Paul Ramsey]

Remember to

ALTER FUNCTION ST_Transform(geometry, INTEGER) RESET search_path;

when you are done your restore, or all your calls to ST_Transform()
will have a 4x performance penalty.

P


On Wed, Jan 17, 2018 at 8:56 AM, schild <andreas.schild at bfw.gv.at> wrote:
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users