Capstone — build, break and defend a production PostgreSQL estate

Every control in this course has been examined on its own. The capstone puts them in one estate and asks whether they hold together — and then breaks them, twelve times, to find out.

Nothing here runs against anything you care about. Every deliverable is a disposable container, and the whole estate is torn down at the end.

The estate you are building

flowchart LR
  subgraph net["capstone network"]
    APP["app client<br/>psql / pgbench"]
    POOL["pgbouncer<br/>transaction mode"]
    P["pg-primary<br/>PostgreSQL 18"]
    S1["pg-standby-1"]
    S2["pg-standby-2"]
    ARC[("wal archive<br/>+ base backups")]
  end
  APP --> POOL --> P
  P -- "streaming" --> S1
  P -- "streaming" --> S2
  P -- "archive_command" --> ARC
  ARC -. "restore_command" .-> S1

Five containers and a directory. That is enough to exercise every failure mode in the course.

Phase 1 — build (deliverables 1 to 5)

Deliverable 1: a cluster whose initdb decisions were made deliberately

Create the primary with encoding, locale, checksums and a separate WAL directory chosen explicitly rather than inherited.

Acceptance: pg_controldata reports Data page checksum version: 1 and the encoding you intended. Record the output.

Deliverable 2: authentication that has been refused

Remove every trust rule. Create a group role, a login role that is a member of it, and ALTER DEFAULT PRIVILEGES for the owning role.

Acceptance: three results, recorded.

1. The application role connects from the app container with the right password.
2. It is refused with the wrong password, and the server log DETAIL names the reason.
3. A table created by the owner after the grants is readable by the application role — which is the only thing that tests ALTER DEFAULT PRIVILEGES.

Deliverable 3: TLS that is enforced, not requested

Generate a CA and a server certificate with a SAN. Enable ssl, distribute the CA to the client, set sslmode=verify-full, then change host to hostssl.

Acceptance: four results.

1. A verify-full client connects by the certificate’s name.
2. The same client, by a different name, fails with server certificate for "..." does not match host name "...".
3. sslmode=disable is refused with a message ending no encryption.
4. pg_stat_activity joined to pg_stat_ssl shows no session with ssl = false.

Deliverable 4: a restore, timed

Configure archiving. Take a base backup with -c fast. Restore it onto a second container and count rows.

Acceptance: the restored cluster has the same row counts, and you have written down how long the restore took. That number is the capstone’s recovery time objective.

Deliverable 5: observability that will be used in phase 3

Enable log_checkpoints, log_connections, log_lock_waits, log_temp_files, log_autovacuum_min_duration and log_min_duration_statement. Install pg_stat_statements.

Acceptance: each of those settings is on, confirmed from pg_settings, and you can grep the log for a lock wait you caused deliberately.

Phase 2 — replicate and recover (deliverables 6 to 9)

Deliverable 6: two standbys, named

Create slots, build both standbys with pg_basebackup -R -c fast, and give each a meaningful application_name and a DNS name in primary_conninfo.

Acceptance: pg_stat_replication shows both by name, streaming; a row written on the primary appears on both within seconds; and an INSERT on each standby fails with cannot execute INSERT in a read-only transaction.

Deliverable 7: a point-in-time recovery to a chosen moment

Insert a marker, note the time from the server log, then perform a destructive change. Recover a third cluster to just before it, with recovery_target_action = 'pause'.

Acceptance: while paused, pg_get_wal_replay_pause_state() reports paused, the destroyed data is present, and the destructive change is not. Record the recovery stopping before commit of transaction N line.

Deliverable 8: a rehearsed switchover

Stop writes, wait for a standby’s LSN to equal the primary’s, shut the primary down cleanly, promote.

Acceptance: pg_is_in_recovery() is false on the new primary, pg_controldata still reports the old timeline until you run a CHECKPOINT — observe that yourself — and no row exists on the old primary that is absent from the new one.

Deliverable 9: a rejoin that costs something

Write to the old primary before rejoining it, deliberately. Count the rows. Take a copy. Then pg_rewind it back.

Acceptance: the rows you wrote to the old primary are gone, and you have the count. That number is what a split brain costs, measured on your own estate rather than read about.

Phase 3 — twelve incidents

Each incident is injected on the built estate. For each one, produce: the evidence you read, the fault (not the symptom), the remedy, and the verification.

Phase 4 — the handover pack (deliverables 10 to 14)

Deliverable 10: the two numbers

Recovery time — measured, from your restore. Recovery point — measured, from your rejoin. Both against a stated objective.

Deliverable 11: every alert, fired once

Configure alerts for archive failure, standby absence, byte lag, connection utilisation and terminated by signal 9. Then cause each condition.

Acceptance: a record of each alert firing. Not the configuration — the firing.

Deliverable 12: the incident log

Twelve entries, each with evidence, fault, remedy and verification. This is the artefact that shows you can distinguish a fault from a symptom.

Deliverable 13: the runbook corrections

Every place where a procedure in this course was wrong for your estate, or incomplete. A capstone that produces no corrections has avoided the parts nobody was sure about.

Deliverable 14: the teardown

Remove every container, every volume, and every replication slot.

Acceptance: docker ps -a shows nothing from this exercise, and — if you built against anything that outlives the containers — no slot remains on it. A slot with no consumer retains WAL indefinitely, and that is the last lesson of the course.

What a pass looks like

Not fourteen ticks. A pass is:

• Two measured numbers you would defend in front of a service owner.
• Twelve incidents where the fault is named, and it is not the symptom.
• At least one place where a measurement contradicted what you expected and you kept the measurement.

That last one is the whole discipline. This course has several: an on-disk sort that beat an in-memory one, a hash aggregate twice as slow with a gigabyte as with four megabytes, a connection pooler three times slower than a direct connection, and an autovacuum that fired below its own threshold.

Every one of those was going to be written the other way round until somebody ran it.

Cross-course references

• Linux for Production Sysadmins — Part LXXXVI (Capstone) builds the cluster underneath this one, and Part LXXXIII (Operational documentation) covers the handover pack Phase 4 asks for.
• Observability for Production Sysadmins — Part CXIII (Documentation and runbooks) covers the alert-and-runbook pairing Deliverable 11 asks you to fire once each, and Part XX (Alert quality) covers why an alert with no runbook is not finished.
• Ceph & Distributed Storage — Part CXXIV (Production reference architecture) covers the storage estate a cluster this size usually sits on.

Quiz