Databases
Intermediate 14 min read Databases

Database Backup & Restore

You do not have a backup until you restores it

A backup is only worth what your restore drills prove. Physical vs logical backups, WAL archiving, point-in-time recovery, and tested restores are the scaffolding of every data-loss survival plan.

backuprestorePITRWAL archivingsnapshotrestore drill
Prerequisites: PostgreSQL In Depth Relational Database Internals
Before we start

What you will learn

  • Distinguish physical, logical, and snapshot backups
  • Set up WAL archiving for point-in-time recovery
  • Practice a restore against a checklist, not under an outage
Introduction

What is this?

Backups come in three shapes: physical (raw data files, fast to restore, must match the engine), logical (dumps/loads, portable across engines and eras), and snapshots (block/images of whole VM/disk). WAL archiving extends a base backup into point-in-time recovery, replaying every change after the snapshot moment.

Motivation

Why do you need it?

  • Data loss is a confidence event: restores sell trust, and the drill is what keeps it.
  • RPO (how much data you can afford to lose) and RTO (how fast you are back) shape every choice.
  • Physical-vs-logical mistakes (engine-version mismatches) surface only during the crisis.
Think about it

Mental model

Imagine

A fire-safe cabinet plus a ledger of every check written since the weekly photo

Think of

Base backup + WAL archive

The weekly photo of the office is your base. The ledger (WAL) of every change since lets you re-run history to the exact second of a loss — not just to when the photo was taken.

Mechanics

How it works

  • Physical backup: copy data directory (pg_basebackup) — consistent, cheap restore, engine-specific.
  • Logical backup: pg_dump / mysqldump — portable SQL to rebuild schema+data elsewhere.
  • WAL archiving: continuous_wal_segments shipped to a safe store after each switchpoint, enabling replay past the backup moment.
  • Point-in-time recovery: restore base + replay WAL to a timestamp or LSN, stopping exactly at the bad moment.
  • The 3-2-1 rule: three copies, two media types, one off-site.

PITR flow: base + WAL replay to target time.

T0 base backup
WAL archive
Restore point T1
Verify
Reference

Key commands

pg_basebackup -D /backup/base -Ft -z -Xs && test -f /backup/base/base.tar.gz

Take a physical base backup (tar + wal included).

pg_dump shop -Fc -f shop.dump && pg_restore -d shop_new shop.dump

Logical dump/restore for portability and version lifts.

sqlite3 shop.db '.backup shop-backup-$(date +%F).db'

Consistent, safe SQLite backup while the DB is live.

Hands-on

Examples

A PITR restore to just before a destructive migration

A PITR restore to just before a destructive migration
pg_basebackup ... -D /restore/base
# restore WAL archive up to 2026-03-14 03:59:55 (migration ran 04:00)
postgres --recovery_target_time='2026-03-14 03:59:55' ...

The base + replayed WAL converges to a consistent point one step before the bad query window.

Real world

In production

  • A monthly outage-response checklist includes a 'restore from the live backup' gate — on a throwaway instance.
  • WAL archiving (or PITR-grade cloud feature) backs every OLTP primary to a separate storage tier.
  • A disaster-recovery region replays archived WAL from your production account into a standby for fast failover.
Guidelines

Best practices

  • State RPO and RTO in hours/minutes and prove both with a quarterly drill.
  • Restore + validate, never just 'the backup completed' — corruption hides in restore.
  • Keep more than one generation and store one off-machine/off-site.
  • Document engine-version compatibility of every logical restore target.
Watch out

Common mistakes

  • Testing only the backup job, not the restore; the backup succeeded and the cluster is unrecoverable.
  • Restoring a logical file with the wrong engine/major version and minting incompatibilities.
  • Ignoring WAL archiving, so a daily physical backup leaves you up to 24 hours of irrecoverable changes.
When things break

Debugging tips

  • When a restore fails, re-inspect source archive headers and the engine version before blaming the data.
  • A slow restore is often WAL replay; check archive locations and shared_buffers during the session.
  • On SQLite, read-only + WAL mode backups require .backup, not file copy, to avoid tearing.
Interviews

Interview checkpoint

Practice

Hands-on lab

Back Up, Restore, and Point-in-Time Recover
35 min · intermediate
In progress
Objectives
  • Take a physical base backup of Postgres
  • Generate fake table changes and capture them via archived WAL
  • Restore to a point before the 'bad' change and prove the data is back

Back Up, Restore, and Point-in-Time Recover

35 min · intermediate

0/6 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which backup type lets you restore to an arbitrary point in time, not just the backup moment?

2

The 3-2-1 backup rule means three copies, two media types, one off-site.

Go deeper

Official documentation

PostgreSQL backup and restore
docs
MySQL backups & recovery
docs
SQLite backup API
docs