Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sLGAe-00E7la-ON for pgsql-general@arkaria.postgresql.org; Sun, 23 Jun 2024 05:53:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sLGAc-00516s-4z for pgsql-general@arkaria.postgresql.org; Sun, 23 Jun 2024 05:53:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sLGAb-00516e-Iu for pgsql-general@lists.postgresql.org; Sun, 23 Jun 2024 05:53:46 +0000 Received: from mail-lj1-x22f.google.com ([2a00:1450:4864:20::22f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sLGAU-002fsH-Uv for pgsql-general@lists.postgresql.org; Sun, 23 Jun 2024 05:53:44 +0000 Received: by mail-lj1-x22f.google.com with SMTP id 38308e7fff4ca-2ec5779b423so7099011fa.0 for ; Sat, 22 Jun 2024 22:53:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1719122017; x=1719726817; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=PN6qPGgg0OsNoo2pF+5Tuu6de3ebPOLCjlZh/2TFeq8=; b=X0fXC/WsJHPbWH/X7O0BbnyDJMh8QkXJRTelfqeigSwNpj1xAyN2imKxRkPh5hBcZz YWnZhiL2Iw56vEjiMwXoAsakkF0ba8NN09umcpiZ3L3gPqpH9zC+bUUwWT3jPA/ex+Cp XaSBRd7Dzt2deUnq7A5wPU+r6krWwZUj0VuudJHazH09qheLbJLCN5ZZfxKcy/AHZXGg Kffqg1+gquLW0RsM99SmOz+5a+7K+7MXYVkoAPcitXFzcxnhvxs8akUB8ZCc6AcOgLr5 99j9XAbt8o6SaTm9ub71YRhJOOftbE3zawGermi41YgfLTCLENejUjCV/HDiG3uQ96/q 5pOQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1719122017; x=1719726817; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=PN6qPGgg0OsNoo2pF+5Tuu6de3ebPOLCjlZh/2TFeq8=; b=bQyyk73vcL+Y0W6YtV5pDlc/QgBA8toqk7mQ0eFA7fXV69IdEnH319d0h3ru+12WlQ /pWY24XpV9Ti/eZPiiDnhw/qiloopI52DjwmJzDouVPXDMIxUQTXCOY7W84tkK6I3gkq lo2dKKdap25XPoGXkcwonMh7wQPbXKWcEWYHES6XikAqRUt46aphDHJvZIcQBBnJrHj3 3prkt9XI11dz0TXNGOyfrsCx8s7pBTKmHOEg7ZpCfjTMbJtVymg0IKUnjPN0lEkFnIqo NGAKBZi6WA/Rxyzy0XJQN18ClzyUbGm8ZIjXcBHPWtbnlSFbHNt8tVa5DozgVWAhHNJx +cKg== X-Forwarded-Encrypted: i=1; AJvYcCWGldfvoCFVB5Tk/5w97yKs3umIqZzq1qYwK78heUTfb+/Vubw6Lby3ZQp+g9jTNFMJDCu1rGPgkFU7DnyrhLdpX55biJV5loiGT1gp4Yf+xrQX X-Gm-Message-State: AOJu0YzVm+pFPRJ0D7n+a+1FZ58z9dFchzE3S5pmC33aT87stICAPGEL lwTCIIHQzR/sl2PgjujiYivf5bFkMaQx7HKwUDNQJ73ioNJgsIbX5tsmF639xKYY0JpYgTZrVP3 Z0UxEJbOpYeZfVTFMugRfU6W4y2s= X-Google-Smtp-Source: AGHT+IEZk0IFmtWtTJO/geHDxIRy4aerapGF24IEVL88h8cCiD4wd2lyGHoU0TuLIjTkNA8CLbwgI3t4E6IjOoART98= X-Received: by 2002:a19:8c17:0:b0:52c:dd7d:3fd4 with SMTP id 2adb3069b0e04-52ce061afadmr1192731e87.25.1719122016269; Sat, 22 Jun 2024 22:53:36 -0700 (PDT) MIME-Version: 1.0 References: <2fa6e6cb-a220-4de0-b08f-6ac4cca667af@aklaver.com> In-Reply-To: <2fa6e6cb-a220-4de0-b08f-6ac4cca667af@aklaver.com> From: Muhammad Ikram Date: Sun, 23 Jun 2024 10:53:19 +0500 Message-ID: Subject: Re: pg_dump restores as expected on some machines and reports duplicate keys on others To: Adrian Klaver Cc: Shaheed Haque , pgsql-general list Content-Type: multipart/alternative; boundary="0000000000001afb5d061b8849b3" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001afb5d061b8849b3 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFAM Adrian Klaver 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 b= e > > 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 i= n > > detail below), but in summary, 3 distinct "child" tables complain o= f > > 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: > > o 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 > > 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 > > > > --=20 Muhammad Ikram --0000000000001afb5d061b8849b3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi=C2=A0 Shaheed,

As pointed above by A= drian 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 restor= ation 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=E2= =80=AFAM Adrian Klaver <adr= ian.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 fai= l
> with reports of duplicate keys on other database instances:
>
>=C2=A0 =C2=A0* My deployments are always a pair, one "logic VM&quo= t; for Django etc and
>=C2=A0 =C2=A0 =C2=A0one "RDS instance". The psql client runs = on the logic VM. The
>=C2=A0 =C2=A0 =C2=A0Postgres version is the same in all cases; psql rep= orts:
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0o psql (14.12 (Ubuntu 14.12-0ubuntu0.22.04.1= ), server 14.9)
>
>=C2=A0 =C2=A0* The pg_restore is done using the same script in both cas= es.
>=C2=A0 =C2=A0* In the failing cases, there are always the same 26 error= s (listed in
>=C2=A0 =C2=A0 =C2=A0detail below), but in summary, 3 distinct "chi= ld" tables complain of
>=C2=A0 =C2=A0 =C2=A0a duplicate id=3D1, id=3D2 and id=3D3 respectively.=
>=C2=A0 =C2=A0* These "child" tables are FK-related via some i= ntermediate table to a
>=C2=A0 =C2=A0 =C2=A0top level table. They form a polymorphic set. There= are other
>=C2=A0 =C2=A0 =C2=A0similar child tables which do not appear to be affe= cted:
>=C2=A0 =C2=A0 =C2=A0 =C2=A0o polymorphicmodel
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0+ companybankdetail
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0# companybankdet= ailde
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0# companybankdet= ailgb=C2=A0 <<< 1 duplicate, id=3D2
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0# companybankdet= ailus
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0+ companypostaldetail
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0# companypostald= etailde
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0# companypostald= etailgb=C2=A0 <<< 1 duplicate, id=3D1
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0# companypostald= etailus
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0+ companytaxdetail
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0# companytaxdeta= ilde
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0# companytaxdeta= ilgb=C2=A0 <<< 1 duplicate, id=3D3
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0# companytaxdeta= ilus
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0+ ...
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0+ several other hierarchies, a= ll error free
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0+ ...
>=C2=A0 =C2=A0* I've looked at the dumped NNNN.dat files but they co= ntain no duplicates.
>=C2=A0 =C2=A0* The one difference I can think of between deployment pai= rs which
>=C2=A0 =C2=A0 =C2=A0work ok, and those which fail is that the logic VM = (i.e. where the
>=C2=A0 =C2=A0 =C2=A0psql client script runs) is the use of a standard A= WS ubuntu image
>=C2=A0 =C2=A0 =C2=A0for the OK case, versus a custom AWS image for the = failing case.
>=C2=A0 =C2=A0 =C2=A0 =C2=A0o The custom image is a saved snapshot of on= e created using the
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0standard 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.klave= r@aklaver.com





--
Muhammad Ikram

--0000000000001afb5d061b8849b3--