Replication and High Availability

Prerequisites: Transactions and Isolation Levels; Docker Compose.

PostgreSQL has two fundamentally different replication mechanisms. Streaming (physical) replication ships the raw WAL byte-for-byte to a standby, which replays it to produce an exact binary copy of the entire cluster — fast, simple, and the foundation nearly all HA setups are built on, but all-or-nothing: you replicate the whole instance, not a chosen subset. Logical replication decodes the WAL into a stream of row-level changes and applies them via normal SQL-like operations on the subscriber, which means you can replicate a single table, transform data along the way, and even replicate between different major Postgres versions — at the cost of more moving parts and specific caveats (sequences, DDL, and large objects aren't replicated automatically).

Two numbers frame every HA decision you'll make in this project: RPO (Recovery Point Objective — how much data, measured in time, can you afford to lose in a failure) and RTO (Recovery Time Objective — how long can you be down). Synchronous replication drives RPO toward zero at the cost of write latency; automated failover (this project's second half) drives RTO down from "however long it takes a human to notice and act" to seconds.