Replication and high availability

Why it matters

A single database server is a single point of failure. When it dies, the application dies with it. Replication keeps one or more copies of the database up to date so that reads can be spread out and, when the main server fails, another one can take over. High availability (HA) is the overall design (replicas, failover automation, connection routing, monitoring) that keeps downtime within the agreed limits.

Core vocabulary

• Primary: the server that accepts writes.
• Replica / standby: a server that receives the primary's changes. A hot standby also serves read-only queries.
• Failover: promoting a replica to become the new primary after the old one fails. A planned version is a switchover.
• Replication lag: how far behind the primary a replica is, in bytes of log or seconds.

How PostgreSQL replicates

Physical streaming replication ships the write-ahead log (WAL), the same log used for crash recovery, from the primary to each standby, which replays it. The standby is a byte-for-byte copy of the whole cluster: same version, same databases. It is set up by taking a pg_basebackup of the primary and configuring primary_conninfo. Replication slots stop the primary from deleting WAL a standby still needs (watch them: an abandoned slot fills the disk).

Logical replication publishes changes to selected tables (CREATE PUBLICATION / CREATE SUBSCRIPTION) as row-level operations. It can cross major versions and replicate a subset of tables, which makes it useful for upgrades and feeding other systems. It does not copy schema changes automatically.

MySQL has the same ideas: binary-log replication (asynchronous or semi-synchronous) and Group Replication / InnoDB Cluster. SQL Server has Always On availability groups. Managed services (Amazon RDS Multi-AZ, Azure, Cloud SQL) run a standby and fail over for you.

Synchronous vs asynchronous

• Asynchronous (the default): the primary commits and replies immediately; replicas catch up shortly after. Fast, but if the primary dies, the last few transactions may never reach a replica. That potential loss is your RPO.
• Synchronous: the primary waits until at least one standby confirms it has the WAL before reporting commit (synchronous_standby_names, synchronous_commit). No committed data is lost on failover, but every write pays a network round trip, and if the synchronous standby is unavailable, writes can stall. Teams often run one synchronous standby nearby and further asynchronous ones.

Reading from replicas

Replicas can serve reporting and read-heavy traffic. Because of lag, a user who just saved a profile may read the old value from a replica. Route "read your own writes" to the primary, or accept and design for slight staleness. Monitor lag on the primary with:

SELECT application_name, state, sync_state, replay_lag
FROM pg_stat_replication;

Failover, done safely

When the primary fails:

1. Detect it reliably (several health checks, not one missed ping).
2. Fence the old primary so it cannot accept writes if it comes back (stop it, cut its network, or revoke its virtual IP).
3. Promote the most up-to-date replica (pg_ctl promote or SELECT pg_promote();).
4. Redirect clients: update DNS, a virtual IP, a proxy (HAProxy, PgBouncer) or a service endpoint.
5. Rebuild the old primary as a new replica (pg_rewind can help) before you rely on redundancy again.

Skipping step 2 risks split brain: two servers both accepting writes, with diverging data that is painful to reconcile. Tools such as Patroni (with etcd or Consul for consensus) or managed cloud HA automate detection, fencing and promotion. Whatever you use, rehearse failovers regularly; an untested failover often fails when you need it.

Measuring availability

Availability targets come in "nines": 99.9% allows about 8.8 hours of downtime a year, 99.99% about 53 minutes. Each extra nine needs more automation, more replicas and more practice. Remember that planned maintenance, such as upgrades, usually counts too.

Common mistakes

• Treating replicas as backups (they copy mistakes instantly).
• Ignoring replication lag until a replica is hours behind and useless for failover.
• Manual failover with no fencing, causing split brain.
• Forgetting that applications cache connections and DNS; clients must reconnect to the new primary.
• Leaving abandoned replication slots that fill the primary's disk with WAL.

Quick recap

• Streaming replication copies the whole cluster via WAL; logical replication copies chosen tables.
• Asynchronous is fast but can lose the last transactions on failover; synchronous trades write latency for zero loss.
• Safe failover: detect, fence, promote, redirect, rebuild. Automate it and rehearse it.

Try it

The Do tab quiz tests replication and failover decisions.