skip to content

In PostgreSQL 17 and later, what must be configured so a logical subscriber can resume from a promoted physical standby through its replication slot?

level: seniorimportance: nice to knowfreq 24%

answer

  1. logical slots live on one node
  2. mark the slot for failover
  3. a sync worker on the standby
  4. primary waits for the standby

basics

~10 s

Create the logical slot with failover enabled, set sync_replication_slots on the standby (with primary_slot_name, hot_standby_feedback and a dbname in primary_conninfo), and list the standby's physical slot in synchronized_standby_slots on the primary.

solid answer

~50 s

A logical slot normally exists only on the primary, so after a failover the subscriber has no position on the new primary and must resync. Since 17, a slot created with `failover` enabled (`CREATE SUBSCRIPTION ... WITH (failover = true)` or the `failover` argument of `pg_create_logical_replication_slot`) can be copied to a physical standby. On the standby, `sync_replication_slots = on` starts the slotsync worker; it requires `primary_slot_name`, `hot_standby_feedback = on`, a `dbname` in `primary_conninfo`, and `wal_level = logical`. On the primary, list the standby's physical slot in `synchronized_standby_slots`, so logical walsenders never send changes the standby has not received. Synced copies show `synced = true` and cannot be used or dropped on the standby. After promotion only persistent synced slots work; disable the subscription before promoting, then point it at the new primary with `ALTER SUBSCRIPTION ... CONNECTION`.

code

ini · 9 lines
ini
# primary postgresql.conf
synchronized_standby_slots = 'standby1_slot'

# standby postgresql.conf
primary_conninfo = 'host=10.0.0.5 user=replicator dbname=postgres'
primary_slot_name = 'standby1_slot'
hot_standby_feedback = on
sync_replication_slots = on
wal_level = logical

go deeper

for a junior

Recall that logical slots do not follow a failover by themselves and that PostgreSQL 17 added failover slots to fix that.

for a middle

Explain the settings on each side: the failover flag, sync_replication_slots and its prerequisites on the standby, synchronized_standby_slots on the primary.

for a senior

Run the failover safely: confirm synced persistent slots, disable subscriptions, promote, alter the connection, and watch for refused syncs.

for a principal

Weigh the added logical-replication latency and shutdown coupling against avoiding full resyncs of large subscribers after every failover.

## The gap failover slots close A PostgreSQL **logical replication slot** records how far a logical consumer (a subscription or a change-data-capture tool) has read, and pins the WAL and catalog rows it still needs. Physical streaming replicates data, not slots: a standby's `pg_replslot` does not receive the primary's logical slots. So after a standby is promoted, the subscriber has no slot on the new primary. Creating a fresh slot there would start from "now" and silently skip changes, so the honest recovery was a full resync. Before 17, core PostgreSQL had no mechanism for this; HA managers filled the gap in their own ways (Patroni's handling belongs to its own leaf). PostgreSQL 17 added **failover slots**: logical slots marked for synchronization to physical standbys. ## What to configure **On the primary** 1. Create the logical slot with failover enabled: `CREATE SUBSCRIPTION ... WITH (failover = true)` on the subscriber (the default is `false`), or `pg_create_logical_replication_slot(..., failover => true)`. The `failover` column of `pg_replication_slots` shows the flag. 2. Set `synchronized_standby_slots` to the **physical slot** the standby streams from. Logical walsenders then send decoded changes only after that standby has confirmed receiving the WAL, so a subscriber can never be ahead of the standby that will become the next primary. **On the physical standby** - `sync_replication_slots = on` (default off; reload) starts the **slotsync worker**, which copies failover slots periodically. - `primary_slot_name`: synchronization requires a physical slot between primary and standby. - `hot_standby_feedback = on`, so the primary keeps the catalog rows the synced slot will need. - a `dbname` in `primary_conninfo`, because the worker queries slot state from a database connection. - `wal_level = logical` on the standby: the source refuses slot synchronization below it (`slotsync.c`), a requirement the documentation section does not spell out. | Setting | Where | Default | Role | |---|---|---|---| | `failover` slot property | primary slot / subscription | `false` | marks the slot for syncing | | `synchronized_standby_slots` | primary | empty | logical senders wait for these standbys | | `sync_replication_slots` | standby | `off` | starts the slotsync worker | | `primary_slot_name` | standby | empty | physical slot required for sync | | `hot_standby_feedback` | standby | `off` | protects catalog rows for synced slots | ## Reading the result - On the standby, `pg_replication_slots.synced = true` marks a copied slot. It **cannot be used for decoding or dropped** there (`This replication slot is being synchronized from the primary server`). - A sync can be refused: if the standby has already removed WAL or catalog rows the remote slot still needs, the log shows `could not synchronize replication slot` and the copy stays temporary until the consumer advances the primary's slot. - `pg_sync_replication_slots()` runs one sync by hand; the docs reserve it for testing and debugging. ## At failover 1. Only slots that were **persistent and `synced = true`** on the standby before failover can be used afterwards; a temporary synced slot cannot. 2. Disable the subscription before promoting, so it cannot keep reading from an old primary that comes back. 3. Promote the standby (promotion mechanics are a sibling leaf's subject). 4. Point the subscription at the new primary with `ALTER SUBSCRIPTION ... CONNECTION`, then enable it. ## Costs and limits - **Latency**: logical consumers now wait for the listed standby, so a lagging or down standby stalls logical replication; a slot listed in `synchronized_standby_slots` that does not exist or is invalidated stops it entirely. - **Shutdown**: the primary does not finish shutting down until those standbys confirm the latest flushed WAL. - **Coverage**: only logical slots with `failover` set are synced; physical slots and plain logical slots are not. ## Checking readiness before a planned switchover A failover slot that is configured but never verified is a common surprise. Before a planned switchover, check on the standby that each failover slot appears with `synced = true`, that its `inactive_since` is NULL (on a synced slot it records when synchronization was last interrupted, and stays NULL while syncing has never stopped), and that the standby log shows no repeated `could not synchronize replication slot` lines. On the primary, confirm that the physical slot named in `synchronized_standby_slots` is active and not invalidated, because a missing or invalidated slot there halts every logical consumer.

  • Why does omitting synchronized_standby_slots risk data loss after failover?
    Without it, a logical walsender can send a change to the subscriber before the standby has received that WAL. If the primary then fails, the promoted standby never had that change, yet the subscriber already applied it, so the two diverge. Listing the standby's physical slot makes logical senders wait until the standby confirms, so the subscriber is never ahead of the next primary.
  • The standby log repeats 'could not synchronize replication slot'. What does it mean and what fixes it?
    The remote slot needs WAL or catalog rows older than the standby still has, so persisting the copy could lose data and it stays temporary. If a consumer is actively using the slot, it advances and sync succeeds on a later cycle. If no consumer is attached, advance the primary's slot by consuming it, as the docs suggest, so its position moves past what the standby holds.

  • Physical streaming replication copies every logical slot to the standby automatically.
  • Setting failover = true on the subscription is all that is needed.
  • A synced slot on the standby can be used to offload decoding before promotion.
  • After promotion, just create a new slot on the new primary and continue.
  • synchronized_standby_slots is set on the standby to choose which slots to copy.

Assumes PostgreSQL 18; failover slots, sync_replication_slots, synchronized_standby_slots and the synced column arrived in 17, and 16 and earlier have no core slot synchronization.