On 6/22/24 10:01, Shaheed Haque 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:
o 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:
o 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.
o 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?
1) Show the complete pg_restore script.
2) The first issue is related to trying to create a database that
already exists. Does that database have data in it?
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
