On Tue, Nov 14, 2023 at 9:44 AM Don Seiler <don@seiler.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.
