Streaming Replication Setup and Failover

Streaming Replication in PostgreSQL

Streaming replication allows a standby PostgreSQL server to stay continuously synchronized with a primary server by receiving Write-Ahead Log (WAL) records over a network connection. This is the foundation of high-availability deployments and is a core skill tested on the PostgreSQL CE (EDB) exam.

How WAL-Based Replication Works

PostgreSQL writes every change to the WAL before applying it to data files. In streaming replication, the standby connects to the primary using a special replication protocol, and the primary streams WAL segments to the standby in near-real time. The standby continuously replays those WAL records, keeping its data close to the primary's state. A hot standby goes further: it allows read-only queries while replay is running, making it useful for reporting workloads as well as failover.

Configuring the Primary Server

On the primary, set `wal_level = replica` in `postgresql.conf`. This level records enough information for the standby to replay changes and to support logical decoding if needed. You must also set `max_wal_senders` to a value greater than zero (commonly 5 or 10) so the primary can serve WAL to standby connections.

Create a dedicated database role for replication:

```sql

CREATE ROLE replicator REPLICATION LOGIN PASSWORD 'strong_password';

```

In `pg_hba.conf`, add a line that permits the standby's IP address to connect with the `replication` database keyword:

```

host replication replicator <standby_ip>/32 scram-sha-256

```

Reload or restart the primary after these changes.

Building the Standby with pg_basebackup

`pg_basebackup` copies the primary's data directory to the standby host and places the server in a consistent state. Run it on the standby machine:

```bash

pg_basebackup -h <primary_ip> -U replicator -D /var/lib/postgresql/16/main \

-P -Xs -R

```

The `-R` flag writes a minimal `standby.signal` file and a `postgresql.auto.conf` entry for `primary_conninfo`, saving manual configuration. Verify the `primary_conninfo` string in `postgresql.auto.conf` contains the correct host, port, and user.

Starting the Standby and Verifying Replication

Start PostgreSQL on the standby. Because `standby.signal` is present, the server enters standby mode and begins WAL streaming. On the primary, query `pg_stat_replication` to confirm the connection:

```sql

SELECT client_addr, state, write_lag, flush_lag, replay_lag

FROM pg_stat_replication;

```

A row with `state = streaming` confirms the standby is receiving WAL. The three lag columns measure latency at different stages of the replication pipeline: write_lag is the delay until the primary acknowledges the WAL was written on the standby, flush_lag is the delay until it was flushed to disk, and replay_lag is the delay until it was replayed into the standby's data files.

Measuring Replication Lag

To observe lag deliberately, call `pg_wal_replay_pause()` on the standby, then insert rows on the primary. The standby receives WAL but does not apply it, so `replay_lag` grows. After calling `pg_wal_replay_resume()`, the standby catches up and the lag columns return toward zero. This exercise shows that committed transactions are not lost — WAL was received and buffered — and that catch-up happens automatically once replay resumes.

Performing a Controlled Failover

A controlled failover (sometimes called a switchover when planned) proceeds as follows:

1. Stop the primary cleanly with `pg_ctl stop -m fast`.

2. On the standby, run `pg_ctl promote` or create a `promote` trigger file if configured. PostgreSQL finishes replaying available WAL, removes `standby.signal`, and opens the server for read-write connections.

3. Verify promotion by connecting with `psql` and running `SELECT pg_is_in_recovery();` — it should return `false`.

4. Confirm that data committed on the primary before the stop is present on the new primary.

Document every step in a failover runbook. A runbook is a step-by-step procedure, kept current and tested, so any qualified team member can execute a failover under pressure without missing a step.

Safety and Operational Notes

Always use strong passwords and encrypted connections (`scram-sha-256` or TLS) for the replication role — replication accounts have elevated access. Restrict `pg_hba.conf` entries to the minimum required IP range. Before performing any failover in production, verify you have a recent backup independent of replication; replication is not a substitute for backup. Capture screenshots or log output at each verification step so you have an audit trail. When working on shared lab VMs, confirm with your instructor before stopping any PostgreSQL service that classmates may be using.