Lesson 0010 · Replication

Replication: streaming vs logical

The WAL is a complete, ordered stream of every change. Ship it to another server and you have a replica — either by copying the bytes (streaming) or the meaning (logical). This closes the mission.

~11 minWarm-up recall + quiz + MySQL contrastCheat sheet: here

You ended 0009 with a key realization: the WAL is not just a crash-recovery tool, it's a complete, ordered record of every change the database makes. That's the whole secret of replication — feed that stream to a second server and it can reconstruct the same data. Postgres offers two ways to do it, and the difference is what travels: raw bytes, or logical row changes.

Streaming (physical) replication — copy the bytes

The default. A standby connects to the primary and continuously receives WAL records as they're produced, replaying them to stay nearly current. — PostgreSQL Docs: "The standby connects to the primary, which streams WAL records to the standby as they're generated, without waiting for the WAL file to be filled." Because it replays the physical WAL, the standby is a byte-for-byte copy of the whole cluster — same version, same layout — and serves read-only queries (a hot standby). It's the backbone of Postgres high availability: if the primary dies, promote a standby.

Synchronous replication — the dial from 0009, aimed at a standby

Want zero loss? Make the primary wait for the standby. Set synchronous_standby_names and each commit blocks until the standby confirms the WAL is on its disk too. — PostgreSQL Docs: "each commit of a write transaction will wait until confirmation is received that the commit has been written to the write-ahead log on disk of both the primary and standby server." This is literally synchronous_commit from 0009 extended over the network — instead of "wait for local disk," it's "wait for the standby's disk."

Logical replication — copy the meaning

Streaming copies bytes, so the replica must be an identical clone. Sometimes you want less, or different: just a few tables, into a newer major version, or a writable target. Logical replication does that by shipping row-level changes instead of physical blocks. — PostgreSQL Docs: "Logical replication is a method of replicating data objects and their changes, based upon their replication identity (usually a primary key). We use the term logical in contrast to physical replication, which uses exact block addresses and byte-by-byte replication." It works publish/subscribe: a publisher exposes a set of tables; subscribers subscribe. — PostgreSQL Docs: "Logical replication uses a publish and subscribe model…"

Because it moves meaning, not bytes, logical replication unlocks what physical can't: replicate a subset of tables, between different major versions, or across different platforms — and the target is a normal, writable database. — PostgreSQL Docs — use cases: "Replicating between different major versions of PostgreSQL"; "Replicating between PostgreSQL instances on different platforms"; "Sending incremental changes in a single database or a subset of a database to subscribers as they occur." The cost is that it's higher-level: it tracks changes by replication identity (the primary key), not raw disk blocks, so it has more rules and some limitations a byte copy never worries about.

Postgres vs MySQL — what crosses the wire

Check yourself

From memory. Two items reach back on purpose.

-- on a primary with a standby attached:
SELECT client_addr, state, sync_state, replay_lag FROM pg_stat_replication;
SHOW synchronous_standby_names;      -- empty = all async

-- logical replication shape (publisher, then subscriber):
CREATE PUBLICATION mypub FOR TABLE orders, customers;                 -- on publisher
CREATE SUBSCRIPTION mysub CONNECTION 'host=… dbname=…' PUBLICATION mypub;  -- on subscriber