Start for Free
Back to Blog

PostgreSQL 17 Logical Failover Slots: How CDC Survives a Primary Promotion

Data know-how
Robin Tang

What is a replication slot?

A replication slot in Postgres tracks a client's exact position in the Write-Ahead Log (WAL). It acts as a persistent bookmark, and informing the primary database not to delete WAL until the client has successfully processed it.

SQL
SELECT
  slot_name,
  slot_type,
  active,
  -- [restart_lsn] The last LSN that was used to restart the slot.
  restart_lsn,
  -- [confirmed_flush_lsn] The last LSN that was confirmed by the client.
  confirmed_flush_lsn
FROM pg_replication_slots;

What is a failover replication slot?

In Postgres 17, a failover option was added to logical replication slots. This enables the replication slot to be synced from the primary to a physical standby. After a failover event, the primary and standby responsibilities are swapped - the primary becomes the standby and the standby becomes the primary.

Things to note:

  1. The slot preserves PostgreSQL replication position.
  2. It does not preserve the CDC consumer's existing TCP connection.
  3. The consumer still needs a stable writer address and durable application offset state.
SQL
-- https://www.postgresql.org/docs/current/functions-admin.html
SELECT slot_name, lsn
FROM pg_create_logical_replication_slot(
  'artie_failover_slot',
  'pgoutput',
  -- [failover] Enable failover synchronization.
  false,
  -- [two_phase] Create a two-phase slot.
  false,
  -- [failover] Enables failover synchronization.
  true
);

How does this work with different providers?

  • RDS for PostgreSQL 17: yes for read replica promotion, with RDS parameter-group names (rds.logical_replication, sync_replication_slots, synchronized_standby_slots, rds.logical_slot_sync_dbname).
  • Aurora PostgreSQL: Online failovers are automatically supported in Aurora, you don't need to do anything.
  • Azure Database for PostgreSQL flexible server 17+: yes for HA failover when sync_replication_slots and hot_standby_feedback are on and the slot is created with failover = true.

How do we trigger a failover replication slot? How can we test this?

The primary needs logical WAL and a physical replication slot that PostgreSQL uses as the synchronization boundary.

Text
# Primary
wal_level = logical
max_wal_senders = 10
max_replication_slots = 10
synchronized_standby_slots = 'standby_physical_slot'

The standby needs logical WAL, the physical slot, hot standby feedback, and the slot-sync worker.

Text
# Standby
wal_level = logical
primary_slot_name = 'standby_physical_slot'
hot_standby_feedback = on
sync_replication_slots = on

Verify the pre-promotion standby slot state before triggering failover.

SQL
SELECT
  slot_name,
  failover,
  synced,
  temporary,
  invalidation_reason,
  confirmed_flush_lsn,
  restart_lsn
FROM pg_replication_slots
WHERE slot_name = 'artie_failover_slot';
  • The readiness condition before promotion is:
    • failover = true
    • synced = true
    • temporary = false
    • invalidation_reason IS NULL
  • The local failover drill:
    • Starts a PostgreSQL 17 primary and a physical standby
    • Creates artie_failover_slot with failover = true.
    • Starts Artie Reader and verifies the slot is active.
    • Start a separate process to continue to write 1 record to the database every second
    • Verifies the standby slot is synchronized.
    • Stops the old primary and promotes the standby.
    • Lets Reader restart automatically and reconnect.
    • Verifies the complete ID range downstream in Kafka.

How does Artie handle this?

  • Artie Reader can consume an existing PostgreSQL 17 failover-enabled pgoutput slot.
  • Reader is configured with the exact slot and publication name.
  • Reader will persist its offset onto a mounted PVC (in Kubernetes).
  • Once the failover happens, Reader's connection will be temporarily disrupted and Reader will restart.
  • Upon DNS resolution and Reader successfully restarts, it will take the offset from the prior step and continue streaming changes from the new primary.

Related articles