a. Overview

Every hands-on so far ran a single db container. When it stops, every service that depends on it stops too: it is a single point of failure. Scaling the gateway to three replicas (hands-on 7) and adding a cache (hands-on 8) took load off the database, but did not remove that single point. A database cluster keeps copies of the data on several servers, so one of them can take over when another fails.

Slides

Click the slides and use ← → to move through them; F goes full screen and P opens the presenter view, with the notes. Open the slides in a new tab

Why a database cluster

Scaling writes needs a different technique, sharding (splitting the data across independent primaries, as Citus or Vitess do). This hands-on is about replication: every node holds all the data.

How PostgreSQL replicates: the WAL

Before changing a data page, PostgreSQL writes the change to the WAL (write-ahead log), an append-only log on disk. After a crash, the server replays the WAL to recover. Replication reuses the same log: the primary sends its WAL to the replicas, which replay it, byte by byte, and end up with identical copies.

sequenceDiagram
    participant App as Service
    participant P as Primary
    participant R1 as Replica 1
    participant R2 as Replica 2
    App->>P: INSERT INTO orders ...
    P->>P: append the change to the WAL
    P-->>App: COMMIT ok
    P->>R1: stream WAL records
    P->>R2: stream WAL records
    R1->>R1: replay the WAL
    R2->>R2: replay the WAL
    App->>R1: SELECT * FROM orders
    R1-->>App: rows (as of the last replayed record)

This hands-on uses physical streaming replication: the basis of high availability in PostgreSQL and of managed services such as Amazon RDS.

Asynchronous or synchronous?

The question is when the primary answers COMMIT ok:

This is the CAP theorem in practice: synchronous replication prefers consistency (no committed write is lost) and gives up availability of writes when the replica is unreachable; asynchronous replication prefers availability and accepts that a reader of a replica may see stale data and that a failover may lose the last writes.

Architecture

flowchart LR
    app["service\n(account, order...)"]
    subgraph cluster [PostgreSQL cluster]
        p[("pg-primary\n:5432\nread-write")]
        r1[("pg-replica1\n:5433\nread-only")]
        r2[("pg-replica2\n:5434\nread-only")]
        p e1@-->|"WAL stream"| r1
        p e2@-->|"WAL stream"| r2
    end
    app e0@==>|"writes + reads"| p
    app e3@-.->|"reads"| r1
    app e4@-.->|"reads"| r2
    e0@{ animate: true }
    e1@{ animate: true }
    e2@{ animate: true }

• All three nodes run the official postgres:17 image. A small entrypoint decides the role: an empty node with PRIMARY_HOST clones the primary with pg_basebackup and starts as a hot standby (it accepts reads).
• The application decides where each query goes. Here the routing lives in the connection string: libpq and the PostgreSQL JDBC driver accept a list of hosts and pick the one with the right role (target_session_attrs / targetServerType).

Failover

When the primary fails, a replica must be promoted to primary, the other replicas must follow it, and the clients must find it. In this hands-on you do each step by hand, to see what an automatic tool does for you: