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@xxxxxxxxxx --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@xxxxxxxxxxx>"
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
