pg_dump restores as expected on some machines and reports duplicate keys on others
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
========
Shaheed Haque <shaheedhaque@gmail.com> writes:
- 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.
Please go to AWS for support for custom AWS stuff.
regards, tom lane
On Sat, Jun 22, 2024 at 1:02 PM Shaheed Haque <shaheedhaque@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.
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@aklaver.com
Hi Shaheed,
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.
Regards,
Muhammad Ikram
Bitnine Global.
On Sun, Jun 23, 2024 at 2:59 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
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 noduplicates.
* 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@aklaver.com
--
Muhammad Ikram
On Saturday, June 22, 2024, Shaheed Haque <shaheedhaque@gmail.com> 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.
- 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?
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.
On Saturday, June 22, 2024, David G. Johnston <david.g.johnston@gmail.com>
wrote:
On Saturday, June 22, 2024, Shaheed Haque <shaheedhaque@gmail.com> 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.
- 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?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.
Sorry, but to be clear/clarify - you must be using an RDS snapshot as well
as an EC2 snapshot, a fresh built RDS cluster isn’t going to be complaining
about things (especially the database) existing.
David J.
First, thanks for all the kind replies.
To my eternal shame, after spending hours trying to debug this, I found,
buried deep in one of my own initialisation scripts, the creation of a
handful of "seed" database objects which, of course, caused all my woes.
Thanks again,
Shaheed