PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15466
PG Version11.0
OSWindows 10
Opened2018-10-29 20:37:35+00
Reported byzam6ak
StatusWon't Fix

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15466
Logged by:          zam6ak
Email address:      (redacted)
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.

Messages

DateAuthorSubject
2018-10-29 20:37:35+00=?utf-8?q?PG_Bug_reporting_form?=BUG #15466: Logical backups from v10 cannot be restored with v11 - "ERROR: schema "public" already exists"
2018-11-08 00:54:25+00Tom LaneRe: BUG #15466: Logical backups from v10 cannot be restored with v11 - "ERROR: schema "public" already exists"