8.3 Replication Slots & Standby Management

8.3 Replication Slots & Standby Management

[!NOTE] The WAL Retention Dilemma: In any replication architecture, the primary server must retain Write-Ahead Log (WAL) records until all downstream standbys have consumed them. However, if the primary retains too little WAL, a network interruption can permanently desynchronize standbys, requiring costly full-cluster re-cloning. Conversely, if the primary retains too much WAL, disk partitions can fill to 100%, crashing the primary server. Physical replication slots and WAL retention thresholds exist to manage this operational balance.

In addition to disk storage management, running production workloads on read replicas introduces query conflicts: the primary's vacuum engine needs to clean dead tuples that active queries on the standby may still be viewing. Mastering replication slots, retention limits, and vacuum conflict parameters is fundamental to robust cluster management.

---

The WAL Recycling Hazard and Standby Desynchronization

PostgreSQL aggressively manages disk space in the pg_wal directory. During periodic checkpoints, the primary's checkpoint process evaluates old WAL segments and either removes them from disk or renames (recycles) them for future write transactions.

How Standbys Break Without Replication Slots

Historically, administrators relied on wal_keep_size (formerly wal_keep_segments) to specify a static volume of WAL (e.g., 16GB) to retain in pg_wal for replicas.

• If a standby node experiences a prolonged network outage, is temporarily shut down for host hardware maintenance, or lags behind during a massive bulk data import on the primary, the primary generates more WAL than wal_keep_size accommodates.
• The primary's checkpoint process recycles the unconsumed WAL segment files.
• When the standby reconnects, its walreceiver requests the WAL segment corresponding to its last received position.
• Because the primary has already deleted that segment, replication terminates with a fatal error: FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 000000010000001B00000042 has already been removed
• Consequence: The standby cannot recover on its own. It is permanently broken and must be destroyed and rebuilt from scratch using a new pg_basebackup.

---

Physical Replication Slots: Guaranteed WAL Retention

To eliminate the risk of WAL recycling desynchronization, PostgreSQL provides Replication Slots. A physical replication slot is a persistent server-side object on the primary that tracks the exact consumption progress of a specific standby replica.

+-----------------------------------------------------------------------------------------+
|                         Physical Replication Slot Architecture                          |
+-----------------------------------------------------------------------------------------+
|  PRIMARY SERVER                                                                         |
|  +-----------------------------------------------------------------------------------+  |
|  | pg_wal Disk Storage                                                               |  |
|  | [Segment 01] -> [Segment 02] -> [Segment 03] -> [Segment 04] -> [Segment 05]      |  |
|  +-----------------------+-----------------------------------------------------------+  |
|                          ^                                                              |
|                          | restart_lsn pinned by slot                                    |
|             +------------+-------------------------------------+                        |
|             | Physical Replication Slot: 'standby1_slot'       |                        |
|             | - active: true                                   |                        |
|             | - restart_lsn: 0/03000000                        |                        |
|             | - wal_status: reserved                           |                        |
|             +----------------------------+---------------------+                        |
|                                          | Streams unconsumed WAL                       |
|                                          v                                              |
|  +-----------------------------------------------------------------------------------+  |
|  | Standby Server (primary_slot_name = 'standby1_slot')                              |  |
|  +-----------------------------------------------------------------------------------+  |
+-----------------------------------------------------------------------------------------+

How Physical Replication Slots Function

1. When a physical replication slot is created on the primary, it establishes a restart_lsn pointer.
2. The primary's checkpoint and WAL recycling subsystems inspect all active replication slots in pg_replication_slots.
3. The checkpoint process is strictly prohibited from deleting or recycling any WAL file containing an LSN equal to or greater than the oldest restart_lsn of any configured slot.
4. As the connected standby receives and flushes WAL, its walreceiver acknowledges progress to the primary's walsender, which continuously advances the slot's restart_lsn.
5. WAL files are only deleted after every replication slot and checkpoint boundary has safely advanced beyond them.

Managing Physical Slots with SQL Functions

-- 1. Create a physical replication slot (with immediate LSN reservation)
SELECT pg_create_physical_replication_slot('standby1_slot', true);

-- 2. Inspect replication slot health, status, and WAL lag
SELECT 
    slot_name,
    slot_type,
    active,
    restart_lsn,
    wal_status,
    safe_wal_size,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_bytes
FROM pg_replication_slots;

-- 3. Drop a replication slot when decommissioning a standby
SELECT pg_drop_replication_slot('standby1_slot');

Configuring the Standby to Use a Slot

On the standby server, the slot is linked by setting the primary_slot_name parameter in postgresql.conf or postgresql.auto.conf:

# Standby configuration (postgresql.auto.conf)
primary_conninfo = 'host=192.168.1.50 port=5432 user=repuser application_name=standby1'
primary_slot_name = 'standby1_slot'

---

The Operational Danger: Primary Disk Saturation

While replication slots guarantee that a standby will never lose required WAL, they introduce a severe operational hazard:

[!CAUTION] The Dead Replica Disk Bomb: If a standby replica configured with a replication slot is shut down, destroyed, or disconnected—and its replication slot is not dropped from the primary—the primary server will faithfully retain every single WAL file generated from that moment forward! Over hours or days of write traffic, the primary's pg_wal directory will grow uncontrollably until the underlying disk filesystem reaches 100% capacity. At that point, PostgreSQL crashes with a PANIC: could not write to file "pg_wal/..." and halts all production operations!

Guardrail Protection: max_slot_wal_keep_size

To protect primary database availability against abandoned or severely lagging replication slots, PostgreSQL provides max_slot_wal_keep_size (introduced in PostgreSQL 13).

# Configured on the primary server in postgresql.conf
max_slot_wal_keep_size = 100GB

• How It Works: Specifies the maximum volume of WAL files that replication slots are permitted to retain in pg_wal. The default is -1 (unlimited, meaning slots can retain WAL indefinitely until disk exhaustion).
• Automatic Slot Invalidation: If an inactive or lagging standby causes unconsumed WAL to exceed max_slot_wal_keep_size:
  1. The primary invalidates the offending slot.
  2. The column wal_status in pg_replication_slots moves off reserved/extended to unreserved while the required WAL is still on disk, and then to lost once that WAL is actually removed. unreserved is the warning window in which a reconnecting standby can still be saved.
  3. The primary immediately removes the retained WAL segments to protect its local disk space and sustain production write transactions.
  4. When the lagging standby attempts to reconnect, the primary rejects the connection with an invalid slot error. The standby must then be rebuilt via pg_basebackup.

PostgreSQL defines exactly four values for pg_replication_slots.wal_status — there is no normal state, and a healthy slot reads reserved:

---

Vacuum Conflicts on Hot Standby Replicas

When hot_standby = on is enabled, standbys service read queries while concurrently replaying WAL records. Because PostgreSQL uses Multi-Version Concurrency Control (MVCC), this concurrency model creates Replication Conflicts.

The Anatomy of a Vacuum Conflict

1. On the primary, transactions delete or update rows, leaving behind dead tuples.
2. Later, the primary's autovacuum runs. Seeing that no primary transaction requires those dead tuples, autovacuum prunes them, cleans the index leaf pointers, or freezes pages, and writes these cleanup actions as WAL records.
3. On the standby, an analytical user starts a long-running reporting query holding an open snapshot from 30 minutes ago. That query is actively reading pages containing those dead tuples.
4. When the standby's startup process attempts to apply the incoming cleanup WAL record, it finds that doing so would physically delete data rows that the active standby query is currently inspecting!
5. The startup process cannot proceed without either corrupting the query's view or waiting.

Primary Autovacuum                      Standby Startup (Redo)               Standby Read Query
        |                                         |                                  |
        |--- Writes WAL: Prune Dead Tuples ------>|                                  |
        |                                         |-- Conflict: Pages in use! ------>|
        |                                         |   (Pauses replay up to delay)    | (Running analytical scan)
        |                                         |                                  |
        |                                         |-- Timeout expires --------------->|
        |                                         |   Cancels conflicting query!     |-- ERROR: canceling statement
        |                                         |                                  |   due to conflict with recovery
        |                                         |-- Replays WAL cleanly ---------->|

Conflict Resolution Parameters

PostgreSQL provides two complementary mechanisms to resolve vacuum conflicts:

1. Grace-Period Delays: max_standby_streaming_delay

# Configured on the standby in postgresql.conf (default: 30s)
max_standby_streaming_delay = 30s
max_standby_archive_delay = 30s

• When a conflict occurs, the standby's startup process pauses WAL replay for up to max_standby_streaming_delay (default: 30 seconds), giving the running read query time to complete.
• If the query does not finish before the delay expires, the standby forcibly terminates the query with the error:
  ERROR: canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
• Setting this parameter to -1 instructs the standby to wait indefinitely, which prevents query cancellation but can cause replication lag to balloon to hours.

2. Feedback to Primary: hot_standby_feedback = on

# Configured on the standby in postgresql.conf (default: off)
hot_standby_feedback = on

• Mechanism: The standby's walreceiver continuously sends its oldest active transaction ID (xmin) and catalog snapshot age back to the primary's walsender.
• Effect on Primary: The primary's autovacuum engine respects the standby's xmin. Autovacuum will refrain from cleaning up dead tuples so long as any active query on the standby still needs them.
• The Tradeoff: hot_standby_feedback = on completely eliminates statement cancellations due to vacuum conflicts on the standby! However, if a user leaves an uncommitted transaction or long-running query open on the standby, dead tuples accumulate on the primary, leading to severe table bloat and performance degradation on the production master database.

---

Exam Tips and Common Pitfalls

• Exam Trap: Dropping Decommissioned Replicas: If a standby replica is permanently decommissioned, removing the standby server itself is not enough. You must explicitly log into the primary server and run SELECT pg_drop_replication_slot('slot_name');. Failing to do so will cause the primary to retain WAL until disk exhaustion.
• Exam Trap: hot_standby_feedback Downsides: While hot_standby_feedback = on eliminates query cancellations on read replicas, its primary operational hazard is table bloat on the primary server. Autovacuum on the primary cannot clean up dead tuples as long as a long-running query on the standby holds an old snapshot.
• Exam Trap: Slot Invalidation Guardrail: If an exam question asks how to prevent an offline standby's replication slot from filling the primary's disk drive, the correct answer is configuring max_slot_wal_keep_size.
• Exam Trap: wal_status Values: The four legal values are reserved, extended, unreserved, and lost. A healthy slot shows reserved, not normal — normal is not a PostgreSQL wal_status value at all, and any answer offering it is a distractor.