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

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #15466: Logical backups from v10 cannot be restored with v11 -"ERROR: schema "public" already exists"
Дата
Msg-id 15466-0b90383ff69c6e4b@postgresql.org
обсуждение исходный текст
Ответы Re: BUG #15466: Logical backups from v10 cannot be restored with v11 - "ERROR: schema "public" already exists"  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
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.


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: BUG #15460: Error while creating index or constraint
Следующее
От: PG Bug reporting form
Дата:
Сообщение: BUG #15467: The database subdirectory"pg_tblspc/1932420460/PG_10_201707211/16400" is missing.