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:[email protected]] On Behalf Of Andrew Joseph Sent: Tuesday, November 07, 2017 3:45 PM To: [email protected] 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 [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
