PostgreSQL High Availability: Physical Streaming Replication & Failover

Created: 2026-11-08  ·  Last updated: 2026-11-08  ·  Nejat Hakan

Executive Summary: High Availability (HA) in production database architectures protects critical services against hardware crashes, kernel panics, and data center outages. PostgreSQL addresses this through physical streaming replication: changes recorded in the Primary's Write-Ahead Log (WAL) are streamed byte-for-byte across TCP sockets to one or more Standby replicas. This guide details how to build an enterprise-grade Primary-Standby cluster on Debian Linux, leverage replication slots to eliminate WAL recycling race conditions, bootstrap nodes with pg_basebackup -R, monitor lag via pg_stat_replication, and execute controlled failover promotions.

• Primary-Standby Architecture

  ---

  Understand the data pipeline: how the Primary's walsender streams binary WAL records directly into the Standby's walreceiver and startup process.

• Replication Slots & Security

  ---

  Prevent replication lag desynchronization: lock WAL retention using physical replication slots and isolate replication traffic via dedicated roles and pg_hba.conf.

• Node Bootstrapping

  ---

  Clone running multi-terabyte databases seamlessly: execute pg_basebackup -R with automatic standby.signal generation and socket pooling.

• Lag Telemetry & Failover

  ---

  Track sub-millisecond replication drift via pg_stat_replication and promote a read-only replica to primary using pg_promote().

---

1. Physical Streaming Replication Architecture

PostgreSQL physical replication operates at the storage page level. When a write transaction commits on the Primary node, the corresponding change records are written to the Write-Ahead Log (WAL). A background sender process then ships these bytes across the network to the Standby:

%%{init: {'flowchart': {'wrappingWidth': 600}}}%%
flowchart TB
    PRI["Primary Node (Debian 12)<br/>Read-Write Transactions"]
    WAL["Primary WAL Engine<br/>Writes changes to 16MB WAL"]
    WS["walsender background process<br/>Streams byte-stream over TCP"]

    STANDBY["Standby Node (Debian 12)<br/>Hot Standby Read-Only Queries"]
    WR["walreceiver process<br/>Accepts stream & writes to WAL"]
    STARTUP["Startup replay process<br/>Applies WAL deltas to data heap"]

    PRI --> WAL
    WAL --> WS
    WS -->|"Encrypted TLS Stream (:5432)"| WR
    WR --> STARTUP
    STARTUP --> STANDBY

Synchronous vs. Asynchronous Replication Trade-offs

Mode	synchronous_commit	Data Loss Guarantee (RPO)	Transaction Latency (Impact on TPS)	Operational Characteristics
Asynchronous (Default)	off or local	Small window (last few ms of unstreamed WAL)	Zero network delay added to commits	High write throughput. Primary commits immediately once WAL is flushed locally.
Remote Write	remote_write	Protected against standby OS crashes	Minor delay (network round-trip to standby RAM)	Primary waits until Standby acknowledges writing WAL to OS buffer cache.
Synchronous	on	Zero data loss (RPO = 0)	Added delay (network RTT + Standby disk fsync)	Primary blocks commit until Standby confirms WAL is flushed to physical storage.
Remote Apply	remote_apply	Read-your-writes consistency across nodes	Highest latency penalty	Primary waits until Standby has applied the WAL changes to its data heap.

---

2. Primary Node Configuration & Security

On the Primary server (192.168.10.10), configure PostgreSQL to retain sufficient WAL records and allow streaming replication connections.

2.1. Tuning postgresql.conf

# File: /etc/postgresql/16/main/postgresql.conf

# Enable replication mode
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10

# Keep enough WAL in reserve in case of network interruptions
wal_keep_size = 4096MB

# Allow read queries while in standby mode
hot_standby = on

# Archive backup command (optional belt-and-suspenders approach)
archive_mode = on
archive_command = 'test ! -f /mnt/wal_archive/%f && cp %p /mnt/wal_archive/%f'

2.2. Dedicated Replication Role & Access Control

Create an unprivileged user restricted exclusively to replication streaming:

-- Execute as postgres superuser
CREATE ROLE replicator WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'Secr3t_Repl_Pass_2026!';

Authorise the standby IP address in pg_hba.conf:

# File: /etc/postgresql/16/main/pg_hba.conf
# TYPE  DATABASE        USER        ADDRESS             METHOD
host    replication     replicator  192.168.10.20/32    scram-sha-256

Reload the Primary configuration:

sudo systemctl reload postgresql

2.3. Provisioning a Physical Replication Slot

Replication slots prevent the Primary from prematurely recycling or deleting WAL segments before the Standby has consumed them:

-- Execute on Primary
SELECT pg_create_physical_replication_slot('standby_node_1');

Replication Slot Monitoring

If a Standby goes offline permanently, its replication slot will cause the Primary's pg_wal directory to grow continuously until disk space is exhausted. Always monitor slot status via SELECT * FROM pg_replication_slots;.

---

3. Provisioning the Standby Node

On the Standby server (192.168.10.20), stop the clean PostgreSQL instance and take an online snapshot of the Primary using pg_basebackup.

3.1. Clustered Snapshot via pg_basebackup

# 1. Stop local PostgreSQL daemon
sudo systemctl stop postgresql

# 2. Clear default empty cluster data
sudo rm -rf /var/lib/postgresql/16/main/*

# 3. Pull physical backup directly from the Primary
sudo -u postgres pg_basebackup \
    -h 192.168.10.10 \
    -p 5432 \
    -U replicator \
    -D /var/lib/postgresql/16/main \
    -Fp -Xs -R \
    -S standby_node_1 \
    -v -P

Parameter Breakdown:

• -Fp: Plain directory format (direct heap files).
• -Xs: Stream WAL concurrently during backup so the snapshot is immediately consistent.
• -R: Creates standby.signal and appends connection settings to postgresql.auto.conf.
• -S standby_node_1: Associates the standby with the pre-created replication slot.

3.2. Starting the Standby

sudo systemctl start postgresql
sudo systemctl status postgresql

Check the log to verify active WAL streaming:

sudo tail -f /var/log/postgresql/postgresql-16-main.log
# Expected output:
# LOG: entering standby mode
# LOG: redo starts at 0/3000028
# LOG: consistent recovery state reached at 0/3000100
# LOG: started streaming WAL from primary at 0/4000000 on timeline 1

---

4. Replication Monitoring & Failover Promotion

4.1. Monitoring Replication Health on the Primary

Execute this administrative telemetry query on the Primary to calculate replication lag in exact bytes:

SELECT 
    client_addr,
    application_name,
    state,
    sync_state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS sent_lag_bytes,
    pg_wal_lsn_diff(sent_lsn, write_lsn) AS write_lag_bytes,
    pg_wal_lsn_diff(write_lsn, flush_lsn) AS flush_lag_bytes,
    pg_wal_lsn_diff(flush_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

4.2. Monitoring on the Standby

-- Verify node state
SELECT pg_is_in_recovery(); -- Returns true on standby, false on primary

-- Compare received vs replayed Log Sequence Numbers (LSN)
SELECT 
    pg_last_wal_receive_lsn(),
    pg_last_wal_replay_lsn(),
    pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS pending_replay_bytes;

4.3. Emergency Promotion to Primary

If the Primary server experiences fatal hardware failure:

Option A: SQL Function (Hot Standby Mode)

SELECT pg_promote();

Option B: Operating System Utility (pg_ctl)

sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl promote -D /var/lib/postgresql/16/main

Upon promotion: 1. PostgreSQL removes standby.signal. 2. The engine writes an End-of-Recovery record and switches to a new timeline (e.g., Timeline 2). 3. The instance begins accepting full read-write transactions without requiring a service restart.