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

Regina Obe lr at pcorp.us
Wed Nov 8 16:51:51 PST 2017

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 


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?


-----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

UPDATE pg_extension
  SET extrelocatable = TRUE
    WHERE extname = '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 
+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

More information about the postgis-users mailing list