How to back up a PostgreSQL database on Linux (pg_dump + restic)

There are two broad ways to back up Postgres. A logical backup (pg_dump) exports the database as SQL or a custom archive — portable, easy to restore selectively, and version-agnostic. A physical backup copies the on-disk data files (via pg_basebackup or a filesystem snapshot) and is the basis for point-in-time recovery.

For most single-server apps, a scheduled logical dump is the pragmatic default, and it is what this guide covers.

pg_dump takes a consistent snapshot inside a single transaction, so you get a coherent backup even while the database is being written to. Use the custom format (-Fc) — it is compressed and lets you restore individual tables later. Keep the password out of the command by using a .pgpass file or the PGPASSWORD environment variable so credentials never land in shell history or a cron log.

# Compressed custom-format dump of one database
pg_dump -Fc -h localhost -U postgres -d myapp -f myapp.dump

# Every database plus roles and tablespaces
pg_dumpall -h localhost -U postgres -f cluster.sql

# Restore a custom-format dump with pg_restore (not psql)
pg_restore -d myapp --clean myapp.dump

A dump left on the same server as the database does not help if the disk fails, the host is compromised, or someone drops the wrong table. Copy it to storage you control, encrypted, and keep previous versions. restic encrypts on the client, deduplicates, and retains each snapshot.

• Initialise a repository against an S3-compatible bucket (once).
• Back up the dump directory on a schedule.
• Prune old snapshots with a retention policy.
• Store the restic password in a password manager, separate from the server — it is never recoverable if lost.

export RESTIC_REPOSITORY="s3:https://s3.example.com/my-backups"
export RESTIC_PASSWORD="a-long-random-passphrase"
export AWS_ACCESS_KEY_ID="..."
export AWS_SECRET_ACCESS_KEY="..."

restic init                      # first time only
restic backup /var/backups/pg    # snapshot the dumps

Run the dump and the restic snapshot together from a small script on a schedule, then prune old snapshots so the repository does not grow without limit. The policy below keeps a week of daily snapshots, a month of weekly ones, and six months of monthly ones.

#!/usr/bin/env bash
set -euo pipefail
ts=$(date +%F)
pg_dump -Fc -d myapp -f "/var/backups/pg/myapp-$ts.dump"
restic backup /var/backups/pg
restic forget --keep-daily 7 --keep-weekly 4 --keep-monthly 6 --prune

A backup that has never been restored is not proven. Periodically restore into a scratch database and check that the data is intact, and note how long it takes so your recovery estimate reflects a real run.

restic restore latest --target /tmp/restore
createdb myapp_restore_test
pg_restore -d myapp_restore_test --clean \
  /tmp/restore/var/backups/pg/myapp-*.dump

Rested runs exactly this workflow for you — scheduled pg_dump, restic encryption to your own storage, retention, run history, and one-click restores — while your encryption key stays with you. Docker Postgres is supported (in beta). If you would rather not maintain the cron scripts and monitoring yourself, see the Postgres backups page.