I'm a bit unclear on how to use the pg_dump/pg_restore duo to move a large-ish database.

Here is the command I use to dump the database (the host and username are taken from environmental variables):

pg_dump --dbname=mydb --format=custom > mydb.dump

I use the custom format because I do this over the network and want to use compression.

When I now try to restore it, I tried this:

pg_restore --dbname=mydb --no-owner mydb.dump

But I get:

pg_restore: [archiver (db)] connection to database "mydb" failed: FATAL:  database "mydb" does not exist

I tried next with the --create option, but that yields the same error message.

Finally I tried passing template1 as a --dbname option:

pg_restore --dbname=template1 --no-owner --create mydb.dump

But here is what I get:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2125; 1262 16395 DATABASE mydb mydbuser
pg_restore: [archiver (db)] could not execute query: ERROR:  invalid locale name: "en_US.utf8"
    Command was: CREATE DATABASE mydb WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8';

pg_restore: [archiver (db)] could not reconnect to database: FATAL:  database "mydb" does not exist

What am I doing wrong here?


When you do

pg_restore --dbname=mydb --no-owner mydb.dump

You are trying to connect to the mydb database which still does not exist. Connect to an existent one and use the --create parameter:

pg_restore --dbname=existentdb --no-owner --create mydb.dump


| improve this answer | |
  • I believe this is what I do, when I connect to the template1 database, is it not? Yet I get this error message mentioned in my question. – lindelof Dec 17 '14 at 13:10
  • @lindelof That happens because the mydb database could not be created while connected to template1. Fix the invalid locale name problem first. – Clodoaldo Dec 17 '14 at 13:17

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.