82

I have a Postgres 8.4 database on a linux server that I have dumped using the following command:

pg_dump --format=c --exclude-table=log --file=/path/to/output my_db

I then ftp the created file to my local Windows 7 machine and attempt to restore the file to my local Postgres 8.4 instance using the following command:

pg_restore --create --exit-on-error --verbose c:\path\to\file

The restore command generates plenty of output claiming it created my database, connected to it, and then created all the other tables as expected. However, when I view the databases on my local machine through pgAdmin the restored database doesn't exist at all.

In an attempt to troubleshoot I tried the following command:

pg_restore --create --exit-on-error --verbose --host=blahblah --username=no_one c:\path\to\file

When I run this command even though the host and username given are complete nonsense I still get the exact same output from the command without any errors.

Has anyone run into this before or know what could by causing this?

Mike Deck
  • 18,045
  • 16
  • 68
  • 92

2 Answers2

137

You have to add the name of a valid database to initially connect to or it will just dump the contents to STDOUT:

pg_restore --create --exit-on-error --verbose --dbname=postgres <backup_file>
Matthew Wood
  • 16,017
  • 5
  • 46
  • 35
  • 75
    Why isn't it mentioned big fat red in the pg_restore documentation?! – Yo Ludke Aug 04 '14 at 09:03
  • 11
    6 years later, I run across the same problem, and finally stumbled across this answer which fixed it right up. I cannot believe the documentation is still not fixed or there's nothing in the command output to let the user know that it's not actually doing anything. Serious usability issues for new people coming in trying pickup PostgreSQL. – James Jan 21 '17 at 22:25
  • 2
    Tell me about it. Two hours (at least) down the drain. To be fair: it says so in the second paragraph of the man page. – jnns Feb 24 '17 at 16:08
  • 6
    @YoLudke because the posgres documentation is the worst documentation in the world – Marco Prins Apr 18 '17 at 10:51
  • 3
    ok i thought it was only me who finds the docs incomprehensible – user798719 Mar 05 '18 at 17:37
  • I think that `pg_restore --create --exit-on-error --verbose | psql -d ` also yields an equivalent result. – cjauvin Mar 17 '18 at 17:52
  • Somewhere along the way, `pg_restore` told me to not include the `--dbname` flag if I was including some other flag, so then I spun wheels for an hour thinking it wasn't updating things, when I *do actually* need the `--dbname` flag. – Josh.F Mar 29 '21 at 17:56
  • Instead of whining about open source documentation (where you're literally living off of other people's effort), why not go contribute and update it? Or send a message of gratitude along with suggested updates. – LMSingh Sep 30 '22 at 18:16
11

This is still confusing, I attempted to execute this thing that the --dbname should be the db I want to create.

pg_restore --create --exit-on-error --verbose --dbname=jiradb jiradb.tar

WRONG!!

It should literally be --dbname=postgres, the --create then will create the real db from the name in the file. In my case, I restored from a tar backup with

pg_restore --create --exit-on-error --verbose --dbname=postgres jiradb.tar
icyerasor
  • 4,973
  • 1
  • 43
  • 52
tgunr
  • 1,540
  • 17
  • 31
  • 1
    I just ran this with `--dbname=my_specific_db`, not `=postgres`, and it worked... I wonder if something changed since your answer? – Josh.F Mar 29 '21 at 18:00
  • @Josh.F it depends if the my_specific_db already exists or not. – rooby Oct 08 '22 at 05:20