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"
|
Список | 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 по дате отправления: