Automate PostgreSQL Backups with pg_dump

PostgreSQL ships no scheduler of its own, so a database without an explicit backup job has no backups. This tutorial builds an automated logical backup with pg_dump and pg_dumpall: one compressed dump per database, a systemd timer that runs it daily, a retention window, and a restore test that proves the files are usable.

What does pg_dump actually back up?

pg_dump backs up the contents of a single PostgreSQL database as a logical dump: schema definitions, table data, indexes, views, sequences and functions, but not cluster-wide objects such as roles, tablespaces or the server configuration files.

That gap matters. If you restore a dump onto a fresh cluster and the roles referenced by the GRANT statements do not exist, the restore fails on every permission statement. Cluster-wide objects are covered by pg_dumpall --globals-only, which writes roles, role memberships and tablespace definitions as plain SQL. A complete logical backup therefore always consists of two parts: the globals file and the per-database dumps.

Logical dumps are also not point-in-time backups. pg_dump uses a repeatable-read snapshot, so a single dump is internally consistent, but two dumps taken in sequence are consistent at two different points in time. For recovery to an arbitrary second, use physical backups with WAL archiving (pg_basebackup plus archive_command). For the everyday case of restoring yesterday's state of a 20 GB application database, pg_dump is the appropriate tool.

Prerequisites

• A Linux host running PostgreSQL 14 or newer, for example a scalable Cloud-VPS with a separate data volume
• Client tools (pg_dump, pg_dumpall, pg_restore, psql) at a version equal to or higher than the server version
• A shell account with sudo rights
• Free disk space for at least two full dumps of your largest database

Check the versions first. A client older than the server refuses to run:

$ pg_dump --version
pg_dump (PostgreSQL) 17.5
$ psql -h 127.0.0.1 -U postgres -Atc 'SHOW server_version;'
17.5

Set up credentials with .pgpass

An automated backup must run without an interactive password prompt. Create a dedicated read-only role and store its credentials in a .pgpass file, which libpq reads automatically.

$ sudo -u postgres psql -c "CREATE ROLE backup LOGIN PASSWORD 'YOUR_PASSWORD';"
$ sudo -u postgres psql -c "GRANT pg_read_all_data TO backup;"

The predefined role pg_read_all_data exists since PostgreSQL 14 and grants SELECT on every table in every database, which is exactly what a dump needs. Now write the password file for the system user that will run the job:

$ sudo -u postgres tee /var/lib/postgresql/.pgpass >/dev/null <<'EOF'
127.0.0.1:5432:*:backup:YOUR_PASSWORD
EOF
$ sudo -u postgres chmod 0600 /var/lib/postgresql/.pgpass

The file format is host:port:database:user:password. Permissions must be 0600, otherwise libpq ignores the file without an error message that points at permissions.

Which pg_dump format should you use?

Use the custom format (--format=custom, short -Fc) for automated backups, because it is compressed by default, allows selective restores of single tables, and supports parallel restore through pg_restore --jobs.

The directory format is the only one that supports parallel dumping (--jobs), which cuts wall-clock time on large databases with many tables. It writes a directory instead of a single file, so archiving to object storage requires an extra tar step. Start with the custom format and switch to directory format when a single dump run no longer fits your maintenance window.

Compression differs by version. Up to PostgreSQL 15, --compress accepts a gzip level from 0 to 9. Since PostgreSQL 16 it accepts a method as well, so --compress=zstd:3 is valid and noticeably faster than gzip at a comparable ratio.

Write the backup script

The script dumps the cluster globals once, loops over every connectable database, writes one custom-format dump per database, and then deletes files older than the retention window.

graph TD
  A["systemd timer: pg-backup.timer"] --> B["/usr/local/bin/pg-backup.sh"]
  B --> C["pg_dumpall --globals-only"]
  B --> D["psql: list databases"]
  D --> E["pg_dump -Fc per database"]
  C --> F["/var/backups/postgresql"]
  E --> F
  F --> G["find -mtime +14 -delete"]
  F --> H["pg_restore --list: verification"]

Create /usr/local/bin/pg-backup.sh:

#!/usr/bin/env bash
set -euo pipefail

BACKUP_DIR="/var/backups/postgresql"
RETENTION_DAYS=14
TIMESTAMP="$(date +%Y%m%dT%H%M%S)"

export PGHOST="127.0.0.1"
export PGPORT="5432"
export PGUSER="backup"
export PGPASSFILE="/var/lib/postgresql/.pgpass"

install -d -m 0700 "$BACKUP_DIR"

# 1. Cluster-wide objects: roles, memberships, tablespaces
pg_dumpall --globals-only --file="${BACKUP_DIR}/globals-${TIMESTAMP}.sql"

# 2. One dump per database, template databases excluded
databases=$(psql -d postgres -Atc \
  "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate;")

for db in $databases; do
  pg_dump --format=custom --compress=9 \
    --file="${BACKUP_DIR}/${db}-${TIMESTAMP}.dump" "$db"
done

# 3. Retention
find "$BACKUP_DIR" -type f -name '*.dump' -mtime +"$RETENTION_DAYS" -delete
find "$BACKUP_DIR" -type f -name 'globals-*.sql' -mtime +"$RETENTION_DAYS" -delete

On PostgreSQL 16 and newer, replace --compress=9 with --compress=zstd:3. Make the script executable and hand it to the postgres user:

$ sudo install -o postgres -g postgres -m 0750 pg-backup.sh /usr/local/bin/pg-backup.sh
$ sudo install -d -o postgres -g postgres -m 0700 /var/backups/postgresql
$ sudo -u postgres /usr/local/bin/pg-backup.sh

The first manual run is the important one. set -euo pipefail aborts on the first failing command, so any credential or permission problem surfaces here rather than silently at 02:30.

Schedule the backup with a systemd timer

Create a oneshot service unit that runs the script as the postgres user, plus a timer unit that triggers it daily. Systemd timers are preferable to cron entries here because they log to the journal, survive downtime through Persistent=true, and can spread load with RandomizedDelaySec.

/etc/systemd/system/pg-backup.service:

[Unit]
Description=PostgreSQL logical backup via pg_dump
After=postgresql.service
Requires=postgresql.service

[Service]
Type=oneshot
User=postgres
Group=postgres
Environment=PGPASSFILE=/var/lib/postgresql/.pgpass
ExecStart=/usr/local/bin/pg-backup.sh
Nice=10
IOSchedulingClass=idle

/etc/systemd/system/pg-backup.timer:

[Unit]
Description=Run the PostgreSQL backup daily at 02:30

[Timer]
OnCalendar=*-*-* 02:30:00
RandomizedDelaySec=900
Persistent=true
Unit=pg-backup.service

[Install]
WantedBy=timers.target

Enable the timer, not the service:

$ sudo systemctl daemon-reload
$ sudo systemctl enable --now pg-backup.timer
$ systemctl list-timers pg-backup.timer
NEXT                        LEFT     LAST  PASSED  UNIT             ACTIVATES
Tue 2026-08-18 02:30:00 UTC 9h left  -     -       pg-backup.timer  pg-backup.service

Dumps written to the same disk as the database survive an accidental DROP TABLE but not a failing volume. Copy the finished files off the host in a second step, for example to object storage or a separate backup volume, and keep at least one copy outside the availability zone of the database server.

How long should you keep pg_dump backups?

Keep at least 14 daily dumps plus one monthly dump for every retained month, because logical damage such as an accidental DELETE or a bad migration is frequently noticed days after it happened.

The script above implements the 14-day part with find -mtime +14 -delete. For monthly copies, add a hardlink on the first day of the month, which costs no additional disk space until the daily file is deleted:

if [ "$(date +%d)" = "01" ]; then
  for f in "${BACKUP_DIR}"/*-"${TIMESTAMP}".dump; do
    ln "$f" "${BACKUP_DIR}/monthly/$(basename "$f")"
  done
fi

Size the volume accordingly. A custom-format dump is typically 10 to 25 percent of the on-disk database size, so 14 dailies of a 20 GB database need roughly 30 to 70 GB.

Verify the backup with a test restore

A backup counts as valid only after it has been restored. Start with the cheap check, which reads the dump's table of contents and fails on a truncated or corrupted file:

$ pg_restore --list /var/backups/postgresql/appdb-20260817T023004.dump | head -5
;
; Archive created at 2026-08-17 02:30:04 UTC
;     dbname: appdb
;     TOC Entries: 412
;     Compression: 9

Then restore into a scratch database and compare a row count:

$ sudo -u postgres createdb restore_check
$ sudo -u postgres pg_restore --dbname=restore_check --exit-on-error --jobs=4 \
    /var/backups/postgresql/appdb-20260817T023004.dump
$ sudo -u postgres psql -d restore_check -Atc 'SELECT count(*) FROM orders;'
148213
$ sudo -u postgres dropdb restore_check

--exit-on-error is what makes this a test. Without it, pg_restore reports errors but returns exit code 0, so an automated check would treat a partial restore as success.

Troubleshooting

pg_dump: error: server version: 17.5; pg_dump version: 15.7 — the client is older than the server. Install the matching client package (postgresql-client-17 on Debian and Ubuntu) and point the script at it, for example with /usr/lib/postgresql/17/bin/pg_dump.

pg_dump: error: permission denied for table ... — the backup role cannot read every table. Grant pg_read_all_data (PostgreSQL 14 and newer). On older versions, grant SELECT per schema and add ALTER DEFAULT PRIVILEGES so future tables are covered too.

The timer fires but no files appear — check journalctl -u pg-backup.service -n 50. The usual cause is a missing PGPASSFILE: systemd services start with a minimal environment, so $HOME may not resolve to /var/lib/postgresql and .pgpass is never found. The explicit Environment=PGPASSFILE=... line in the unit fixes this.

Wrap-up

You now have a daily logical backup covering globals and every database, a 14-day retention window, and a documented restore path. Add two things next: an off-host copy of /var/backups/postgresql, and a monitoring check that alerts when the newest .dump file is older than 26 hours. A backup job that stops running without anyone noticing is the failure mode that actually causes data loss.