Backup, Replication & High Availability
The photos that were "backed up" 📸
Hana keeps every family photo on her phone, plus a copy in a second folder on the same phone, "just in case". The phone falls into the sea. Both copies are gone: a copy that shares the same fate is not a backup.
Databases fail in more ways than phones: disks die, a data centre loses power, ransomware encrypts the files, a deploy runs a migration with a bug, and somebody types DELETE FROM orders without a WHERE. This lesson is about getting the data back, and about staying online while one machine is down.
What you'll learn
- Logical and physical backups, and how to take a consistent one
- Point-in-time recovery (PITR): restoring to the second before a mistake
- Why a backup you have never restored is only a hope
- Replication with a primary and replicas, synchronous versus asynchronous, replication lag and failover
- The two numbers that drive every design: RPO and RTO
The labs are in Python: you verify a restored dump, replay an archived log to a point in time, and plan a failover.
Two numbers: RPO and RTO
- RPO (Recovery Point Objective): how much data, measured in time, the business can afford to lose. "RPO 5 minutes" means after a disaster at 14:06 you must be able to recover everything up to at least 14:01.
- RTO (Recovery Time Objective): how long the service may be down before it is back. "RTO 1 hour" means users are working again by 15:06.
Both are business decisions with a price. A nightly backup gives an RPO of up to 24 hours; continuous log archiving brings it down to minutes; a synchronous replica brings it close to zero for a failed server. A smaller RTO needs faster restores, or a standby that is already running.
Replication is not a backup. A replica copies every change within seconds, including DROP TABLE, a buggy migration and ransomware's writes. Replicas protect you from failed machines; backups protect you from bad changes. You need both.
Logical and physical backups
Logical backups save the data as SQL statements or a portable archive: PostgreSQL's pg_dump, MySQL's mysqldump, SQLite's .dump. They are readable, work across versions and platforms, and can restore a single table. They are slower to restore, because every row is inserted again and every index rebuilt. pg_dump takes a consistent snapshot even while the database is in use (mysqldump --single-transaction does the same for InnoDB tables), and it does not include roles: pg_dumpall --globals-only saves those.
Physical backups copy the database's files: pg_basebackup, storage snapshots, or for SQLite the online backup API (.backup, Python's Connection.backup()) and VACUUM INTO. They restore much faster. A PostgreSQL or MySQL physical backup restores only onto the same major version and platform; a SQLite database file, by contrast, is portable across platforms and stays readable by newer versions. Never just copy the files of a running database with cp: a write in the middle of the copy can leave you with a file that is inconsistent or corrupt. Use the tools made for it.
Where backups live
- The 3-2-1 rule: at least 3 copies of the data, on 2 different kinds of storage, with 1 copy off-site.
- Keep at least one copy immutable or offline, so ransomware or a stolen admin account cannot delete it.
- Encrypt backups and restrict who can read them: a backup is the whole database in one file.
- Decide retention: how many daily, weekly and monthly copies, and for how long.
A backup is only a hope until you restore it
Backups fail silently: the job dumped an empty database, missed a new schema, ran out of disk halfway, or the files are encrypted with a key nobody can find. The only proof is a restore. Schedule restore drills: restore into a fresh server, check row counts and checksums against what the backup job recorded, run the application's smoke tests, and time the whole thing: that time is your real RTO.
Lab exercise 1
PythonGraded in Code LabA nightly job writes a logical dump of a SQLite database. Its first line is a manifest with the row count of every table at backup time, such as -- manifest: customers=3 orders=4. The helper read_manifest(dump) returns it as a dictionary.
Write restore_and_verify(dump) so it restores the dump into a fresh in-memory database and returns exactly one line:
restore FAILED: the dump does not runwhen running the dump raisessqlite3.Errorrestore FAILED: table T is missingwhen a manifest table does not exist after the restorerestore FAILED: T has N rows, manifest says Mfor the first table whose count differs- otherwise
restore OK: customers=3 orders=4(every manifest table with its count)
Check tables in manifest order. Only count tables that really exist, so a strange name in the manifest never reaches your SQL.
Example: a dump in which the job skipped the last order prints restore FAILED: orders has 3 rows, manifest says 4. (A dump cut off before its final COMMIT is worse: the whole dump is one transaction, so a real restore keeps nothing at all.)
- A complete dump restores and matches
- A dump that missed a row does not match
- A corrupted dump does not run
- A table the backup job forgot
- An empty table is fine when the manifest says 0
Example run
- Input
-- manifest: customers=3 orders=4 BEGIN TRANSACTION; CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL); INSERT INTO "customers" VALUES(1,'Amal'); INSERT INTO "customers" VALUES(2,'Bilal'); INSERT INTO "customers" VALUES(3,'Carla'); CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), total_cents INTEGER NOT NULL); INSERT INTO "orders" VALUES(1,1,2500); INSERT INTO "orders" VALUES(2,1,900); INSERT INTO "orders" VALUES(3,2,4100); INSERT INTO "orders" VALUES(4,3,1500); COMMIT;
- Expected output
restore OK: customers=3 orders=4
import sqlite3
import sys
# stdin: a logical backup ("dump") produced by a nightly job. Its first line is a
# manifest written at backup time: "-- manifest: customers=3 orders=4".
dump = sys.stdin.read()
def read_manifest(dump_text):
"""{"customers": 3, "orders": 4} from the manifest line, in the order it lists them."""
first = dump_text.splitlines()[0]
pairs = first.split(":", 1)[1].split()More lines in Code Lab: 16
Hints and instant feedback are in Code Lab. Optional practice: it does not block finishing the lesson.
JavaScript and Python also run in your browser without an account; other languages use the secure server runner and need a free account.
Point-in-time recovery
A nightly backup alone can only take you back to last night. Point-in-time recovery gets you to any moment since then, by combining two things:
- a base backup (a physical copy taken at some point), and
- the archive of the write-ahead log (WAL): every change the database made after that, in order.
To recover, you restore the base backup and replay the archived log up to a target time, for example the second before someone ran DELETE FROM products by mistake.
00:00 base backup 09:00 T1 commits 10:15 T2 commits 14:06 T5: the accidental DELETE
│──────────────────────│────────────────────│──────────────────────────│──────────▶ archived WAL
restore here, then replay ────────────────────────────────▶ stop at 14:05:59
Replay applies only committed work. A transaction that rolled back, or that had not committed by the target time, leaves no trace, exactly as atomicity promises. In PostgreSQL you enable continuous archiving (archive_mode with archive_command or archive_library), take base backups with pg_basebackup, and recover by setting restore_command and recovery_target_time. By default (recovery_target_inclusive = on) a transaction that committed exactly at the target time is included. MySQL does the same by replaying its binary log with mysqlbinlog.
PITR also explains the RPO of a backup design: you can lose at most the log that had not been archived yet. Archive every few minutes, or stream it continuously, and the RPO shrinks to match.
With these example figures the nightly dump misses both targets, PITR fixes the data loss but replaying a day of log keeps the restore slow, and only the standby replica meets both. Yet the last lines matter most: the replica would have copied the mistake, so a real design combines a replica for failures with backups and PITR for bad changes. Change the targets and sizes to see how the choice moves.
# Worst-case data loss (RPO) and restore time (RTO) of three strategies, for one database.
# These are EXAMPLE figures: measure your own sizes and speeds in a real restore drill.
size_gb = 200
logical_restore_gb_per_min = 1 # re-runs every INSERT and rebuilds every index
physical_restore_gb_per_min = 4 # copies data files back
replay_min_per_log_hour = 3 # time to replay one hour of archived WAL
strategies = [
# name, worst-case minutes of lost data, minutes to be back online
("nightly logical dump", 24 * 60, size_gb / logical_restore_gb_per_min),
("nightly base backup + WAL archived every 5 min", 5,
size_gb / physical_restore_gb_per_min + 24 * replay_min_per_log_hour),
("synchronous replica, promoted on failure", 0, 1),
]
target_rpo, target_rto = 15, 60 # what the business asked for, in minutes
print(f"targets: lose at most {target_rpo} min of data, be back within {target_rto} min")
for name, rpo, rto in strategies:
ok = "meets both" if rpo <= target_rpo and rto <= target_rto else "misses a target"
print(f"{name:48} RPO {rpo:>5.0f} min RTO {rto:>4.0f} min {ok}")
print("but only a backup brings back a table that was dropped by mistake:")
print("the replica replays the DROP within seconds.")Lab exercise 2
PythonGraded in Code LabThe base backup (products Notebook 10, Pen 50, Backpack 5) is already restored. Now replay the archived log to a target time. Each record is (time, tx, statement) in log order, and statement is SQL, COMMIT or ROLLBACK. Transactions interleave, and one of them is an accidental DELETE.
Write recover(records, target) so it:
- ignores every record later than
target(times areHH:MM:SSand compare as strings) - applies a transaction's statements only when its
COMMITis reached at or before the target (inclusive, as in PostgreSQL) - forgets a transaction on
ROLLBACK, and drops any transaction with noCOMMITby the target - returns the replayed transaction ids in commit order
Input: target HH:MM:SS, then the log. Output: replayed: ... and the products.
Example: target 14:05:59 replays T1 T2: Lamp is there, Pen is still 50, and nothing was deleted.
- Stop one second before the accidental DELETE
- A commit exactly at the target is included
- Transactions not committed by the target leave no trace
- Before the first commit: the base backup as it was
- Replaying everything repeats the accident, in commit order
Example run
- Input
target 14:05:59 09:00:00 T1 UPDATE products SET stock = stock - 2 WHERE name = 'Notebook' 09:00:01 T1 COMMIT 10:15:00 T2 INSERT INTO products VALUES ('Lamp', 7) 10:15:02 T3 UPDATE products SET stock = stock - 10 WHERE name = 'Pen' 10:15:03 T2 COMMIT 11:30:00 T3 ROLLBACK 14:05:00 T4 UPDATE products SET stock = stock + 20 WHERE name = 'Pen' 14:06:00 T5 DELETE FROM products WHERE stock < 10 14:06:01 T5 COMMIT 14:07:00 T4 COMMIT- Expected output
replayed: T1 T2 Backpack: 5 Lamp: 7 Notebook: 8 Pen: 50
import sqlite3
import sys
# The base backup, taken at midnight: the state recovery starts from.
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE products (name TEXT PRIMARY KEY, stock INTEGER NOT NULL);
INSERT INTO products VALUES ('Notebook', 10), ('Pen', 50), ('Backpack', 5);
""")
# stdin line 1: "target HH:MM:SS", the moment to recover to.
# Then the archived log, in the order it was written: "HH:MM:SS <tx> <statement>",More lines in Code Lab: 29
Hints and instant feedback are in Code Lab. Optional practice: it does not block finishing the lesson.
JavaScript and Python also run in your browser without an account; other languages use the secure server runner and need a free account.
Replication: a primary and its replicas
With replication, one server, the primary, accepts writes and streams its changes to one or more replicas (also called standbys) that apply them in the same order. PostgreSQL's streaming replication ships the WAL; MySQL replicas read the primary's binary log. Replicas give you two things: a warm copy ready to take over when the primary fails, and extra servers for read-only queries (in PostgreSQL, a hot standby).
writes WAL / binlog stream
app ──────────────────▶ PRIMARY ──────┬──────────────▶ replica A (failover candidate, reads)
└──────────────▶ replica B (another zone, reads)
app ◀──────────── reads that may lag ──────────────── replicas
Synchronous or asynchronous?
- Asynchronous: the primary confirms a commit without waiting for replicas. Fast, but a replica can be behind, and if the primary dies, the commits it had not shipped yet are lost on failover. That gap is your RPO.
- Synchronous: the commit waits until at least one replica confirms it (PostgreSQL:
synchronous_standby_names). Committed data survives the loss of the primary, but every commit pays a network round trip, and if the synchronous replica is unreachable, commits wait. MySQL's semi-synchronous replication is close, but when no replica acknowledges withinrpl_semi_sync_source_timeoutit falls back to asynchronous, and the guarantee lapses until a replica catches up.
Replication lag is how far a replica is behind. It means a user can save a change and then not see it, because the next read went to a lagging replica. The usual cure is read-your-writes: send a user's reads to the primary for a short time after they write, or only to a replica that has already replayed their write's log position. Monitor lag (PostgreSQL's pg_stat_replication view shows it per replica) and alert on it.
Failover and high availability
Failover promotes a replica to be the new primary when the old one fails. Choose the healthiest, most up-to-date replica, because with asynchronous replication anything newer than its last replayed commit is lost. Then point the application at the new primary and rebuild a new replica, so you are protected again.
The great danger is split brain: the old primary was only unreachable, not dead, and now two servers accept writes that conflict. Automatic failover tools prevent this with quorum (a majority of nodes in a consensus store such as etcd must agree, as Patroni does for PostgreSQL) and fencing, which makes sure the old primary can no longer accept writes. High availability is the result of all this: redundancy across machines and zones, tested failover, and monitoring, sized to the RTO. One more useful trick is a delayed replica (PostgreSQL's recovery_min_apply_delay), which stays an hour behind on purpose so a bad change can be caught before it reaches it.
One second after the rename, a plain replica read still returns the old name: that is replication lag as users see it. The read-your-writes read knows the log position of the user's own write, sees the replica has not reached it, and asks the primary instead. At t=2 the replica has caught up and both answers agree. Set LAG to 0 to model a fully caught-up replica.
// A primary and an asynchronous replica that applies each change 2 seconds late.
const LAG = 2;
const primary = { log: [], data: {} };
const replica = { applied: 0, data: {} };
function write(key, value, now) {
primary.log.push({ key, value, at: now });
primary.data[key] = value;
return primary.log.length; // the position of this write in the log (like an LSN)
}
function catchUp(now) {
while (replica.applied < primary.log.length && primary.log[replica.applied].at + LAG <= now) {
const change = primary.log[replica.applied];
replica.data[change.key] = change.value;
replica.applied += 1;
}
}
// Reads go to the replica unless it has not yet replayed the position the caller needs.
function read(key, now, needPosition = 0) {
catchUp(now);
if (replica.applied >= needPosition) return replica.data[key] + " (replica)";
return primary.data[key] + " (primary: replica is behind)";
}
write("name:7", "Amal", -60); // an old value, long replicated
const myWrite = write("name:7", "Amal Haddad", 0); // the user renames herself at t=0
console.log("t=1 any replica read: ", read("name:7", 1));
console.log("t=1 read-your-writes read:", read("name:7", 1, myWrite));
console.log("t=2 any replica read: ", read("name:7", 2));Lab exercise 3
PythonGraded in Code LabThe primary has failed. You know the time of its last commit, and for every replica the time of the last commit it replayed and whether it is healthy or down. The business set an RPO in seconds. The program already prints each replica's lag (times are converted to seconds for you), then calls plan_failover(primary, replicas, rpo), where replicas is a list of (name, replayed, status).
Write plan_failover so it returns the decision lines:
- if no replica is healthy:
promote: none, restore from the latest backup - otherwise promote the healthy replica with the most recent replayed commit (on a tie, the alphabetically smaller name):
promote: NAME - then
data loss: Ns (RPO Rs met), orviolated, whereN = primary - replayedof the promoted replica, and the RPO is met whenN <= R
Example: with RPO 5, primary at 12:00:30 and healthy r1 at 12:00:27, you get promote: r1 and data loss: 3s (RPO 5s met).
- Skip the down replica, promote the freshest healthy one
- A tie goes to the smaller name; zero loss
- The best replica is still too far behind: RPO violated
- No healthy replica: fall back to the backup
- Exactly at the RPO counts as met
Example run
- Input
rpo 5 primary 12:00:30 r1 12:00:27 healthy r2 12:00:30 down r3 12:00:20 healthy
- Expected output
r1: lag 3s, healthy r2: lag 0s, down r3: lag 10s, healthy promote: r1 data loss: 3s (RPO 5s met)
import sys
# stdin:
# rpo <seconds> the most data loss (in time) the business accepts
# primary <HH:MM:SS> the last commit the failed primary is known to have made
# <replica> <HH:MM:SS> <healthy|down> the last commit each replica has replayed, and its health
def to_seconds(hms):
hours, minutes, seconds = map(int, hms.split(":"))
return hours * 3600 + minutes * 60 + seconds
More lines in Code Lab: 29
Hints and instant feedback are in Code Lab. Optional practice: it does not block finishing the lesson.
JavaScript and Python also run in your browser without an account; other languages use the secure server runner and need a free account.
Question 1 of 5
The business says: "We can lose at most 10 minutes of orders, and we must be back online within 2 hours." Which numbers are these?
Question 2 of 5
Someone runs DROP TABLE orders on the primary. The database has two replicas. What saves you?
Question 3 of 5
With asynchronous replication, the primary crashes and a replica that was 4 seconds behind is promoted. What happens to the last 4 seconds of commits?
Question 4 of 5
A user saves a new address, reloads the page and sees the old address. Reads are served by replicas. What is the most likely cause and fix?
Question 5 of 5
Your nightly backup job has reported "success" every night for a year. What actually proves you can recover?
Recap and what to practice next
- RPO is how much data you may lose, RTO how long you may be down; both are business decisions that pick the design.
- Logical backups are portable and slower to restore; physical backups are fast and version-bound. Take consistent backups with the right tools, follow 3-2-1, keep one copy immutable, and encrypt them.
- A backup is only a hope until a restore drill proves it, checked against counts or checksums, and timed.
- PITR restores a base backup and replays the archived WAL to a target time; only committed transactions come back.
- Replication keeps warm copies: asynchronous is fast but can lose the lag on failover, synchronous protects commits at a latency cost. Handle lag with read-your-writes, and fail over to the healthiest, most up-to-date replica while guarding against split brain.
- Replicas protect against failed machines, backups against bad changes: you need both.
Practice next: extend the failover lab so a replica in another data centre is preferred only when it is as fresh as the local one. In the PITR lab, add a target given as a transaction id ("stop just before T5"). Then move on to security: backups and replicas are full copies of your data, so the next lesson's least privilege, encryption and audit trails apply to them too.
Practice
Reading is not learning until you run something. Pick one.
Graded lab exercises
- Lab exercise 1PythonNot passed yet Try in Code Lab
- Lab exercise 2PythonNot passed yet Try in Code Lab
- Lab exercise 3PythonNot passed yet Try in Code Lab