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) | |
|---|---|---|
| Copies | SQL definitions and data of one database | The whole cluster’s data files |
| Restore to | Any server, any newer major version | Same major version and platform |
| Point-in-time recovery | No, only the moment of the dump | Yes, to any second covered by archived WAL |
| Selective restore | One table, one schema | No, whole cluster |
| Speed for large databases | Slow to restore (rebuilds indexes) | Fast: files are copied back |
| Typical use | Small databases, migrations, copies for dev | Production 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
| Format | Flag | Restore with | Notes |
|---|---|---|---|
| Plain SQL | -Fp (default) | psql -f | Human-readable; restores everything or nothing |
| Custom | -Fc | pg_restore | Compressed, selective, reorderable |
| Directory | -Fd | pg_restore | Like custom, plus parallel dump with -j |
| Tar | -Ft | pg_restore | Rarely 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 -zwrites compressed tar files;-Fpwrites a plain copy of the data directory.-X streamstreams the WAL generated during the backup alongside it.-Pshows 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_dumpmeans 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:
- Restore last night’s backup into a scratch server or database.
- Run sanity queries: row counts, the newest
created_at, a few known records. - Measure how long it took and compare with your RTO.
- 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
| Task | Command |
|---|---|
| Dump one database | pg_dump -Fc -f db.dump db |
| Parallel dump | pg_dump -Fd -j 4 -f dir db |
| Roles and tablespaces | pg_dumpall --globals-only -f globals.sql |
| Restore | pg_restore -d newdb -j 4 db.dump |
| One table | pg_restore -d db -t notes db.dump |
| Plain SQL restore | psql -v ON_ERROR_STOP=1 -1 -f db.sql |
| Physical backup | pg_basebackup -D dir -Ft -z -X stream -P |
| Archive WAL | archive_mode = on + archive_command |
| Point-in-time restore | restore_command + recovery_target_time + recovery.signal |
| Verify a backup | pg_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?
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?
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?
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_dumpdoes not include roles: also runpg_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.