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

[Regina Obe]

FWIW I tend to standardize on putting postgis in schema called postgis.  Not sure where you got the idea that public is the preferred location.



You'll need to move postgis back into postgis schema to restore your data.
You can then move to whatever schema you want by repeating the below steps with a different schema.

Here is how you do it.

First change meta catalog to allow you to move the postgis extension

UPDATE pg_extension SET extrelocatable = true where extname = 'postgis';


Next move extension back to postgis

ALTER EXTENSION postgis SET SCHEMA postgis;


Next fix up the functions, they are broken at this point without doing the below.

ALTER EXTENSION postgis  UPDATE TO "2.4.1next";
ALTER EXTENSION postgis  UPDATE TO "2.4.1";


Now run pg_restore  

Hope that helps,
Regina


-----Original Message-----
From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Andrew Joseph
Sent: Tuesday, November 07, 2017 3:45 PM
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] Importing relations from database where postgis extension was created with a different schema

 I have the following databases:

db1 - postgres 9.4 postgis 2.2 with postgis installed in a schema named "postgis"
db2 - postgres 10 postgis 2.4 with postgis installed in public schema

I am attempting to copy tables from myschema in db1 into db2 via pg_dump:

sudo -u postgres pg_dump -n myschema db1 | psql --username=postgres
--host=172.18.0.3 --dbname=db2

predictably this yields the following error:

ERROR:  type "postgis.geometry" does not exist
LINE 8:     geom postgis.geometry(MultiPolygon,4326)

This error results in the relevant tables not being created and corrupts the entire import process. Since the postgis extension location now cannot be altered, how do I import the data without issue? (I switched from using a specific postgis schema to using public in db2 -since this seems to be
preferred) 



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