CODEWITHSUNDEEP
postgresqlpg-dumppg-restore
Swapnil17
Swapnil17

Reputation: 796

pg_restore with -C option does not create the database

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

  1. Deleted the test database

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

Upvotes: 39

Views: 40723

Answers (4)

borracciaBlu
borracciaBlu

Reputation: 4225

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

Upvotes: 3

Andre Leon Rangel
Andre Leon Rangel

Reputation: 1799

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

Upvotes: 19

Jendrusk
Jendrusk

Reputation: 813

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

Upvotes: 28

Eelke
Eelke

Reputation: 21993

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.

Upvotes: 16

Related Questions