As pointed above by Adrian Klaver, I suspect that you did multiple attempts that caused Database Already Exists. ( There must be data in the tables, which the next attempt is trying to write again) . I can't think of any scenario where restoration succeeds on one environment and fails on another, if there were some locale difference then it must have failed with a different reason, not the duplicates
Perform a clean up and try again. Hope it succeeds.
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?