PostgreSQL
Streaming Replication: Primary and Standbys
December 2020. AWS us-east-1 goes down. Slack has streaming replication to a standby in another AZ. Failover takes 4 minutes. Zero lost messages. Competitors without replicas: hours of downtime. Streaming replication is not an enterprise luxury, it is insurance that pays out exactly when you need it. PostgreSQL gives it all out of the box: WAL streaming, hot standby for read scaling, synchronous mode for zero RPO.
- **GitLab.com:** streaming replication with hot standby. Heavy analytical queries (activity reports) go to the read replica and remove 40 percent of load from the primary.
- **Zalando:** patroni + streaming replication. Automatic failover in under 30 seconds when the primary fails. RDS Aurora is replaced with self-managed Postgres for full control.
- **Supabase:** every project gets primary + standby out of the box. pg_basebackup runs on project creation. Synchronous replication is enabled for databases holding financial data.
How Streaming Replication Works
**Streaming replication delivers the WAL stream from primary to standby in real time.** The primary generates WAL on every change. A WAL sender process on the primary forwards new records to a WAL receiver process on the standby. The standby applies the received records to its copy of the data. Standby lag is usually 1 to 100 ms.
**pg_stat_replication** is the primary tool for monitoring replication. Four LSN positions: sent (sent), write (written to standby memory), flush (flushed to standby disk), replay (applied to data). The difference between replay_lsn and sent_lsn is the real lag.
A standby is 500 MB behind in WAL. flush_lsn and replay_lsn are almost equal. What does this mean?
pg_basebackup: Initial Synchronization
**pg_basebackup** creates an exact physical copy of a PostgreSQL cluster over the network. It is the only official way to initialize a standby server. It copies all data files, takes a checkpoint, and bundles WAL segments produced during the copy, giving the standby a consistent starting point.
**pg_basebackup does not block the primary.** It uses the backup mode mechanism (pg_backup_start/pg_backup_stop). The primary keeps serving queries. All changes during the copy are recorded in WAL and shipped via `--wal-method=stream`.
pg_basebackup with --wal-method=stream runs against a primary with 50 GB of data. What happens to production traffic during the copy?
Standby Setup and Recovery
**Standby mode is activated by a `standby.signal` file** in the data directory. With this file present, PostgreSQL enters continuous recovery and applies WAL from the primary. In PostgreSQL 12+ the configuration moved to `postgresql.conf` and `postgresql.auto.conf`. The `recovery.conf` file is no longer used.
**primary_slot_name vs wal_keep_size:** without a replication slot the standby depends on `wal_keep_size` on the primary. If the standby falls further behind than retained WAL, replication breaks. With a replication slot, WAL is retained guaranteed, but the disk may fill during long lag.
A standby is configured without a replication slot, wal_keep_size = 1GB. The primary generates 500 MB/hour of WAL. The standby goes down for 3 hours. What happens on reconnect?
Hot Standby: Reads from the Replica
**Hot standby lets the standby server serve SELECT queries.** `hot_standby = on` (default in modern versions). The standby applies WAL while simultaneously serving read-only queries. This offloads the primary by routing analytical and reporting queries to the replica.
**Routing queries between primary and standby** is the job of the application layer or pgPool-II / HAProxy. A popular pattern: writes go to the primary, reads go to the standby. GitLab routes heavy reports to a dedicated read replica, removing 40 percent of load from the primary.
An application runs INSERT on the primary, then immediately SELECT on the hot standby. The SELECT might not find the just-inserted row. Why?
Synchronous Replication: Zero RPO
**Synchronous replication:** the primary confirms a transaction to the client only after the standby has written WAL to disk. RPO = 0: even if the primary fails immediately, the standby has every change. The cost: transaction latency increases by the round-trip to the standby (typically 1 to 10 ms over a LAN).
| synchronous_commit | Guarantee | Latency |
|---|---|---|
| off | None. Loss of recent transactions possible. | Minimal |
| local (default) | WAL on primary disk | Normal |
| remote_write | WAL in standby memory | +RTT/2 |
| on | WAL on standby disk | +RTT |
| remote_apply | Change applied on standby | +RTT + apply time |
**If the synchronous standby is unavailable, the primary stalls.** Every COMMIT waits. Configure `ANY 1 (standby1, standby2)` for resilience, or monitor standby availability and switch to asynchronous mode automatically.
Streaming replication protects against data loss in any primary failure
Asynchronous replication can lose the last few transactions (RPO > 0). Only synchronous_commit = 'on' gives RPO = 0, at the cost of higher latency.
Under async replication, the primary confirms COMMIT to the client before shipping WAL to the standby. If the primary dies between COMMIT and shipping, those transactions are lost. Typical lag of 1 to 100 ms equals a potential window of data loss. Most production systems accept this trade-off for performance.
synchronous_standby_names = 'standby1'. Standby1 goes down. What happens to INSERT on the primary?
Key Ideas
- **WAL streaming**: the primary ships WAL via a WAL sender, the standby applies it via the startup process. Lag is typically 1 to 100 ms.
- **pg_basebackup** creates the initial copy without blocking the primary. It is the only official way to initialize a standby.
- **standby.signal** plus primary_conninfo in postgresql.auto.conf trigger continuous recovery. recovery.conf is obsolete since PostgreSQL 12.
- **Hot standby** (hot_standby=on) allows SELECT on the standby for read scaling without extra components.
- **Synchronous replication** (synchronous_standby_names) gives RPO=0 at the cost of +RTT latency. If a sync standby fails, the primary stalls.
Related Topics
Streaming replication underpins several advanced PostgreSQL features:
- WAL: Write-Ahead Log — Streaming replication transports the WAL stream. Understanding WAL is critical for diagnosing replication.
- High Availability (HA) — Patroni / repmgr use streaming replication as transport and automate failover when the primary fails.
- Logical Replication — Logical replication runs on top of WAL but replicates only selected tables and supports cross-version replication.
Вопросы для размышления
- The primary generates 2 GB/hour of WAL, wal_keep_size = 1 GB. The standby runs nightly analytical queries lasting 2 hours. What happens to replication? Two ways to fix it?
- A team uses synchronous replication for financial transactions. The standby lives in another datacenter 50 ms away. How much will sync replication slow every transaction down? Is it worth it?
- After failover to the standby, the old primary comes back. How can it be safely turned into the new standby without losing data?