Backups vs replicas: PITR, RPO and RTO
Why a replica is not a backup.
An engineer runs DELETE FROM orders with no WHERE clause at 3:07 p.m. The primary obeys, and within a second every replica obeys too. All your copies are now identically empty. Replicas protect against hardware failure, not against mistakes, bugs or malicious changes, because they faithfully copy those too. For that you need backups: separate, older snapshots of the data.
Backups come in two parts. A full backup copies the database as of one moment, say 02:00 each night. Between full backups you also archive the continuous change log. With both, you can restore the nightly snapshot and replay the log up to any chosen moment: point-in-time recovery (PITR). For the 3:07 p.m. mistake, restore the 02:00 snapshot and replay the log up to 3:06:59 p.m., just before the delete.
Two numbers define your recovery promise. RPO (recovery point objective) is how much data you can afford to lose, measured in time. If the log is archived continuously, RPO can be seconds; if you only take a nightly snapshot, RPO is up to 24 hours. RTO (recovery time objective) is how long you can be down. Restoring a 2 TB database from storage might take an hour or more, which sets your RTO unless you keep a warm standby.
Choosing numbers for the delivery app
Step 1 of 4
Decide what loss hurts
Losing 1 hour of order status updates is annoying; losing 1 hour of paid orders means refunds and angry customers. Payments get an RPO of seconds.
Your turn
Write an RPO and RTO for (a) the restaurant menu data, which changes rarely, and (b) the payments table. Justify each in one line.
Stuck? Think about cost of loss and cost of downtime separately.
What to remember
Replicas protect against hardware loss, not mistakes; backups protect against both.
PITR combines a snapshot with an archived change log.
RPO is acceptable data loss; RTO is acceptable downtime.
Test restores regularly and store backups separately.