Re: BUG #16732: pg_dump creates broken backups

It happens with 1 row in a 3 GB gzip compressed database dump. I'm
thinking about how could I possibly give you a reproducible case. Do you
know any way which doesn't require me to share the whole production
database? (which is not an option)

I can send you a --schema-only sql dump, if that helps. There are no
circular relationships that I know.

On 20 Nov 2020 at 17:28:33, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Zsolt Ero <zsolt(dot)ero(at)gmail(dot)com> writes:
>
> 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 suspect actually the problem has more to do with having split the
> restore into --schema-only and --data-only steps. That forces
> pg_restore to create the FK constraints before it's loaded the
> data, and in certain cases such as circular FK relationships,
> there will be no safe restore order for the data.
>
> However, that's all just speculation since you haven't shown us
> a reproducible case.
>
> regards, tom lane
>