Replication copies database changes to one or more other PostgreSQL instances so standby nodes can support redundancy and failover. PostgreSQL replication began with log shipping: continuous archiving copied WAL files to another server, producing a warm standby ready to take over. A single server without failover provision creates an avoidable downtime risk; standby nodes are the basic redundancy measure. section 9.5 section 9.7
Streaming replication changes the trade-off. Since PostgreSQL 9.0, WAL can stream automatically and asynchronously to a standby, keeping it closer to the primary than whole-file copying. Such a standby can serve read-only queries as a hot standby. Synchronous replication, introduced in PostgreSQL 9.1, waits for the WAL round trip so committed transactions are present on both sides; it removes loss caused by replication lag, but adds commit latency. section 9.5
Choose and operate replication with its failure modes in mind:
- Use cascading replication when a standby relays streaming changes to another standby, rather than requiring every standby to stream directly from the primary. section 9.5
- Do not substitute manual failover or custom scripts for established HA management: they risk prolonged downtime, divergence, or data loss. RepMgr, Patroni, and CloudNativePG are named alternatives. section 9.7
- Treat long-running transactions as a lag risk for synchronous or logical replication, because their changes replicate only after commit. Asynchronous streaming can be selected by setting synchronous_standby_names to an empty string. section 6.5.2
Read workload conflicting with WAL replay
A heavy update on the primary can conflict with a simultaneous long-running SELECT on a standby. The standby must apply WAL that affects the rows being read, so PostgreSQL can cancel the standby query. This makes a hot standby useful for reads, but not a guarantee that every long analytical query can finish while primary-side updates continue. section 6.5.2
Figure 9.3 A sample PostgreSQL HA setup with a cascaded replication for redundancy and backup
Inspect a topology in which cascaded replication supplies redundancy and supports backup planning. section 9.5
