Backup Restore

By the end of this lesson you'll be able to design a backup plan you can actually bet your job on — choosing logical vs physical backups, setting up point-in-time recovery, picking a schedule from real RTO/RPO targets, and proving it works by restoring it. The hard truth of this lesson: a backup you have never restored isn't a backup — it's a wish.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

Why this matters

A database backup is the seatbelt of your data. Hardware dies, a bad migration drops a table, a tired engineer types DELETE without a WHERE — and the only thing standing between that and a closed business is a backup you can restore. This lesson is about making sure the seatbelt is actually buckled, not just hanging in the car.

1. Logical Backups (pg_dump / mysqldump)

A logical backup exports your database as the SQL needed to rebuild it — the CREATE TABLE s and INSERT s, in a file you could open in a text editor. Because it's just SQL, it's portable : you can restore it onto a newer database version, a different server, even a different operating system. The trade-off is speed — rebuilding a huge database row by row is slower than copying files.

A logical backup is like photocopying every page of a book . Slow for a 1,000-page book, but you can read any page, restore a single chapter, and the copy works in any library. A physical backup (next section) is like cloning the whole bookshelf — fast, but it only fits the same shelf.

Your Turn: complete the dump & restore

Fill in the four blanks so the commands back up shopdb and restore it into shopdb_test . The expected result is in the comments so you can check yourself.

2. Physical Backups (files & snapshots)

A physical backup copies the raw data files on disk instead of regenerating SQL. PostgreSQL's pg_basebackup grabs the entire data directory; cloud platforms take a snapshot — a frozen image of the disk. This is dramatically faster for large databases and it's the foundation for point-in-time recovery. The catch: it's version-locked (restore onto the same database version) and it's all-or-nothing — you can't cherry-pick a single table out of it.

3. Full vs Incremental vs Differential

A full backup copies everything. To avoid doing that every single night, you layer smaller backups on top:

• Full — the complete database. Slowest to make, simplest and fastest to restore (one file).
• Incremental — only what changed since the last backup of any kind . Tiny and quick, but to restore you must replay the full plus every incremental in order.
• Differential — everything that changed since the last full . Bigger than an incremental, but restore needs only the full + the one latest differential.

Continuous WAL/binlog archiving (next) is the ultimate "incremental" — it captures every change as it happens, which is what makes second-by-second recovery possible.

4. Point-in-Time Recovery (WAL / binlog)

Every change a database makes is first written to a transaction log — PostgreSQL calls it the WAL (Write-Ahead Log), MySQL the binlog (binary log). If you keep a base backup and archive that log, you can restore the base and then replay the log up to any moment you choose. That's point-in-time recovery (PITR) : a rewind button for your data.

💡 Pro Tip — PITR is your "undo the disaster" button

If someone runs DELETE FROM users at 14:35, you restore last night's base backup and replay the log to 14:34:59 — the second before the mistake. You lose only that one statement, not the whole day. This is why production databases should always archive their WAL/binlog.

5. RTO vs RPO — the two numbers that decide everything

Before you pick any backup method, the business has to answer two questions. Their answers drive every choice that follows:

• RPO — Recovery Point Objective: how much data can you afford to lose, measured in time? A nightly dump means up to 24 hours of work could vanish. WAL archiving brings RPO down to minutes or seconds.
• RTO — Recovery Time Objective: how long can you be down while you recover? Restoring a 100 GB dump might take an hour (big RTO); failing over to a hot standby replica takes seconds.

Easy way to remember: RP O = how much data you lose (looking back to the last good copy). RT O = how long until you're running again. Tighter targets cost more — match the spend to what the data is actually worth.

Your Turn: pick the strategy for the targets

Read the RTO/RPO requirement in the comments and choose the one option (A, B, or C) that meets both. The reasoning and answer are in the comments.

6. The Golden Rule: test your restores

Here is the rule every senior engineer learns the hard way: a backup you haven't restored isn't a backup. Backups fail silently — a cron job that quietly errored, a corrupt dump, a missing WAL segment, an archive bucket that filled up months ago. You only find out the moment you desperately need it, which is the worst possible time. So you practise : on a schedule, restore into a throwaway database and verify it.

A good restore test answers three things: did it restore at all, is the data complete (compare row counts against production), and how long did it take (that number is your real RTO). The recovery scenario below shows a clean restore test passing.

Common Errors (and the fix)

• Never testing restores. The single most common — and most expensive — mistake. The first time you discover a backup is corrupt should not be during an outage. Fix: schedule a monthly restore into a scratch database with row-count checks.
• No offsite copy. Storing every backup on the same server (or same region) as the database means one fire, flood, or account deletion wipes out both. Fix: follow 3-2-1 — ship at least one copy to a different region or provider.
• Ignoring RPO. "We back up nightly" sounds safe until a crash at 5 PM loses a full day of orders. Fix: set an explicit RPO with the business and add WAL/binlog archiving if minutes-not-hours matter.
• Backing up without a consistent snapshot. Copying live files with cp , or a long mysqldump without --single-transaction , captures a half-written, unrestorable state. Fix: use pg_basebackup / a consistent snapshot, or mysqldump --single-transaction .
• "pg_restore: error: could not connect / database does not exist". pg_restore -d needs the target database to already exist. Fix: run createdb shopdb_restored first, then restore into it.

📘 Quick Reference

Command / Term

What it does

Frequently Asked Questions

Use both. Logical ( pg_dump / mysqldump ) is portable and great for migrations and grabbing one table; physical ( pg_basebackup /snapshots) is fast for large databases and is required for point-in-time recovery. Many shops run a daily logical dump and continuous WAL archiving.

Work backwards from your RPO. If you can lose 24 hours, a nightly full is fine. If you can only lose minutes, you need continuous WAL/binlog archiving on top of periodic full backups.

Q: Isn't a read replica the same as a backup?

No. A replica copies changes in real time — including the bad ones. DROP TABLE users on the primary instantly drops it on the replica too. A replica protects against hardware failure (great RTO); a backup protects against mistakes and corruption . You need both.

Q: Do cloud-managed databases (RDS, Cloud SQL) back up automatically?

They offer automated snapshots and PITR, but the defaults (retention window, region) are yours to configure — and you still must test that you can actually restore. Managed ≠ hands-off.

Mini-Challenge: Design a Backup Plan

Put it all together — a brief, a blank outline, and a sample passing answer in the comments. Write your plan as comments, then sanity-check it against the RTO, RPO, retention, and offsite requirements.

🎉 Lesson Complete

• ✅ Logical backups ( pg_dump / mysqldump ) are portable; physical ( pg_basebackup /snapshots) are fast
• ✅ Full is simplest to restore; incremental / differential save space at restore-time cost
• ✅ PITR replays the WAL/binlog to rewind to any second before a disaster
• ✅ RPO is how much data you can lose; RTO is how long you can be down — they drive every choice
• ✅ A backup you haven't restored isn't a backup — test restores, set retention, keep 3-2-1 offsite copies
• ✅ Next: Replication — keep a live standby for near-zero RTO

Practice quiz

Continue this course

• Previous: SQL Injection Defenses
• Next: Replication