Dupe Key Violations in Logical Replication with PKs in Place

From: Don Seiler <don(at)seiler(dot)us>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: Dupe Key Violations in Logical Replication with PKs in Place
Date: 2023-11-14 15:44:16
Message-ID: CAHJZqBC_mnKAH_vwep8cmVqyLhnj1CN+tc36kt73VBXpK_Etsg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Good morning,

I'm in the midst of a logical replication migration, using PG native
logical replication from PG 12 on Ubuntu 18.04 to PG 15 on Ubuntu 22.04.
All PG packages are from the PGDG apt repo.

Things had been going smoothly for the most part over the past week,
however in the past 24 hours I've had the subscribers error out (I have
disable-on-error set) on 3 separate tables for duplicate key violations on
INSERT statements. In all 3 cases, the table in question has a valid PK on
both the publication and subscription sides.

The record in question exists on both sides and is identical. In all 3
cases, I delete the row on the subscriber and re-enable the subscriptions.
The INSERT proceeds and inserts an identical row to the one that I just
deleted and everything proceeds happily.

I'm very confused, however, as to how this scenario is possible if I have a
PK enforced on both sides, although I believe that the publication side PK
alone should have prevented this.

--
Don Seiler
www.seiler.us

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Ron 2023-11-14 16:00:21 Re: Regarding the details of postgresql10 download
Previous Message Ramesh Penuballi 2023-11-14 15:39:32 Regarding the details of postgresql10 download