Lesson 16 advanced

Backup and Restore

Protect Orbit's data with logical backups (pg_dump, pg_dumpall, pg_restore) and physical backups (pg_basebackup, WAL archiving, incremental backups), restore to any point in time, choose a strategy from your recovery objectives, and prove that backups work by restoring them regularly.

Disks fail, people run DELETE without WHERE, and migrations go wrong. A backup is the only thing between those events and losing Orbit’s data. This lesson covers both kinds of Postgres backup, how to restore each, and how to make sure a backup is actually usable before you need it.

Two kinds of backup

Logical (pg_dump)Physical (pg_basebackup + WAL)
CopiesSQL definitions and data of one databaseThe whole cluster’s data files
Restore toAny server, any newer major versionSame major version and platform
Point-in-time recoveryNo, only the moment of the dumpYes, to any second covered by archived WAL
Selective restoreOne table, one schemaNo, whole cluster
Speed for large databasesSlow to restore (rebuilds indexes)Fast: files are copied back
Typical useSmall databases, migrations, copies for devProduction disaster recovery

Production systems usually use both: physical backups for recovery, logical dumps for long-term archives and moving data around.

Logical backups with pg_dump

pg_dump takes a consistent snapshot of one database without blocking normal reads and writes. Dump Orbit in the custom format, which is compressed and lets pg_restore pick what to restore:

$ pg_dump -h localhost -U orbit -Fc -f orbit.dump orbit
FormatFlagRestore withNotes
Plain SQL-Fp (default)psql -fHuman-readable; restores everything or nothing
Custom-Fcpg_restoreCompressed, selective, reorderable
Directory-Fdpg_restoreLike custom, plus parallel dump with -j
Tar-Ftpg_restoreRarely needed

For big databases, the directory format dumps several tables at once:

$ pg_dump -h localhost -U orbit -Fd -j 4 -f orbit_dir orbit

Useful filters: -t notes (only that table), -n public (only that schema), -T 'notes_audit' (exclude a table), --exclude-table-data=notes_audit (keep its definition, skip its rows), -s (schema only) and -a (data only).

What pg_dump does not include

Roles and tablespaces belong to the cluster, not to a database, so pg_dump does not include them. pg_dumpall --globals-only does:

$ pg_dumpall -h localhost -U postgres --globals-only -f globals.sql

Settings made with ALTER DATABASE ... SET (like Orbit’s UTC time zone) are only included if you dump with --create. Keep globals.sql next to your dumps; without it, a restore on a new server fails on missing roles.

Restoring a dump

Restore into a new, empty database so you never overwrite the live one by accident:

$ createdb -h localhost -O orbit orbit_restored
$ pg_restore -h localhost -U orbit -d orbit_restored -j 4 orbit.dump

-j 4 restores tables and builds indexes in parallel. Check that the data arrived:

$ psql -h localhost -U orbit -d orbit_restored
SELECT (SELECT count(*) FROM users) AS users, (SELECT count(*) FROM notes) AS notes;
 users | notes
-------+--------
     3 | 200005
(1 row)

Three users and 200,005 notes: the five from lesson 6 plus the generated ones. Other options you will use:

$ pg_restore -l orbit.dump > contents.list           # list what is inside
$ pg_restore -d orbit_restored -t notes orbit.dump   # restore one table
$ pg_restore -d orbit --clean --if-exists orbit.dump # replace existing objects
$ pg_restore -d orbit_dev --no-owner --no-privileges orbit.dump

--no-owner --no-privileges is handy when restoring a production dump on a laptop that does not have the same roles. A plain SQL dump is restored with psql -v ON_ERROR_STOP=1 -1 -f orbit.sql.

Physical backups with pg_basebackup

pg_basebackup copies the entire data directory of a running server over a replication connection, together with the WAL needed to make that copy consistent. It needs a role with the REPLICATION attribute and a matching replication line in pg_hba.conf (lesson 18 sets both up):

$ pg_basebackup -h db.example.com -U replicator -D /backups/base_2026-11-09 \
    -Ft -z -X stream -P
  • -Ft -z writes compressed tar files; -Fp writes a plain copy of the data directory.
  • -X stream streams the WAL generated during the backup alongside it.
  • -P shows progress.

Restoring is copying the files back into an empty data directory and starting the server. On its own, though, a base backup only gets you back to the moment it finished.

Point-in-time recovery

To restore to any moment, for example one second before someone dropped a table, combine a base backup with archived WAL. Every change is in the WAL, so replaying it up to a target time rebuilds the database exactly as it was.

Enable archiving on the server (archive_mode needs a restart):

ALTER SYSTEM SET archive_mode = on;
ALTER SYSTEM SET archive_command = 'test ! -f /archive/%f && cp %p /archive/%f';

To recover, restore the latest base backup taken before the accident into an empty data directory, then tell Postgres where the WAL is and where to stop:

# appended to postgresql.auto.conf in the restored data directory
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-11-09 14:29:00+00'
recovery_target_action = 'promote'
$ touch /var/lib/postgresql/18/main/recovery.signal
$ sudo systemctl start postgresql

The recovery.signal file tells Postgres to start in recovery mode. It replays WAL from the archive up to the target time, then opens the database for writes.

Incremental backups

Since PostgreSQL 17, pg_basebackup --incremental copies only blocks changed since a previous backup, which makes daily backups of large databases much smaller. It needs summarize_wal = on. Before restoring, pg_combinebackup merges the full backup and its incrementals into one directory:

$ pg_basebackup -D /backups/incr_mon --incremental=/backups/full_sun/backup_manifest
$ pg_combinebackup /backups/full_sun /backups/incr_mon -o /restore/data

Use a backup tool

Copying WAL with cp works in a lesson, but production needs retention policies, compression, encryption, cloud storage, parallelism and verification. pgBackRest, Barman and WAL-G handle all of that and are what most teams run. Managed services such as RDS and Cloud SQL do PITR for you.

Choosing a strategy

Start from two numbers your business can accept:

  • RPO (recovery point objective): how much data you can afford to lose. Nightly pg_dump means up to 24 hours; WAL archiving means seconds or minutes.
  • RTO (recovery time objective): how long you can be down. Restoring a 500 GB dump can take hours; promoting a replica (lesson 18) takes seconds.

A solid setup for Orbit: weekly full and daily incremental physical backups with continuous WAL archiving, kept for 30 days in another region; a nightly pg_dump kept for a year; a streaming replica for fast failover. Follow the 3-2-1 rule: three copies, on two kinds of storage, one off-site.

Warning: A replica is not a backup. A DROP TABLE or a bad UPDATE is replicated to it within milliseconds. Only backups can take you back in time.

Test your restores

An untested backup is a hope, not a backup. Automate a regular restore drill:

  1. Restore last night’s backup into a scratch server or database.
  2. Run sanity queries: row counts, the newest created_at, a few known records.
  3. Measure how long it took and compare with your RTO.
  4. Alert if any step fails.

pg_verifybackup checks a physical backup against its manifest, and pg_restore -l confirms a dump is readable, but only a real restore proves the whole chain works.

Quick reference

TaskCommand
Dump one databasepg_dump -Fc -f db.dump db
Parallel dumppg_dump -Fd -j 4 -f dir db
Roles and tablespacespg_dumpall --globals-only -f globals.sql
Restorepg_restore -d newdb -j 4 db.dump
One tablepg_restore -d db -t notes db.dump
Plain SQL restorepsql -v ON_ERROR_STOP=1 -1 -f db.sql
Physical backuppg_basebackup -D dir -Ft -z -X stream -P
Archive WALarchive_mode = on + archive_command
Point-in-time restorerestore_command + recovery_target_time + recovery.signal
Verify a backuppg_verifybackup dir

Exercise

At 14:30 someone ran DELETE FROM notes without a WHERE clause. Which backup setup can bring the data back to 14:29?

  1. A nightly pg_dump taken at 02:00
  2. A streaming replica
  3. A base backup plus continuous WAL archiving, restored with recovery_target_time
  4. pg_dumpall –globals-only
Show answer

Answer: C. Point-in-time recovery replays archived WAL up to the moment you choose. A nightly dump loses everything since 02:00, and a replica has already applied the DELETE.

Exercise

You restore orbit.dump on a brand-new server and get errors that role “orbit_app” does not exist. What was missing?

  1. The cluster-wide objects from pg_dumpall –globals-only
  2. The -j option of pg_restore
  3. The directory format
  4. The archive_command setting
Show answer

Answer: A. Roles belong to the cluster, so pg_dump never includes them. Restore globals.sql first, or restore with –no-owner –no-privileges.

Exercise

Why is pg_restore usually run into a new, empty database?

  1. pg_restore cannot restore into a database that has a name
  2. It avoids overwriting or clashing with live data, and lets you check the result before switching
  3. Restores into existing databases are slower
  4. Custom-format dumps only work with createdb
Show answer

Answer: B. Restoring next to the live database is safer: you can compare, copy back only what you need, or rename databases once you are sure.

Key takeaways

  • Logical backups (pg_dump) are portable and selective; physical backups (pg_basebackup) with WAL archiving allow point-in-time recovery.
  • pg_dump does not include roles: also run pg_dumpall --globals-only.
  • Restore into a new database and check it before touching the live one.
  • Pick a strategy from your RPO and RTO; use a tool such as pgBackRest in production.
  • A replica is not a backup, and a backup you have never restored is not a backup either.

FAQ

Does pg_dump lock the database?

It takes light locks that only conflict with schema changes such as ALTER TABLE and DROP TABLE. Normal reads and writes continue, and the dump is a consistent snapshot of the moment it started.

Can I restore a dump from an older version on a newer server?

Yes, that is the recommended way to move a dump forward. Use the newer version’s pg_dump to take it when possible. Restoring a newer dump on an older server is not supported.

How long should I keep backups?

It depends on your obligations and how long a problem might go unnoticed. A common pattern is 30 days of point-in-time recovery plus monthly logical dumps kept for a year.

Can I back up just by copying the data directory?

Only if the server is stopped, or with filesystem snapshots that are atomic across all of the cluster’s files. Copying files of a running server with cp or rsync produces a corrupt backup; use pg_basebackup instead.

How do I back up a Postgres database running in Docker?

Run the client inside the container and write to the host: docker exec orbit-db pg_dump -U postgres -Fc orbit > orbit.dump. Leave out -t on docker exec, because a terminal can corrupt binary output.

↑ ↓ navigate Enter open Esc close