Replication and backups
Part of the free LogicWiz SQL & Databases course, Module 17: Beyond one database. The queries and practice run in the app on real Postgres.
Aarav and Meher's food app has one database server. On a Friday night its disk dies. How many orders do they lose, and how long is the app down? Those two numbers have names, and every company is asked for them.
You'll learn to
- Explain primary and replica servers, and sync vs async replication
- Spot replication lag and the read-after-write problem
- Define RPO and RTO and work them out for a backup plan
- Restore lost rows from a backup table with SQL
Learn
One primary, many replicas
Replication means keeping copies of the same database on more than one server.
- The primary takes every write (INSERT, UPDATE, DELETE).
- Each replica (also called a standby or read replica) receives the primary's changes and replays them. Postgres ships its WAL, the write-ahead log you met in Module 16.
Why bother? Two reasons. Availability: if the primary dies, a replica is promoted and the app keeps running. Read scaling: reports and menu pages read from replicas, so the primary is free for orders.
Synchronous vs asynchronous replication
| Synchronous | Asynchronous |
|---|---|
| COMMIT waits until a replica has the change | COMMIT returns as soon as the primary has it |
| No committed data is lost if the primary dies | The last few seconds of commits can be lost |
| Slower writes, and a slow replica slows everyone | Fast writes; replicas trail behind (lag) |
| Banks, UPI ledgers | Most web apps, read replicas for reports |
Read-after-write on an async replica
| Session A | Session B |
|---|---|
| UPDATE customers SET city = 'Pune' WHERE customerid = 2; | |
| SELECT city FROM customers WHERE customerid = 2; | |
| SELECT city FROM customers WHERE customerid = 2; |
Which replicas can serve reads?
SELECT name, region, lag_seconds
FROM replicas
WHERE healthy
AND lag_seconds <= 5
ORDER BY lag_seconds, name;
A load balancer runs a check like this: send reads only to healthy replicas that are less than 5 seconds behind. replica-che-1 has an unknown lag (NULL), so lag_seconds <= 5 is not true and it is left out, which is what we want.
RPO and RTO
Two numbers describe any backup plan:
- RPO (Recovery Point Objective): how much data you can afford to lose, measured in time. Nightly backups only? RPO is up to 24 hours of orders.
- RTO (Recovery Time Objective): how long you can afford to be down while you restore.
A UPI ledger wants RPO near zero (synchronous replicas plus WAL archiving) and an RTO of seconds (automatic failover). A college notice board can live with a day and an hour.
A restore drill: put back deleted rows
CREATE TABLE order_items_backup AS SELECT * FROM order_items;
-- oops: someone deletes two orders' items
DELETE FROM order_items WHERE order_id IN (1001, 1004);
-- restore only the rows that are missing
INSERT INTO order_items
SELECT b.*
FROM order_items_backup AS b
WHERE NOT EXISTS (
SELECT 1 FROM order_items AS o
WHERE o.order_id = b.order_id AND o.item_id = b.item_id
);
SELECT COUNT(*) AS rows_back FROM order_items;
CREATE TABLE … AS SELECT is a quick copy, not a real backup tool, but the restore idea is the same: compare the backup with the live table on the primary key and insert what is missing. Real backups use pg_dump (a logical copy) or base backups plus archived WAL for point-in-time recovery.
A replica is not a backup. If someone runs DELETE FROM orders; without a WHERE, the replica replays that DELETE within a second. Replicas protect you from a dead server; only backups (with point-in-time recovery) protect you from a bad query.
Exams ask the three backup types. Full: everything. Incremental: changes since the last backup of any kind (small, but a restore needs the whole chain). Differential: changes since the last full backup (bigger, but a restore needs only the full plus the latest differential). Interviews ask RPO vs RTO and sync vs async.
In one line
Replicas keep you running when a server dies; backups with point-in-time recovery save you from bad queries. RPO = data you can lose, RTO = time you can be down.
Interview corner
What is the difference between synchronous and asynchronous replication?
With synchronous replication, COMMIT waits until at least one replica has the change, so a failover loses no committed data, but writes are slower. With asynchronous replication, COMMIT returns at once and replicas trail behind, so writes are fast but the last few seconds can be lost if the primary dies.
Explain RPO and RTO.
RPO (Recovery Point Objective) is how much data, measured in time, you can afford to lose: the gap between the last safe copy and the failure. RTO (Recovery Time Objective) is how long the system may be down while you recover. Nightly backups give an RPO up to 24 hours; WAL archiving or sync replicas bring it near zero.
A user updates their profile and the page still shows the old value. Why, and how do you fix it?
The write went to the primary but the read went to an async replica that has not replayed it yet (replication lag). Fixes: read a user's own data from the primary for a short time after they write, route reads by session, or wait until the replica has reached the write's WAL position.
Is a read replica a backup?
No. A replica replays every change, including an accidental DELETE or DROP. It protects against hardware failure, not against bad queries. For that you need backups, ideally a base backup plus archived WAL so you can do point-in-time recovery.