PostgreSQL Cluster Switch-Over streaming issues!

How to handle replication after a failover of PostgreSQL Cluster from Primary to Secondary and back to Primary for the data streaming using Debezium and Kafka?

In a standard PostgreSQL and Debezium architecture, Debezium will not automatically pick up the new WAL and resume seamlessly after a true failover and failback. It almost always requires manual intervention or a reset.

The reason for this comes down to how PostgreSQL handles logical replication.

Ø  The Core Problem: Logical Replication Slots

Debezium does not read the raw physical WAL files directly. It relies on a Logical Replication Slot (usually using the pgoutput plugin) created on the source database.

1.     Slots are Node-Specific: By default, logical replication slots are tied exclusively to the primary server. They are not physically replicated to the DR standby node alongside the data.

2.     The Failover (To DR): When you failed over to the DR node, that DR database did not have Debezium's logical replication slot. The Debezium connector immediately threw an error and failed.

3.     The Timeline Change: When a DR node is promoted to Primary, PostgreSQL increments its "timeline."

4.     The Failback (To Primary): When you failed back to the original primary, the primary had to reconcile with the DR node's new timeline. In most failback scenarios (especially if pg_rewind or a fresh backup was used), the original logical replication slot is destroyed or rendered invalid because the Log Sequence Numbers (LSNs) no longer perfectly align.

Ø  How to Resolve and Reset

If the Debezium connector is currently in a FAILED state, then we will likely need to reset it. Here is the standard recovery path:

ü          Step 1: Check the Slot on the Primary First, verify if the slot survived the failback on the source PostgreSQL database:

Query:

SELECT slot_name, plugin, slot_type, active, restart_lsn

FROM pg_replication_slots

WHERE slot_name = 'your_debezium_slot_name';

·       If the query returns zero rows, the slot was destroyed.

·       If it returns a row but active is false and Debezium refuses to connect, the LSNs are out of sync.

ü          Step 2: Drop the Corrupted Slot (If it exists) If the slot is there but broken, we must drop it manually so Debezium can recreate it:

Query:

               SELECT pg_drop_replication_slot('your_debezium_slot_name');

ü          Step 3: Reset the Debezium Connector Because the LSN pointers have changed and the slot is gone, we cannot just restart the connector. 

            We have to force Debezium to take a new initial snapshot to ensure zero data loss.

·       Delete the connector's offsets in the Kafka Connect offsets topic (or via the Kafka Connect REST API if you are using a tool that supports offset deletion).

·       Reconfigure the Debezium connector with snapshot.mode set to initial (or always).

·       Restart the connector. It will recreate the replication slot, perform a fresh SELECT * baseline read of your tables, and then begin streaming the new WAL changes.