knaxuslearn · build · ship

Roadmaps / Databases / Phase 4

Phase 4 of 4 · Study weeks 13-16

Recovery, replication and change

This phase makes the last checkpoint possible: a ledger you can restore on a deadline, fail over and change without an outage.

Topics every week

  1. Week 138 h
    • The write-ahead log
    • Base backups and WAL archiving
    • Point-in-time recovery
    • Backup verification

    Hand inA restore to a chosen minute with the timeline logged

  2. Week 148 h
    • Streaming replication
    • Failover and promotion
    • Split brain and fencing
    • Logical replication and change capture

    Hand inA promotion drill with measured data loss and downtime

  3. Week 158 h
    • Online schema changes
    • Locks taken by DDL
    • Backfills in batches
    • Expand and contract migrations
    • Tools such as gh-ost for MySQL

    Hand inThe safe column-add sequence and the longest-lock report

  4. Week 168 h
    • Security: roles, least privilege and row-level security
    • Encryption in transit and at rest
    • Audit logging
    • Review

    Hand inCheckpoint 4, the restore log, the drill report and the migration report

Practice projects

  • Practice 1, the restore (Week 13): drop a table at a known time, then restore to one minute before. Done when: the table is back, the log shows the timeline and the restore time is measured.
  • Practice 2, the failover (Week 14): kill the primary during writes and promote the replica. Done when: a table shows the writes lost, the downtime and what the old primary needed to rejoin.
  • Practice 3, the safe migration (Week 15): add a column and a constraint to a large table. Done when: the longest lock is under 1 s and a seeded bad migration (a full-table rewrite under lock) is caught on a copy.
  • Optional, change capture: stream ledger changes with logical replication or Debezium. Done when: a consumer rebuilds the balances and they match.

GitHub repos to study

Existence and last commit were checked on 22 Nov 2026 by shallow clone. Every repository below had a commit in the last 12 months.

RepoWhat to readWeek
postgres/postgressrc/backend/access/transam (the write-ahead log) and the replication code13
debezium/debeziumHow change capture reads a log and handles failures14
github/gh-ostHow an online schema change copies and switches tables (MySQL)15
brianfrankcooper/YCSBA standard load generator for comparing stores16

Milestone checkpoint

Run Checkpoint 4. Write the restore goal (15 minutes), the failover drill steps and the migration plan first. Turn on archiving, run the restore, run the promotion drill and run the safe migration. Hand in the restore log, the drill report, the lock report, ADR-7 (the recovery and failover policy) and a one-page memo on which database you would pick for each of the three side workloads.

Check yourself

Answer each question in your head first, then open it to check.

  1. What is the difference between recovery time and recovery point?
    Recovery time is how long you are down. Recovery point is how much recent data you can lose.
  2. Why test a restore, not just a backup?
    A backup that cannot be restored is not a backup. Only a restore shows missing files, wrong versions and time needed.
  3. What is split brain, and how does fencing prevent it?
    Two nodes both believe they are the primary and accept writes. Fencing stops the old primary, for example by cutting its network or storage access, before the new one starts.
  4. Why is adding a column with a default sometimes dangerous?
    On older versions or with a volatile default, it can rewrite the whole table under a lock. Check the version's behaviour and test on a copy.
  5. What should an application role be allowed to do?
    Only the statements it needs on the tables it uses: no superuser, no schema changes and no access to other schemas.