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

[Andrew Joseph]

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