Module 9 · Operations

Streaming Replication and High Availability

Lesson 26 of 30 13 min read Updated

Streaming replication lets one PostgreSQL server continuously ship its write-ahead log to another server so the second server can replay the same changes. The practical outcome is a standby that is nearly current with the primary and can serve read-only traffic, backups, or promotion during an outage. In this Operations section, the topic matters because PostgreSQL durability is centered on WAL, so high availability starts by understanding exactly how WAL is produced, transported, retained, replayed, and inspected.

Purpose and Outcome

A primary database accepts writes. Each committed change is recorded in WAL before it is considered durable. A physical standby connects to the primary, receives WAL records, writes them locally, and replays them into its own data files. If the primary fails, an operator or failover manager can promote a standby. Promotion ends recovery mode, creates a new timeline, and allows writes on the promoted server.

After this lesson you should be able to configure a small primary-standby pair, describe the sender and receiver processes involved, read the replication status views, choose asynchronous or synchronous commit behavior deliberately, and diagnose common failures such as a disconnected standby, a retained WAL buildup, or a promoted server that no longer belongs to the old primary’s timeline.

How Streaming Replication Works Internally

PostgreSQL physical replication is block-compatible replication driven by WAL. It does not send SQL statements. It sends the low-level WAL stream generated by changes to heap pages, indexes, transaction status, catalog rows, and other storage structures. Because the standby replays the same WAL, it must run the same major PostgreSQL version and be initialized from a base backup of the primary or another compatible source.

The primary side uses a walsender process for each streaming standby. The standby side uses a walreceiver process while it is connected. The startup process on the standby reads WAL from local storage and applies it. Replication progress is usually described with log sequence numbers, or LSNs. An LSN is a byte position in the WAL stream. The important positions are where WAL has been sent by the primary, written by the standby, flushed to durable storage on the standby, and replayed into visible data files.

Replication begins with a base backup. A base backup copies data files while the source server is running and records the WAL position needed to make that copy consistent. The standby is then started with recovery configuration that points at the primary, commonly through primary_conninfo. A standby.signal file tells PostgreSQL to remain in standby recovery instead of opening as a normal writable server.

WAL retention is a central reliability detail. If the standby falls behind and the primary removes WAL files before the standby receives them, streaming cannot catch up from the primary alone. A replication slot prevents that by telling the primary to retain WAL needed by a named consumer. Slots protect lagging standbys, but they also create risk: if the standby is gone and the slot remains active or reserved, WAL can fill the primary’s disk. Archiving is another protection. With a WAL archive, a standby can fetch older WAL through restore_command when streaming is interrupted.

Configuration Anatomy

On the primary, wal_level must support replication, max_wal_senders must allow enough streaming connections, and pg_hba.conf must permit the replication user to connect. A dedicated role with the REPLICATION attribute is common. On the standby, primary_conninfo supplies host, user, database connection options, and usually an application name that appears in monitoring views. primary_slot_name binds the standby to a physical replication slot when slot-based retention is wanted.

CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD 'use-a-secret-manager';
SELECT pg_create_physical_replication_slot('standby_a');

This SQL creates the minimum database-side identity for a streaming standby and reserves a physical slot. In a real system, the password should be injected through a secret manager or service account mechanism, not stored in shell history or committed configuration. The deterministic result of the second statement is one row naming the slot and usually a null LSN until the slot is first used.

# postgresql.conf on the primary
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on

These settings allow physical replication connections and standby query service. hot_standby is read on standby servers too; leaving it enabled allows read-only queries during recovery. The values shown are a small-lab shape, not capacity planning guidance.

# postgresql.auto.conf or postgresql.conf on the standby
primary_conninfo = 'host=primary.example.internal port=5432 user=repl application_name=standby_a sslmode=verify-full'
primary_slot_name = 'standby_a'
hot_standby = on

The standby connection string gives the primary enough information to authenticate the receiver and labels the connection as standby_a. With TLS verification, the standby is less likely to stream from an impostor endpoint after a network or DNS mistake.

Worked Example 1: Observing a Healthy Stream

Once a standby is connected, the primary exposes per-standby state in pg_stat_replication. The most useful first query compares sent, written, flushed, and replayed LSNs.

SELECT application_name, state, sync_state,
       sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag_bytes
FROM pg_stat_replication;

On a quiet system, a healthy asynchronous standby commonly shows state as streaming, sync_state as async, and replay lag near zero bytes. If a write-heavy workload is running, sent_lsn may advance ahead of replay_lsn. That does not automatically mean failure; it means the standby has not replayed every byte sent by the primary yet.

Worked Example 2: Measuring Replay Lag From the Standby

The standby can report whether it is in recovery and how far replay has reached. This is useful because a load balancer or application may be connected to the standby, not the primary.

SELECT pg_is_in_recovery() AS is_standby,
       pg_last_wal_receive_lsn() AS received,
       pg_last_wal_replay_lsn() AS replayed,
       now() - pg_last_xact_replay_timestamp() AS replay_delay;

The deterministic part is pg_is_in_recovery(): it returns true on a standby and false after promotion. The delay value can be null if no transaction timestamp has been replayed yet, and it can be misleading on an idle system because it measures time since the last replayed commit, not necessarily current network delay. For byte-level comparison, compare LSNs; for user-visible staleness, combine LSN and timestamp checks with application expectations.

Worked Example 3: Synchronous Commit Trade-Off

Asynchronous replication lets the primary acknowledge commits after local durability. The standby may be behind, so a primary crash can lose transactions that were committed to clients but not streamed or flushed elsewhere. Synchronous replication changes that commit path. The primary waits for one or more synchronous standbys to confirm a configured durability point.

# primary postgresql.conf
synchronous_standby_names = 'FIRST 1 (standby_a, standby_b)'
synchronous_commit = remote_write

With this configuration, commits wait until one listed standby has written WAL to its operating system, but not necessarily flushed it to durable storage or replayed it. Stronger settings such as remote flush behavior reduce data-loss windows further and add more latency. If every synchronous standby is unavailable and the configuration requires one, writes can stall. That is the core trade-off: stronger commit acknowledgement improves recovery guarantees but couples write availability to standby health and network quality.

Design Choices and Trade-Offs

Physical streaming replication is simple and faithful because it reproduces the storage-level WAL stream. It is a good fit for high availability, read replicas, and backup offload. It is not selective: the standby contains the whole cluster, not just chosen tables, and it cannot accept independent writes while it remains a physical standby.

Replication slots are usually worth using for important standbys because they prevent silent loss of required WAL. The cost is operational: every slot needs monitoring for retained bytes and an owner who can drop or advance it when a standby is retired. WAL archiving provides another recovery path and is valuable for point-in-time recovery, but archives must be tested. A backup that depends on an unreadable archive is only a false sense of safety.

Failover automation is a separate design choice. PostgreSQL provides promotion primitives, not a complete distributed consensus system. Tools such as cluster managers can decide when to promote, update virtual IPs or service discovery, and prevent stale primaries from accepting writes. The dangerous failure is split brain: two writable servers diverge. Avoiding it requires fencing the old primary, making clients follow a single write endpoint, and treating promotion as a timeline change that old standbys must be reattached to deliberately.

Failure Modes and Troubleshooting

Disconnected standby. Symptoms include no row for the standby in pg_stat_replication on the primary, connection errors in standby logs, or a receiver process repeatedly restarting. Check network reachability, the replication role, password source, TLS settings, and the matching pg_hba.conf replication entry. Correct the connection or authentication problem, then confirm that state returns to streaming.

WAL retained until disk pressure. Symptoms include rapid growth in pg_wal, low disk alerts, and a physical slot with a large retained distance. Query pg_replication_slots and compare restart_lsn with current WAL. The cause is often a failed or paused standby whose slot still protects old WAL. Restore the standby, remove the slot if the standby is intentionally retired, or rebuild the standby from a new base backup if it cannot catch up.

Standby query cancellations. A hot standby can run read-only queries, but replay sometimes needs to remove row versions or locks that a long standby query still wants. Symptoms are canceled reports or errors mentioning recovery conflict. Diagnose long-running queries on the standby and replay delay. Corrections include shortening reporting queries, using a dedicated analytics replica with acceptable delay, or tuning standby feedback carefully. Feedback can reduce cancellations, but it may also cause bloat on the primary by delaying cleanup.

Wrong timeline after failover. After promotion, the new primary writes on a new timeline. Old standbys following the former primary may not automatically follow the promoted server. Symptoms include recovery errors, a standby stuck waiting for WAL, or clients writing to the old primary after it returns. Fence the old primary before service restoration, repoint standbys to the promoted node, and use rewind or rebuild procedures only after confirming the correct source timeline.

Security, Performance, and Reliability Implications

The replication role can read the WAL stream, which may contain sensitive data changes. Limit where it can connect from, protect its secret, prefer encrypted transport across untrusted networks, and avoid sharing it with application users. The standby is also a copy of production data, so backup, logging, filesystem permissions, and query access controls matter there too.

Performance impact comes from WAL generation, network transfer, retention, and replay. Heavy bulk loads, index builds, vacuum activity, and checkpoints can create bursts. Monitor replication lag in bytes and time, slot retained WAL, disk free space, checkpoint behavior, and standby conflicts. Reliability depends on rehearsed actions: taking base backups, promoting, redirecting clients, fencing the old primary, verifying the new write endpoint, and rebuilding replicas.

Hands-On Lab: Build and Verify a Local Standby

Prerequisites: two PostgreSQL instances or containers of the same major version, local administrative access, enough disk for a base backup, and a network path from standby to primary. Use a throwaway environment; do not run this lab against an unmanaged production server.

  1. On the primary, create a replication role and physical slot using the SQL shown earlier. Add a narrow pg_hba.conf entry that allows the standby host to connect for replication as that role.
  2. Set primary replication parameters such as wal_level = replica, max_wal_senders, and max_replication_slots. Reload or restart only when the setting requires it.
  3. Take a base backup for the standby with pg_basebackup, using stream mode for WAL and writing standby configuration if your tooling supports it.
  4. Start the standby and inspect its logs until it enters consistent recovery and begins streaming.
  5. Create a small table and insert rows on the primary, then query the standby to confirm the rows appear after replay.
  6. Run the primary-side monitoring query from Worked Example 1 and record state, sync_state, and replay lag.
  7. Promote the standby in the lab. Verify that pg_is_in_recovery() becomes false and that writes now succeed on the promoted server.
pg_basebackup -h primary.example.internal -U repl -D /var/lib/postgresql/standby \
  -Fp -Xs -P -R -S standby_a
pg_ctl -D /var/lib/postgresql/standby start
psql -c "SELECT pg_is_in_recovery();"

Verification is complete when the primary reports a streaming standby before promotion, the standby reports recovery mode before promotion, data written on the primary appears on the standby, and the promoted standby reports pg_is_in_recovery() as false. Cleanup in a lab means stopping both instances, removing the standby data directory, dropping the replication slot if it is no longer used, and deleting temporary replication credentials.

Assessment Exercises

  1. A standby is connected but replay_lsn is far behind sent_lsn. What separate causes could explain this, and which queries would distinguish network delay from slow replay?
  2. You enable a physical replication slot for a standby that is later deleted. What symptom appears on the primary, and what operational check should have caught it?
  3. Your business says committed orders must survive loss of the primary data directory. Which synchronous commit behavior would you evaluate, and what availability cost must be accepted?
  4. After a failover, why is it unsafe to simply restart the old primary and let applications connect wherever they can?
  5. A reporting query on a hot standby is repeatedly canceled. Explain the conflict between replay and the query, then propose two different corrections with their trade-offs.

Summary

PostgreSQL streaming replication is WAL shipping with continuous replay. Its high-availability value comes from understanding LSN progress, standby recovery, slots, archiving, promotion, and timelines. The main design choices are asynchronous versus synchronous commit, slot retention versus disk risk, read-only standby usefulness versus replay conflicts, and manual versus automated failover. Treat every replica as a real production data copy, monitor both lag and retained WAL, and rehearse promotion and rebuild procedures before an outage forces the issue.

Progress is saved in this browser.