Re: BUG #15466: Logical backups from v10 cannot be restored with v11 - "ERROR: schema "public" already exists"

=?utf-8?q?PG_Bug_reporting_form?= <noreply(at)postgresql(dot)org> writes:
> When trying to restore logical backups taken on PostgreSQL v10.x (v10
> pg_dump, either using custom file format or directory) on PostgreSQL v11
> (v11 pg_restore), following error occurs:
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 3; 2615 2200 SCHEMA public
> postgres
> pg_restore: [archiver (db)] could not execute query: ERROR: schema "public"
> already exists
> Command was: CREATE SCHEMA public;

Yeah, this is a side effect of some intentional changes to the way that
the public schema is backed up. v10 used to include the public schema
in the dump, and then pg_restore had some very ugly (and buggy) hacks
to skip that dump item. v11 doesn't do it like that anymore. We got
rid of the bugs, but the price is that if you want to restore a pre-v11
dump file using v11 pg_restore, you can't use --exit-on-error.

(It's not that unusual for similar situations to occur with cross-version
dump-restore situations. I don't know if we'll ever get to a situation
where --exit-on-error can be counted on to never complain; but there is
a reason why it's not the default behavior.)

regards, tom lane