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 <[email protected]>: > 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:[email protected]] > Sent: Wednesday, November 08, 2017 7:52 PM > To: 'PostGIS Users Discussion' <[email protected]> > 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:[email protected]] On > Behalf Of Andrew Joseph > Sent: Wednesday, November 08, 2017 2:12 PM > To: [email protected] > 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 > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users > > _______________________________________________ > postgis-users mailing list > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
