40

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.

Improve this question

4 Answers 4

Reset to default
28

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

The -C flag does not work.
@RichardBarraclough pg_restore -C -d postgres ... (note the database name is postgres) may look scary, but it does nothing to the postgres database, it only uses it for the initial connection.
Is there a psql equivalent of this command? I get the "input file appears to be a text format dump. Please use psql." error when I use pg_restore.
22

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

Improve this answer

1 Comment

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

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.

Improve this answer

3 Comments

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?
@Swapnil17: you always have an empty database: template1
Wow, I've been using these tools far too long to learn this only now. What a weird way of working!
3

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
Improve this answer

1 Comment

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.