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.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

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.

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.

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.

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.

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

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.

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.

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

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.

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.

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.

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.

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.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary