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?