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?

Improve this question
6
  • 1
    Can't you just run pg_restore with the --clean option? Commented 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? Commented 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.. Commented 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. Commented Sep 2, 2022 at 6:58
  • 1
    Does this answer your question? pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists Commented Dec 7, 2023 at 14:11

1 Answer 1

Reset to default
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.

Improve this answer
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? Commented Sep 2, 2022 at 7:31
  • Yes, it should work fine with pg_dump from pg11. Commented Sep 2, 2022 at 7:40

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.