1

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 - Team Monica Dec 15 '15 at 5:02
  • No errors, it just quietly puts it all in the postgres database. – Rob N Dec 15 '15 at 5:24
0

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

0

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, privacy policy and cookie policy

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