Replication in 17 and 18: failover slots, pg_createsubscriber and incremental backup

conceptThe concept

whyWhy it matters

Before 17, a logical slot existed only on the primary. A failover meant every subscriber and CDC pipeline lost its position and had to resynchronise, which for large tables took hours and often caused duplicate or missing events. Abandoned slots were the most common cause of a full disk. Both problems now have built-in answers, but only if you configure them.

Use caseReal-world scenario

An orders database feeds Kafka through Debezium. The team creates the Debezium slot with failover = true, sets sync_replication_slots = on and hot_standby_feedback = on on the Patroni standby, and lists the standby's physical slot in synchronized_standby_slots. When the primary fails, Patroni promotes the standby, Debezium reconnects through the same service name, and streaming resumes from the synced slot with no snapshot.

Try itExamples

Run and edit these in the interactive lesson.

SELECT name, setting
FROM pg_settings
WHERE name IN ('wal_level', 'max_replication_slots', 'max_wal_senders', 'hot_standby_feedback',
               'sync_replication_slots', 'synchronized_standby_slots', 'max_slot_wal_keep_size',
               'idle_replication_slot_timeout', 'summarize_wal')
ORDER BY name;

SELECT slot_name FROM pg_create_physical_replication_slot('standby_b', true);
SELECT slot_name, slot_type, active, failover, synced, wal_status,
       invalidation_reason, inactive_since IS NOT NULL AS idle_clock_running,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;
SELECT pg_drop_replication_slot('standby_b');

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'pg_stat_subscription_stats' AND column_name LIKE 'confl%'
ORDER BY ordinal_position;

-- primary
ALTER SYSTEM SET wal_level = logical;                        -- restart
ALTER SYSTEM SET synchronized_standby_slots = 'standby_b';   -- the failover standby's physical slot
SELECT pg_create_logical_replication_slot('orders_cdc', 'pgoutput', false, false, true);  -- last argument: failover
-- or, from a subscriber: CREATE SUBSCRIPTION ... WITH (failover = true);

-- standby (postgresql.conf)
primary_conninfo = 'host=db1 user=replicator dbname=postgres'
primary_slot_name = 'standby_b'
hot_standby_feedback = on
sync_replication_slots = on

-- on the standby, before trusting a failover
SELECT slot_name, synced, failover, conflicting FROM pg_replication_slots;

# turn a physical standby into a logical subscriber (standby stopped first)
pg_createsubscriber -D /pgdata/standby -P "host=db1 dbname=shop user=postgres" -d shop --dry-run
pg_createsubscriber -D /pgdata/standby -P "host=db1 dbname=shop user=postgres" -d shop

# incremental backups
psql -c "ALTER SYSTEM SET summarize_wal = on" -c "SELECT pg_reload_conf()"
pg_basebackup -D /backups/full -c fast
pg_basebackup -D /backups/incr1 --incremental=/backups/full/backup_manifest
pg_combinebackup /backups/full /backups/incr1 -o /restore/data

internalsUnder the hood

The slot sync worker on a standby connects to the primary (it needs dbname in primary_conninfo) and copies the state of failover-enabled logical slots, marking them synced = true. Synced slots cannot be used or dropped on the standby; after promotion they become normal slots. Because a logical consumer must not get ahead of the standby that will replace the primary, walsenders for logical slots wait until the physical slots in synchronized_standby_slots have confirmed receipt. Slots retain WAL from their restart_lsn; max_slot_wal_keep_size caps that by invalidating the slot (invalidation_reason = wal_removed), and idle_replication_slot_timeout invalidates by inactivity (idle_timeout).

Avoid / preferDo this, not that

SELECT pg_create_logical_replication_slot('orders_cdc', 'pgoutput');
-- standby without sync_replication_slots

SELECT pg_create_logical_replication_slot('orders_cdc', 'pgoutput', false, false, true);
-- standby: sync_replication_slots = on, hot_standby_feedback = on
-- primary: synchronized_standby_slots = 'standby_b'

max_slot_wal_keep_size = -1
-- a CDC connector was decommissioned; its slot is still there

max_slot_wal_keep_size = '100GB'
idle_replication_slot_timeout = '2d'
-- and alert on retained WAL per slot

PitfallsCommon mistakes

• Enabling sync_replication_slots without hot_standby_feedback or a primary_slot_name, so slots cannot sync.
• Forgetting dbname in primary_conninfo; the slot sync worker needs a database connection.
• Leaving synchronized_standby_slots pointing at a standby that has been removed, which stalls logical walsenders.
• Running pg_createsubscriber on a standby that is still running.

LimitsWhen not to use it

If your HA manager or cloud service already handles slot failover (Patroni can keep permanent slots, some managed services sync slots themselves), do not configure both; check which mechanism owns the slots. Incremental backups add a chain to manage; for small databases a nightly full backup plus WAL archive is simpler.

SecuritySecurity notes

Replication connections can read every change in the database. Use a dedicated role with REPLICATION (and only the publications it needs for logical), require TLS and scram-sha-256 in pg_hba.conf for replication entries, and restrict replication lines to the standby and CDC hosts.

ChallengeTry it yourself

SELECT slot_name FROM pg_create_physical_replication_slot('lag_check', true);
SELECT slot_name, slot_type, failover,
       invalidation_reason IS NOT NULL AS invalidated,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;
SELECT pg_drop_replication_slot('lag_check');

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• High availability and replication
• Logical replication