postgresqlpg-restore

pg_restore using improper database name

I want copy a 'local' postgres database, which living in docker, into remote machine. Local docker's databse have default name postgres, and remote have proddb. I run following command:

pg_dump --format=custom --dbname=postgresql://postgres:postgres@localhost:5432/postgres | PGPASSWORD=pa$$word pg_restore -v --clean --create --host=production.local --username=swasher --dbname=proddb

In the terminal, i see that this command creating postgres database instead proddb:

pg_restore: connecting to database for restore
pg_restore: dropping DATABASE postgres
pg_restore: creating DATABASE "postgres"
pg_restore: connecting to new database "postgres"
pg_restore: creating COMMENT "DATABASE postgres"
pg_restore: creating EXTENSION "hstore"
pg_restore: creating COMMENT "EXTENSION hstore"
pg_restore: terminated by user

Why pg_restore ignored specified dbname key?

Solution

  • If you use the --create option with pg_restore, the restore process will first execute

    CREATE DATABASE postgres;

    then connect to the new database with

    \c postgres

    and proceed to restore the dump.

    If you additionally add --clean, pg_restore will first execute

    DROP DATABASE postgres;

    So the database you connect to with pg_restore is irrelevant, because pg_restore will create and connect to database postgres anyway.

    If you want to restore the dump to a different database, omit the --create option, so that pg_restore connects to proddb and restores the dump to that database.