29

I am trying to run following command:

sshpass  -p "pass" ssh  x@1.2.3.4 "pg_dump -Fc -U foo some_db" | pg_restore --create --dbname=new_db

I get:

failed: FATAL:  database "new_db" does not exist
31

This is because it is the way pg_restore works.

pg_restore manual reads as follow :

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

The -d will restore in the given database if and only if -C is not used. If -C is used, the database is used as a "launchpad", not as the destination.

4
  • 13
    To clarify: there's no way to create an arbitrary database name and restore into it with pg_restore. The -C option can only create a database whose name matches the database name in the dump file. To restore to an arbitrary database, you have to run CREATE DATABASE new_db; in psql before you run pg_restore --dbname=new_db.
    – Luke
    Jun 20 '17 at 0:56
  • my command docker-compose -p dump_import -f docker/dump_import.yml run --rm be bash -c 'waitforit -host=postgres -port=5432 -timeout=30 && export PGPASSWORD=$DATABASE_PASSWORD && createdb --echo --port=$DATABASE_PORT -host=$DATABASE_HOST --username=$DATABASE_USER $DATABASE_NAME && pg_restore --format=c --dbname=$DATABASE_URL_WITHOUT_QUERY tmp/full.dump && echo "Import done"'
    – srghma
    May 1 '18 at 8:56
  • 13
    Why do tools have to be confusing when they could be easy? May 16 '19 at 15:24
  • Please note: though using -C will solve this particular issue, if you are using postgres < 12 you will start seeing this error stackoverflow.com/questions/58970102/…
    – Vishrant
    Nov 29 '21 at 20:07
12

In short, you want either (clean existing): (note the database name is postgres)

pg_restore -c -d postgres db.dump

or (create new)

pg_restore -C -d postgres db.dump

or (create new explicitly)

createdatabase the_database
pg_restore -d the_database db.dump

See what SCO said for more details.

3
  • Your first suggestion didn't work on 9.1: pg_restore: [archiver] -C and -c are incompatible options
    – peetasan
    Jan 18 '17 at 15:49
  • 2
    I can confirm that this works (after the edit, and after fixing the misspelling of "postgres"). pg_restore -C -d postgres db.dump may look scary, but it does nothing to the postgres database, it only uses it for the initial connection.
    – Zilk
    Mar 16 '19 at 14:37
  • I was so mush confused about that. This is ugly. createdb uses --maintenance-db to specify the db used just to issue the 'target' db, which I find much more readable. From what I understand, by just removing the --clean or --create options, your maintenance db suddenly becomes the target db which could cause loss of data when it's overwritten.
    – liberforce
    Sep 2 '21 at 16:46
4

Let me reword @Izap's answer, because I had to read all answers over and over to figure things out...

Tools needs in some cases a database to connect to, in order to perform maintenance operations. pg_restore needs one for example when asked to create the database we will restore data to. The database we connect to in order to perform operations on other databases is the maintenance database. The database we restore the data into is the target database.

So the problem I see with pg_restore is that it uses the same semantics (same option parameter) for 2 completely different things.

-d/--dbname is understood as the maintenance database when --create is used. However when it's not used, --dbname suddenly becomes the target database.

Let's define these in our shell for clarity:

MAINTENANCE_DB=postgres
TARGET_DB=the_database

So, you have:

# Clean the target database, then restore into it
pg_restore --clean --dbname $TARGET_DB db.dump
  • or
# Create the database (which name is extracted from the dump)
pg_restore --create --dbname $MAINTENANCE_DB db.dump
  • or
# Create explicitly the database first
createdb $TARGET_DB
# Restore into that freshly created database (ignoring the name in the dump)
pg_restore --dbname $TARGET_DB db.dump

This means that by just removing the --create option, one could inadvertently make pg_restore use the maintenance database as the target database.

I wish they had made the choice of using --maintenance-db like in createdb, always using --dbname for the target.

0
1

To be a bit more explicit, this is what I did that solved the problem for me:

  1. Create an empty database with the name you want: (in my case the username was 'postgres')

    psql -U [username]
    

It will then prompt you for your password. At this point you will be logged in as [username]. Type the following:

    CREATE DATABASE [dbname];

Now exit the session and go back to your regular terminal session.

  1. Restore the database from the file you have setting the target database name as that database name you just created.

    cat [your_file_path/filename] | psql -U [username] [dbname]
    

Where [your_file_path/filename] is the location of the db file or text file you want to restore.

1
  • When creating the backup using de -F option you have to use pg_restore to restore the backup unless you use -F p which produces a plain text file with sql sentences.
    – EAmez
    Sep 9 '19 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.