Re: BUG #16732: pg_dump creates broken backups

Sorry this is not so general, I over-simplified the example. The key is
--use-list (with possibly --jobs)

What I've double checked is that the following breaks it consistently:

pg_restore --schema-only --dbname=app --format=custom --single-transaction
$BACKUP_FILE

pg_restore --list $BACKUP_FILE > dump_list.full
pg_restore --data-only --dbname=app --format=custom
--use-list=dump_list.full --jobs=4 $BACKUP_FILE

I think it occured without --jobs=4 but I need to confirm that tomorrow.

Zsolt

On 20 Nov 2020 at 02:13:12, PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 16732
> Logged by: Zsolt Ero
> Email address: zsolt(dot)ero(at)gmail(dot)com
> PostgreSQL version: 12.4
> Operating system: Ubuntu 16.04
> Description:
>
> On a production server (12.4) backups are created by the following
> command:
> pg_dump --dbname=app --format=custom --file=$BACKUP_DIR/app.dump
>
> Then on any other machine (can be the same server, can be a my macOS laptop
> running 12.5), this command always fails (or it'd fail with
> --single-transaction):
> pg_restore --dbname=maphub_web --format=custom $BACKUP_FILE
>
> The error is always:
> pg_restore: while PROCESSING TOC:
> pg_restore: from TOC entry 2976; 0 63529 TABLE DATA map_versions app
> pg_restore: error: COPY failed for table "map_versions": ERROR: insert or
> update on table "map_versions" violates foreign key constraint
> "fk_map_versions_map_id_maps"
> DETAIL: Key (map_id)=(112664) is not present in table "maps".
>
> The target database is cleaned with dropdb + createdb before pg_restore. I
> compared the backups by md5, the file is not-corrupt.
>
> This is quite shocking in a way, meaning that right now I couldn't restore
> my prod server in case I'd need to.
>
>