PostgreSQL – What Happens with pg_restore Without -d Switch?

postgresql

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;

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
Related Topic