public inbox for [email protected]
help / color / mirror / Atom feedFrom: Muhammad Ikram <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: Shaheed Haque <[email protected]>
Cc: pgsql-general list <[email protected]>
Subject: Re: pg_dump restores as expected on some machines and reports duplicate keys on others
Date: Sun, 23 Jun 2024 10:53:19 +0500
Message-ID: <CAGeimVq6yqLga1_L9mFt15QoMXR_H0jOK5zRrSpkPmWKStrTEw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAHAc2jeB+Mzm1Kz-4Uq2CN=EGxdJO17tE0Aqm20BQUV9rGr1QQ@mail.gmail.com>
<[email protected]>
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 <[email protected]>
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 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
> [email protected]
>
>
>
>
--
Muhammad Ikram
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: pg_dump restores as expected on some machines and reports duplicate keys on others
In-Reply-To: <CAGeimVq6yqLga1_L9mFt15QoMXR_H0jOK5zRrSpkPmWKStrTEw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox