pg_restore with -C option does not create the database

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


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.


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