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

On Tue, Nov 14, 2023 at 9:44 AM Don Seiler <don(at)seiler(dot)us> wrote:

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

Well this looks to be human error/cause after all. I made the mistake of
announcing the upcoming migration and one eager developer connected to the
new/subscription DB and ran some inserts (also running them on the
old/publication DB). The inserts were all in one transaction, and look to
be responsible for all 3 of duplicate key incidents.

Lesson learned would be that I should have disabled HBA to our
apps/developers until the maintenance window and migration are over if I
don't want them to connect.

--
Don Seiler
www.seiler.us