PostgreSQL Restore – Why pg_restore Ignores –create Option

postgresqlpostgresql-9.3psql

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

Best Answer

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.

Related Question