1

This is a specific scenario where we are restoring the custom dump extracted from Postgres 10 to Postgres 14. We are getting the below error:

ERROR: Schema "public" already exists

I understand that ERROR can be ignored and all subsequent objects are restored successfully. However since we are calling pg_Restore from a JAVA program, the error is returning an exit code other than 0 and exiting the program. Of course, we were able to tweak the JAVA code so that by parsing this error message we could hide the exit code. But we still think this is not an ideal solution. Is there any other PG way we can avoid this error?

Options tried:

  • Drop and recreate DB before restoring.

  • Using "-n public" actually avoids the error. But this will ignore the large objects while restoring.

  • Solution proposed here-answer

No Luck yet ! This error or warning appears only when restoring an older version dump.

Why aren't we fixing the pg_restore source code to use "if no exists" in create schema statement in the first place?

5
  • 1
    Can't you just run pg_restore with the --clean option?
    – user1822
    Sep 2, 2022 at 6:09
  • That doesn't happen usually. Did you modify the schema in the original database? Are you running the latest minor release on v10? Sep 2, 2022 at 6:39
  • @LaurenzAlbe , Postgres 10 version is 10.17. Original database was created, initiated and used by us as time passed. We did not specifically do any modifications on schema. Database creation, created default schema public, and we added objects and data onto it.. Sep 2, 2022 at 6:58
  • @a_horse_with_no_name , No, clean did not work. We got other issues like foreign key violations. Thanks. Sep 2, 2022 at 6:58
  • 1

1 Answer 1

3

Was changed in postgresql 11 by this commit.

This has the visible effect that the public schema won't be mentioned in the output at all, except for updating its ACL if it has a non-default ACL.

Try dumping the pg10 database using pg_dump from the pg14 binaries. pg_dump can dump older PG releases.

2
  • Hi @Melkij, Thanks!. Can you clarify below please: So since it is fixed in PG11 source, can we also use pg11 binaries and the issue would be resolved? Sep 2, 2022 at 7:31
  • Yes, it should work fine with pg_dump from pg11.
    – Melkij
    Sep 2, 2022 at 7:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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