Re: pg_dump restores as expected on some machines and reports duplicate keys on others

On Sat, Jun 22, 2024 at 1:02 PM Shaheed Haque <shaheedhaque(at)gmail(dot)com>
wrote:

> Hi,
>
> I am using Postgres 14 on AWS RDS and am seeing the output of pg_dump be
> restored as expected by pg_restore on some database instances, and fail
> with reports of duplicate keys on other database instances:
>
> - My deployments are always a pair, one "logic VM" for Django etc and
> one "RDS instance". The psql client runs on the logic VM. The Postgres
> version is the same in all cases; psql reports:
>
>
> - psql (14.12 (Ubuntu 14.12-0ubuntu0.22.04.1), server 14.9)
>
>
> - The pg_restore is done using the same script in both cases.
> - In the failing cases, there are always the same 26 errors (listed in
> detail below), but in summary, 3 distinct "child" tables complain of a
> duplicate id=1, id=2 and id=3 respectively.
> - These "child" tables are FK-related via some intermediate table to a
> top level table. They form a polymorphic set. There are other similar child
> tables which do not appear to be affected:
> - polymorphicmodel
> - companybankdetail
> - companybankdetailde
> - companybankdetailgb <<< 1 duplicate, id=2
> - companybankdetailus
> - companypostaldetail
> - companypostaldetailde
> - companypostaldetailgb <<< 1 duplicate, id=1
> - companypostaldetailus
> - companytaxdetail
> - companytaxdetailde
> - companytaxdetailgb <<< 1 duplicate, id=3
> - companytaxdetailus
> - ...
> - several other hierarchies, all error free
> - ...
> - I've looked at the dumped NNNN.dat files but they contain no
> duplicates.
> - The one difference I can think of between deployment pairs which
> work ok, and those which fail is that the logic VM (i.e. where the psql
> client script runs) is the use of a standard AWS ubuntu image for the OK
> case, versus a custom AWS image for the failing case.
> - The custom image is a saved snapshot of one created using the
> standard image.
>
> Why should the use of one type of VM image versus another cause pg_restore
> to hallucinate the duplicate records?
>
> Encls: 26 errors as mentioned...
>
> ========
> pg_restore: while PROCESSING TOC:
> pg_restore: from TOC entry 5635; 1262 53894 DATABASE foo postgres
> pg_restore: error: could not execute query: ERROR: database "foo" already
> exists
> Command was: CREATE DATABASE foo WITH TEMPLATE = template0 ENCODING =
> 'UTF8' LOCALE = 'en_US.UTF-8';
>

Check *all* of the client and server encodings.

99.99% of the time, that's the problem when the same dump file fails to
restore on different servers.