Yesterday, I created backups of my various PostgreSQL databases using the following command:

pg_dump -Fc -d junk -h localhost -p 5434 -U postgres -W > z:\pg_dump\96_junk.dump

I created an individual backup for each database.

Today, I'm trying to restore them to an upgraded database server using the following command:

pg_restore -h localhost -p 5434 -U postgres -W -C -v z:\pg_dump\96_junk.dump

When I run pg_restore using the above it seems that the database was restored. The console screen displays a series of SQL statements and other messages I would expect to see for this database and, at the end, "PostreSQL database dump complete" is the output shown.

When I check the database, the database is not present. What am I doing wrong?

Employed versions info

  • Backups created with version 9.6.0

  • Attempting to restore using version 9.6.1

  • Try to check your schema name between dump and restore.
    – Luan Huynh
    Dec 8 '16 at 2:24
  • Everything is in the public schema. Dec 8 '16 at 3:39
  • 1
    Okay, it sounds good. Please show your restore log.
    – Luan Huynh
    Dec 8 '16 at 4:09
  • How do I create a restore log? I'm running the above command from a command prompt in the \bin folder of the install and the output is sent to the screen. Dec 8 '16 at 16:04
  • You can try pg_restore -h -p.... >> restore.log
    – Luan Huynh
    Dec 9 '16 at 2:04

Try specifying the name of the DB to start in, using the -d or --dbname argument. This worked for me when I had the same problem. Note that it doesn't have to be the target DB:

pg_restore -h localhost -p 5434 -U postgres -d postgres -W -C -v z:\pg_dump\96_junk.dump

Like Jen R-S said, it would work when using the "-d" option.

The output that was displaying in the console when not using the "-d" is the expected behaviour; in the documentation says:

pg_restore can operate in two modes. If a database name is specified, pg_restore connects to that database and restores archive contents directly into the database. Otherwise, a script containing the SQL commands necessary to rebuild the database is created and written to a file or standard output. This script output is equivalent to the plain text output format of pg_dump.

When no database is specified, pg_restore generates all commands, you could then redirect the output to a file and then excecute the file directly.

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.