Replication and HA Practice — PostgreSQL Mastery

What You'll Build

You will set up streaming replication between a primary and a standby using a replication slot, verify that writes flow to the standby and that it serves read-only queries, then perform a controlled failover by promoting the standby — observing exactly what happens to replication, client routing, and the old primary. It is a hands-on walk through the core mechanics behind every HA setup.

Using Docker for two PostgreSQL instances keeps the exercise self-contained and repeatable. You will configure the primary for replication, clone it into a standby that streams via a slot, confirm replication health, and then promote the standby — experiencing both the power and the responsibilities (slot management, client redirection) that the HA tooling lessons automate.

Analogy🏏Cricket

🏏 Think of it like cricket: Just as setting up a new league means first writing the registration rules and eligibility checks, then entering the teams and players, then running the fixtures, you will first define the constrained schema, then load data, then query it. The insight is that the rules come before the play — get the eligibility and scoring regulations right first, and the matches that follow stay orderly because the framework already enforces fair play. Run the sequence backwards to see why the order is non-negotiable: admit teams first and write the eligibility rules afterwards, and you discover mid-season that three registered players were never eligible — now every result they touched is in dispute and unwinding it means re-examining a season of records. That is retrofitting constraints onto a table already full of violating rows: the ALTER fails until you hand-clean the data it was supposed to prevent. Founding the league in the right order also changes how boldly you can operate later: fixtures can be scheduled aggressively because the framework guarantees every listed player is legal, just as queries and application code can stay simple when the schema has already promised the data is sound.

Prerequisites

Completion of lessons 26–29, or equivalent understanding of streaming replication, slots, and failover.
Docker installed and running, for two isolated PostgreSQL 16 instances.
Basic command-line comfort and the psql client.
Open ports for two PostgreSQL containers (e.g. 5432 and 5433).
Understanding that this is a practice setup, not a production-hardened HA cluster.

Setup & Project Structure

Create a Docker network and start a primary PostgreSQL container configured for replication: wal_level set to replica, enough WAL senders, and a replication role permitted in pg_hba.conf. You will create a physical replication slot on the primary so the WAL the standby needs is retained even if it briefly disconnects.

Analogy🏏Cricket

🏏 Think of it like cricket: before a tournament you set up a dedicated ground and a clean scoring system rather than borrowing a crowded club pitch. Just as you create a fresh database so your exercise work is isolated, a groundsman prepares a separate net facility so practice never disturbs the main square. Just as you will build four related tables — team, player, match, and appearance — a league secretary maintains four linked registers: the clubs, the registered players, the fixtures, and the record of who actually took the field in each fixture. Just as an appearance ties a specific player to a specific match, that turnout register is the join that connects players to the games they played. And just as keeping everything in one schema keeps the practice tidy, running one competition under one governing body keeps all records consistent. The payoff: a clean, isolated setup lets you apply type and constraint choices deliberately and see exactly how they behave.

Keep both containers on the same Docker network so the standby can reach the primary by name. Use clearly named containers (pg-primary, pg-standby) and distinct host ports so you can connect to each independently with psql to observe their roles.

bash

docker network create pgnet

# Primary
docker run -d --name pg-primary --network pgnet -p 5432:5432 \
  -e POSTGRES_PASSWORD=secret postgres:16 \
  -c wal_level=replica -c max_wal_senders=10 -c max_replication_slots=10

# Create a replication role and a physical slot on the primary
docker exec -it pg-primary psql -U postgres -c \
  "CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD 'replpass';"
docker exec -it pg-primary psql -U postgres -c \
  "SELECT pg_create_physical_replication_slot('standby1');"
# (allow the repl role in pg_hba.conf for the standby's address, then reload)

Step 1 — Clone the Standby with pg_basebackup

Create the standby by taking a base backup of the primary directly into the standby's data directory, using the replication role and the slot you created. The -R flag writes the standby's connection settings automatically, and --slot=standby1 ties it to the retained-WAL slot so it streams from the right position.

Start the standby container pointing at that data directory. On startup it connects to the primary, begins streaming WAL, and comes up in hot-standby mode — a read-only, continuously-updated copy. You have now turned one database into a replicated pair.

Analogy🏏Cricket

🏏 Think of it like cricket: Just as a new scorer is brought up to speed by handing them a complete copy of the scorebook so far, then having them follow the live commentary from that point on, the standby is seeded with a base backup and then streams WAL from there. The insight is that you join a replica by giving it the full current state once, then keeping it in sync with the ongoing feed — a snapshot to start, the stream to maintain. The two-phase join has its precise mechanics: the copied book must state exactly which ball it ends on, so the new scorer knows where to pick up the commentary — pg_basebackup records the WAL position the copy corresponds to, and streaming resumes from that LSN, no ball missed, none double-entered. The handoff is also where joins fail in practice: if the live feed's archive has already discarded the overs between the copy and now — WAL recycled before the standby connected — the new scorer has a gap nothing can fill and must be handed a fresh copy; a replication slot created before seeding holds those overs until the join completes. And the copy itself rides the live feed without stopping play: the primary keeps scoring while being copied, which is the whole reason the position bookmark, not a quiet moment, defines where the stream begins.

sql

# Base-backup the primary into the standby's data dir, wiring up streaming + slot
docker run -d --name pg-standby --network pgnet -p 5433:5432 \
  -e POSTGRES_PASSWORD=secret postgres:16

docker exec -it pg-standby bash -lc '
  rm -rf /var/lib/postgresql/data/* &&
  PGPASSWORD=replpass pg_basebackup -h pg-primary -U repl \
    -D /var/lib/postgresql/data -Fp -Xs -R --slot=standby1 -P'
# -R writes primary_conninfo + standby.signal; restart the container to stream

Step 2 — Verify Replication Is Working

Confirm the link from both ends. On the primary, pg_stat_replication should list the standby with a streaming state and minimal lag; on the standby, pg_is_in_recovery() returns true, marking it read-only. Then write a row on the primary and read it back on the standby to prove changes flow across within moments.

Also confirm the standby rejects writes — attempting an INSERT on it errors because it is in recovery. This read-only behaviour is exactly what lets you safely route read queries to a standby while all writes go to the primary, the read-scaling benefit from the replication lesson.

Analogy🏏Cricket

🏏 Think of it like cricket: Just as you confirm the second scorer is genuinely keeping up by checking a freshly recorded run appears in their book too — and that they only copy, never invent entries — you verify the standby receives writes and refuses to originate them. The insight is that trust in a replica requires checking both directions: that it faithfully receives the feed, and that it never tries to author its own changes that would diverge from the source. Each direction has its concrete test. Receipt: write a marked row on the primary and watch it appear on the standby — then quantify the watching with pg_stat_replication on the primary (is the standby connected, how far behind are its write and replay positions) and pg_is_in_recovery() on the standby, which must answer true: 'I am a follower'. Non-authorship is enforced, not merely hoped: a standby in recovery refuses writes outright — attempt an INSERT and the 'read-only transaction' error is the system keeping the promise — because a copier who invents even one entry has diverged, and divergence discovered later means rebuilding the replica entirely.

sql

-- On the PRIMARY: the standby should appear, streaming, near-zero lag
docker exec -it pg-primary psql -U postgres -c \
  "SELECT client_addr, state, sync_state,
          pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag FROM pg_stat_replication;"

-- On the STANDBY: it is in recovery (read-only), and sees primary's writes
docker exec -it pg-standby psql -U postgres -c "SELECT pg_is_in_recovery();"   -- t
docker exec -it pg-primary psql -U postgres -c "CREATE TABLE t(x int); INSERT INTO t VALUES (42);"
docker exec -it pg-standby psql -U postgres -c "SELECT * FROM t;"               -- sees 42
docker exec -it pg-standby psql -U postgres -c "INSERT INTO t VALUES (1);"      -- ERROR: read-only

Step 3 — Promote the Standby (Failover)

Simulate a primary failure by stopping the primary container, then promote the standby with pg_promote (or pg_ctl promote). The standby exits recovery, becomes a read-write primary on its own timeline, and pg_is_in_recovery() now returns false. You have performed a manual failover — the core action HA tooling automates.

Observe the consequences you must now handle manually: clients still pointing at the old primary's address are stranded, so they must be redirected to the promoted node; and the old primary, if restarted naively, could become a second primary — the split-brain risk. This is precisely why production uses consensus and a proxy rather than manual steps.

Analogy🏏Cricket

🏏 Think of it like cricket: Just as promoting the vice-captain mid-match means nothing until the team is told to follow them, and the old captain must be kept from issuing rival orders, promoting the standby means nothing until clients are redirected and the old primary is fenced. The insight is that the promotion is only half the job — succession also demands telling everyone where to look and ensuring the former leader cannot countermand, which is the part naive manual failover gets wrong. The two neglected halves each have their mechanics and their catastrophe. Redirection: clients hold connections to the old address, so promotion without rerouting yields an absurd tableau — a fully capable new primary idle while applications retry against a corpse; real setups pre-wire the switch (a proxy consulting health checks, or connection strings listing both hosts with target_session_attrs=read-write) so 'telling everyone' is a flip, not an emailed announcement. Fencing: if the old primary comes back — a reboot, a healed network — still believing it leads, any client with a stale connection can write to it, and now two books diverge: split-brain, where reconciliation means discarding someone's committed runs.

sql

# Simulate primary failure, then promote the standby to a read-write primary
docker stop pg-primary
docker exec -it pg-standby psql -U postgres -c "SELECT pg_promote();"

docker exec -it pg-standby psql -U postgres -c "SELECT pg_is_in_recovery();"   -- f (now primary)
docker exec -it pg-standby psql -U postgres -c "INSERT INTO t VALUES (99);"     -- now succeeds

# Responsibilities now on YOU (automated by Patroni/pg_auto_failover):
#  - redirect clients to pg-standby (the new primary)
#  - do NOT simply restart pg-primary as-is -> split-brain risk; reintegrate via pg_rewind

Step 4 — Testing & Verification

Confirm the full lifecycle: replication streamed writes to the standby, the standby served reads but refused writes, and after promotion it became a writable primary. Reflect on the two manual responsibilities the exercise exposed — client redirection and preventing two primaries — which are exactly what Patroni's consensus store and a proxy automate in production.

Analogy🏏Cricket

🏏 Think of it like cricket: before a match counts, you check the ground, the kit, and the Laws all behave as intended. Just as you inspect the table definitions with \d, the ground authority reviews the official team sheets and pitch report to confirm everything is set up as designed. Just as you verify that constraint-violating inserts fail, an umpire confirms that an illegal action — a bowler exceeding their over limit, an unregistered player trying to bat — is correctly rejected rather than slipping through. Just as you check that queries return correct, deterministically ordered results, the scorers confirm the batting order and totals come out the same every time, not shuffled at random. And just as you run the constraint and referential checks to prove the database enforces what you intended, the officials run through the rulebook to prove the game will hold to its Laws. The payoff: verifying the enforcement, not just the happy path, is what proves the schema is genuinely doing its job.

sql

-- After promotion, verify the new primary is writable and on a new timeline
docker exec -it pg-standby psql -U postgres -c "SELECT pg_is_in_recovery();"          -- f
docker exec -it pg-standby psql -U postgres -c "SELECT * FROM t ORDER BY x;"           -- 42, 99

-- To reintegrate the OLD primary as a standby of the new one (concept), you would
-- use pg_rewind against pg-standby, then start it with standby.signal pointing at it.
-- DO NOT bring the old primary back up accepting writes -> split-brain.

-- Clean up the practice environment
-- docker rm -f pg-primary pg-standby && docker network rm pgnet

Warning: After a manual promotion, never simply restart the old primary so that it also accepts writes — you will have two primaries diverging (split-brain), and reconciling them means data loss. Reintegrate the old node only as a standby of the new primary, using pg_rewind to align it. This hazard is the entire reason production HA relies on a consensus store and fencing rather than manual steps.

Extension Challenge: Put HAProxy in front of both nodes, health-checking each for whether pg_is_in_recovery() is false, so clients always reach the current primary automatically. Then repeat the failover and confirm your client connection follows the promotion with no config change — a hands-on glimpse of the routing layer Patroni and pg_auto_failover manage for you.

Streaming replication + a physical slot keeps a standby in sync and retains WAL it needs.
pg_basebackup with -R --slot clones the primary and wires the standby to stream.
pg_stat_replication (primary) and pg_is_in_recovery() (standby) confirm replication health and read-only status.
A standby serves reads but rejects writes; promotion (pg_promote) makes it a writable primary.
Manual failover exposes two responsibilities: redirecting clients and preventing a second primary (split-brain).
Production tooling (Patroni/pg_auto_failover + proxy) automates exactly these steps with consensus and fencing.