Skip to main content
Back to module
Database Administration · Operations

Backup and recovery

Reading1h 1mLesson 2 of 3 in this module

Plan backups around RPO and RTO, take logical dumps, restore them into a separate database and prove the recovery works.

Why it matters

Hardware fails, people run DELETE without a WHERE, ransomware encrypts disks, and a bad migration can corrupt a column. A DBA's most important promise is: we can get the data back. A backup that has never been restored is only a hope; recovery is what counts.

Start with two numbers

  • RPO (recovery point objective): how much data can you afford to lose? "Up to 24 hours" allows nightly dumps; "under 5 minutes" needs continuous log archiving.
  • RTO (recovery time objective): how long can you be down while restoring? A 2 TB dump can take hours to load; a standby replica can take over in minutes.

Agree these with the business first; they decide the method and the cost.

Logical vs physical backups

Logical backups export SQL-level data and definitions.

# One database, custom format (compressed, restorable selectively)
pg_dump -U postgres -d shop -Fc -f shop_2026-10-23.dump

# Roles and other cluster-wide objects are NOT in pg_dump
pg_dumpall -U postgres --globals-only -f globals.sql

Restore into a new, empty database, never straight over the live one:

createdb -U postgres shop_restore
pg_restore -U postgres -d shop_restore --no-owner shop_2026-10-23.dump

Logical dumps are portable across versions and machines, good for small to medium databases, migrations and copying single tables (pg_restore -t orders). They are slow for very large databases, and you can only recover to the moment the dump was taken.

Physical backups copy the database files plus the write-ahead log (WAL). pg_basebackup takes a consistent copy of a running cluster. If you also archive every WAL segment (archive_mode = on and an archive_command, or a tool such as pgBackRest, Barman or WAL-G), you can replay the log up to any moment: point-in-time recovery (PITR). In recovery you set restore_command and a target such as recovery_target_time = '2026-10-23 14:05:00', and create a recovery.signal file (PostgreSQL 12+). This is how you undo "someone dropped a table at 14:06" with minutes of loss.

MySQL has the same split: mysqldump (logical) versus physical tools such as Percona XtraBackup or MySQL Enterprise Backup, plus binary logs for point-in-time recovery. Managed cloud databases offer automated snapshots and PITR windows; you still own testing them.

Where backups live: 3-2-1

  • 3 copies of the data,
  • on 2 different kinds of storage,
  • with 1 copy off-site (another region or provider), ideally immutable or offline so ransomware or an angry admin cannot delete it.

Encrypt backups, because a dump contains everything, including personal data. Restrict who can read them and log access. Apply your retention policy and data protection rules to backups too.

Worked example: a restore test

A good restore test is scripted and repeatable:

  1. Take the backup: pg_dump -Fc -d shop -f shop.dump.
  2. Record facts to check: row counts of key tables and a checksum-like query, for example SELECT COUNT(*), SUM(total_cents) FROM app.orders;.
  3. Restore into shop_restore on a separate server or container.
  4. Run the same queries there and compare.
  5. Time the whole thing. That is your real RTO for this method.
  6. Write down what went wrong (missing roles, extensions, permissions) and fix the runbook.

Doing this monthly, or automatically after each backup, turns "we have backups" into "we can recover".

Replication is not a backup

A replica copies every change, including the accidental DROP TABLE, within milliseconds. Replicas protect against hardware loss, not against mistakes. Keep real backups with history as well.

Common mistakes

  • Never testing a restore.
  • Forgetting globals (roles, tablespaces) and extensions, so the restore fails on permissions.
  • Storing backups on the same disk or account as the database.
  • Restoring over production in a panic. Restore beside it, verify, then copy back only what is needed.
  • No monitoring: a backup job that silently failed for three weeks.
  • Keeping dumps of real customer data on laptops.

Quick recap

  • RPO and RTO choose the method: logical dumps, physical base backups plus WAL for PITR, or both.
  • pg_dump -Fc plus pg_dumpall --globals-only, restored with pg_restore into a new database.
  • Follow 3-2-1, encrypt, monitor backup jobs, and test restores on a schedule.

Try it

In the Do tab, back up a practice database, "lose" some data, restore it beside the original and prove the counts match. Use practice data only.

Also in this lesson: