pg_restore with -C option does not create the database
Solution 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:
- connect to postgres database
- Create test database
- Disconnect from postgres and connect to test
- Upload data into database
Of course check who is owner of postgres database - in most cases you have to run this as user 'postgres'.
Solution 2
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.
Solution 3
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
Related videos on Youtube
Comments
-
Swapnil17 almost 2 years
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:
- 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
Deleted the test database
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.
-
Swapnil17 over 7 yearsdoes 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?
-
a_horse_with_no_name over 7 years@Swapnil17: you always have an empty database:
template1
-
adamczi over 6 yearsThere is a typo in this command a someone may not notice:
postgres-v
should bepostgres -v
. I cannot edit less than 6 chars in a post. -
Andre Leon Rangel about 4 yearsit is part of the pg installation. Depends on you OS and PG version. I was using ubuntu 18.04 and PG9
-
Ethan T over 3 yearsWow, I've been using these tools far too long to learn this only now. What a weird way of working!