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.
