How do you stop pg_restore from returning an error code if it encounters a meaningless non-error like “schema "public" already exists”?
I'm trying to automate a database transfer, and after I upgrading PostgreSQL to 12, pg_restore
throws an error condition for things that aren't actually errors.
For example, my database setup script is basically:
sudo psql --user=postgres --no-password --host=localhost --command="DROP DATABASE IF EXISTS mydb;"
sudo psql --user=postgres --no-password --host=localhost --command="CREATE DATABASE mydb;"
sudo psql --user=postgres --no-password --host=localhost --dbname mydb --command="DROP USER IF EXISTS myuser; CREATE USER myuser WITH PASSWORD 'supersecretpassword';"
sudo psql --user=postgres --no-password --host=localhost --dbname mydb --command="GRANT ALL PRIVILEGES ON DATABASE mydb to myuser;"
sudo pg_restore -U postgres --format=c --create --dbname=mydb /tmp/mydb_snapshot.sql.gz
However, even though the last line logically succeeds in loading the database snapshot, it returns an error code, reporting that:
pg_restore: error: could not execute query: ERROR: database "mydb" already exists
It seems like a change in PostgreSQL v12 is that creation of a new database also now populates it with a default public schema, which conflicts with the public schema in any snapshot. I never ran into this issue in v11 or v10.
I could modify my script to ignore stderr, but then I'd be blind to actual problems.
How do I tell pg_restore to ignore this error, but still report other errors?