Обсуждение: pg_dump restores as expected on some machines and reports duplicate keys on others

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

• 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?

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.