pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDupe Key Violations in Logical Replication with PKs in Place
11+ messages / 3 participants
[nested] [flat]
* Dupe Key Violations in Logical Replication with PKs in Place
@ 2023-11-14 15:44 Don Seiler <don@seiler.us>
0 siblings, 2 replies; 11+ messages in thread
From: Don Seiler @ 2023-11-14 15:44 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Dupe Key Violations in Logical Replication with PKs in Place
@ 2023-11-14 16:04 Ron <ronljohnsonjr@gmail.com>
parent: Don Seiler <don@seiler.us>
1 sibling, 2 replies; 11+ messages in thread
From: Ron @ 2023-11-14 16:04 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 11/14/23 09:44, Don Seiler 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.
What data type(s) in those columns, and what locale on each server? Maybe
the change from 18.04/12 to 22.04/15 has caused some weirdness with
"foreign" characters.
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Dupe Key Violations in Logical Replication with PKs in Place
@ 2023-11-14 16:09 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Scott Ribe @ 2023-11-14 16:09 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> On Nov 14, 2023, at 9:04 AM, Ron <ronljohnsonjr@gmail.com> wrote:
>
> What data type(s) in those columns, and what locale on each server? Maybe the change from 18.04/12 to 22.04/15 has caused some weirdness with "foreign" characters.
Problems with the locale updates are not limited to non-ASCII characters, sorting rules involving "-" changed, for instance. When you make that OS upgrade, you really, really, need to reindex all text types.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Dupe Key Violations in Logical Replication with PKs in Place
@ 2023-11-14 16:10 Don Seiler <don@seiler.us>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Don Seiler @ 2023-11-14 16:10 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Tue, Nov 14, 2023 at 10:04 AM Ron <ronljohnsonjr@gmail.com> wrote:
>
> What data type(s) in those columns, and what locale on each server? Maybe
> the change from 18.04/12 to 22.04/15 has caused some weirdness with
> "foreign" characters.
>
The data is UUID strings (typical alphanumeric characters and hyphens)
stored in varchar(36) fields, a few booleans and an int. Nothing
exotic/weird/foreign to a US-based observer. The DBs are both using UTF8,
the LANG env is set to "en_US.utf8" in both sides as well. The primary key
fields are those varchar fields storing UUID strings, not using a sequence
or any type of auto-incrementing value.
Don.
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Dupe Key Violations in Logical Replication with PKs in Place
@ 2023-11-14 16:12 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Don Seiler <don@seiler.us>
0 siblings, 1 reply; 11+ messages in thread
From: Scott Ribe @ 2023-11-14 16:12 UTC (permalink / raw)
To: Don Seiler <don@seiler.us>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
> On Nov 14, 2023, at 9:10 AM, Don Seiler <don@seiler.us> wrote:
>
> The data is UUID strings (typical alphanumeric characters and hyphens) stored in varchar(36) fields,
Well, there you go: digits and dashes, the sort order changed.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Dupe Key Violations in Logical Replication with PKs in Place
@ 2023-11-14 16:12 Don Seiler <don@seiler.us>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 11+ messages in thread
From: Don Seiler @ 2023-11-14 16:12 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
On Tue, Nov 14, 2023 at 10:10 AM Scott Ribe <scott_ribe@elevated-dev.com>
wrote:
> > On Nov 14, 2023, at 9:04 AM, Ron <ronljohnsonjr@gmail.com> wrote:
> >
> > What data type(s) in those columns, and what locale on each server?
> Maybe the change from 18.04/12 to 22.04/15 has caused some weirdness with
> "foreign" characters.
>
> Problems with the locale updates are not limited to non-ASCII characters,
> sorting rules involving "-" changed, for instance. When you make that OS
> upgrade, you really, really, need to reindex all text types.
>
That's precisely why I'm using logical replication for this migration. The
indexes are new on the subscriber server. Re-indexing should only be
necessary if I were to do physical replication/restore onto a 22.04 server,
no?
Don.
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Dupe Key Violations in Logical Replication with PKs in Place
@ 2023-11-14 16:14 Don Seiler <don@seiler.us>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 11+ messages in thread
From: Don Seiler @ 2023-11-14 16:14 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
On Tue, Nov 14, 2023 at 10:12 AM Scott Ribe <scott_ribe@elevated-dev.com>
wrote:
> > On Nov 14, 2023, at 9:10 AM, Don Seiler <don@seiler.us> wrote:
> >
> > The data is UUID strings (typical alphanumeric characters and hyphens)
> stored in varchar(36) fields,
>
> Well, there you go: digits and dashes, the sort order changed.
But this is a logical replica. That problem and its re-indexing solution
should only apply to physical replicas.
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Dupe Key Violations in Logical Replication with PKs in Place
@ 2023-11-14 16:15 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Don Seiler <don@seiler.us>
0 siblings, 0 replies; 11+ messages in thread
From: Scott Ribe @ 2023-11-14 16:15 UTC (permalink / raw)
To: Don Seiler <don@seiler.us>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
> On Nov 14, 2023, at 9:12 AM, Don Seiler <don@seiler.us> wrote:
>
> That's precisely why I'm using logical replication for this migration. The indexes are new on the subscriber server. Re-indexing should only be necessary if I were to do physical replication/restore onto a 22.04 server, no?
As far as I know, you are right about that--I missed the part where you said logical replication onto new clusters.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Dupe Key Violations in Logical Replication with PKs in Place
@ 2023-11-14 17:54 Don Seiler <don@seiler.us>
parent: Don Seiler <don@seiler.us>
1 sibling, 1 reply; 11+ messages in thread
From: Don Seiler @ 2023-11-14 17:54 UTC (permalink / raw)
To: pgsql-admin
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.
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Dupe Key Violations in Logical Replication with PKs in Place
@ 2023-11-14 18:02 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Don Seiler <don@seiler.us>
0 siblings, 1 reply; 11+ messages in thread
From: Scott Ribe @ 2023-11-14 18:02 UTC (permalink / raw)
To: Don Seiler <don@seiler.us>; +Cc: pgsql-admin
> On Nov 14, 2023, at 10:54 AM, Don Seiler <don@seiler.us> wrote:
>
> 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.
So this would be about the 2 billionth or so episode in the DBA series titled "shit happens" ;-) Glad you figured it out.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Dupe Key Violations in Logical Replication with PKs in Place
@ 2023-11-14 18:19 Don Seiler <don@seiler.us>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 11+ messages in thread
From: Don Seiler @ 2023-11-14 18:19 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin
On Tue, Nov 14, 2023 at 12:02 PM Scott Ribe <scott_ribe@elevated-dev.com>
wrote:
> > On Nov 14, 2023, at 10:54 AM, Don Seiler <don@seiler.us> wrote:
> >
> > 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.
>
> So this would be about the 2 billionth or so episode in the DBA series
> titled "shit happens" ;-) Glad you figured it out.
I'm not sure why his connection didn't show up in my PG logs. I log
connects and disconnects but I don't see anything coming from our apps or
VPN network CIDRs, that would have been an important lead early on.
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2023-11-14 18:19 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-14 15:44 Dupe Key Violations in Logical Replication with PKs in Place Don Seiler <don@seiler.us>
2023-11-14 16:04 ` Ron <ronljohnsonjr@gmail.com>
2023-11-14 16:09 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-11-14 16:12 ` Don Seiler <don@seiler.us>
2023-11-14 16:15 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-11-14 16:10 ` Don Seiler <don@seiler.us>
2023-11-14 16:12 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-11-14 16:14 ` Don Seiler <don@seiler.us>
2023-11-14 17:54 ` Don Seiler <don@seiler.us>
2023-11-14 18:02 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-11-14 18:19 ` Don Seiler <don@seiler.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox