Schedule and Verify PostgreSQL Backups with pg_dump and Retention

Before you run this

This guide sets up a bash script that runs pg_dump (and pg_dumpall for cluster-wide roles) on a schedule, writes each backup to a directory with a timestamped filename, and then deletes any backup older than a retention window. The purpose is a hands-off nightly logical backup you can restore from.

Two things change your system, so read them carefully:

• The retention step deletes files. The script runs find … -delete against the backup directory. If you point BACKUP_DIR at the wrong path, or loosen the filename pattern, you can delete files you meant to keep. Deletion here is irreversible — there is no recycle bin. Test the script with a long retention window (or comment the find lines out) until you trust it.
• Backups are only useful if they restore. A dump that was never test-restored is not a backup, it is a hope. The verification section below is not optional.

Privileges: the dump itself does not need root. It needs a PostgreSQL role that can read every database you back up — in practice the postgres superuser role. On Debian/Ubuntu the postgres OS user connects over the local socket with peer authentication, so running the script as the postgres OS user needs no password and no root. Creating the script in /usr/local/bin, the backup directory under /var/backups, and the cron job under /etc/cron.d does need sudo.

Test first. Read the script before you run it. Run it once by hand on a non-production server, or against a throwaway test database, and confirm the files appear and restore cleanly before you let cron drive it on a live host.

What I'm assuming

• Debian 12 or Ubuntu 22.04, with PostgreSQL 15 installed from the distro or PGDG packages.
• PostgreSQL listens on the local Unix socket and the postgres OS user has peer access (the default on these packages).
• You want logical backups (pg_dump), which are portable and easy to restore selectively. This is not a substitute for physical/PITR backups (pg_basebackup + WAL archiving) on a large, high-write database — that is a different guide.

If you are on RHEL/Alma the binaries are the same; paths like the data directory and the cron drop-in differ slightly, and the service is still managed by systemd.

The backup script

Save this as /usr/local/bin/pg_backup.sh. Replace the database names in the DATABASES array with your own, and set BACKUP_DIR and RETENTION_DAYS to suit you.

#!/usr/bin/env bash
# Nightly logical backup of named PostgreSQL databases, plus cluster globals.
set -euo pipefail

# --- Config: edit these ---
BACKUP_DIR="/var/backups/postgresql"   # where dumps are written
RETENTION_DAYS=14                       # delete dumps older than this
DATABASES=("appdb" "reportsdb")         # replace with YOUR database names
# --------------------------

TIMESTAMP="$(date +%Y%m%d_%H%M%S)"
mkdir -p "$BACKUP_DIR"

# Roles, tablespaces and other cluster-wide objects are NOT in per-db dumps,
# so capture them separately. --globals-only writes only those definitions.
pg_dumpall --globals-only --file="${BACKUP_DIR}/globals_${TIMESTAMP}.sql"

# Custom format (-Fc) is compressed and lets pg_restore be selective.
for db in "${DATABASES[@]}"; do
    pg_dump --format=custom \
            --file="${BACKUP_DIR}/${db}_${TIMESTAMP}.dump" \
            "$db"
done

# Retention: remove dumps older than RETENTION_DAYS. This DELETES files.
find "$BACKUP_DIR" -type f -name '*.dump'      -mtime +"$RETENTION_DAYS" -delete
find "$BACKUP_DIR" -type f -name 'globals_*.sql' -mtime +"$RETENTION_DAYS" -delete

A few notes on the non-obvious parts:

• set -euo pipefail makes the script stop on the first error rather than carrying on and reporting success. If a dump fails, you want to know.
• --format=custom produces a single compressed archive per database that you restore with pg_restore. The other common choice is plain SQL (--format=plain, the default), which you restore with psql; custom format is the mainstream choice because it supports selective and parallel restore.
• pg_dumpall --globals-only captures roles and tablespaces that a per-database pg_dump leaves out. Without it, a bare-metal restore would have tables but no login roles.

Make it executable and owned so the postgres user can run it:

sudo install -o postgres -g postgres -m 0755 pg_backup.sh /usr/local/bin/pg_backup.sh
sudo install -d -o postgres -g postgres -m 0750 /var/backups/postgresql

If you connect over TCP instead of the socket

If your database requires a password (a remote host, or md5/scram auth on localhost), do not put the password in the script. Create a ~/.pgpass file for the account that runs the backup, with permissions 0600, in the documented format hostname:port:database:username:password, and add the connection flags (-h, -p, -U) to the pg_dump/pg_dumpall calls. See the PostgreSQL manual's "The Password File" section for the exact .pgpass format.

Run it once by hand

Before scheduling anything, run it as the postgres user and look at the result:

sudo -u postgres /usr/local/bin/pg_backup.sh
ls -lh /var/backups/postgresql

You should see one .dump per database and one globals_*.sql, all with a non-trivial size and the current timestamp.

Schedule it with cron

Create a cron drop-in at /etc/cron.d/pg_backup so the job runs as postgres, not root. This example runs at 02:30 every night and logs to a file:

# m h dom mon dow user  command
30 2 * * *  postgres  /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1

Create the log file the postgres user can write:

sudo install -o postgres -g postgres -m 0640 /dev/null /var/log/pg_backup.log

Cron picks up files in /etc/cron.d automatically; there is nothing to reload. A systemd timer is the common alternative if you prefer systemctl-managed jobs — same script, different scheduler.

Verify the backups are real and restorable

Two checks. First, confirm the archive is structurally readable — pg_restore --list prints the table of contents without touching your database:

pg_restore --list /var/backups/postgresql/appdb_YYYYMMDD_HHMMSS.dump

If that prints a list of objects, the archive is intact. If it errors, the dump is corrupt and you have a problem to fix now, not during an outage.

Second — and do this periodically, not just once — restore into a scratch database and confirm it comes up. This is the only test that proves a backup works:

# Create an empty throwaway database, then restore into it.
sudo -u postgres createdb restore_test
sudo -u postgres pg_restore --dbname=restore_test \
     /var/backups/postgresql/appdb_YYYYMMDD_HHMMSS.dump
sudo -u postgres psql -d restore_test -c '\dt'   # list tables to sanity-check

Restore into the throwaway database, never over your live one. When you are satisfied, drop it:

sudo -u postgres dropdb restore_test

Check the retention behaviour too. After the job has run for longer than RETENTION_DAYS, ls -lt /var/backups/postgresql should show nothing older than that window. To rehearse it without waiting, hand-create a dummy file with an old timestamp and confirm a run removes it:

sudo -u postgres touch -d '30 days ago' /var/backups/postgresql/old_test.dump
sudo -u postgres /usr/local/bin/pg_backup.sh
ls /var/backups/postgresql/old_test.dump   # should now be "No such file"

Undoing it

There is nothing destructive to reverse in the setup — to stop the job, delete /etc/cron.d/pg_backup (or disable the timer), and cron stops running it immediately. Remove /usr/local/bin/pg_backup.sh and, if you want, the backup directory. The one irreversible action is the retention deletion; if you ever need to keep a backup beyond the window, copy it out of BACKUP_DIR — the script only ever deletes files that are still there.

For the exact flags and behaviour of pg_dump, pg_dumpall and pg_restore, see their reference pages in the official PostgreSQL documentation.