I backed up a database called app_data, on a 9.3 server, with this basic command:

pg_dump -n public -F custom app_data > app_data.pg.dump

Then I try to restore it on another server (running 9.4) like this:

pg_restore -C -d postgres app_data.pg.dump 

But it puts all the tables in the postgres database. The man page says it will create and use a new database, app_data.

-C --create

Create the database before restoring into 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.

That's not what it's doing. The name in the archive is app_data:

bash-4.2$ pg_restore -l app_data.pg.dump 
; Archive created at Tue Dec 15 04:16:52 2015
;     dbname: app_data

Am I doing something wrong?

  • Do you get errors?
    – Tom V
    Commented Dec 15, 2015 at 5:02
  • No errors, it just quietly puts it all in the postgres database.
    – Rob N
    Commented Dec 15, 2015 at 5:24

2 Answers 2


Yes that behaviour is something that I should report to postgres as use of different flags doesnt seem to have an effect and it just doesnt work. To solve it use:

  • create DB

createdb -h HOST -U USER -W DB_NAME

  • restore and in case of Error Stop

egrep -v 'EXTENSION.*plpgsql' DUMPFILE.sql | psql -h HOST -U USER -W -d DB_NAME -v ON_ERROR_STOP=1


pg_restore -C -d postgres app_data.pg.dump

But it puts all the tables in the postgres database

Isn't that exactly what you asked it to do with the "-d postgres" argument?

If you want to load a different database, you need to tell pg_restore so:

pg_restore -d app_data -C app_data.pg.dump

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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