3

Why pg_restore is unable to create database with --create option before restoring from dump?

pg_restore --username=myuser --create --dbname=mydb --verbose  mybackup

Error,

pg_restore: [archiver (db)] connection to database "mydb" failed: FATAL:  database "mydb" does not exist
2
  • ,As per your error log it's clearly say that your 'mydb' does not exist. Sep 10, 2017 at 5:46
  • did you figure this out?
    – Arya
    Oct 28, 2017 at 1:01

2 Answers 2

2

In order to create a database, it needs to be able to connect to your database server in order to issue the "CREATE DATABASE" command. Connecting to the server requires you to specify an existing database to connect under, and obviously that cannot be the database which fails to exist and which is to be created. There are special databases usually reserved for maintenance operations, usually postgres, which you can connect to for this purpose.

When using --create mode, --dbname specifies not the name of the database to be restored into, but rather the name of the database to be used to create the other database.

3
  • then how do you specify the name of the database to be created?
    – NateW
    Mar 3, 2018 at 0:08
  • With the --create option, the name of the database to be created comes from the dump file itself. It is the same as the name of the database that was dumped.
    – jjanes
    Mar 3, 2018 at 0:27
  • Ahh I see. Is there a way to make the newly created database have another name with using --create?
    – NateW
    Mar 3, 2018 at 2:20
0

Without -C it works as expected. For example, if I wanted to make a exampleDB_dev based on exampleDB, I would do the following:

pg_dump -Fc exampleDB > exampleDB_temp.dump
dropdb exampleDB_dev
<created empty exampleDB_dev database>
pg_restore -d exampleDB_dev exampleDB_temp.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.