[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] WORKAROUND: Restore of PostgreSQL/PostGIS Database fails with functional Index o
From:       "Regina Obe" <lr () pcorp ! us>
Date:       2018-01-17 17:26:17
Message-ID: 000001d38fb8$491df1d0$db59d570$ () pcorp ! us
[Download RAW message or body]

This issue should be fixed in newly released PostGIS 2.4.3

So if you pg_restore on a PostGIS 2.4.3, no set search path should be needed anymore.

Thanks,
Regina

-----Original Message-----
From: postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] On Behalf Of Paul \
                Ramsey
Sent: Wednesday, January 17, 2018 12:11 PM
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
Subject: Re: [postgis-users] WORKAROUND: Restore of PostgreSQL/PostGIS Database fails \
with functional Index on ST_Transform

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@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@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users


[prev in list] [next in list] [prev in thread] [next in thread]