Re: pg_dump restores as expected on some machines and reports duplicate keys on others - Mailing list pgsql-general

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