Why is pg_restore returning successfully but not actually restoring my database?

Question

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?

Nonjuror

Answer 1

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>

Addlepated

Why isn't it mentioned big fat red in the pg_restore documentation?! – Cata

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. – Dive

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. – Tonina

@YoLudke because the posgres documentation is the worst documentation in the world – Poaceous

ok i thought it was only me who finds the docs incomprehensible – Acrylonitrile

I think that pg_restore --create --exit-on-error --verbose <backup_file> | psql -d <dbname> also yields an equivalent result. – Rutherfordium

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. – Mol

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. – Kigali

Answer 2

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

Freddie

I just ran this with --dbname=my_specific_db, not =postgres, and it worked... I wonder if something changed since your answer? – Mol

@Mol it depends if the my_specific_db already exists or not. – Fleck

Answer 3

You can actually create the db you are trying to insert into using:

createdb new-db -h HOST -p PORT -U USER

But this is probably not going to solve the issue.

You have have to make sure that the database you are restoring to has the same name as the database you made the backup of (using pg_dump).

For me the name of the backup database was actually the default database name which is just postgres. Again, make sure that the name of the database (-d flag) is the same as the name of the database you made a backup of.

So this command worked for me:

pg_restore -vFd -d postgres backups/location -j 5 --create -h HOST -p PORT -U USERNAME

Rustice