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
- 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
- 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
- Week 158 h
- Online schema changes
- Locks taken by DDL
- Backfills in batches
- Expand and contract migrations
- Tools such as
gh-ostfor MySQL
Hand inThe safe column-add sequence and the longest-lock report
- 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.
| Repo | What to read | Week |
|---|---|---|
| postgres/postgres | src/backend/access/transam (the write-ahead log) and the replication code | 13 |
| debezium/debezium | How change capture reads a log and handles failures | 14 |
| github/gh-ost | How an online schema change copies and switches tables (MySQL) | 15 |
| brianfrankcooper/YCSB | A standard load generator for comparing stores | 16 |
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.
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.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.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.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.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.