Why is Debezium looking for old LSN?

[Don Seiler]

PG 15, Debezium 3.6.0 Final, Confluent Kafka (still). I did also post this to the zulip forum here, I understand it may be bad form to double-post but we're getting quite frazzled.

We have two debezium apps, each pointing to a completely different PG instance (separate hosts, etc). We performed another failover last night. One of the debezium apps started up just fine, the other failed with the "last recorded offset is no longer available on the server" error. It was looking for an LSN that was from about 15 minutes prior the failover.

Here is our failover process:

1. stop "producer" apps that create the data debezium is pushing to Kafka.
2. wait a few minutes for debezium to process any remaining records. (we usually give it 5 minutes, on this non-prod environment it should certainly have very little to do)
3. stop the debezium apps
4. stop the PG primary DB instances (~15 minutes will have passed since stopping debezium)
5. promote the PG DR instances
6. start the debezium apps (~15-30 minutes will have passed since promoting PG DR instance)
7. ONLY IF DEBEZIUM APPS ARE HEALTHY: start producer apps

Last night at step 6 is when we observed the problem so we held off.

I'm trying to play out how it would be that the one app is looking for an old record. Last night we stopped debezium around 19:14, then stopped PG around 19:36. The LSN that debezium asked for after restarting was in a WAL file that logs show was archived around 19:15. The only theory I have is that Debezium somehow had not finished processing its queue to send to Kafka. What's odd is that the other app is much busier and didn't have any such problem (if that is indeed the case).

Are there any sort of checks or metrics exposed that we can tell if Debezium is still processing or if it's otherwise idle? Any other possible causes? If we can be certain that all data was processed, then there's no data lost and we can just set offset.mismatch.strategy to trust_slot again as before. But ideally we shouldn't see this problem in a planned DR failover unless our process is still flawed here.

[Don Seiler]

I left out what is no-doubt an important part of this process in that we're also failing over confluent kafka clusters. We mirror all the topics and run a script to break the mirrors after the PG instances are promoted but before any apps are started. So like a step 5.5. Then in a post-failover step we reset the old primary cluster to mirror the new primary cluster, similar to what we'd do with the PG DBs to have the old primary instances now be the DR replicas.

There can be Kafka mirroring lag but in the case of yesterday, it was over an hour after stopping the apps and PG before we performed the Kafka failover. Any lag should have definitely been resolved in that time, certainly in a non-prod low-traffic environment. We ran checks prior to the DR window starting and lag was 0 as far as we could tell.

Also, last year we did not see these problems in our failovers. We were on Debezium 3.1.3.Final then, versus 3.6.0.Final now. Would there possibly be any change between those that might warrant us to change our failover process and/or expectations?

[Chris Cranford]

Hi Don -

There have been a few changes with Debezium since 3.1.3.Final, particularly DBZ-9641 [1] and DBZ-9688 [2], both which influence how offsets are managed.

That said, the likely root cause is PG 15. Failover slots (`slot.failover` in Debezium and `sync_replication_slots` in PG) require PG17+. Local decoding on a standby only arrived in PG 16, so on PG15, the logical slot your DR instance has cannot be fully synchronized with the old primary, it must be created fresh either by your promotion tooling or Debezium. A freshly created logical slot gets `confirmed_flush_lsn` set to the server's current WAL position at creation time. On a just promoted standby, that position by definition will always be ahead of wherever Debezium was at 19:14, hence the offset behind slot and warning.

As Vojta mentioned on Zulip, we'd need to see the logs, but I'd also go a step further and say we need the configuration as well. 

My recommendation here for seamless cut-overs, moving to PG17 is the ideal avenue so that PostgreSQL manages this all for you, without any concern for desyncs.

-cc

[1]: https://issues.redhat.com/browse/DBZ-9641
[2]: https://issues.redhat.com/browse/DBZ-9688

NOTICE: This electronic mail message and any files transmitted with it are intended
exclusively for the individual or entity to which it is addressed. The message,
together with any attachment, may contain confidential and/or privileged information.
Any unauthorized review, use, printing, saving, copying, disclosure or distribution
is strictly prohibited. If you have received this message in error, please
immediately advise the sender by reply email and delete all copies.

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/debezium/b19eaff4-5d0a-433e-9855-48b7176ad194n%40googlegroups.com.

[Don Seiler]

Good morning Chris.

I guess what I don't understand then is why we only saw this issue with one of the Debezium apps. As I mentioned, the other one started up just fine with no complaints. so it presumably found the LSN that it was looking for after the failover on PG15 as well. I'll make note of PG17 but it is almost definitely unlikely we'll be able to upgrade those DBs before migrating these apps off of PG as planned.

I'll see if I can gather the requested info shortly.

Thanks!

Don.