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

From: Shaheed Haque <[email-masked]>
Date: Sat, 22 Jun 2024 18:01:44 +0100

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';

pg_restore: from TOC entry 4808; 2606 80439 CONSTRAINT
paiyroll_companybankdetail paiyroll_companybankdetail_pkey dbcoreuser
pg_restore: error: could not execute query: ERROR:  could not create unique
index "paiyroll_companybankdetail_pkey"
DETAIL:  Key (polymorphicmodel_ptr_id)=(2) is duplicated.
Command was: ALTER TABLE ONLY public.paiyroll_companybankdetail
   ADD CONSTRAINT paiyroll_companybankdetail_pkey PRIMARY KEY
(polymorphicmodel_ptr_id);

pg_restore: from TOC entry 4812; 2606 80443 CONSTRAINT
paiyroll_companybankdetailgb paiyroll_companybankdetailgb_pkey dbcoreuser
pg_restore: error: could not execute query: ERROR:  could not create unique
index "paiyroll_companybankdetailgb_pkey"
DETAIL:  Key (companybankdetail_ptr_id)=(2) is duplicated.
Command was: ALTER TABLE ONLY public.paiyroll_companybankdetailgb
   ADD CONSTRAINT paiyroll_companybankdetailgb_pkey PRIMARY KEY
(companybankdetail_ptr_id);

pg_restore: from TOC entry 4817; 2606 80447 CONSTRAINT
paiyroll_companypostaldetail paiyroll_companypostaldetail_pkey dbcoreuser
pg_restore: error: could not execute query: ERROR:  could not create unique
index "paiyroll_companypostaldetail_pkey"
DETAIL:  Key (polymorphicmodel_ptr_id)=(1) is duplicated.
Command was: ALTER TABLE ONLY public.paiyroll_companypostaldetail
   ADD CONSTRAINT paiyroll_companypostaldetail_pkey PRIMARY KEY
(polymorphicmodel_ptr_id);

pg_restore: from TOC entry 4821; 2606 80451 CONSTRAINT
paiyroll_companypostaldetailgb paiyroll_companypostaldetailgb_pkey
dbcoreuser
pg_restore: error: could not execute query: ERROR:  could not create unique
index "paiyroll_companypostaldetailgb_pkey"
DETAIL:  Key (companypostaldetail_ptr_id)=(1) is duplicated.
Command was: ALTER TABLE ONLY public.paiyroll_companypostaldetailgb
   ADD CONSTRAINT paiyroll_companypostaldetailgb_pkey PRIMARY KEY
(companypostaldetail_ptr_id);

pg_restore: from TOC entry 4826; 2606 80455 CONSTRAINT
paiyroll_companytaxdetail paiyroll_companytaxdetail_pkey dbcoreuser
pg_restore: error: could not execute query: ERROR:  could not create unique
index "paiyroll_companytaxdetail_pkey"
DETAIL:  Key (polymorphicmodel_ptr_id)=(3) is duplicated.
Command was: ALTER TABLE ONLY public.paiyroll_companytaxdetail
   ADD CONSTRAINT paiyroll_companytaxdetail_pkey PRIMARY KEY
(polymorphicmodel_ptr_id);

pg_restore: from TOC entry 4830; 2606 80459 CONSTRAINT
paiyroll_companytaxdetailgb paiyroll_companytaxdetailgb_pkey dbcoreuser
pg_restore: error: could not execute query: ERROR:  could not create unique
index "paiyroll_companytaxdetailgb_pkey"
DETAIL:  Key (companytaxdetail_ptr_id)=(3) is duplicated.
Command was: ALTER TABLE ONLY public.paiyroll_companytaxdetailgb
   ADD CONSTRAINT paiyroll_companytaxdetailgb_pkey PRIMARY KEY
(companytaxdetail_ptr_id);

pg_restore: from TOC entry 5018; 2606 80614 CONSTRAINT
paiyroll_polymorphicmodel paiyroll_polymorphicmodel_pkey dbcoreuser
pg_restore: error: could not execute query: ERROR:  could not create unique
index "paiyroll_polymorphicmodel_pkey"
DETAIL:  Key (id)=(3) is duplicated.
Command was: ALTER TABLE ONLY public.paiyroll_polymorphicmodel
   ADD CONSTRAINT paiyroll_polymorphicmodel_pkey PRIMARY KEY (id);

pg_restore: from TOC entry 5207; 2606 81004 FK CONSTRAINT
paiyroll_companybankdetailde
paiyroll_companybank_companybankdetail_pt_282859e0_fk_paiyroll_ dbcoreuser
pg_restore: error: could not execute query: ERROR:  there is no unique
constraint matching given keys for referenced table
"paiyroll_companybankdetail"
Command was: ALTER TABLE ONLY public.paiyroll_companybankdetailde
   ADD CONSTRAINT
paiyroll_companybank_companybankdetail_pt_282859e0_fk_paiyroll_ FOREIGN KEY
(companybankdetail_ptr_id) REFERENCES
public.paiyroll_companybankdetail(polymorphicmodel_ptr_id) DEF
ERRABLE INITIALLY DEFERRED;

pg_restore: from TOC entry 5209; 2606 81009 FK CONSTRAINT
paiyroll_companybankdetailus
paiyroll_companybank_companybankdetail_pt_555d62e4_fk_paiyroll_ dbcoreuser
pg_restore: error: could not execute query: ERROR:  there is no unique
constraint matching given keys for referenced table
"paiyroll_companybankdetail"
Command was: ALTER TABLE ONLY public.paiyroll_companybankdetailus
   ADD CONSTRAINT
paiyroll_companybank_companybankdetail_pt_555d62e4_fk_paiyroll_ FOREIGN KEY
(companybankdetail_ptr_id) REFERENCES
public.paiyroll_companybankdetail(polymorphicmodel_ptr_id) DEF
ERRABLE INITIALLY DEFERRED;

pg_restore: from TOC entry 5208; 2606 81014 FK CONSTRAINT
paiyroll_companybankdetailgb
paiyroll_companybank_companybankdetail_pt_5cf33abb_fk_paiyroll_ dbcoreuser
pg_restore: error: could not execute query: ERROR:  there is no unique
constraint matching given keys for referenced table
"paiyroll_companybankdetail"
Command was: ALTER TABLE ONLY public.paiyroll_companybankdetailgb
   ADD CONSTRAINT
paiyroll_companybank_companybankdetail_pt_5cf33abb_fk_paiyroll_ FOREIGN KEY
(companybankdetail_ptr_id) REFERENCES
public.paiyroll_companybankdetail(polymorphicmodel_ptr_id) DEF
ERRABLE INITIALLY DEFERRED;

pg_restore: from TOC entry 5206; 2606 81019 FK CONSTRAINT
paiyroll_companybankdetail
paiyroll_companybank_polymorphicmodel_ptr_d05e0f06_fk_paiyroll_ dbcoreuser
pg_restore: error: could not execute query: ERROR:  there is no unique
constraint matching given keys for referenced table
"paiyroll_polymorphicmodel"
Command was: ALTER TABLE ONLY public.paiyroll_companybankdetail
   ADD CONSTRAINT
paiyroll_companybank_polymorphicmodel_ptr_d05e0f06_fk_paiyroll_ FOREIGN KEY
(polymorphicmodel_ptr_id) REFERENCES public.paiyroll_polymorphicmodel(id)
DEFERRABLE INITIALLY DEFER
RED;

pg_restore: from TOC entry 5213; 2606 81029 FK CONSTRAINT
paiyroll_companypostaldetailgb
paiyroll_companypost_companypostaldetail__86015d44_fk_paiyroll_ dbcoreuser
pg_restore: error: could not execute query: ERROR:  there is no unique
constraint matching given keys for referenced table
"paiyroll_companypostaldetail"
Command was: ALTER TABLE ONLY public.paiyroll_companypostaldetailgb
   ADD CONSTRAINT
paiyroll_companypost_companypostaldetail__86015d44_fk_paiyroll_ FOREIGN KEY
(companypostaldetail_ptr_id) REFERENCES
public.paiyroll_companypostaldetail(polymorphicmodel_ptr_id)
DEFERRABLE INITIALLY DEFERRED;

pg_restore: from TOC entry 5214; 2606 81034 FK CONSTRAINT
paiyroll_companypostaldetailus
paiyroll_companypost_companypostaldetail__963c29cd_fk_paiyroll_ dbcoreuser
pg_restore: error: could not execute query: ERROR:  there is no unique
constraint matching given keys for referenced table
"paiyroll_companypostaldetail"
Command was: ALTER TABLE ONLY public.paiyroll_companypostaldetailus
   ADD CONSTRAINT
paiyroll_companypost_companypostaldetail__963c29cd_fk_paiyroll_ FOREIGN KEY
(companypostaldetail_ptr_id) REFERENCES
public.paiyroll_companypostaldetail(polymorphicmodel_ptr_id)
DEFERRABLE INITIALLY DEFERRED;

pg_restore: from TOC entry 5212; 2606 81039 FK CONSTRAINT
paiyroll_companypostaldetailde
paiyroll_companypost_companypostaldetail__f51f0ce0_fk_paiyroll_ dbcoreuser
pg_restore: error: could not execute query: ERROR:  there is no unique
constraint matching given keys for referenced table
"paiyroll_companypostaldetail"
Command was: ALTER TABLE ONLY public.paiyroll_companypostaldetailde
   ADD CONSTRAINT
paiyroll_companypost_companypostaldetail__f51f0ce0_fk_paiyroll_ FOREIGN KEY
(companypostaldetail_ptr_id) REFERENCES
public.paiyroll_companypostaldetail(polymorphicmodel_ptr_id)
DEFERRABLE INITIALLY DEFERRED;

pg_restore: from TOC entry 5211; 2606 81044 FK CONSTRAINT
paiyroll_companypostaldetail
paiyroll_companypost_polymorphicmodel_ptr_d9fc1568_fk_paiyroll_ dbcoreuser
pg_restore: error: could not execute query: ERROR:  there is no unique
constraint matching given keys for referenced table
"paiyroll_polymorphicmodel"
Command was: ALTER TABLE ONLY public.paiyroll_companypostaldetail
   ADD CONSTRAINT
paiyroll_companypost_polymorphicmodel_ptr_d9fc1568_fk_paiyroll_ FOREIGN KEY
(polymorphicmodel_ptr_id) REFERENCES public.paiyroll_polymorphicmodel(id)
DEFERRABLE INITIALLY DEFER
RED;

pg_restore: from TOC entry 5217; 2606 81054 FK CONSTRAINT
paiyroll_companytaxdetailde
paiyroll_companytaxd_companytaxdetail_ptr_6509fa4d_fk_paiyroll_ dbcoreuser
pg_restore: error: could not execute query: ERROR:  there is no unique
constraint matching given keys for referenced table
"paiyroll_companytaxdetail"
Command was: ALTER TABLE ONLY public.paiyroll_companytaxdetailde
   ADD CONSTRAINT
paiyroll_companytaxd_companytaxdetail_ptr_6509fa4d_fk_paiyroll_ FOREIGN KEY
(companytaxdetail_ptr_id) REFERENCES
public.paiyroll_companytaxdetail(polymorphicmodel_ptr_id) DEFER
RABLE INITIALLY DEFERRED;

pg_restore: from TOC entry 5219; 2606 81059 FK CONSTRAINT
paiyroll_companytaxdetailus
paiyroll_companytaxd_companytaxdetail_ptr_b8b74e53_fk_paiyroll_ dbcoreuser
pg_restore: error: could not execute query: ERROR:  there is no unique
constraint matching given keys for referenced table
"paiyroll_companytaxdetail"
Command was: ALTER TABLE ONLY public.paiyroll_companytaxdetailus
   ADD CONSTRAINT
paiyroll_companytaxd_companytaxdetail_ptr_b8b74e53_fk_paiyroll_ FOREIGN KEY
(companytaxdetail_ptr_id) REFERENCES
public.paiyroll_companytaxdetail(polymorphicmodel_ptr_id) DEFER
RABLE INITIALLY DEFERRED;

pg_restore: from TOC entry 5218; 2606 81064 FK CONSTRAINT
paiyroll_companytaxdetailgb
paiyroll_companytaxd_companytaxdetail_ptr_d9437c9a_fk_paiyroll_ dbcoreuser
pg_restore: error: could not execute query: ERROR:  there is no unique
constraint matching given keys for referenced table
"paiyroll_companytaxdetail"
Command was: ALTER TABLE ONLY public.paiyroll_companytaxdetailgb
   ADD CONSTRAINT
paiyroll_companytaxd_companytaxdetail_ptr_d9437c9a_fk_paiyroll_ FOREIGN KEY
(companytaxdetail_ptr_id) REFERENCES
public.paiyroll_companytaxdetail(polymorphicmodel_ptr_id) DEFER
RABLE INITIALLY DEFERRED;

pg_restore: from TOC entry 5216; 2606 81069 FK CONSTRAINT
paiyroll_companytaxdetail
paiyroll_companytaxd_polymorphicmodel_ptr_41f5cde7_fk_paiyroll_ dbcoreuser
pg_restore: error: could not execute query: ERROR:  there is no unique
constraint matching given keys for referenced table
"paiyroll_polymorphicmodel"
Command was: ALTER TABLE ONLY public.paiyroll_companytaxdetail
   ADD CONSTRAINT
paiyroll_companytaxd_polymorphicmodel_ptr_41f5cde7_fk_paiyroll_ FOREIGN KEY
(polymorphicmodel_ptr_id) REFERENCES public.paiyroll_polymorphicmodel(id)
DEFERRABLE INITIALLY DEFER
RED;

pg_restore: from TOC entry 5221; 2606 81079 FK CONSTRAINT paiyroll_debbie
paiyroll_debbie_polymorphicmodel_ptr_8b0fe25a_fk_paiyroll_ dbcoreuser
pg_restore: error: could not execute query: ERROR:  there is no unique
constraint matching given keys for referenced table
"paiyroll_polymorphicmodel"
Command was: ALTER TABLE ONLY public.paiyroll_debbie
   ADD CONSTRAINT
paiyroll_debbie_polymorphicmodel_ptr_8b0fe25a_fk_paiyroll_ FOREIGN KEY
(polymorphicmodel_ptr_id) REFERENCES public.paiyroll_polymorphicmodel(id)
DEFERRABLE INITIALLY DEFERRED;

pg_restore: from TOC entry 5234; 2606 81159 FK CONSTRAINT
paiyroll_employeebankdetail
paiyroll_employeeban_polymorphicmodel_ptr_6b4ae3ff_fk_paiyroll_ dbcoreuser
pg_restore: error: could not execute query: ERROR:  there is no unique
constraint matching given keys for referenced table
"paiyroll_polymorphicmodel"
Command was: ALTER TABLE ONLY public.paiyroll_employeebankdetail
   ADD CONSTRAINT
paiyroll_employeeban_polymorphicmodel_ptr_6b4ae3ff_fk_paiyroll_ FOREIGN KEY
(polymorphicmodel_ptr_id) REFERENCES public.paiyroll_polymorphicmodel(id)
DEFERRABLE INITIALLY DEFER
RED;

pg_restore: from TOC entry 5239; 2606 81184 FK CONSTRAINT
paiyroll_employeepostaldetail
paiyroll_employeepos_polymorphicmodel_ptr_4b75f2aa_fk_paiyroll_ dbcoreuser
pg_restore: error: could not execute query: ERROR:  there is no unique
constraint matching given keys for referenced table
"paiyroll_polymorphicmodel"
Command was: ALTER TABLE ONLY public.paiyroll_employeepostaldetail
   ADD CONSTRAINT
paiyroll_employeepos_polymorphicmodel_ptr_4b75f2aa_fk_paiyroll_ FOREIGN KEY
(polymorphicmodel_ptr_id) REFERENCES public.paiyroll_polymorphicmodel(id)
DEFERRABLE INITIALLY DEFER
RED;

pg_restore: from TOC entry 5249; 2606 81229 FK CONSTRAINT
paiyroll_employeetaxeedetail
paiyroll_employeetax_polymorphicmodel_ptr_2554ee34_fk_paiyroll_ dbcoreuser
pg_restore: error: could not execute query: ERROR:  there is no unique
constraint matching given keys for referenced table
"paiyroll_polymorphicmodel"
Command was: ALTER TABLE ONLY public.paiyroll_employeetaxeedetail
   ADD CONSTRAINT
paiyroll_employeetax_polymorphicmodel_ptr_2554ee34_fk_paiyroll_ FOREIGN KEY
(polymorphicmodel_ptr_id) REFERENCES public.paiyroll_polymorphicmodel(id)
DEFERRABLE INITIALLY DEFER
RED;

pg_restore: from TOC entry 5244; 2606 81234 FK CONSTRAINT
paiyroll_employeetaxdetail
paiyroll_employeetax_polymorphicmodel_ptr_8c064f71_fk_paiyroll_ dbcoreuser
pg_restore: error: could not execute query: ERROR:  there is no unique
constraint matching given keys for referenced table
"paiyroll_polymorphicmodel"
Command was: ALTER TABLE ONLY public.paiyroll_employeetaxdetail
   ADD CONSTRAINT
paiyroll_employeetax_polymorphicmodel_ptr_8c064f71_fk_paiyroll_ FOREIGN KEY
(polymorphicmodel_ptr_id) REFERENCES public.paiyroll_polymorphicmodel(id)
DEFERRABLE INITIALLY DEFER
RED;

pg_restore: from TOC entry 5258; 2606 81264 FK CONSTRAINT paiyroll_missing
paiyroll_missing_polymorphicmodel_ptr_a1958c79_fk_paiyroll_ dbcoreuser
pg_restore: error: could not execute query: ERROR:  there is no unique
constraint matching given keys for referenced table
"paiyroll_polymorphicmodel"
Command was: ALTER TABLE ONLY public.paiyroll_missing
   ADD CONSTRAINT
paiyroll_missing_polymorphicmodel_ptr_a1958c79_fk_paiyroll_ FOREIGN KEY
(polymorphicmodel_ptr_id) REFERENCES public.paiyroll_polymorphicmodel(id)
DEFERRABLE INITIALLY DEFERRED;

pg_restore: warning: errors ignored on restore: 26
========

---

From: Tom Lane <[email-masked]>
Date: Sat, 22 Jun 2024 13:18:42 -0400

>    versus a custom AWS image for the failing case.

Please go to AWS for support for custom AWS stuff.

			regards, tom lane

---

From: Ron Johnson <[email-masked]>
Date: Sat, 22 Jun 2024 17:41:51 -0400

On Sat, Jun 22, 2024 at 1:02 PM Shaheed Haque <[email-masked]>
wrote:

>    - 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.

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.

---

From: "David G. Johnston" <[email-masked]>
Date: Sat, 22 Jun 2024 23:02:55 -0700

> to hallucinate the duplicate records?

To tie the other comments to your description: you took/have a snapshot of
the base image after you created the database and added some records to
it.  Nothing wrong here - you just need to decide how you want to deal with
the situation.

David J.