Several years ago at a company I no longer work at, when we swapped from single-threaded pgdumps (from a replica) to multi-threaded pgdumps (-j flag), we occasionally saw some dumps which were "inconsistent". Not sure if that's the best term for it, but we would occasionally get duplicate foreign key errors on pg_restore or even if pg_restore succeeded, sometimes primary key sequences would be less than the maximum id of our table (so new inserts would fail). This was on standard postgres 13.3 (not aurora) on AWS RDS. Nothing really noteworthy about our setup either that I can remember.
I tried reporting it at the time on both the postgres mailing list and with AWS support and unfortunately both pointed fingers at the other and I eventually gave up since it was not easy to reproduce. We unfortunately ended up having to go back to single threaded dumps which never produced any of these "corrupt" dumps but was obviously much slower.
I can’t speak specifically to duplicate foreign key errors but I possibly fixed that bug via a fix to parallel dump/restore in the 12-13 era. It would manifest as a dump that would fail on restore with errors related to schema dependencies (trying to restore database objects out-of-order).
reply