Stop pg_restore from returning false errors?

Question

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?

Answer 1

That is not a meaningless error: you used --create to tell pg_restore to create the database, but it is already present. This does not make a lot of sense, since with --dbname=mydb you tell pg_restore to connect to mydb to issue that CREATE DATABASE statement.

So if the database doesn't exist, you get an error because you cannot connect to it, and if it exists, you get an error because you cannot create it.

You should simply omit the --create option, then you are rid of the error.

This is all not new in PostgreSQL v12, so the error would occur with older versions of PostgreSQL as well. Also, the public schema has always been created automatically with CREATE DATABASE.