Backups: Logical vs Physical

Two kinds of backup, two recovery stories

Logical backups (pg_dump) are SQL statements that recreate your data. They're portable across versions, selective (one database, one table), and human-readable. Slow to restore, fine for weekly archives or migrating between Postgres versions.

Physical backups (pg_basebackup) are byte-for-byte copies of the data directory. Fast to take and restore, can support point-in-time recovery (PITR) when combined with WAL archiving. The right answer for production.

The 3-2-1 rule, applied

Three copies of your data, on two different media, with one off-site. For a hosted Postgres: the live database + provider's snapshot + your own off-site copy (e.g. nightly logical dump to S3/R2/Backblaze). For self-hosted: live + local snapshot + remote.

Test the restore

An untested backup is a wish. Periodically restore your latest backup into a sandbox; verify row counts; run a smoke-test query. The first time you discover your backups are broken should not be the day production is down.

Code

Logical backup (one database)·bash

# Plain SQL dump — readable, slow to restore
pg_dump -h prod-db -U app -F p -f mydb.sql mydb

# Custom format — compressed, parallel restore, selective
pg_dump -h prod-db -U app -F c -f mydb.dump mydb

# Restore the custom dump
createdb mydb_restored
pg_restore -h localhost -U app -d mydb_restored mydb.dump

Selective dump and restore·bash

# Dump one schema, one table, only data, only schema...
pg_dump -h prod -U app -t public.orders -t public.line_items mydb > orders.sql
pg_dump -h prod -U app --schema-only mydb > schema.sql
pg_dump -h prod -U app --data-only --table=public.users mydb > users_data.sql

Physical base backup·bash

# Take a base backup (requires replication-capable user)
pg_basebackup -h prod -U replicator -D /var/backups/pg/$(date +%F) -X stream -P -R
# Combine with WAL archiving for point-in-time recovery

Two kinds of backup, two recovery stories

The 3-2-1 rule, applied

Test the restore

Code

External links

Exercise

Progress

Comments 0