Postgres Replication Slots: Confirmed Flush LSN vs. Restart LSN - Gunnar Morling

[giscus[bot]]

Postgres Replication Slots: Confirmed Flush LSN vs. Restart LSN - Gunnar Morling

Replication slots in Postgres keep track of how far consumers have read a replication stream.
After a restart, consumers—​either Postgres read replicas or external tools for change data capture (CDC), like Debezium—resume reading from the last confirmed log sequence number (LSN) of their replication slot. The slot prevents the database from disposing of required log segments, allowing safe resumption after downtime.

In this post, we are going to take a look at why Postgres replication slots don’t have one but two LSN-related attributes: restart_lsn and confirmed_flush_lsn.
Understanding the difference between the two is crucial for troubleshooting replication issues, optimizing WAL retention, and avoiding common pitfalls in production environments.

https://www.morling.dev/blog/postgres-replication-slots-confirmed-flush-lsn-vs-restart-lsn/

[comdotlinux]

Nice article, and love the details about the pg_walinspect extension. We have an issue with debezium that might be in the same ballpark. Whenever we upgrade our PostgreSQL version we have to throw away all data in kafka (we use redpanda but I suspect it doesn't matter here) and start the CDC with the initial snapshot again before starting to send the usual CDC crud operations. Is there something in the PostgreSQL logical replication slot that we need to configure or something that we need to preserve on debezium? This is puzzling because if we start to rely on CDC for information sent to customers we will have to build logic to skip all records that were already sent by storing the offset in another data store or even a kafka topic.

[ugurozgen]

Hey, thanks for the deep dive.

Have you ever encountered an issue where confirmed_flush_lsn keeps advancing, but restart_lsn remains unchanged? The connector is healthy, data is continuously being published to Kafka topics, and the replication slot is active, but PostgreSQL keeps retaining old WAL files instead of releasing them.

I'm trying to understand what could cause the slot to hold onto WAL segments even though consumption appears to be progressing normally.