PlanetScale for Postgres is here. Request early access
Log in|Book a meeting
Navigation

Blog|Engineering

Postgres High Availability with CDC

By Sam Lambert |

Change Data Capture (CDC) from your database is a common practice for most businesses. Postgres’ replication design adds high-availability (HA) constraints and operational coupling in ways that are impractical.

The Postgres approach

Start with a standard HA Postgres cluster topology. One primary. Two standbys configured for semi-synchronous replication. A CDC client reading a logical replication slot via pgoutput. WAL level is logical on the primary, and the standbys are configured in synchronous_standby_names = 'ANY 1 (r1, r2)' so commits on the primary wait for at least one standby to flush. The CDC client doesn’t stream continuously; it polls every few hours.

How Postgres moves data across this cluster:

  • The primary emits WAL
  • Physical standbys stream and apply WAL
  • The CDC client reads a logical slot that decodes WAL into row changes

The critical detail is that the logical replication slot is a durable, primary-local object that carries two pieces of state: the oldest WAL the slot requires (restart_lsn) and the most recent position the subscriber has confirmed (confirmed_flush_lsn). The presence of that slot pins WAL on the primary until the CDC client advances. If the client lags, WAL accumulates. That’s expected. The brittle part shows up when you try and achieve HA.

Postgres 17 introduced logical replication failover, so slot state can be synchronized to promotion candidates, but slot eligibility on the replica has caveats; A standby only becomes eligible to carry the slot after the subscriber has actually advanced the slot at least once while that standby is receiving the slot metadata. This guard exists to prevent promoting a node that has never observed real slot progress and would present an inconsistent stream to the subscriber. In practice, if the CDC client hasn’t connected in hours, any freshly added or recently restarted standby won’t be eligible. Attempting a controlled primary promotion becomes impossible without breaking the CDC stream because no replica candidates have an eligible slot.

Failover readiness for a logical slot is determined by three conditions on the standby:

  • The slot is synchronized on the standby, synced = true.
  • The slot's position in the WAL is consistent with the position of the standby, not too far behind or too far ahead.
  • The slot is persistent and not invalidated, temporary = false AND invalidation_reason IS NULL

Explicit failure scenarios:

  1. During a CDC quiet period, logical slots on standbys may remain in temporary status due to position inconsistencies. If forced failover occurs, the temporary slots are not failover-ready. The CDC stream breaks, requiring connector reinitialization and snapshot reload.
  2. Replacing replicas: You add new replicas (fresh pg_basebackup) and plan to retire the old ones. Each new standby begins synchronizing slot metadata from the primary but, by design, starts at a conservative point (older XID/LSN) and won’t consider the slot synchronized until it has seen the subscriber advance. If the CDC client polls every 6 hours, all new replicas remain ineligible for promotion until that polling event occurs. Any switchover in the interim either stalls or breaks CDC exactly like case 1.
  3. Not just CDC. Any replication client backed by a slot can create a similar problem. A physical standby connected through a physical slot that stops pulling WAL will pin restart_lsn indefinitely. That doesn’t directly affect slot eligibility the way logical failover slots do, but it can fill the primary’s WAL volume and trip the cluster into write unavailability, emergency failover, or drop the slot entirely if the maximum WAL size has been reached. The core fragility is the same: progress of the slowest slot determines how far the system can move without manual intervention.

This happens due to the way Postgres records replication progress. The WAL is a physical redo log for crash recovery and physical standby replication. The fact that a downstream consumer needs certain WAL retained is tracked in a primary-local catalog state inside pg_replication_slots. That state advancement only occurs when the consumer connects and acknowledges data. Historically, this state never rode along in WAL, so standbys had no authoritative copy. Postgres 17’s failover slots serialize slot metadata into WAL so candidates can mirror it, but they still refuse to declare a node eligible until a real subscriber has advanced the slot at least once while that node is following along. This preserves exactly-once CDC semantics at the expense of HA flexibility.

The MySQL approach

MySQL’s approach doesn’t create this coupling. MySQL’s binary log is an action log. Every transaction carries a GTID. Replicas with log_replica_updates=ON re-emit transactions they apply into their own binlogs, preserving GTID continuity. A CDC connector records the last committed GTID set. On reconnect it tells any suitable server, “resume from this GTID.” If the binlog containing that GTID still exists, streaming continues with no slot object and no eligibility gate.

Failover looks like:

  • Promote a replica
  • Point the connector at any replica and it resumes from it's GTID position

The success of this operation is only determined by whether binlog retention covers the downtime, not by whether the connector recently polled. A lagging consumer can’t stall switchover; at worst, if binlogs are purged past the last GTID the connector processed, the connector must resnapshot but HA completes immediately. You can even recover binlogs from other sources and apply those.

Which is better for HA?

Put the two designs side by side in the same topology:

Postgres: primary P, synchronous standbys R1 and R2, CDC slot S on P. Commits require ANY 1 flush by R1 or R2. CDC polls every 6 hours. New R3 is added during maintenance. Until the CDC client advances S, R3 is not eligible to carry S after promotion; neither is R2 if it joined recently or restarted without seeing slot progress. Switchover options are to wait for CDC to advance or promote anyway and accept slot drop. This ties a write-availability action to the behavior of an external downstream system. Tight coupling.

MySQL: primary M, two replicas MR1 and MR2 with GTID and row-based binlog; log_replica_updates=ON on replicas so they have full binlog history. CDC connector persists a GTID position. Maintenance adds MR3; it catches up and emits the same GTIDs. Switchover can proceed immediately. Direct the CDC connector to any replica and it resumes from its GTID position. There is no eligibility concept because replication progress is embedded in the binlog; nothing special needs to be mirrored across nodes. A much more flexible design.

This is the brittle edge in Postgres high availability with logical consumers: slot progress is a single-node concern that must be coordinated across the cluster at failover time, and eligibility depends on subscriber behavior outside your control. Even with failover slots, eligibility deliberately waits for the subscriber to move the slot to prevent broken streams. If the subscriber is slow by design (batch CDC) or temporarily offline, you inherit either long switchover delays or intentional CDC breakage. If a physical slot backs a dormant standby, you inherit WAL growth risk on the primary and potential write outages.