Postgresql – pg_restore ERROR: “Relation does not exist” and creating new database

existspg-dumppg-restorepostgresqlpsql

I have made a backup of my specific tables that I want to restore into a new database using:

call pg_dump -Fc -h server -d database -U user -p password -v -f dump.sql -t public.table1 -t public.table2

And I have no problems.

I then want to restore the dump by creating a new database with pg_restore using:

call pg_restore --clean --create -d temp -h server -p password -U user dump.sql

This gives me a "database temp does not exist" error. This should have created the database and restored it as far as I understand.

I However then create the "temp" database manually and run:

call pg_restore --clean --create -d temp -h server -p password -U user dump.sql

This follows through, but does not create the tables and gives me an error "relation table1" and "relation table2" does not exist and only creates the corresponding id_sewuences for the two tables.

What I actually want is to not have to manually create the new database and that all tables in the backup is restored into a brand new database via pg_restore using:

call pg_restore --clean --create -d temp -h server -p password -U user dump.sql

as I understand it.

Please Help, very frustrating

Best Answer

When --create and -d are used together, the argument to -d is not the name of the database to create, it's the name of an existing database to connect to run the CREATE DATABASE statement, because it's impossible to create a database if you're not already connect to another database.

This is documented as:

When this option is used, the database named with -d is used only to issue the initial DROP DATABASE and CREATE DATABASE commands. All data is restored into the database name that appears in the archive.

This is why pg_restore errors out with database temp does not exist

You should add a createdb step in your restore procedure since you want to create a specific database name anyway, not one that comes from the backup.

For the second problem, it's a completely different question, hard to guess why it happens without complete error messages.

Also note that -p is not followed by a password but by a port number.

Related Solutions

Postgresql – use pg_restore to restore data to subset of tables

From pg_restore's documentation you can see:

-t table --table=table

Restore definition and/or data of named table only.
Multiple tables may be specified with multiple -t switches.
This can be combined with the -n option to specify a schema.

Highlighting "Multiple tables may be specified with multiple -t switches", which means that you can use -t switch multiple times, and your example would work as:

pg_restore -a -d ACMETEST -n public -v -U acme -t hardware -t location /var/acme/backups/restore/ACMEDatabase.bak

As seen, I just used -t hardware -t location instead of -t hardware,location.

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 – use pg_restore to restore data to subset of tables

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

Related Question