Thread: pg_restore: error: schema "public" already exists

Hi,

A colleague is getting this error when trying to load a
database dump:

  pg_restore: error: could not execute query: ERROR:  schema "public" already exists

I'm wondering if anyone can explain it.

Here's the background.

I have a "dump" script that calls pg_dump and pipes the
output into gpg to encrypt it, and a I have "load"
script that decrypts a dump with gpg and pipes the
output into pg_restore (followed by a bunch of other
things).

The dump+encrypt and decrypt+load commands look like this:

For dump:

  pg_dump -U OWNERNAME -Fc DBNAME | \
  gpg --recipient key20@domain.com --encrypt --output YYYYMMDD-HHMMSS-LABEL.pgdump.gpg.key20

For load:

  dropdb -U postgres DBNAME # if it exists
  createdb -U postgres -T template0 -E utf8 -O OWNERNAME DBNAME
  gpg --decrypt YYYYMMDD-HHMMSS-LABEL.pgdump.gpg.key20 | pg_restore -1 -U postgres -d DBNAME -Fc

I've just added support for choosing the key to encrypt
to, so that I can encrypt a backup to the key of a new
colleague. The only real change to the above commands
was to replace gpg's "--default-recipient XXX" option
with "--recipient XXX", which shouldn't really change
anything.

The dumps happen on debian-9, postgresql-9.6.15,
gpg-2.1.18. The loads happen there as well, but also on
macOS hosts (10.14 and 10.15), postgresql-9.6 (and 12),
gpg-2.2.23.

I use macOS 10.14, postgresql-9.6, gpg-2.2.23 (or
gpg-1.4.23) and the above has worked without problem.

But my colleague (macOS-10.15, postgresql-12,
gpg-2.2.23) is enecountering a strange error when he
tries to load a dump:

  Restoring...
  gpg: encrypted with 4096-bit RSA key, ID A44C904AA8B9BB48, created 2020-09-21
         "colleague <colleague@company.com>"
       pg_restore: while PROCESSING TOC:
        pg_restore: from TOC entry 3; 2615 2200 SCHEMA public postgres
  pg_restore: error: could not execute query: ERROR:  schema "public" already exists
  Command was: CREATE SCHEMA public;

  pgrestore encountered errors
  Continue? ([y]/n): y

The next bit was from a subsequent SQL statement
failing because pg_restore had failed:

  Failed to run pg_restore: ERROR:  relation "lock_company" does not exist
  LINE 1: delete from lock_company

To me, this looks like the decryption worked, but there
was something else wrong.

My colleague decrypted the backup to a file, and then
used the load script to load the unencrypted dump, and
that worked fine.

Does anyone have any idea why this might have happened?

Under what circumstances (immediately after createdb)
might "CREATE SCHEMA public" result in "ERROR:  schema
"public" already exists"?

And why might it differ depending on whether the
unencrypted dump was read from stdin or from a file?

cheers,
raf

raf <raf@raf.org> writes:
> A colleague is getting this error when trying to load a
> database dump:
>   pg_restore: error: could not execute query: ERROR:  schema "public" already exists
> I'm wondering if anyone can explain it.

The public schema is a bit of a strange beast, and pg_dump has to
special-case it in some ways.  We've moved those special cases around
from time to time, too.  So one likely explanation here has to do
with version discrepancies between the pg_dump that made the dump
file and the pg_restore that's restoring it.

Also, I do not think that you're telling us the whole truth about
how your colleague is running pg_dump and/or pg_restore.  There
shouldn't be any "CREATE SCHEMA public" issued if you didn't say -c.

            regards, tom lane