How to Automate PostgreSQL Backups

PostgreSQL ships with excellent backup tools, but they solve different problems. This guide explains when to use pg_dump, pg_dumpall and pg_basebackup, how WAL archiving enables point-in-time recovery, and how to automate the whole routine with restore tests.

Three tools, three purposes

Logical backups with pg_dump

pg_dump always works on a consistent snapshot of the database, even while users keep writing, so you do not need to lock anything. Prefer the custom format (-Fc): it is compressed, and pg_restore can restore selected tables and run restores in parallel.

# Custom-format dump of database "shop"
pg_dump -h localhost -U backup -Fc -f /srv/backups/shop-$(date +%F_%H%M).dump shop

# Directory format with 4 parallel jobs, useful for larger databases
pg_dump -h localhost -U backup -Fd -j 4 -f /srv/backups/shop-$(date +%F) shop

Global objects are not included in pg_dump. Save roles separately, otherwise a restore will fail on missing owners and grants:

pg_dumpall -h localhost -U postgres --globals-only > /srv/backups/globals-$(date +%F).sql

To restore into a new database:

psql -U postgres -f globals-2026-10-19.sql
createdb -U postgres -O shop shop_restored
pg_restore -U postgres -d shop_restored -j 4 --no-owner shop-2026-10-19_0200.dump

A dump restores the data as it was at the moment the dump started. Anything written afterwards is lost unless you also have WAL archives, which only work with physical backups.

Physical backups with pg_basebackup

pg_basebackup copies the entire data directory of a running cluster through the replication protocol. It needs a role with the REPLICATION attribute and a matching entry in pg_hba.conf.

pg_basebackup -h db1 -U replicator -D /srv/backups/base-$(date +%F) \
  -Ft -z -X stream -P --checkpoint=fast

• -Ft -z writes compressed tar files.
• -X stream includes the WAL generated during the backup, so the base backup is consistent on its own.
• --checkpoint=fast starts immediately instead of waiting for the next scheduled checkpoint.

The backup also contains a backup_manifest file. Use pg_verifybackup to check the backup against it.

PostgreSQL 17 added incremental base backups: with summarize_wal = on, pg_basebackup --incremental=<previous backup_manifest> copies only changed blocks, and pg_combinebackup reconstructs a full backup from a chain before restoring. This reduces backup time for large clusters, at the cost of a slightly more complex restore.

WAL archiving and point-in-time recovery

PostgreSQL writes every change to the write-ahead log (WAL). If you keep a base backup plus all WAL segments since then, you can recover the cluster to any moment in between, for example one minute before someone ran DELETE FROM orders without a WHERE clause.

Enable archiving in postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /srv/wal-archive/%f && cp %p /srv/wal-archive/%f'
archive_timeout = 300

Changing archive_mode requires a restart. The example copies segments to a local directory to keep it readable; in production, the archive must live on another machine or on object storage, and the command must return success only when the file is safely stored. archive_timeout forces a segment switch at least every five minutes, which bounds the data loss on low-traffic servers. Monitor pg_stat_archiver for failures: a failing archive_command makes WAL accumulate on the primary until the disk fills.

Dedicated tools such as pgBackRest, Barman or WAL-G handle compression, encryption, parallel transfer to S3, retention and verification. For most production clusters they are a better choice than a hand-written archive_command.

Performing a point-in-time recovery

1. Stop PostgreSQL on the recovery server and restore the base backup into an empty data directory.
2. Configure the restore command and target in postgresql.conf:

   restore_command = 'cp /srv/wal-archive/%f %p'
   recovery_target_time = '2026-10-19 14:31:00-03'
   recovery_target_action = 'pause'

3. Create an empty recovery.signal file in the data directory and start PostgreSQL.
4. When recovery pauses at the target, inspect the data. If it is correct, run SELECT pg_wal_replay_resume(); to end recovery (with recovery_target_action = 'pause', resuming promotes the server).

Always include the time zone in recovery_target_time; without it the server's TimeZone setting is used, which may not be what you expect during an incident.

Automating the routine

A simple automated setup for a medium database could be:

• continuous WAL archiving to offsite storage;
• a weekly base backup with pg_basebackup (or daily incrementals on PostgreSQL 17+);
• a nightly pg_dump of each database for easy single-table restores;
• a nightly pg_dumpall --globals-only.

Schedule with systemd timers or cron, and fail loudly:

#!/usr/bin/env bash
set -euo pipefail
export PGPASSFILE=/root/.pgpass   # credentials stored with mode 0600
DIR=/srv/backups/$(date +%F)
mkdir -p "$DIR"
pg_dumpall -h localhost -U backup --globals-only > "$DIR/globals.sql"
for db in $(psql -h localhost -U backup -At -c "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate"); do
  pg_dump -h localhost -U backup -Fc -f "$DIR/$db.dump" "$db"
done
test -s "$DIR/globals.sql"

Send the exit status to your monitoring system, then copy the directory offsite with encryption. A backup role for pg_dump needs read access to all objects; on PostgreSQL 14+ the predefined role pg_read_all_data simplifies this.

Restore strategies

Test your backups

Automate a weekly restore of the latest dump into a scratch database and run checks such as row counts and the most recent timestamps. Once a quarter, perform a full point-in-time recovery to prove the WAL archive is complete. Record restore durations to validate your recovery objectives (see RPO vs RTO).

Common mistakes

• Backing up databases with pg_dump and forgetting roles.
• Copying the data directory with rsync while the server runs, without pg_backup_start()/pg_backup_stop() or pg_basebackup.
• Archiving WAL to the same disk as the database.
• Not monitoring archive_command failures until the disk is full.
• Assuming a replica is a backup: replicas copy deletions instantly.

Next steps

1. Add a nightly pg_dump plus --globals-only if you have nothing today.
2. Plan WAL archiving with an offsite destination, ideally with pgBackRest, Barman or WAL-G.
3. Run a point-in-time recovery drill on a test server.

The Backnix database backup page describes how Backnix approaches PostgreSQL backups and their current status. For other engines, see MySQL and MariaDB.

References

• PostgreSQL documentation: Backup and Restore
• PostgreSQL documentation: Continuous Archiving and Point-in-Time Recovery
• PostgreSQL documentation: pg_basebackup
• PostgreSQL documentation: pg_combinebackup

Topics: pg_basebackuppg_dumpPostgreSQLWAL