title: How to restore a postgres backup - ERROR: cannot drop the currently open database
body: <p>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.</p>
  <ul>
  <li>I have only the backups, no original servers to refer to</li>
  <li>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)</li>
  <li>I believe that a former colleague (no longer available) has successfully restored this particular backup</li>
  <li>I've encountered the same error when:
  <ul>
  <li>trying the backup using postgresql@9.4</li>
  <li>trying a previous known-good version of the database (using postgres 11)</li>
  </ul>
  </li>
  </ul>
  <p>(I've read related/suggested questions, and they don't seem relevant)</p>
  <p>The backup was created on using the following command (in a bash script):</p>
  <pre><code>pg_dump --schema=public -Fc
  </code></pre>
  <p>And the command used to restore the script (which is echo'd as help text by backup script) is:</p>
  <pre class="lang-bash prettyprint-override"><code>dropdb ${PGDATABASE}
  createdb ${PGDATABASE}
  time pg_restore \
      -j4 \
      -d ${PGDATABASE} \
      --create \
      --no-privileges \
      --no-owner \
      --clean \
      --if-exists \
      --exit-on-error \
      &quot;${dirname}/${filename}&quot;
  </code></pre>
  <p>When I run the restore command locally, I get the following:</p>
  <pre class="lang-none prettyprint-override"><code>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
  </code></pre>
  <p>Previously, I've only ever had 'out of disk space errors' after minutes of operation, but as you can see from the <code>time</code> output, this error occurs almost immediately!</p>
  <p>The backup is created with compression <code>-Fc</code> , and I'm able to de-compress the backup to plaintext using <code>pg_restore backup.pg_dump &gt; backup.sql</code>, but I'm unable to find any commands in there related to <code>DROP DATABASE</code> :/</p>
  <pre class="lang-none prettyprint-override"><code>-- Dumped from database version 9.6.11
  -- Dumped by pg_dump version 11.3 (Ubuntu 11.3-1.pgdg14.04+1)
  </code></pre>
  <p>I see that the DB was originally dumped from <code>9.6.11</code> but I'm reasonably sure that I previously restored successfully with PG 10...</p>
  <p>I have tried various things, like:</p>
  <pre class="lang-none prettyprint-override"><code>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 &quot;public&quot; 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 &quot;mydb&quot; failed: FATAL:  database &quot;mydb&quot; does not exist
  
  # guess I misunderstood the create flag!
  
  ptim:directory ptim$ dropdb mydb; createdb mydb
  dropdb: database removal failed: ERROR:  database &quot;mydb&quot; 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 &quot;mydb&quot; already exists
      Command was: CREATE DATABASE mydb WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
  </code></pre>
  <p>I also tried unsuccessfully to restore from the plaintext dump:</p>
  <pre class="lang-none prettyprint-override"><code>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 &quot;public&quot; 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
  </code></pre>
  <p>Any suggestions?!</p>