On Sat, Jun 22, 2024 at 1:02 PM Shaheed Haque <shaheedha...@gmail.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.
