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.

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'.