How to restore a postgres backup - ERROR: cannot drop the currently open database

Question

I'm trying to restore a postgres backup from a system that is no longer provisioned, and I'm getting a range of errors, depending on what I try.

• I have only the backups, no original servers to refer to
• I've successfully restored from these production backups in the past, but have messed with my local environment since last success (eg reinstalled postgres via Homebrew)
• I believe that a former colleague (no longer available) has successfully restored this particular backup
• I've encountered the same error when:
  • trying the backup using [email protected]
  • trying a previous known-good version of the database (using postgres 11)

(I've read related/suggested questions, and they don't seem relevant)

The backup was created on using the following command (in a bash script):

pg_dump --schema=public -Fc

And the command used to restore the script (which is echo'd as help text by backup script) is:

dropdb ${PGDATABASE}
createdb ${PGDATABASE}
time pg_restore \
    -j4 \
    -d ${PGDATABASE} \
    --create \
    --no-privileges \
    --no-owner \
    --clean \
    --if-exists \
    --exit-on-error \
    "${dirname}/${filename}"

When I run the restore command locally, I get the following:

time pg_restore -j4 -d ${PGDATABASE} --create --no-privileges --no-owner --clean --if-exists --exit-on-error ../backups/backup__2019-09-03_16-00-19.pg_dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4954; 1262 962277 DATABASE mydb mydb
pg_restore: [archiver (db)] could not execute query: ERROR:  cannot drop the currently open database
    Command was: DROP DATABASE IF EXISTS mydb;

real    0m0.049s
user    0m0.019s
sys    0m0.012s

Previously, I've only ever had 'out of disk space errors' after minutes of operation, but as you can see from the time output, this error occurs almost immediately!

The backup is created with compression -Fc , and I'm able to de-compress the backup to plaintext using pg_restore backup.pg_dump > backup.sql, but I'm unable to find any commands in there related to DROP DATABASE :/

-- Dumped from database version 9.6.11
-- Dumped by pg_dump version 11.3 (Ubuntu 11.3-1.pgdg14.04+1)

I see that the DB was originally dumped from 9.6.11 but I'm reasonably sure that I previously restored successfully with PG 10...

I have tried various things, like:

time pg_restore -d ${PGDATABASE} --no-privileges --no-owner --exit-on-error ../backups/backup__2019-09-03_16-00-19.pg_dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 7; 2615 1033373 SCHEMA public mydb
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "public" already exists
    Command was: CREATE SCHEMA public;

ptim:dropdb mydb; createdb mydb
ptim:directory ptim$ dropdb mydb

ptim:directory ptim$ time pg_restore -d ${PGDATABASE} --create --no-privileges --no-owner --exit-on-error ../backups/backup__2019-09-03_16-00-19.pg_dump
pg_restore: [archiver (db)] connection to database "mydb" failed: FATAL:  database "mydb" does not exist

# guess I misunderstood the create flag!

ptim:directory ptim$ dropdb mydb; createdb mydb
dropdb: database removal failed: ERROR:  database "mydb" does not exist

ptim:directory ptim$ time pg_restore -d ${PGDATABASE} --create --no-privileges --no-owner --exit-on-error ../backups/backup__2019-09-03_16-00-19.pg_dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4954; 1262 962277 DATABASE mydb mydb
pg_restore: [archiver (db)] could not execute query: ERROR:  database "mydb" already exists
    Command was: CREATE DATABASE mydb WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';

I also tried unsuccessfully to restore from the plaintext dump:

psql -U ptim -d mydb -1 -f ../backups/backup__2019-09-03_16-00-19.sql

SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
psql:../backups/backup__2019-09-03_16-00-19.sql:23: ERROR:  schema "public" already exists
psql:../backups/backup__2019-09-03_16-00-19.sql:26: ERROR:  current transaction is aborted, commands ignored until end of transaction block

# snip... repeated, and varied errors follow

Any suggestions?!

Answer 1

Thanks to the tip from @a_horse_with_no_name, I learned:

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

(Further explained at dba.stackexchange: why pg_restore ignores --create. Note that --create has further, unrelated effects in newer versions of pg_restore)

My solution was to remove the --create param:

dropdb ${PGDATABASE}
createdb ${PGDATABASE}
time pg_restore \
    -j4 \
    -d ${PGDATABASE} \
    --no-privileges \
    --no-owner \
    --clean \
    --if-exists \
    --exit-on-error \
    backup.pg_dump