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