Every company has backups. Far fewer have restores. We regularly meet teams whose nightly pg_dump cron job has been failing silently for weeks, whose backups sit on the same server as the database, or who have never measured how long a restore of their 400 GB database actually takes. This guide covers how to back up PostgreSQL so that you can actually recover: the backup types, point-in-time recovery, tooling such as pgBackRest, the numbers to agree on with the business, and the restore drills that turn backups from hope into evidence.

Start with RPO and RTO

Two numbers define your backup strategy:

  • RPO (Recovery Point Objective) — how much data you can afford to lose. "Up to 5 minutes of orders" is an RPO.
  • RTO (Recovery Time Objective) — how long the system may be down while you restore. "Back online within 1 hour" is an RTO.

Nightly dumps give an RPO of up to 24 hours. If the business expects minutes, you need continuous WAL archiving. If it expects near-zero downtime, you need replicas and failover in addition to backups. Agree on these numbers in writing before choosing tools.

Logical vs physical backups

PostgreSQL offers two families of backups (PostgreSQL: backup and restore):

Type Tool Strengths Limitations
Logical pg_dump, pg_dumpall Portable across versions and platforms, selective (one database or table) Slow for large databases, point-in-time only at dump moment
Physical pg_basebackup, pgBackRest, Barman, WAL-G Fast for large databases, enables point-in-time recovery Same major version and platform, whole cluster

Logical dumps are excellent for migrations, copying a database to staging, or archiving a single database (pg_dump). For production disaster recovery of anything beyond a small database, physical backups with WAL archiving are the standard.

Point-in-time recovery (PITR)

PostgreSQL writes every change to the write-ahead log (WAL) before applying it. If you take a physical base backup and continuously archive WAL files, you can restore the base backup and replay WAL up to any moment — for example, one second before someone ran DELETE without a WHERE clause (PostgreSQL: continuous archiving and PITR).

The building blocks:

  1. wal_level = replica (the default) and archive_mode = on.
  2. An archive_command or archive library that copies completed WAL segments to safe storage.
  3. Regular base backups (weekly full, daily incremental or differential).
  4. A tested restore procedure that sets recovery_target_time and replays WAL.

PostgreSQL 17 added native incremental backups to pg_basebackup, combined into a full backup with pg_combinebackup, and pg_verifybackup checks backup integrity (PostgreSQL 17 release; pg_combinebackup; pg_verifybackup).

pgBackRest in practice

You can build PITR with built-in tools, but a dedicated backup tool handles the details: parallel compression, retention, encryption, object storage, verification and restore. We use pgBackRest on most self-managed installations; Barman and WAL-G are solid alternatives.

A minimal configuration that sends backups and WAL to S3-compatible object storage with encryption:

# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-type=s3
repo1-s3-endpoint=s3.eu-central-1.example.com
repo1-s3-bucket=pg-backups-prod
repo1-s3-region=eu-central-1
repo1-path=/cluster-main
repo1-retention-full=4
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=<from-secrets-manager>
process-max=4
compress-type=zst
start-fast=y

[main]
pg1-path=/var/lib/postgresql/18/main

# postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'

pgbackrest --stanza=main stanza-create
pgbackrest --stanza=main --type=full backup     # weekly
pgbackrest --stanza=main --type=diff backup     # daily
pgbackrest --stanza=main check                  # verifies archiving works

# Restore to a point in time on a new server
pgbackrest --stanza=main --type=time \
  --target="2026-06-01 14:31:00+03" --target-action=promote restore

See the pgBackRest user guide for retention, multiple repositories and standby setups.

Where to store backups: the 3-2-1 rule

Keep at least three copies of data, on two different media or services, with one copy offsite (CISA: data backup options). For PostgreSQL that typically means: the production database, a backup repository with the same provider in another region or storage class, and a second repository with a different provider. Add immutability (object lock) or a separate account with restricted credentials, so ransomware or a compromised server cannot delete backups.

On our Hetzner clusters, for example, backups go to a Storage Box and an offsite S3-compatible provider, as described in Kubernetes on Hetzner.

Kubernetes: let an operator do it

If PostgreSQL runs in Kubernetes, use an operator rather than hand-written StatefulSets. CloudNativePG, a CNCF project, manages replicas, failover, WAL archiving and backups to object storage declaratively (CloudNativePG; CNCF).

Restore drills: the only proof that backups work

A backup you have never restored is a hypothesis. Schedule drills:

  • Monthly automated restore: restore the latest backup to a temporary server, run sanity queries (row counts, latest order timestamp), record the duration, then destroy the server.
  • Quarterly PITR drill: restore to a specific timestamp and verify the expected data is there.
  • Yearly full disaster exercise: assume the primary region is gone; follow the runbook end to end, including DNS and application configuration.

Measure actual restore time against your RTO. A 500 GB database restore over a slow link can take hours — better to learn that during a drill.

Monitoring backups

  • Alert if the latest successful backup is older than expected.
  • Alert on WAL archiving failures; pg_stat_archiver shows failed attempts.
  • Track backup size and duration trends.
  • Report drill results to the business owners of RPO and RTO.

Feed these metrics into the same observability stack as your applications; see OpenTelemetry observability. For Odoo installations, backups and performance go together, see Odoo performance tuning.

Common mistakes

  • Backups stored on the same server or disk as the database.
  • Only nightly pg_dump on a database where losing a day of data is unacceptable.
  • No encryption, or encryption keys stored next to the backups.
  • WAL archiving failing silently until the disk fills up.
  • No documented, tested restore procedure.
  • Replicas mistaken for backups — a replica faithfully replicates an accidental DROP TABLE.

A restore runbook template

Write the procedure down before you need it, and keep it next to the on-call documentation:

  1. Declare the incident and decide the recovery target: latest state or a point in time before the problem.
  2. Provision a new server from infrastructure code rather than repairing the damaged one.
  3. Restore with the backup tool, setting the target time if needed, and wait for WAL replay to finish.
  4. Verify data with prepared checks: row counts of key tables, latest order or invoice timestamps, application smoke tests.
  5. Switch traffic: update connection strings or DNS, restart application pools.
  6. Re-enable backups and archiving on the new primary immediately.
  7. Write a post-incident review including actual RPO and RTO compared with targets.

FAQ

Are replicas a backup? No. Replicas protect against hardware failure, not against human error or corruption, which they replicate instantly. You need both.

How often should we take full backups? Weekly full plus daily differential or incremental backups, with continuous WAL archiving, is a sensible default for most databases.

Is pg_dump enough for small databases? For small, non-critical databases where losing up to a day of data is acceptable, yes — if dumps are stored offsite and restore-tested.

What about managed databases like RDS or Cloud SQL? They handle backups and PITR for you, but you should still test restores and keep an independent copy for provider-level incidents.

Sources

  1. PostgreSQL. Backup and restore and Continuous archiving and PITR.
  2. PostgreSQL. pg_dump, pg_basebackup, pg_combinebackup, pg_verifybackup.
  3. PostgreSQL Global Development Group (2024). PostgreSQL 17 released.
  4. pgBackRest and its user guide; Barman; WAL-G.
  5. CISA. Data backup options.
  6. CloudNativePG.