I am trying to run following command:
sshpass -p "pass" ssh email@example.com "pg_dump -Fc -U foo some_db" | pg_restore --create --dbname=new_db
failed: FATAL: database "new_db" does not exist
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.
In short, you want either (clean existing): (note the database name is
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.
To be a bit more explicit, this is what I did that solved the problem for me:
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.
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.