3

I am trying to restore a binary pg_dump backup under a different database name that was in the original dump. Despite giving --dbname command line argument, pg_restore tries to restore everything under the original database name - the behavior I especially want to avoid.

createdb mydb

pg_restore --dbname=mydb --exit-on-error -Fc database.bak

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8; 2615 16396 SCHEMA topology u9o2f4j0
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "topology" already exists
    Command was: CREATE SCHEMA topology;

How can I make pg_restore to restore the database under a different name?

pg_dump --version
pg_dump (PostgreSQL) 9.5.5

pg_restore --version
pg_restore (PostgreSQL) 9.6.1
  • Edited the question, leaving out --create doesn't change anything – Mikko Ohtamaa Jan 25 '17 at 9:01
  • 1
    sure? "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." is for -C so it looked like your case – Vao Tsun Jan 25 '17 at 9:03
  • Your example code (without the --create) works for me. Are you sure that your createdb mydb succeeded, rather than failing because mydb already existed? – jjanes Jan 29 '17 at 21:02
3

There are few command line options you need to consider.

  pg_restore -x --no-owner --dbname=mynewdb --exit-on-error -Fc database.bak
  • -Fc: restore from a binary back up as its more flexible than plain text SQL dump

  • --dbname: restore under this database name (must be created beforehand using createdb)

  • -x: do not try to restore access priviledges (e.g. the superuser name may change between databases)

  • --no-owner: the database owner user is different on the system where restore is performed

  • --exit-on-error: If bad stuff happens, abort and don't flood your terminal with useless errors

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.