Backup & Restore

Reviewed & published by Brayan K

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

• Logical backups with pg_dump / mysqldump
• Physical backups: file copies, snapshots, pg_basebackup
• Full vs incremental vs differential backups
• Point-in-time recovery with the WAL / binlog
• RTO vs RPO — the two numbers that drive every choice
• Retention, offsite copies, and the 3-2-1 rule

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 TABLEs and INSERTs, 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.

-- LOGICAL BACKUP: export the database as SQL you could read in a text editor.
-- Portable across versions and even across machines. Run these in a terminal,
-- NOT in the SQL editor — they are command-line tools, not SQL statements.

-- PostgreSQL: pg_dump backs up ONE database --------------------------------
pg_dump -U postgres -d shopdb -F custom -f shopdb.dump
--      \__ user    \__ db    \__ format   \__ output file
-- -F custom  = compressed binary (recommended: smaller + parallel restore)
-- -F plain   = raw .sql text (readable, but larger and slower to restore)

-- MySQL / MariaDB: the equivalent tool is mysqldump -----------------------
mysqldump -u root -p --single-transaction shopdb > shopdb.sql
--                   \__ takes a CONSISTENT snapshot without locking writes

-- ✅ Result: one self-contained file that recreates schema + data anywhere.

-- RESTORE a logical backup = run the dump back into a database.

-- PostgreSQL custom format -> use pg_restore:
createdb shopdb_restored                       -- make an empty target first
pg_restore -U postgres -d shopdb_restored shopdb.dump

-- PostgreSQL plain .sql text -> just pipe it through psql:
psql -U postgres -d shopdb_restored < shopdb.sql

-- MySQL -> pipe the .sql file into the mysql client:
mysql -u root -p shopdb_restored < shopdb.sql

-- ✅ Result: shopdb_restored now contains every table and row from the dump.
-- TIP: restoring into a NEW database lets you verify before touching production.

-- 📄 GRANULARITY: what a logical backup gives you that a physical one cannot.
-- A dump is just SQL, table by table, so you can replay ONE table out of it and
-- leave everything else alone. Here is that restore, small enough to watch.

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price INTEGER);
CREATE TABLE orders   (id INTEGER PRIMARY KEY, product_id INTEGER, qty INTEGER);
INSERT INTO products VALUES (1, 'Keyboard', 49), (2, 'Monitor', 219), (3, 'Mouse', 25);
INSERT INTO orders   VALUES (1, 1, 2), (2, 3, 1), (3, 2, 1), (4, 1, 1);

-- Last night's dump. In the real file each table is its own CREATE + INSERTs,
-- which is exactly why one of them can be replayed on its own:
CREATE TABLE dump_products AS SELECT * FROM products;
CREATE TABLE dump_orders   AS SELECT * FROM orders;

-- 11:04 — someone empties the wrong table:
DELETE FROM products;

-- Replay ONLY the products section of the dump. orders is never touched:
INSERT INTO products SELECT * FROM dump_products;

SELECT p.id, p.name, p.price,
       (SELECT COUNT(*) FROM orders o WHERE o.product_id = p.id) AS orders_still_there
FROM products p
ORDER BY p.id;

-- ✅ Expected result:
-- id | name | price | orders_still_there
-- 1 | Keyboard | 49 | 2
-- 2 | Monitor | 219 | 1
-- 3 | Mouse | 25 | 1

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.

-- 🎯 YOUR TURN — complete the backup, then complete the restore.
-- Goal: dump the "shopdb" PostgreSQL database to a compressed file,
--       then restore that file into a fresh database called "shopdb_test".

-- (a) Back it up — fill in the database name and the output file:
pg_dump -U postgres -d ___ -F custom -f ___    -- 👉 shopdb   and   shopdb.dump

-- (b) Restore it — fill in the tool and the target database:
createdb shopdb_test
___ -U postgres -d ___ shopdb.dump             -- 👉 pg_restore   and   shopdb_test

-- ✅ Expected: shopdb.dump is created, then shopdb_test contains every table
--    and row from shopdb. (For MySQL the pair would be mysqldump / mysql.)

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.

-- PHYSICAL BACKUP: copy the actual data FILES on disk, not SQL statements.
-- Much faster for large databases, and it is the foundation for PITR (below).

-- PostgreSQL: pg_basebackup copies the whole data directory + WAL:
pg_basebackup -h primary -U repl_user -D /backup/base \
  --checkpoint=fast --wal-method=stream -P
--                   \__ -P prints a live progress bar

-- Cloud snapshots are physical backups too (a frozen image of the disk):
-- AWS    -> EBS / RDS snapshot
-- GCP    -> Persistent Disk snapshot
-- Azure  -> Managed Disk snapshot
-- Fast and consistent, but tied to that cloud + database version.

-- ✅ Logical vs physical, in one line:
--    Logical  = portable, granular, slower to restore   (pg_dump / mysqldump)
--    Physical = fast, version-locked, all-or-nothing     (pg_basebackup / snapshot)

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.

-- POINT-IN-TIME RECOVERY (PITR): rewind to ANY second in the past.
-- Built from a physical base backup PLUS the transaction log that records
-- every change after it: Postgres calls it the WAL, MySQL calls it the binlog.

-- 1) Turn on continuous log archiving (PostgreSQL, postgresql.conf):
wal_level = replica
archive_mode = on
archive_command = 'cp %p /wal_archive/%f'   -- ship each WAL segment offsite

-- 2) To recover, restore the base backup, then replay logs up to a moment:
restore_command = 'cp /wal_archive/%f %p'
recovery_target_time = '2026-06-15 14:34:59'   -- the second BEFORE the mistake
recovery_target_action = 'promote'

-- MySQL does the same thing with its binary log:
mysqlbinlog --stop-datetime="2026-06-15 14:34:59" binlog.000007 | mysql -u root -p

-- ✅ Scenario: someone ran DELETE FROM users at 14:35. Restore last night's
--    base backup, replay the log to 14:34:59, and the users table is back —
--    with only that one mistaken statement lost.

-- 🔁 PITR IN MINIATURE — the same mechanism, in SQL you can actually run.
-- Real PITR needs a server, a base backup on disk and archived WAL segments.
-- The mechanism, though, is only this: restore the base copy, then replay the
-- log of everything that happened after it, stopping just before the mistake.

-- 02:00 — the users of "shopdb", and last night's base backup taken from them:
CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, signed_up TEXT);
INSERT INTO users VALUES
  (1, '[email protected]',  '2026-06-14'),
  (2, '[email protected]',  '2026-06-14'),
  (3, '[email protected]', '2026-06-15');

CREATE TABLE base_backup AS SELECT * FROM users;

-- Every change after 02:00 is also appended to the archived log. Postgres
-- ships WAL segments; here the same idea is a table you can read:
CREATE TABLE wal_archive (logged_at TEXT, action TEXT, id INTEGER, email TEXT, signed_up TEXT);
INSERT INTO wal_archive VALUES
  ('2026-06-15 14:30:12', 'INSERT', 4, '[email protected]', '2026-06-15'),
  ('2026-06-15 14:35:07', 'DELETE', NULL, NULL, NULL);   -- DELETE FROM users, no WHERE

-- 14:35:07 — the mistake really happens. Every row is gone:
DELETE FROM users;

-- RECOVERY, with recovery_target_time = '2026-06-15 14:34:59':
INSERT INTO users SELECT * FROM base_backup;                     -- 1) restore the base
INSERT INTO users (id, email, signed_up)                         -- 2) replay the log
SELECT id, email, signed_up FROM wal_archive
WHERE action = 'INSERT' AND logged_at <= '2026-06-15 14:34:59';  --    up to the target

SELECT id, email, signed_up FROM users ORDER BY id;

-- ✅ Expected result:
-- id | email | signed_up
-- 1 | [email protected] | 2026-06-14
-- 2 | [email protected] | 2026-06-14
-- 3 | [email protected] | 2026-06-15
-- 4 | [email protected] | 2026-06-15

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: RPO = how much data you lose (looking back to the last good copy). RTO = 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.

-- 🎯 YOUR TURN — pick the right strategy for the requirement.
-- A payments app says: "We can lose AT MOST 1 minute of data (RPO = 1 min)
-- and must be back online within 5 minutes (RTO = 5 min)."

-- Which ONE approach meets BOTH targets? Replace ___ with: A, B, or C.
--   A) A single pg_dump every night at 2 AM
--   B) WAL/binlog archiving + a hot standby replica for fast failover
--   C) A weekly cloud disk snapshot

my_choice = ___    -- 👉 think: nightly dump loses up to 24h; snapshot loses up to 7 days

-- ✅ Expected: B.
--    A nightly dump has RPO up to 24h (fails the 1-min target).
--    A weekly snapshot has RPO up to 7 days (far worse).
--    Continuous log archiving gives RPO ≈ seconds, and a standby replica
--    gives RTO ≈ seconds — only B satisfies both numbers.

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.

-- 🔎 THE RESTORE TEST: the step that turns a backup into a backup.
-- Restore the dump into a THROWAWAY database, then prove it is complete by
-- comparing the row count of every table against production, one by one.

-- Production "shopdb", at the sizes quoted in the table above:
CREATE TABLE users    (id INTEGER PRIMARY KEY, email TEXT);
CREATE TABLE orders   (id INTEGER PRIMARY KEY, user_id INTEGER);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT);

WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 50000)
INSERT INTO users    SELECT n, 'user' || n || '@shop.test' FROM seq;

WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 250000)
INSERT INTO orders   SELECT n, 1 + (n % 50000) FROM seq;

WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 5000)
INSERT INTO products SELECT n, 'widget ' || n FROM seq;

-- The restore. On a real server this line is the whole job:
--     createdb shopdb_test && pg_restore -d shopdb_test shopdb.dump
-- Here the *_restored copies stand in for that scratch database.
CREATE TABLE users_restored    AS SELECT * FROM users;
CREATE TABLE orders_restored   AS SELECT * FROM orders;
CREATE TABLE products_restored AS SELECT * FROM products;

-- The check itself — this is the query you run after every restore:
SELECT table_name, production, restored,
       CASE WHEN production = restored THEN 'PASS' ELSE 'MISMATCH' END AS verdict
FROM (
  SELECT 1 AS ord, 'users'    AS table_name,
         (SELECT COUNT(*) FROM users)             AS production,
         (SELECT COUNT(*) FROM users_restored)    AS restored
  UNION ALL
  SELECT 2, 'orders',   (SELECT COUNT(*) FROM orders),   (SELECT COUNT(*) FROM orders_restored)
  UNION ALL
  SELECT 3, 'products', (SELECT COUNT(*) FROM products), (SELECT COUNT(*) FROM products_restored)
) checks
ORDER BY ord;

-- ✅ Expected result:
-- table_name | production | restored | verdict
-- users | 50000 | 50000 | PASS
-- orders | 250000 | 250000 | PASS
-- products | 5000 | 5000 | PASS

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.

Frequently Asked Questions

Q: Logical or physical — which should I use?

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.

Q: How often should I back up?

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.

-- 🎯 MINI-CHALLENGE: design a backup PLAN (write it as comments — no live SQL).
-- Requirements for an online store "shopdb":
--   • RPO = 5 minutes   (lose at most 5 minutes of orders)
--   • RTO = 30 minutes  (back online within half an hour)
--   • Keep 7 years of monthly records for tax/audit (retention)
--   • Survive losing the whole data centre (offsite / 3-2-1)
--
-- Sketch the schedule. Decide, for each line, the TYPE, FREQUENCY and RETENTION:
--   1. Continuous log archiving  -> frequency? retention? (drives your RPO)
--   2. Full logical/base backup  -> how often? kept how long?
--   3. Long-term audit export     -> how often? kept how long?
--   4. Where do copies live so you keep 3 copies, 2 media, 1 offsite?
--   5. How (and how often) will you TEST a restore?
--
-- ✅ A passing answer: WAL archived every few minutes to cloud storage (RPO≈5m);
--    daily base backup kept 30 days; monthly export kept 7 years; copies on
--    local disk + cloud bucket + second region (3-2-1); monthly test restore
--    into a throwaway database with row-count checks (meets the 30-min RTO).

🎉 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 & Secure Query Practices
• Next: Database Replication: Synchronous vs Asynchronous, Failover & Clustering — Set up primary/replica replication and automatic failover for your database
• Quick reference: SQL cheat sheet