Home / server / Questions / 986577
Accepted
JL Peyret
Asked: 2019-10-03 16:11:45 +0800 CST

pg_restore without -d switch: what does it do?

  • 772

versions: postgresql 9.6, macos 10.14

As the postgres user, I ran a pg_restore command:

pg_restore -C backup.curated.20190901.0.bup

i.e. create the database as per what's in the dump file.

This seems to work fine, it flashed a bunch of sql commands on the screen.

But... no database created.

In order to actually get it created I had to change the command to add a -d to specify a database.

pg_restore -d postgres -C backup.curated.20190901.0.bup

Same behavior, except the database is restored this time.

From the pg_restore doc

-C --create

Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before connecting to it.

When this option is used, the database named with -d is used only to issue the initial DROP DATABASE and CREATE DATABASE commands. All data is restored into the database name that appears in the archive.

Basically, I am curious why the absence of the -d switch isn't complained about, if it's necessary for db restoration to happen.

What does the pg_restore do here? A dry-run preview?

In case it's relevant, the dump command I used was:

sudo -u postgres $pgbin/pg_dump -o -Fc $dbname > $dirbackup/$dbname.bup;

postgresql

2 Answers

  1. Best Answer

    Basically, I am curious why the absence of the -d switch isn't complained about, if it's necessary for db restoration to happen.

    What does the pg_restore do here? A dry-run preview?

    It outputs a script without presuming what you want to do with it. Previewing, certainly, but also piping into psql, or to ssh to play it on a remote instance that you cannot reach directly, or just saving to a file to edit it or transfer it somewhere else.

    In the newest version of PostgreSQL (12.0 released Oct 3, 2019) it has been estimated that too many people were surprised as you are by pg_restore going to the standard output by default, so starting from that release we now need to specify a -f - argument to get that behavior. The first invocation you mentioned now fails with:

    pg_restore: error: one of -d/--dbname and -f/--file must be specified
    • 2

  2. The flags needed with pg_restore depend on both:

    • the current state of the database/server before you start a restore
    • on the flags used to create the backup of a single database with gd_dump
    • on the flags used to create a complete server backup with pg_dumpall

    and that last one is the reason pg_restore -C does not require have a mandatory (single) -d <database name> dependency, the backup file can contain the backups more than one database and when that is the case then the names of those databases will included in the backup file.

    • 1