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
