pg_restore with -C option does not create the database

Question

I am using pg_dump and pg_restore for backup and restore of postgres database.

Here is some information from documentation that will be relevant for this question For Pg_restore, -C option is described as follows

-C

--create

Create the database before restoring into it. If --clean is also specified, > > drop and recreate the target database before connecting to it. 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.

However even when I use this option with pg_restore, I get following error

pg_restore: [archiver (db)] connection to database "test" failed: FATAL: > database "test" does not exist

As per the description the -C option should have created the missing database. However it does not in my case.

Following are the steps that I did for backup and restore:

1. Use pg_dump to backup database

pg_dump -N backup -d test --format custom -v -h xxhostxx -p 5432 -U xxuserxx --lock-wait-timeout 300000 -f test_pg_dump.dmp

Note: not using -C option since it is meaningful for the plain-text formats only

2. Deleted the test database

3. use pg_resore to restore database

   pg_restore -C -d test -v -h xxhostxx -p 5432 -U xxuserxx test_pg_dump.dmp**
   

I cannot understand what is the issue here! Am I doing anything wrong ? Let me know if more information is needed.

Sheryl

Answer 1

Exactly like @Eelke said - you've got in file wrote 'create database' so this database does not exist when you're running script... That's what for there is always 'postgres' database. Try this:

pg_restore -C -d postgres -v -h xxhostxx -p 5432 -U xxuserxx test_pg_dump.dmp**

And this should:

1. connect to postgres database
2. Create test database
3. Disconnect from postgres and connect to test
4. Upload data into database

Of course check who is owner of postgres database - in most cases you have to run this as user 'postgres'.

Corliss

Answer 2

It never worked for me so this command creates the database

createdb -h HOST -U USER -W DB_NAME

then execute the pg restore

pg_restore -d DB_NAME -v -h HOST -p PORT -U USER DUMP_FILE.dump**

End of story

Snowden

it is part of the pg installation. Depends on you OS and PG version. I was using ubuntu 18.04 and PG9 – Snowden

Answer 3

The following quote doesn't mean what you might think it means. I also had to read it thrice before realizing what they were saying.

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.

It means that pg_restore will initially connect to the database specified with -d. It will NOT create that database. It creates a database with the name from the archive you are restoring and restores the data into that database.

Yare

does this mean you cannot restore a database if it does not exist? To restore you need to issue a command to create an empty database first and then restore? – Sheryl

@Swapnil17: you always have an empty database: template1 – Graminivorous

Wow, I've been using these tools far too long to learn this only now. What a weird way of working! – Precipitancy

Answer 4

From man pg_restore EXAMPLES section

Assume we have dumped a database called mydb into a custom-format dump file:

   $ pg_dump -Fc mydb > db.dump

To drop the database and recreate it from the dump:

   $ dropdb mydb
   $ pg_restore -C -d postgres db.dump

The database named in the -d switch can be any database existing in the cluster; pg_restore only uses it to issue the CREATE DATABASE command for mydb.
With -C, data is always restored into the database name that appears in the dump file.

To reload the dump into a new database called newdb:

   $ createdb -T template0 newdb
   $ pg_restore -d newdb db.dump

Notice we don't use -C, and instead connect directly to the database to be restored into. Also note that we clone the new database from template0 not
template1, to ensure it is initially empty.

Thus in your case:

$ createdb -h xxhostxx -p 5432 -U xxuserxx -T template0 test
$ pg_restore -h xxhostxx -p 5432 -U xxuserxx -d test db.dump

Masoretic

in the same section there is a mention to pg_restore -l db.dump > db.lis that will show you the name of the orginal db backuped – Masoretic