PostgreSQL Backups — Logical, Physical, and Off-Site

Tested on: PostgreSQL 16.x on Ubuntu 24.04 LTS, restic 0.16.x. The patterns are version-portable to PostgreSQL 14+ with minor command differences; the principles are unchanged.

Why this matters

A backup you have never restored is a hope. Most “we have backups” incidents reduce to:

• The backup ran nightly for two years and the most recent twelve failed silently.
• The backup ran successfully, but the dump’s permissions excluded a schema the application started using six months ago.
• The backup ran successfully and is intact, but lives on the same disk as the database — so when the disk dies, both die.
• The backup ran successfully, off-site, encrypted, restorable — but the encryption key was on the same server, and that server is the one that just died.

This guide gives you a PostgreSQL backup baseline that survives each of those failure modes. It covers logical backups (pg_dump) for portability, physical backups with WAL archiving for point-in-time recovery, encrypted off-site storage, and a restoration drill cadence.

Decide what each backup type is for

PostgreSQL gives you two backup primitives. They serve different purposes and most production setups use both.

For most installations: nightly logical backup for portability and partial restores, plus continuous WAL archiving and weekly base backups for PITR.

1. A dedicated backup role

pg_dump does not need superuser. Create a role with read access only to the data being backed up:

1CREATE ROLE backup_user WITH LOGIN PASSWORD '<long random>';
2GRANT pg_read_all_data TO backup_user;
3GRANT pg_read_all_settings TO backup_user;

pg_read_all_data (introduced in PostgreSQL 14) grants SELECT on every current and future table — exactly what a backup needs and no more. Add a pg_hba.conf entry that lets backup_user connect from localhost via scram-sha-256.

For physical backups, you’ll also need a role with REPLICATION attribute — keep it separate from the logical-backup role.

2. Logical backups with pg_dump

Daily dump, compressed, with a stable filename rotation:

 1#!/usr/bin/env bash
 2set -euo pipefail
 3
 4PGUSER=backup_user
 5PGHOST=127.0.0.1
 6PGDATABASE=appdb
 7DEST=/var/backups/postgres
 8
 9DATE=$(date -u +%Y%m%dT%H%M%SZ)
10TARGET="${DEST}/${PGDATABASE}-${DATE}.sql.zst"
11
12umask 077
13mkdir -p "${DEST}"
14
15PGPASSFILE=/etc/postgres/backup.pgpass \
16  pg_dump \
17    --host="${PGHOST}" \
18    --username="${PGUSER}" \
19    --dbname="${PGDATABASE}" \
20    --format=custom \
21    --no-owner \
22    --no-privileges \
23  | zstd -T0 -19 -o "${TARGET}"
24
25# Verify the dump opens cleanly.
26zstd -dcq "${TARGET}" | pg_restore --list >/dev/null
27
28echo "OK ${TARGET}"

Notes:

• --format=custom produces a pg_restore-readable archive — more flexible for partial restores than plain SQL.
• --no-owner / --no-privileges make the dump portable across environments (staging vs production).
• The pg_restore --list step verifies the file is readable. A backup that has not been at least listed is not a confirmed backup.

The /etc/postgres/backup.pgpass file holds the credentials, owned by the user running the backup, mode 600. The cron job itself runs as a dedicated postgres-backup user — not root, not postgres.

3. Physical backups + WAL archiving for PITR

For point-in-time recovery, you need a periodic base backup plus continuous archiving of write-ahead logs.

In postgresql.conf:

# Enable archiving
wal_level = replica
archive_mode = on
archive_command = '/usr/local/bin/wal-archive %p %f'
archive_timeout = 300s     # force WAL switch every 5 min — caps RPO

/usr/local/bin/wal-archive:

 1#!/usr/bin/env bash
 2# Called by PostgreSQL for every completed WAL segment.
 3# %p = absolute path, %f = filename.
 4set -euo pipefail
 5
 6SRC="$1"
 7NAME="$2"
 8DEST=/var/backups/postgres/wal
 9
10mkdir -p "${DEST}"
11
12# Atomic copy: write to temp, fsync, rename.
13cp --preserve=mode "${SRC}" "${DEST}/${NAME}.tmp"
14sync
15mv "${DEST}/${NAME}.tmp" "${DEST}/${NAME}"

Weekly base backup (cron):

1sudo -u postgres pg_basebackup \
2    --pgdata=/var/backups/postgres/base/$(date -u +%Y%m%dT%H%M%SZ) \
3    --format=tar \
4    --gzip \
5    --checkpoint=fast \
6    --progress \
7    --wal-method=stream

Retention: keep base backups for 4 weeks plus the WAL segments since the oldest retained base backup. Older WAL is safe to delete.

4. Encrypted off-site storage with restic

Local backups protect against single-file mistakes. Off-site backups protect against everything else — disk failure, ransomware, a hostile administrator. restic provides encrypted, deduplicated, incremental backups to S3-compatible storage:

 1# One-time init:
 2export RESTIC_REPOSITORY=s3:s3.eu-central-1.amazonaws.com/example-backups
 3export RESTIC_PASSWORD_FILE=/etc/postgres/restic.passphrase
 4export AWS_ACCESS_KEY_ID=...
 5export AWS_SECRET_ACCESS_KEY=...
 6restic init
 7
 8# Daily push:
 9restic backup \
10    --tag postgres \
11    --tag $(hostname) \
12    /var/backups/postgres
13
14# Apply retention:
15restic forget \
16    --keep-daily 7 \
17    --keep-weekly 4 \
18    --keep-monthly 12 \
19    --prune

Critical: the restic passphrase must be:

1. Long (>30 characters), randomly generated.
2. Stored in a password manager that survives the loss of this server.
3. Known to at least two people on the operations team.

A backup you cannot decrypt is not a backup. The single most common cause of “we have backups but can’t restore” is the encryption passphrase being lost when the original server is.

EEA-region storage (Hetzner Storage Box, OVH, Wasabi EU, S3 eu-central-1 etc.) avoids the cross-border-transfer headache from the privacy-by-design guide.

5. Retention policy — and matching the policy you publish

Pick numbers and write them down. A defensible starting set:

The off-site monthly retention should match — not exceed — the retention period stated in your privacy policy for the underlying data. If the policy says “we delete user data within 30 days of account closure,” and you have monthly backups for 12 months, the policy’s 30-day claim is contradicted by the backup retention. Reconcile the two numbers before you publish either.

6. The drill

A backup is not a backup until you have restored from it. The drill:

1. Quarterly minimum. Run a full restoration to a staging server.
2. Restore from off-site. Not from the local copy — verify the off-site copy plus the passphrase plus the credentials all work.
3. Verify content. Smoke-test a representative slice of data (latest user record, recent transaction, etc.).
4. Verify the application starts against the restored data. “Database restored” is not the same as “service operating.”
5. Document time-to-restore. This is your real RTO; the theoretical one in the policy is aspirational until measured.

A spreadsheet line per drill — date, who, RTO measured, anomalies — is sufficient documentation for an audit and far better than nothing.

Gotchas

pg_dump is consistent; the application may not be

pg_dump runs in a serializable snapshot transaction, so the dump is internally consistent. But if your application maintains invariants across multiple databases (or across PostgreSQL and Redis / external services), pg_dump of just one is not a consistent system snapshot. Document the boundaries of your consistency story.

Free-tier S3 buckets and Article 28

A backup target is a processor under GDPR Article 28. A “free tier” S3-compatible service without a published DPA is not a viable production backup target. Confirm the DPA exists and lives somewhere you can produce it on request.

Archive command failures silently filling the disk

If archive_command fails, PostgreSQL retries indefinitely and WAL segments accumulate in pg_wal/. The data directory will eventually fill the disk and the database will refuse writes. Monitor archive_failures (pg_stat_archiver view) and alert on any non-zero value.

pg_dump of a database with custom extensions

Extensions you installed manually (PostGIS, pg_trgm, etc.) are referenced in the dump but not bundled in it. The restore target must have the same extensions available, or pg_restore will fail partway. Document required extensions alongside the dump.

Retention edits without auditor approval

Shortening retention deletes evidence that may be subject to a legal hold. Lengthening retention extends the privacy-policy contradiction above. Treat retention changes as governance decisions, not as operational tuning.

Companion script

A wrapper that runs the daily dump, the weekly base backup, and the restic push — plus failure alerting — is planned at /scripts/postgres-backup/.

What this guide deliberately does not cover

• Logical replication for read-replica or cross-region scenarios — separate guide.
• Backup encryption with pg_dump’s native encryption (added in PostgreSQL 17) — useful but adoption is uneven; restic remains the more portable option for now.
• Specific cloud provider backup services (AWS RDS snapshots, Hetzner snapshots) — these complement but do not replace application-aware backups; their guarantees are provider-specific.