0

I have database1 where postgis is installed in the public schema. And database2 where postgis is located in a schema called postgis.

When I dump database1.schema1, schema1.table1 references its geom column as public.geometry.

Therefore pg_restore throws "schema public does not exists" error because schema1.table1 has public.geometry column and the schema does not exists nor is postgis installed in that schema.

How can I make a clean pg_dump without having schema qualified geometry columns?

The database2 search_path points to the schema postgis. I am using PostgresSQL 10.

0

You cannot do that. Install PostGIS in the same schema in both databases. That shouldn't be a problem in the new database.

The alternative would be to manually edit the dump file, which is tedious and error prone.

6
  • Ok thanks. Just to be sure, can I move postgis to public in the new database, restore the data then move postgis back where it was? I'm unsure whether moving postgis around in a database is actually safe even though the how-to is on postgis.net
    – Boodoo
    Nov 14 '19 at 13:51
  • If postgis.control contains relocatable = true, you can move it to another schema using ALTER EXTENSION without any problem. Nov 14 '19 at 16:13
  • I don't have acess to the server itself. "update pg_extension set extrelocatable = True where extname = 'postgis'" does the same?
    – Boodoo
    Nov 18 '19 at 9:29
  • Sorry, I just checked, and PostGIS is not relocatable. So the simple solution is not possible. Nov 18 '19 at 9:43
  • 1
    On postgis.net they explain how to relocate it. Doesn't it work? postgis.net/2017/11/07/tip-move-postgis-schema
    – Boodoo
    Nov 18 '19 at 13:15
0

As stated By Laurenz, you must install PostGIS in the same schema in both databases.

Afterward, it is possible to migrate postgis extensions doing the following : https://www.postgis.net/2017/11/07/tip-move-postgis-schema/

The migration can be done in an intermediate DB if need be.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.