Re: Dupe Key Violations in Logical Replication with PKs in Place

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.