[postgis-users] Importing relations from database where postgis extension was created with a different schema

Thu Nov 9 02:24:58 PST 2017

Alternatively you can play with sed to patch the dump and get another
schema name:

pg_dump ... |sed s/postgis./public./g    | psql

чт, 9 нояб. 2017 г. в 4:25, Regina Obe <lr at pcorp.us>:

> Okay I just tested installing postgis in public schema.  Then creating a
> whole bunch of foreign tables with ogr_fdw and confirmed the geometries
> showed as public.geometry and confirmed I could query with ST_SetSRID  and
> ST_Transform.
>
> Then I moved postgis to postgis schema, making sure to add postgis to the
> database search_path first.
> Opened a new database connection as was able to open up my ogr tables fine.
>
> Now if I remove postgis schema from database search path and reconnect,
> then my database crashes if I try to query an ogr_fdw table.
>
> -- so basic steps
>
> CREATE EXTENSION postgis;
>
> CREATE SERVER svr_shp FOREIGN DATA WRAPPER ogr_fdw
> OPTIONS (datasource 'C:/fdw_data/massgis/shps',
>  format 'ESRI Shapefile'
> );
> CREATE SCHEMA shps;
>
> IMPORT FOREIGN SCHEMA ogr_all
> FROM SERVER svr_shp INTO shps;
>
> -- then disconnect from database and reopen.
> -- this works fine
> SELECT ST_Transform(geom, 4269) from shps.towns_poly limit 10;
>
> CREATE SCHEMA postgis;
>
> ALTER DATABASE test_db
> SET search_path = public, postgis;
>
> ALTER EXTENSION postgis
>   SET SCHEMA postgis;
>
> ALTER EXTENSION postgis
>   UPDATE TO "2.4.1next";
>
> ALTER EXTENSION postgis
>   UPDATE TO "2.4.1";
>
> -- disconnect and make new connection
> -- if you are using pgAdmin, make sure to refresh as pgAdmin will show a
> cached structure from before.
> -- inspected table shps.towns_poly using pgAdmin and confirmed geometry
> column now reads postgis.geometry
> -- ran query
>
> -- then disconnect from database and reopen.
> -- this works fine
> SELECT ST_Transform(geom, 4269) from shps.towns_poly limit 10;
>
>
> -- now if I remove postgis from search_path by either removing it from my
> database search_path and reconnecting.
> -- or doing
>
> set search_path=public;
>
> -- returns true -- all nulls - aka bad
> SELECT postgis.ST_Transform(geom, 4269) is null from shps.towns_poly limit
> 10;
>
> -- this one is a bit weird in that it still requires me to disconnect and
> reconnect before trying, as there still seems to be a shared read hook on
> table if I try to reuse the same connection.  Without disconnecting I
> sometimes get invalid transform (all nulls back) and a crash in one case.
>
> set search_path=public,postgis;
>
> -- returns false -- no nulls aka good
> SELECT postgis.ST_Transform(geom, 4269) is null from shps.towns_poly limit
> 10;
>
> Anyway I am able to repeat the above to switch postgis back to public and
> all seems fine as far as I can tell.
>
>
>
>
>
> -----Original Message-----
> From: Regina Obe [mailto:lr at pcorp.us]
> Sent: Wednesday, November 08, 2017 7:52 PM
> To: 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org>
> Subject: RE: [postgis-users] Importing relations from database where
> postgis extension was created with a different schema
>
> You can move it back I've done that before.
>
> Though I'm puzzled how this broke ogr_fdw.  I do recall some code in
> ogr_fdw that looks for where postgis is installed.
>
> It looks for it in search_path
>
>
>
> https://github.com/pramsey/pgsql-ogr-fdw/blob/07238c75d8bee08acb14619c413495a6965405d6/ogr_fdw.c#L187
>
> Are you sure you have the schema that postgis resides in in your database
> search_path?
>
>
> The error might be a temporary one caused by open connections during the
> move.  The issue should resolve if you kill all connections or restart the
> database service.
>
> I'll try to replicate the issue on my end.  When did you get the proj
> error below?  Was it during querying a foreign table or a physical one?
>
>
>
>
> Thanks,
> Regina
>
>
>
>
> -----Original Message-----
> From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On
> Behalf Of Andrew Joseph
> Sent: Wednesday, November 08, 2017 2:12 PM
> To: postgis-users at lists.osgeo.org
> Subject: Re: [postgis-users] Importing relations from database where
> postgis extension was created with a different schema
>
> I just learned that the hard way: all my ogr_fdw foreign tables broke
> after the relocation. I attempted moving the extension back to the public
> schema
> using:
>
> UPDATE pg_extension
>   SET extrelocatable = TRUE
>     WHERE extname = 'postgis';
>
> ALTER EXTENSION postgis
>   SET SCHEMA public;
>
> ALTER EXTENSION postgis UPDATE TO "2.4.0devnext";
>
> ALTER EXTENSION postgis UPDATE TO "2.4.0dev";
>
> UPDATE pg_extension
>   SET extrelocatable = FALSE
>     WHERE extname = 'postgis';
>
> However, it appears this doesn't actually move the relations back to the
> proper schema, and also seems to have corrupted proj4 in a way that
> persists across databases:
>
> [2017-11-08 13:04:00] [XX000] ERROR: AddToPROJ4SRSCache: could not parse
> proj4 string '+proj=lcc +lat_1=31.88333333333333 +lat_2=30.11666666666667
> +lat_0=29.66666666666667 +lon_0=-100.3333333333333
> ++x_0=699999.9998983998
> +y_0=3000000 +datum=NAD83 +units=us-ft +no_defs ' unknown elliptical
> parameter name
>
> Is it safe to assume moving the schema using the methods you outlined is a
> one-time only operation (i.e you can't move it back after importing the
> relevant data)?
>
>
>
>
> --
> Sent from: http://postgis.17.x6.nabble.com/PostGIS-User-f3516033.html
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20171109/7e95fe65/attachment.html>