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

[Regina Obe]

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