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

The following bug has been logged on the website:

Bug reference:      15466
Logged by:          zam6ak
Email address:      zam6ak@gmail.com
PostgreSQL version: 11.0
Operating system:   Windows 10
Description:

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;
-----

How to reproduce (same machine, 2 PG clusters, v10 running on port 5432, v11
running on port 5434, Windows CMD shell)

1) Backup on v10

set PGCLIENTENCODING=UTF8
set PGUSER=postgres
set PGPASSWORD=INeverTell
set PGHOST=localhost
set PGDATABASE=dvdrental

set PGPORT=5432
"I:\PostgreSQL\10\bin\pg_dump.exe" --no-password --no-owner --format custom
--file "I:\Backups\%PGDATABASE%.bak"


2) On v11, create blank DB and attempt to restore into it

set PGPORT=5434
"I:\PostgreSQL\11\bin\psql.exe" -d template1 -c "CREATE DATABASE
%PGDATABASE% WITH ENCODING='UTF8';"
"I:\PostgreSQL\11\bin\pg_restore.exe" --dbname %PGDATABASE% --jobs 4
--no-owner --no-acl  --exit-on-error "I:\Backups\%PGDATABASE%.bak"

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;



WORKAROUNDS:

1) Use pg_restore to produce TOC listing, edit it and comment out CREATE
SCHEMA entry (for public schema only!), then use it with v11 pg_restore     
    a) "I:\PostgreSQL\11\bin\pg_restore.exe" --list --file
"I:\Backups\%PGDATABASE%.toc.txt" "I:\Backups\%PGDATABASE%.bak"
    b) edit the TOC file and comment out line (1st number may be different):
"3; 2615 2200 SCHEMA - public postgres"
    c) "I:\PostgreSQL\11\bin\pg_restore.exe" --dbname %PGDATABASE% --use-list
"I:\Backups\%PGDATABASE%_toc.txt" --jobs 4 --no-owner --no-acl
--exit-on-error "I:\Backups\%PGDATABASE%.bak"

2) use v11 pg_backup binary to create a backup on v10 server and then
restore with v11 pg_restore
   FYI, is not always feasible (not at all for us) as customer backups are
being produced automatically (overnight) on remote systems.

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.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