0

I’m using CNPG Postgres with 2 node availability. There’s a primary and secondary. The primary uses synchronous replication. I am using Change Data Capture with debezium. It creates a replication slot, reads from the WAL, and then handles each event.

My understanding of WAL positions is that they are identified by a Logical Sequence Number (LSN).

Let’s say the latest LSN on the primary has LSN=A.

At this point, failover happens, where the secondary becomes the primary and then a new replication slot is created. The former primary hasn’t yet read LSN of A. Will the LSN on the primary (formerly secondary) still contain A?

In other words, do the LSNs match between a primary and secondary? Or do they vary per database?

Improve this question

2 Answers 2

Reset to default
1

With streaming replication, primary and standby are physical copies of each other, so they use the same log sequence numbers (LSNs).

With synchronous streaming replication, not every WAL record that exists on the primary does yet exist on the standby. But it is guaranteed that the LSN of every COMMIT record that was reported to the client as successful has already made it to the standby.

Improve this answer
-1

If the last LSN on the primary corresponds to a commit record, the secondary already has it. If it’s not a commit record, the secondary may or may not have received it yet, and any incomplete transaction will be rolled back when the secondary is promoted.

Debezium uses logical replication slots, which are asynchronous and generally slower than physical replication, so some transactions may be missed by debezium during failover. When Debezium handles replication slot creation on a new primary, the starting LSN of slot may not match the last consumed LSN. Depending on the snapshot mode, Debezium may create a new snapshot or start streaming from the starting LSN, the latter case could result in missing transactions in the target where debezium writes to .

Starting from PostgreSQL 17, this problem can be avoided by using Logical Replication Failover

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.