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?
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?