Backup and recovery

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

• RPORecovery point objective: the maximum amount of data, measured in time, the business can afford to lose. (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.
• RTORecovery time objective: how long the service can be down while data is restored. (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 backupsExports of SQL-level data and definitions, such as pg_dump files, portable across versions. 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 logPostgreSQL's log of every change, used for crash recovery, replication and point-in-time recovery. (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 recoveryRestoring a physical base backup and replaying archived WAL up to a chosen moment. (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-1Keep three copies of the data on two kinds of storage, with one copy off-site., 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.