BUG #16514: pg_dump v12 puts CREATE SCHEMA public statement into the backup file
The following bug has been logged on the website:
Bug reference: 16514
Logged by: Kirill Kravtsov
Email address: kravtsov.k@gmail.com
PostgreSQL version: 12.3
Operating system: CentOS 7
Description:
I’m experiencing some weird behaviour of pg_dump: based on this thread
/messages/by-id/20849.1541638465@sss.pgh.pa.us
, starting from v11 pg_dump should not include CREATE SCHEMA public
statements to the dump. However, if I run pg_dump v12 with --schema public ,
it still adds that line to the backup file, which breaks the ability to
restore from it
$ /usr/pgsql-12/bin/pg_dump -d db --schema public --schema-only | grep
'CREATE SCHEMA'
CREATE SCHEMA public;
Without specifying a schema it works as expected:
$ /usr/pgsql-12/bin/pg_dump -d db --schema-only | grep 'CREATE SCHEMA'
CREATE SCHEMA admin;
CREATE SCHEMA stats;
When attempting to restore from such a backup, the following error occurs:
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 12; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR: schema "public" already
exists
Command was: CREATE SCHEMA public;
PG Bug reporting form <noreply@postgresql.org> writes:
I’m experiencing some weird behaviour of pg_dump: based on this thread
/messages/by-id/20849.1541638465@sss.pgh.pa.us
, starting from v11 pg_dump should not include CREATE SCHEMA public
statements to the dump. However, if I run pg_dump v12 with --schema public ,
it still adds that line to the backup file,
Our interpretation of that is that you've deliberately selected the
schema, so it should be dumped.
which breaks the ability to
restore from it
That's rather an overstatement no? You can just ignore the "already
exists" error.
regards, tom lane