Part V — Transactions, Concurrency, and Performance

Chapter 21. Backup, Recovery, and Administration

The last skill in the book's core sequence is the one you hope never to need and must never be without: getting the database back after it is gone. Disks fail; humans run UPDATE without WHERE (Chapter 10's classic); ransomware encrypts data directories; a migration script drops the wrong table. Backup is the copy you made; recovery is the act of restoring it — and the industry's oldest lesson is that they are only the same thing if you have practiced: an untested backup is a hope, not a plan. This chapter covers both platforms' backup tooling, the log-driven recovery that makes databases survive crashes, point-in-time recovery, the maintenance that keeps engines healthy (vacuum, statistics), monitoring, replication, and the administrator's routine.

After studying this chapter you will be able to:

• Describe the DBA's responsibilities and daily toolkit.
• Configure and monitor a running database.
• Choose between logical and physical backups and know when each wins.
• Use pg_dump/pg_restore and mysqldump (and their siblings) fluently.
• Explain WAL, redo/undo, and the binary log as recovery substrates.
• Perform and reason about point-in-time recovery.
• Run maintenance: VACUUM, ANALYZE, table optimization, and statistics.
• Monitor resource utilization and the metrics that matter.
• Explain replication and high-availability options on both platforms.
• Run the administrative routine and troubleshoot methodically.

21.1 Database administrator responsibilities

Chapter 1 drew the DBA role; this is its job description. The DBA owns: availability (the database is up when users need it), durability (backups exist, are verified, and restore drills have proved them), performance (Chapter 19's tuning, on call), security (Chapter 20's checklist, audited), schema evolution (migrations reviewed and applied — Chapter 24), capacity (disk, memory, connections ahead of demand), and incidents (the troubleshooting ladder of Section 21.11, at 3 a.m. if needed).

The posture that organizes all of it is the same one this book has taught throughout: automation over heroics. Every recurring task in this chapter — backup, verify, vacuum, statistics, monitoring alerts — is a script with a schedule, not a memory. The DBA's real product is a system that runs itself and pages only when judgment is needed.

21.2 Database configuration and monitoring

Chapter 14 configured the basics; administration makes them a reviewed, monitored set. The settings that matter on any deployment:

Monitoring starts with seeing what is running: PostgreSQL's pg_stat_activity (every session, its query, its wait state) and MySQL's SHOW PROCESSLIST (or the sys schema's views) answer "what is the database doing right now" — the first question of every incident. Configuration review is periodic: what changed, what is non-default, and why (a config file full of unexplained overrides is technical debt with uptime consequences).

21.3 Logical and physical backups

The two backup families differ in what they copy:

• Logical backups (pg_dump, mysqldump) copy rows and schema as SQL or archives — portable (across versions, often across platforms), compressible, per-database selective, and slower (they read and serialize everything). They restore by replaying statements — constraints included, which is both a correctness feature and the reason restores are slow.
• Physical backups (pg_basebackup, MySQL's clone plugin / XtraBackup-family) copy the data directory itself — fast (file copies), exact (a byte-level twin, including indexes and statistics), and tightly bound to the same major version (and often the same platform). Restores are file copies plus recovery — minutes, not hours.

The policy that follows: logical dumps as the portability and dev-seeding layer (every developer's local university database is born from Appendix H's dump), physical plus archived logs as the production recovery layer (fast restore plus point-in-time, Section 21.7). And the rule that overrides both: verify by restoring — a backup that has never been restored is a hypothesis.

21.4 PostgreSQL backup and restoration tools

pg_dump's three formats and their restore paths:

pg_dump -Fc university > university.dump          # custom: compressed, pg_restore
pg_dump -Fd university -j 4 -f dumpdir/           # directory: parallel dump/restore
pg_dump -s university > schema.sql               # plain SQL (schema only here)

pg_restore -d university_new -j 4 university.dump
psql -d university_new -f schema.sql              # plain SQL restores via psql
pg_dumpall --globals-only > globals.sql           # roles and tablespaces (no -d!)

The professional details: custom format (-Fc) is the default choice (compressed, selective restore with -t table, parallel with -j); pg_dumpall is the only tool that captures roles — the Chapter 20 account inventory lives outside the database, and a restore that skips globals restores data without users; and consistency is automatic (the dump sees one snapshot — Chapter 18's MVCC in your favor). The restore drill: create university_restore, restore the dump, run the six-count verification (5, 6, 12, 10, 13, 28) — the same discipline as every load since Chapter 9.

21.5 MySQL backup and restoration tools

mysqldump, with the flags that matter:

mysqldump --single-transaction --routines --triggers \
          --databases university > university.sql

mysql -u root -p university_new < university.sql   # restore via the client

--single-transaction gives the consistent snapshot (InnoDB, no locks); --routines and --triggers capture the stored objects of Chapter 22 (a dump that omits them restores a database missing its logic — the classic silent gap); --databases includes the CREATE DATABASE. The modern layer on top: MySQL Shell's util.dumpInstance/util.loadDump (parallel, chunked, faster) and, for physical copies, the clone plugin (CLONE INSTANCE FROM 'user'@'host':3306 IDENTIFIED BY '...' — a built-in physical provisioning tool) and the XtraBackup family. The same verification law applies: restore to university_restore, run the counts, diff the dashboards (Chapter 17's parity method, used on your own backups).

21.6 Transaction logs and recovery

The substrate of both crash recovery and point-in-time recovery is the log — Chapter 4's WAL promise, mechanized.

PostgreSQL's WAL records every change before the data pages change (write-ahead). Crash recovery: after a crash, the server replays WAL from the last checkpoint — a periodic point where all changed pages were flushed — reapplying changes for committed transactions and discarding records for uncommitted ones. Recovery is automatic: start the server, it replays, it comes up consistent. The same WAL, archived (archive_mode = on, archive_command), becomes the replay tape of Section 21.7.

MySQL's logs divide the labor: the redo log (InnoDB's WAL — crash recovery identical in spirit: replay from the last checkpoint, innodb_fast_shutdown and recovery phases on startup) and the binary log (server-level record of committed changes — the source of replication and point-in-time recovery, written after commit). The undo log provides rollback and MVCC (Chapter 18). The operational distinction to carry: PostgreSQL has one log doing both jobs (WAL drives recovery and replication); MySQL splits them (redo for crashes, binlog for replication and PITR) — which is why Chapter 17 maps them side by side and why each platform's PITR recipe below differs in its tape, not its idea.

21.7 Point-in-time recovery concepts

Backups answer "restore to last night." Point-in-time recovery (PITR) answers "restore to 14:32, before the UPDATE without WHERE" — base backup plus log replay:

• PostgreSQL: a base backup (pg_basebackup -D backup/) plus continuously archived WAL (restore_command fetching segments). Restore = copy the base back, create recovery.signal, configure restore_command and recovery_target_time, start the server — it replays WAL to the target, then opens for service. pg_wal/archive_retention policies keep the tape long enough.
• MySQL: restore the last physical/base copy, then replay the binary log to the chosen moment: mysqlbinlog --start-datetime=... --stop-datetime=... binlog.* | mysql — with GTIDs (--source-id/auto-positioning) making the replay exact and idempotent-safe.

The canonical story that makes PITR worth its setup: a migration at 14:35 drops enrollment. Base backup is from midnight. PITR replays midnight → 14:32 (just before), the table never drops, and the lost window is minutes — versus a full day with the midnight-only backup. The setup cost (archiving, retention, a practiced runbook) buys the difference between an incident and a resume-generating event.

21.8 Database maintenance and vacuuming

Engines need housekeeping, and each platform's history is visible in its tools.

PostgreSQL's VACUUM reclaims the dead row versions of Chapter 18's MVCC — the table's old versions that updates and deletes left behind. VACUUM reclaims space for reuse (fast, concurrent); VACUUM FULL rewrites the table compactly (exclusive lock — rare, scheduled); autovacuum runs both on thresholds automatically, and tuning autovacuum (per-table thresholds, more aggressive on hot tables) is the classic PostgreSQL operational skill. ANALYZE refreshes statistics (Chapter 19) — autovacuum does it too, but after bulk loads you do it yourself. And the maintenance horizon unique to PostgreSQL: transaction ID wraparound — the counter is finite, so very old dead tuples must be vacuumed before it wraps; monitoring wraparound risk (datfrozenxid) is the platform's one do-not-ignore alarm.

MySQL's equivalents: purge threads reclaim undo history automatically (the Chapter 18 pathology's cure); OPTIMIZE TABLE rebuilds (defragments) tables — on InnoDB it recreates the clustered index; ANALYZE TABLE refreshes statistics. The shared law: maintenance is automatic until it isn't — monitoring (next section) is what tells you when, and the maintenance calendar is what keeps 3 a.m. quiet.

21.9 Monitoring resource utilization

The metrics that matter, in the order incidents actually find them:

Two disciplines make the table useful. First, OS metrics belong in the dashboard too — disk space (the silent killer; WAL/undo growth from Section 21.8's pathologies lands here first), I/O wait, memory. Second, alerts on thresholds, not eyeballs: cache hit ratio falling, dead tuples climbing, disk at 80%, replication lag (next section) — each is a script and a threshold, because monitoring you must remember to check is monitoring that fails at 3 a.m. pg_stat_statements deserves its own sentence: install it, reset it after tuning, and it is Chapter 19's slow-query list, pre-computed.

21.10 Replication and high availability

Replication keeps a second copy of the database continuously fed from the first — for read scale, for failover, and for the restore drills that never touch production. The platforms' mechanisms mirror their logs (Section 21.6):

• PostgreSQL streaming replication: a standby connects to the primary and ships WAL records as they are written — physical replication of everything (Chapter 26 details the cluster topology); logical replication (publications/subscriptions of tables) moves selected tables between clusters, versions, and even into analytics targets.
• MySQL replication: the replica reads the primary's binary log and replays committed changes — statement or row based, with GTIDs giving each transaction a global identifier that makes replicas positionable and failover auditable; group replication and InnoDB Cluster add automated failover.

High availability is the goal the machinery serves: a failover target warm enough to promote in minutes. The vocabulary that measures it: RPO (recovery point objective — how much data you may lose: async replication's seconds-to-zero with sync) and RTO (recovery time objective — how long to be back: promotion time plus routing). The open-source ecosystem wraps both platforms in the same patterns — PostgreSQL's Patroni, MySQL's Orchestrator/InnoDB Cluster — automating health checks, promotion, and routing. Chapter 26 takes replication to distributed architecture; here it is the administrator's insurance layer.

21.11 Routine administration and troubleshooting

The routine that keeps everything above boring — the calendar of a well-run database:

• Daily: backup ran and verified (automated restore-into-scratch plus counts); log scan (errors, lock timeouts, slow queries over threshold); disk and replication-lag alerts green.
• Weekly: bloat/history growth review; pg_stat_statements/sys top-query review against Chapter 19's checklist; capacity trend (two weeks of disk growth extrapolated).
• Monthly: restore drill on a real backup (the untested-backup law, executed); security checklist pass (Chapter 20); version/patch review (both platforms ship regular minor releases — security patches are not optional).
• Quarterly: recovery runbook rehearsal (pick an hour, lose a table on purpose, recover with PITR, time it) and the configuration review of Section 21.2.

Troubleshooting extends Chapter 14's connection ladder upward — the same methodical rungs: is it up (service, port), can you connect (auth ladder), what is it doing (pg_stat_activity/PROCESSLIST — locks? long queries? waiting?), is it healthy (disk, memory, IO wait — Section 21.9), is it the query (EXPLAIN, Chapter 19), is it maintenance debt (bloat, undo, stale statistics — Section 21.8). Two incident rules to internalize now: write down the timeline as you go (the post-incident review is written from notes, not memory), and change one thing at a time — an incident plus two uncoordinated fixes is now two incidents.

Chapter Summary

• The DBA owns availability, durability, performance, security, schema evolution, capacity, and incidents — automated, not heroic.
• Configuration review (memory, connections, logging) plus activity views (pg_stat_activity, PROCESSLIST) are the administration baseline.
• Logical backups (pg_dump/mysqldump) are portable and selective; physical (basebackup/clone) are fast and exact — logical for portability, physical+logs for production recovery.
• pg_dump's custom format, parallel directory dumps, and pg_dumpall's globals; mysqldump's --single-transaction/--routines/--triggers; both restore into verification counts.
• WAL (PostgreSQL) and redo+binary log (MySQL) drive crash recovery — automatic replay from the last checkpoint — and archived logs are PITR's tape.
• PITR: base + replay to a target time — the "restore to 14:32" capability whose practice separates an incident from a disaster.
• Maintenance: PostgreSQL's vacuum/autovacuum/analyze and wraparound horizon; MySQL's purge, OPTIMIZE, ANALYZE — automatic until monitoring says otherwise.
• Monitoring: activity, top queries (pg_stat_statements / sys), cache ratios, locks, connections, bloat, disk — thresholds and alerts, not eyeballs.
• Replication: WAL streaming (physical) and logical publications; binlog + GTIDs; RPO/RTO measure high availability; Patroni/InnoDB Cluster automate it.
• The routine is daily/weekly/monthly/quarterly, ending in rehearsed restore drills; troubleshooting climbs the extended ladder, one change at a time, timeline written down.

Key Terms

Laboratory Exercises

1. The full backup cycle: dump university on both platforms (pg_dump -Fc; mysqldump --single-transaction --routines), restore each into university_restore, and verify the six counts plus one dashboard query. Expected results: counts 5, 6, 12, 10, 13, 28 on both restored copies; dashboard rows identical to the originals.
2. Selective and parallel: dump only course and course_section (pg_restore-compatible custom format; mysqldump named tables), restore them into a scratch database, and time a directory-format parallel dump (-j 4) against the plain dump. Expected results: selective restore verified by counts (10, 13); parallel dump measurably faster — record the ratio.
3. Globals and stored objects: run pg_dumpall --globals-only and inspect the roles in the output; on MySQL, create a trivial procedure in university_dev, dump without and with --routines, and diff the files. Expected results: roles present in globals dump; procedure absent from the first dump, present in the second — the silent-gap lesson in one diff.
4. Crash recovery observation (dev server): stop the server uncleanly (or simulate by restarting mid-write script — carefully, in a disposable VM), restart, and read the recovery log lines (PostgreSQL replay messages; MySQL InnoDB recovery phases). Expected results: the server replays from the last checkpoint and comes up consistent — recovery demonstrated, not just described.
5. PITR rehearsal (PostgreSQL, disposable instance): take a base backup, enable WAL archiving, note the clock, make three changes an hour apart (including one "accidental" DELETE of Fall 2026 enrollments), then recover to just before the delete and verify the 8 in-progress enrollments are back. Expected result: recovered database matches the pre-delete state (28 enrollments, 8 NULL grades) — with the timeline and commands documented as the runbook.
6. Maintenance pass: after the Laboratory 19 bulk load and its updates, run and record VACUUM (ANALYZE) statistics (dead tuples before/after, n_dead_tup) on PostgreSQL and ANALYZE TABLE + OPTIMIZE TABLE on MySQL; capture sizes before and after. Expected result: dead-tuple counts drop, statistics refresh, sizes shrink (or space marked reusable) — each platform's housekeeping, evidenced.

Review Questions and Exercises

1. Why is a logical dump the wrong primary tool for a 2 TB production database, and what replaces it? Restore time (hours of statement replay) and dump time; physical base backups + archived logs with PITR — logical dumps remain the portability/dev layer.
2. What does pg_dumpall capture that every pg_dump misses, and why does a restore need it? Roles, tablespaces, cluster globals — users live outside per-database dumps; without them the restored database has data and no accounts.
3. Name the three mysqldump flags a stored-object database must carry, and the failure each prevents. --single-transaction (consistency), --routines (procedures/functions), --triggers (trigger logic) — omitting the last two restores a brainless database.
4. Explain crash recovery in one sentence, for either platform. Replay the change log from the last checkpoint, reapplying committed work and discarding uncommitted — automatically, at startup.
5. PostgreSQL has one log for recovery and replication; MySQL has two. Which is which, and what does each do? PG: WAL does both; MySQL: redo log for crash recovery, binlog for replication/PITR (committed changes only).
6. A table was dropped at 14:35; base backup is from midnight; logs are archived. State the recovery recipe and the expected data-loss window. Restore the base, replay logs stopping just before 14:35 (restore_target_time / mysqlbinlog stop); loss window ≈ zero to the last transactions after 14:35-backup-base... precisely: everything after the stop point — choose 14:32 and lose ~3 minutes of writes.
7. Why does VACUUM FULL need scheduling care, and what does plain VACUUM not do? It takes an exclusive lock while rewriting the table; plain VACUUM reclaims space for reuse but does not return it to the OS or compact the table.
8. What is transaction ID wraparound in one sentence, and what is the monitoring signal? The finite transaction counter forces old dead tuples to be vacuumed before it wraps; datfrozenxid age against the wraparound limit is the alarm.
9. Which two views answer "what is the database doing right now" and "what are the top queries over time" on each platform? PG: pg_stat_activity, pg_stat_statements; MySQL: PROCESSLIST/sys.processlist, sys top-statement views.
10. Why does disk-space monitoring outrank query tuning in operational priority? Disk exhaustion stops the database cold (WAL cannot be written, undo cannot grow) — no query matters when the server is down; it is also the earliest signal of the maintenance pathologies.
11. Define RPO and RTO, and give the async-replication values for each. Recovery point / recovery time objectives; async gives RPO ≈ replication lag (seconds) and RTO ≈ promotion + routing (minutes with tooling).
12. State the two incident rules and why the second one exists. Write the timeline as you go; change one thing at a time — concurrent uncoordinated fixes turn one incident into two.

Mini-Project

Write the operations runbook, RUNBOOK.md, for your laboratory: (1) the backup plan — what is dumped (databases, globals, stored objects), how often, to where, with the exact commands and their expected outputs; (2) the verification job — the automated restore-to-scratch and count/dashboard checks, with the pass criteria; (3) the PITR recipe for your PostgreSQL instance — base backup, archiving, the three commands of recovery, and the practiced timeline from Laboratory 5; (4) the monitoring checklist — every metric of Section 21.9 with its source query, its threshold, and its alert action; (5) the maintenance calendar — daily/weekly/monthly/quarterly, each item a command, not a memory; (6) the troubleshooting ladder, extended from Chapter 14's with the this-chapter rungs, as a diagram; (7) the incident template — timeline, one-change-at-a-time rule, and the post-incident review questions. Then execute the quarterly item once for real: deliberately destroy enrollment in university_dev, recover it from your backup with PITR, and time the exercise. The runbook that has been used is the deliverable; the runbook that has not is the hypothesis this chapter exists to kill.