Postgresql – Stop pg_restore from returning false errors

pg-restorepostgresqlpostgresql-12

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?

Best Answer

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.

Related Solutions

Postgresql – Created user can access all databases in PostgreSQL without any grants

At the SQL level, every user can indeed connect to a newly created database, until the following SQL command is issued:

REVOKE connect ON DATABASE database_name FROM PUBLIC;

Once done, each user or role that should be able to connect has to be granted explicitly the connect privilege:

GRANT connect ON DATABASE database_name TO rolename;

Edit: In a multi-tenant scenario, more than just the connect privilege would be removed. For multi-tenancy tips and best practices, you may want to read on the postgresql public wiki: Shared Database Hosting and Managing rights in PostgreSQL.

Postgresql – pg_restore: [archiver (db)] could not execute query: ERROR: schema “public” already exists

The error is harmless but to get rid of it, I think you need to break this restore into two commands, as in:

dropdb -U postgres mydb && \
 pg_restore --create --dbname=postgres --username=postgres pg_backup.dump

The --clean option in pg_restore doesn't look like much but actually raises non-trivial problems.

For versions up to 9.1

The combination of --create and --clean in pg_restore options used to be an error in older PG versions (up to 9.1). There is indeed some contradiction between (quoting the 9.1 manpage):

--clean Clean (drop) database objects before recreating them

and

--create Create the database before restoring into it.

Because what's the point of cleaning inside a brand-new database?

Starting from version 9.2

The combination is now accepted and the doc says this (quoting the 9.3 manpage):

--clean Clean (drop) database objects before recreating them. (This might generate some harmless error messages, if any objects were not present in the destination database.)

--create Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before connecting to it.

Now having both together leads to this kind of sequence during your restore:

DROP DATABASE mydb;
...
CREATE DATABASE mydb WITH TEMPLATE = template0... [other options]
...
CREATE SCHEMA public;
...
CREATE TABLE...

There is no DROP for each individual object, only a DROP DATABASE at the beginning. If not using --create this would be the opposite.

Anyway this sequence raises the error of public schema already existing because creating mydb from template0 has imported it already (which is normal, it's the point of a template database).

I'm not sure why this case is not handled automatically by pg_restore. Maybe this would cause undesirable side-effects when an admin decides to customize template0 and/or change the purpose of public, even if we're not supposed to do that.

Best Answer

Related Solutions

Postgresql – Created user can access all databases in PostgreSQL without any grants

Postgresql – pg_restore: [archiver (db)] could not execute query: ERROR: schema “public” already exists

Related Question