skip to content

Replication Slots & Retention

A PostgreSQL replication slot makes the primary keep WAL and old catalog rows until its consumer confirms them. Interviewers ask about the disk-full outage an abandoned slot causes.

part ofPostgreSQLread it →
on this pageshow

explore

questions

5

In PostgreSQL streaming replication, what does a replication slot guarantee that a standby without one lacks, and what risk does it add?

level: juniorimportance: must knowfreq 72%

answer

  1. the primary remembers the consumer
  2. checkpoints respect the oldest need
  3. persists across disconnects and crashes
  4. no upper bound by default

basics

~20 s

A PostgreSQL replication slot makes the primary keep all WAL its consumer has not yet confirmed, even while that consumer is offline, so the standby cannot fall behind recycled WAL. The risk: an abandoned slot retains WAL until the disk fills.

solid answer

~50 s

Without a slot, the primary removes or recycles old WAL at checkpoints regardless of standbys; a standby that falls far enough behind gets `requested WAL segment ... has already been removed` and must be fed from an archive or rebuilt. `wal_keep_size` only keeps a fixed amount. A slot is a named, crash-safe record on the primary of how far its consumer has got (`restart_lsn`), and checkpoints never remove WAL the slot still needs, so the standby can disconnect for hours and resume. With `hot_standby_feedback` it also holds back VACUUM on the primary while the standby is away. The cost is the same guarantee turned around: by default (`max_slot_wal_keep_size = -1`) retention is unbounded, so a standby decommissioned without dropping its slot makes `pg_wal` grow until the file system fills and the primary does a PANIC shutdown. Only temporary slots vanish on their own, at session end.

code

sql · 4 lines
sql
SELECT * FROM pg_create_physical_replication_slot('reporting_standby');

SELECT slot_name, slot_type, active, restart_lsn, wal_status
FROM pg_replication_slots;

go deeper

for a junior

Recall that a slot is a bookmark on the primary that keeps WAL its consumer still needs, and that a forgotten slot can fill the disk.

for a middle

Explain restart_lsn, how a standby uses primary_slot_name, why wal_keep_size is a fixed guess while a slot is exact, and that max_slot_wal_keep_size defaults to -1.

for a senior

Show the operating discipline: drop the slot when a standby is retired, alert on retained WAL, and decide deliberately whether to cap retention with max_slot_wal_keep_size.

for a principal

Frame the trade-off: a slot turns a replica outage into growing disk use on the primary, so choose between protecting the replica and protecting the primary per consumer.

## The problem a slot solves A PostgreSQL primary writes every change to the **write-ahead log (WAL)** in the `pg_wal` directory. At each **checkpoint** it removes or recycles segment files that crash recovery no longer needs. A **streaming standby** reads that same WAL over a replication connection, so if the standby is slow, paused or disconnected long enough, the segment it needs next may already be gone. The walsender then reports `requested WAL segment ... has already been removed`, and the standby can continue only by fetching WAL from an archive (`restore_command`) or by being rebuilt from a new base backup. There are two ways to stop that: - **`wal_keep_size`** keeps a fixed amount of extra WAL. It is a guess: too small and a long outage still breaks the standby, too large and you keep WAL nobody needs. - A **replication slot** keeps exactly what its consumer still needs, however much that turns out to be. ## What a slot is A **replication slot** is a named object on the primary, listed in `pg_replication_slots` and stored under `pg_replslot`, so it survives restarts and crashes. It records the consumer's position: - `restart_lsn` is the oldest WAL location the consumer may still need. Checkpoints do not remove WAL at or after it. - For a **physical slot** used with `hot_standby_feedback = on`, `xmin` records the standby's oldest snapshot, so VACUUM on the primary keeps rows the standby's queries may still read, even while the standby is disconnected. - A **logical slot** additionally pins `catalog_xmin` and tracks `confirmed_flush_lsn`. The slot knows nothing about whether its consumer is healthy or will ever return. It only knows the last position it was told about. | Mechanism | What it keeps | Bounded? | Survives a long outage? | |---|---|---|---| | No slot, no `wal_keep_size` | WAL needed for crash recovery only | yes | no | | `wal_keep_size` | a fixed extra amount of WAL | yes | only if the outage fits | | Replication slot | everything after `restart_lsn` | no, by default | yes | ## The risk it adds The slot's guarantee has no ceiling unless you set one. `max_slot_wal_keep_size` defaults to `-1`, which means slots may retain an unlimited amount of WAL. `max_wal_size` (default `1GB`) does not help: it is the size that triggers a checkpoint, not a limit on what a slot can pin. So the classic failure is a standby that was shut down or replaced while its slot was left behind. Nothing reads from the slot, `restart_lsn` stops moving, and `pg_wal` grows with every write until the file system is full. When the file system that holds `pg_wal` fills, PostgreSQL does a **PANIC shutdown**: committed transactions are not lost, but the database stays offline until space is freed. Slots are not dropped automatically, with one exception: a **temporary slot** is never saved to disk and is dropped when its session ends or errors. ## Using one for a standby 1. On the primary, create the slot with `pg_create_physical_replication_slot('name')`; this needs a superuser or a role with `REPLICATION`. 2. On the standby, set `primary_slot_name` to that name beside `primary_conninfo`, and reload. 3. Check `pg_replication_slots`: `active` should be true and `restart_lsn` should advance. 4. When the standby is retired, **drop its slot** with `pg_drop_replication_slot`, as part of the same change. 5. Decide on a ceiling with `max_slot_wal_keep_size`, accepting that a standby beyond it loses its slot. ## What a slot is not - It does not send WAL itself; the walsender process streams it, and the slot only records the position. - It is not a backup or an archive: once the consumer confirms WAL, the primary may remove it. - It does not limit itself: monitoring retained WAL is the operator's job, because the default is to keep everything. ## Checking slots in practice A short routine on the primary catches most slot trouble before it becomes an outage: - List every slot in `pg_replication_slots` and match each one to a consumer you can name; a slot nobody recognises is a candidate for dropping. - Watch `active`: a physical slot whose standby is running should be active almost all the time. - Watch how far `restart_lsn` trails the current WAL position with `pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)`, and alert when it grows past an amount you chose in advance. - Watch free space on the file system that holds `pg_wal`, since that is what actually runs out.

  • Why does a newly created physical slot show a NULL restart_lsn, and does it retain WAL yet?
    `pg_create_physical_replication_slot` reserves WAL only when its `immediately_reserve` argument is true; otherwise the position is reserved on the first connection from a streaming client. Until then `restart_lsn` is NULL and the slot pins no WAL. Passing `immediately_reserve => true` is useful when you create the slot before taking the base backup, so nothing the new standby needs is removed in between.
  • Is a replication slot a substitute for WAL archiving?
    No. A slot keeps WAL only until its consumer confirms it, then the primary may remove it, so it cannot support point-in-time recovery. An archive keeps WAL for restores and can also feed a standby that lost its slot through `restore_command`. Many setups use both: a slot for a standby's normal streaming and an archive for backups and as a fallback.
  • How many slots can a primary hold, and what does changing that cost?
    `max_replication_slots` sets the maximum number of slots and defaults to 10. It can only be set at server start, so raising it needs a restart. Each standby or logical consumer that uses a slot takes one, so plan the number with headroom rather than discovering the limit during a migration.

A replication slot is a bookmark that stops the library from recycling pages a reader still needs. It works perfectly while the reader comes back; a bookmark left by a reader who never returns keeps every later page on the shelf until the shelves overflow.

  • A replication slot copies WAL to the standby by itself.
  • wal_keep_size caps how much WAL a replication slot can retain.
  • max_wal_size is a hard limit, so pg_wal can never grow beyond it.
  • A persistent slot is dropped automatically when its standby disconnects.
  • Deleting old files from pg_wal by hand is a safe way to free space.
open as a page

A PostgreSQL primary filled its disk after the data-warehouse change consumer stopped; how do you confirm a replication slot caused it, recover safely, and prevent a repeat?

level: seniorimportance: must knowfreq 58%

basics

~20 s

Free or add disk space (never delete pg_wal files), start the primary, find the inactive slot whose restart_lsn trails far behind in pg_replication_slots, drop it, then checkpoint. Prevent repeats with max_slot_wal_keep_size, idle_replication_slot_timeout and retained-WAL alerts.

open as a page

In PostgreSQL, what does a physical replication slot hold back on the primary compared with a logical slot, as pg_replication_slots shows it?

level: middleimportance: should knowfreq 48%

basics

~10 s

Both slot types pin WAL from restart_lsn. A physical slot also pins xmin for all tables when its standby sends hot_standby_feedback. A logical slot pins catalog_xmin, keeping old system-catalog rows, and tracks confirmed_flush_lsn.

open as a page

In PostgreSQL 18, how do max_slot_wal_keep_size and idle_replication_slot_timeout each bound what a replication slot retains, and what does each cost?

level: middleimportance: should knowfreq 40%

basics

~20 s

max_slot_wal_keep_size invalidates a slot whose restart_lsn trails the current WAL by more than the given size; idle_replication_slot_timeout, new in 18, invalidates a slot unused for longer than the given time. Both act at checkpoints, protect the primary, and force the consumer to resync.

open as a page

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%

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.

open as a page