PostgreSQL Streaming Replication: What Every DBA Should Know

If you run PostgreSQL in production, you need replication. Full stop. A single server is a single point of failure, and the question is not if it will go down but when. Streaming replication is how PostgreSQL solves this - a continuous, near-real-time copy of your database on one or more standby servers, ready to take over when the primary fails.

I have set up and managed streaming replication on hundreds of PostgreSQL clusters across 11 datacenters. This post covers what you need to know - not just the theory, but the operational details that matter when things go wrong at 3 AM.

What replication actually does

PostgreSQL uses a Write-Ahead Log (WAL) to guarantee durability. Before any change is applied to the actual data files, it is first written to the WAL. This is a sequential, append-only log of every modification to the database.

Streaming replication works by shipping these WAL records from the primary server to one or more replicas in near-real-time over a persistent TCP connection. The replica receives the WAL records and replays them against its own copy of the data directory. The result is an identical copy of the database, typically lagging the primary by only milliseconds.

Two key processes make this work:

• walsender runs on the primary. One walsender process is spawned for each connected replica. It reads WAL records and streams them to the replica.
• walreceiver runs on the replica. It connects to the primary, receives WAL records, writes them to the replica's WAL, and triggers the startup process to replay them.

You can monitor the health of replication at any time with two views:

-- On the primary: shows connected replicas, their state, and lag
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

-- On the replica: shows connection to primary and receive status
SELECT status, received_lsn, latest_end_lsn
FROM pg_stat_wal_receiver;

If pg_stat_replication is empty on your primary, no replicas are connected. If replay_lag_bytes is growing, your replica is falling behind. Both situations need immediate attention.

Setting up your first replica

The process starts with pg_basebackup, which takes a consistent physical copy of the primary's entire data directory and transfers it to the replica. This is not a logical export - it is a byte-for-byte copy of the data files, WAL included.

pg_basebackup -h primary-host -D /var/lib/postgresql/17/main \
  -U replicator -Fp -Xs -P -R

The -R flag is important - it creates the standby.signal file and writes the primary_conninfo setting into postgresql.auto.conf on the replica. Without -R, you would need to create these manually.

On the primary, you need to configure a few things first:

1. A replication user. Create a user with the REPLICATION privilege:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';

2. pg_hba.conf entry. Allow the replica to connect for replication:

host replication replicator replica-ip/32 scram-sha-256

3. WAL configuration. Ensure the primary is generating enough WAL information:

wal_level = replica          # minimum for streaming replication
max_wal_senders = 5          # max concurrent replication connections

After pg_basebackup completes, start PostgreSQL on the replica. It will automatically connect to the primary and begin receiving WAL records. You will see streaming in pg_stat_replication on the primary within seconds.

Replication slots - preventing WAL recycling

Here is a scenario that catches almost every new DBA: the replica goes down for maintenance. While it is offline, the primary continues writing WAL. By default, the primary recycles old WAL segments once they are no longer needed locally. When the replica comes back, the WAL segments it needs are gone. Replication is broken, and you need to rebuild the replica from scratch with pg_basebackup.

Replication slots solve this. A physical replication slot tells the primary: "Do not recycle any WAL segments that this replica has not yet received."

-- Create a slot on the primary
SELECT pg_create_physical_replication_slot('replica_1');

Then configure the replica to use it in primary_conninfo or primary_slot_name:

primary_slot_name = 'replica_1'

The trade-off is significant. If a replica with an active slot goes down and stays down, WAL segments will accumulate on the primary indefinitely. I have seen primaries run out of disk space because a forgotten replica slot held WAL for days. Always monitor your slots:

SELECT slot_name, active,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
FROM pg_replication_slots;

PostgreSQL 13 introduced max_slot_wal_keep_size as a safety valve - set it to limit how much WAL a slot can retain before the primary drops it. I recommend setting this on every production cluster.

Synchronous vs asynchronous replication

By default, streaming replication is asynchronous. The primary sends WAL records to replicas but does not wait for confirmation. It acknowledges the commit to the client as soon as the WAL is flushed to its own disk.

This means there is always a window - typically milliseconds, but potentially longer under load - where a transaction has been committed on the primary but not yet received by the replica. If the primary crashes during that window, the committed data is lost.

For most workloads, this is acceptable. The replication lag is tiny, and the performance benefit of asynchronous mode is significant.

Synchronous replication changes the equation. With synchronous_commit = on and synchronous_standby_names configured, the primary waits for at least one replica to confirm it has received (and optionally flushed) the WAL before acknowledging the commit. Zero data loss, guaranteed.

synchronous_standby_names = 'FIRST 1 (replica_1, replica_2)'
synchronous_commit = on

The cost is real:

• Higher write latency. Every commit must round-trip to the replica. If your replica is in a different region, this can add 20-100ms per transaction.
• Availability risk. If all synchronous replicas go down, writes on the primary block until a replica comes back. Your database becomes read-only.

Most production clusters I manage use asynchronous replication with tight monitoring on lag. We switch to synchronous mode only for workloads where data loss is legally or financially unacceptable - think financial transactions or healthcare records.

A practical middle ground is synchronous_commit = remote_write - the primary waits for the replica to confirm it has received the WAL and written it to the OS cache, but not flushed to disk. This reduces the window of data loss to the replica's OS crash (unlikely) while keeping latency lower than full synchronous mode.

Common pitfalls

After managing replication on hundreds of clusters, these are the issues I see most often:

1. pg_hba.conf misconfiguration. The replica connects using the replication database keyword, not a real database name. Your pg_hba.conf entry must use replication as the database field, not all or a specific database name. This trips up at least one student per week in our labs.

2. Forgetting to restart after config changes. Changes to wal_level, max_wal_senders, and max_replication_slots require a full restart, not just a reload. Changes to synchronous_standby_names and synchronous_commit only need a reload. Know which is which.

3. Network firewalls blocking port 5432. Replication uses the same port as regular PostgreSQL connections. If your firewall rules allow application traffic but not replication traffic from the replica's IP, the walreceiver will fail silently.

4. WAL level set too low. wal_level = minimal does not generate enough WAL information for replication. You need at least replica. If you set it to logical, you can also do logical replication later without another restart.

5. Not monitoring replication lag. A replica that is connected but falling behind is worse than a replica that is disconnected, because you think you have redundancy when you do not. Monitor replay_lag_bytes and alert when it exceeds your RPO tolerance.

6. Running long transactions on replicas. If you run reporting queries on a replica with hot_standby_feedback = on, those queries can prevent the primary from vacuuming old row versions. The primary's table bloat grows until the query finishes. Use max_standby_streaming_delay to set a hard limit.

Build the muscle memory

Reading about replication gives you vocabulary. Setting it up gives you muscle memory. Debugging a broken replica at 3 AM gives you intuition. You need all three.

On GritLabs, the Streaming Replication lab gives you two real PostgreSQL servers. You configure replication from scratch, verify it works, then break it on purpose. Kill the walreceiver. Fill the primary's disk. Promote the replica and watch what happens to the old primary. These are the exact scenarios you will face in production.

The best time to see a replication failure for the first time is in a lab, not during an incident.