MIME-Version: 1.0
References: <CAHAc2jeB+Mzm1Kz-4Uq2CN=EGxdJO17tE0Aqm20BQUV9rGr1QQ@mail.gmail.com>
In-Reply-To: <CAHAc2jeB+Mzm1Kz-4Uq2CN=EGxdJO17tE0Aqm20BQUV9rGr1QQ@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Sat, 22 Jun 2024 17:41:51 -0400
Message-ID: <CANzqJaDBC7BK_55LFpfu7MXEQ5jOV2D9jzQ_-UTtqnR9hW1+kQ@mail.gmail.com>
Subject: Re: pg_dump restores as expected on some machines and reports
 duplicate keys on others
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001e894c061b816b8e"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaDBC7BK_55LFpfu7MXEQ5jOV2D9jzQ_-UTtqnR9hW1%2BkQ%40mail.gmail.com>
Precedence: bulk

--0000000000001e894c061b816b8e
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sat, Jun 22, 2024 at 1:02=E2=80=AFPM Shaheed Haque <shaheedhaque@gmail.c=
om>
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=3D1, id=3D2 and id=3D3 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=3D2
>             - companybankdetailus
>             - companypostaldetail
>             - companypostaldetailde
>             - companypostaldetailgb  <<< 1 duplicate, id=3D1
>             - companypostaldetailus
>          - companytaxdetail
>             - companytaxdetailde
>             - companytaxdetailgb  <<< 1 duplicate, id=3D3
>             - 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 psq=
l
>    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_restor=
e
> to hallucinate the duplicate records?
>
> Encls: 26 errors as mentioned...
>
> =3D=3D=3D=3D=3D=3D=3D=3D
> 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" alread=
y
> exists
> Command was: CREATE DATABASE foo WITH TEMPLATE =3D template0 ENCODING =3D
> 'UTF8' LOCALE =3D '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.

--0000000000001e894c061b816b8e
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Sat, Jun 22, 2024 at 1:02=E2=80=AFPM S=
haheed Haque &lt;<a href=3D"mailto:shaheedhaque@gmail.com">shaheedhaque@gma=
il.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>Hi,</div><div><br></=
div><div>I am using Postgres 14 on AWS RDS and am seeing the output of pg_d=
ump be restored as expected by pg_restore on some database instances, and f=
ail with reports of duplicate keys on other database instances:</div><div><=
ul><li>My deployments are always a pair, one &quot;logic VM&quot; for Djang=
o etc and one &quot;RDS instance&quot;. The psql client runs on the logic V=
M. The Postgres version is the same in all cases; psql reports:</li></ul><u=
l><ul><li><span style=3D"font-family:monospace"><span style=3D"color:rgb(0,=
0,0);background-color:rgb(255,255,255)">psql (14.12 (Ubuntu 14.12-0ubuntu0.=
22.04.1), server 14.9)</span></span></li></ul></ul></div><div><span style=
=3D"font-family:monospace"><span style=3D"color:rgb(0,0,0);background-color=
:rgb(255,255,255)"></span></span></div><div><ul><li>The pg_restore is done =
using the same script in both cases.</li><li>In the failing cases, there ar=
e always the same 26 errors (listed in detail below), but in summary, 3 dis=
tinct &quot;child&quot; tables complain of a duplicate id=3D1, id=3D2 and i=
d=3D3 respectively.=C2=A0</li><li>These &quot;child&quot; tables are FK-rel=
ated via some intermediate table to a top level table. They form a polymorp=
hic set. There are other similar child tables which do not appear to be aff=
ected:</li><ul><li>polymorphicmodel</li><ul><li>companybankdetail</li><ul><=
li>companybankdetailde</li><li>companybankdetailgb=C2=A0 &lt;&lt;&lt; 1 dup=
licate, id=3D2<br></li><li>companybankdetailus<br></li></ul><li>companypost=
aldetail</li><ul><li>companypostaldetailde</li><li>companypostaldetailgb=C2=
=A0 &lt;&lt;&lt; 1 duplicate, id=3D1<br></li><li>companypostaldetailus</li>=
</ul><li>companytaxdetail</li><ul><li>companytaxdetailde</li><li>companytax=
detailgb=C2=A0 &lt;&lt;&lt; 1 duplicate, id=3D3<br></li><li>companytaxdetai=
lus</li></ul><li>...</li><li>several other hierarchies, all error free</li>=
<li>...</li></ul></ul><li>I&#39;ve looked at the dumped NNNN.dat files but =
they contain no duplicates.</li><li>The one difference I can think of betwe=
en deployment pairs which work ok, and those which fail is that the logic V=
M (i.e. where the psql client script runs) is the use of a standard AWS ubu=
ntu image for the OK case, versus a custom AWS image for the failing case.<=
/li><ul><li>The custom image is a saved snapshot of one created using the s=
tandard image.<br></li></ul></ul><div>Why should the use of one type of VM =
image versus another cause pg_restore to hallucinate the duplicate records?=
<br></div><div><br></div><div>Encls: 26 errors as mentioned...</div><div><b=
r></div><div>=3D=3D=3D=3D=3D=3D=3D=3D</div><div><span style=3D"font-family:=
monospace"><span style=3D"color:rgb(0,0,0);background-color:rgb(255,255,255=
)">pg_restore: while PROCESSING TOC:
</span><br>pg_restore: from TOC entry 5635; 1262 53894 DATABASE foo postgre=
s
<br>pg_restore: error: could not execute query: ERROR: =C2=A0database &quot=
;foo&quot; already exists
<br>Command was: CREATE DATABASE foo WITH TEMPLATE =3D template0 ENCODING =
=3D &#39;UTF8&#39; LOCALE =3D &#39;en_US.UTF-8&#39;;<br></span></div></div>=
</div></blockquote><div><br></div><div>Check <b>all</b>=C2=A0of the client =
and server encodings.</div><div><br></div><div>99.99% of the time, that&#39=
;s the problem when the same dump file fails to restore on different server=
s.</div></div></div>

--0000000000001e894c061b816b8e--