Replication & High Availability Basics

This chapter covers Postgres's own two genuinely distinct replication mechanisms, and closes with an honest correction of a common misconception about how Postgres's own replication compares to MySQL's.

Why Replication Matters

Three real, distinct motivations: high availability (a standby ready to take over if the primary fails), read scaling (offloading read-only queries to replicas), and disaster recovery or geographic distribution.

Streaming Replication (Physical Replication)

Every change to a Postgres database is first written to the WAL (Write-Ahead Log) before being applied to the actual data files — a durability mechanism this course hasn't named explicitly until now, though it underlies crash recovery generally. Streaming replication works by continuously shipping WAL records from a primary server to one or more standby servers, which replay those records to stay in sync.

This is "physical" replication — a byte-for-byte, block-level copy of the entire database cluster. A replica built this way is an exact physical copy: it can't have a different schema, can't replicate only a subset of tables, and in the classic setup is read-only.

A genuine, concrete trade-off: asynchronous replication (the default) is faster, but leaves a small window where data could be lost if the primary fails before a replica catches up; synchronous replication means a commit doesn't complete until at least one replica confirms receipt — zero data loss, at the cost of real added latency on every write.

Logical Replication

Logical replication (Postgres 10+) is a genuinely different, newer mechanism: it replicates actual row-level changes via a publish/subscribe model (CREATE PUBLICATION / CREATE SUBSCRIPTION), rather than raw physical WAL bytes. This unlocks real capability physical replication can't offer: replicating just a subset of tables rather than the whole cluster, replicating between different major Postgres versions (useful for near-zero-downtime major version upgrades), and — critically — the subscriber remains a genuinely independent, writable database that simply happens to receive a stream of changes for the tables being replicated. This is a real, meaningfully different tool, not the same mechanism with different configuration.

Contrasted With MySQL's Own Replication Model

MySQL's own replication has historically centered on binlog-based statement or row-based replication — conceptually much closer to Postgres's own logical replication in spirit, since both work at a row/statement level rather than raw physical bytes. MySQL never really had a direct equivalent to Postgres's own byte-for-byte physical streaming replication in the same way.

It's worth correcting a common misconception directly: it's not accurate to say "Postgres invented modern replication and MySQL is behind." MySQL has had working, production-grade replication for a very long time, including modern Group Replication and InnoDB Cluster for genuine multi-primary high availability. The real difference isn't "does it work" — it's architectural: Postgres offers both a physical (WAL-streaming) and a logical (row-level) replication mechanism as two genuinely distinct tools for different jobs, while MySQL's own replication has centered more consistently on the logical/row-level style throughout its history.

A Basic HA Pattern

A common real setup: one primary plus one or more streaming replicas, with a tool like Patroni or repmgr handling automatic failover — promoting a replica to primary if the original fails. It's worth being honest that Postgres itself doesn't include automatic failover out of the box: a raw streaming replica setup requires manual promotion (pg_promote()) unless a separate HA-management tool is layered on top.

Hands-On Exercises