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

Andrew Joseph ap.joseph at live.com
Tue Nov 7 12:44:55 PST 2017

 I have the following databases:

db1 - postgres 9.4 postgis 2.2 with postgis installed in a schema named
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= --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

Sent from: http://postgis.17.x6.nabble.com/PostGIS-User-f3516033.html

More information about the postgis-users mailing list