Automating PostgreSQL Backups with pg_dump and Cron

PostgreSQL gives you two ways to get data out of a server, and for daily operations you mostly need one: pg_dump. It is logical, portable across server versions, and safe to run on a busy VPS without stopping traffic. The part everyone learns the hard way is the automation around it. A dump sitting on the same disk as the database is a false sense of safety. The pipeline from dump, to off-site archive, to verified restore is the actual backup.

Choose the right pg_dump format

Plain SQL dumps are fine for small databases, but the custom format earns its keep quickly. Custom-format archives are compressed, support selective restore with pg_restore, and let you apply restores in parallel. Plain text is easier to grep; custom is easier to live with.

A solid single-database command looks like this:

pg_dump --format=custom --compress=9 --no-owner --no-privileges   --quote-all-identifiers --file=/var/backups/postgres/appdb.dump   "postgres://backup_user@10.0.0.4/appdb"

--no-owner and --no-privileges stop the dump from pinning roles that exist only on the source host, which matters when the restore target is a fresh machine. --quote-all-identifiers keeps the dump round-trippable even with unusual column names.

One dump per database, not one guess

Hardcoding a single database name means every other database silently loses its backup. Loop over the list the server actually reports and build one archive per database, stamped with the date.

#!/usr/bin/env bash
# /usr/local/bin/pg_dump_all.sh
set -euo pipefail
BACKUP_DIR=/var/backups/postgres
RETENTION_DAYS=30
stamp=$(date +%Y%m%d)
mkdir -p $BACKUP_DIR
for db in $(psql -At -c "SELECT datname FROM pg_database WHERE datistemplate = false;" postgres); do
  pg_dump --format=custom --compress=9 --no-owner --no-privileges     --quote-all-identifiers     --file=$BACKUP_DIR/$db.$stamp.dump     "postgres://backup_user@10.0.0.4/$db"
done
find $BACKUP_DIR -name '*.dump' -mtime +$RETENTION_DAYS -delete
ls -lh $BACKUP_DIR | tail -n +2

The query filters out template databases, so you never attempt a dump of an internal structure.

Cron, with a smoke test attached

A cron entry that runs a script is one line. A cron entry that quietly writes a zero-byte dump for three weeks is how disasters become famous. Add a post-run smoke test that fails loudly when the newest archive cannot be read back.

# /etc/cron.d/pg-backup
30 1 * * * root /usr/local/bin/pg_dump_all.sh
15 6 * * * root /usr/local/bin/pg_backup_audit.sh

#!/usr/bin/env bash
# /usr/local/bin/pg_backup_audit.sh
set -euo pipefail
latest=$(ls -t /var/backups/postgres/appdb.*.dump | head -1)
pg_restore --list $latest >/dev/null 2>&1   || { echo "audit failed: $latest unreadable"; exit 1; }
echo "audit ok: $(basename $latest)"

The audit runs a few hours after the dump finishes, which leaves time for transient hiccups to surface before you rely on the file.

The last mile: off-site and retention

None of this matters if the only copy lives beside the database. Push the archive directory off the box after each dump, and keep pruning honest:

# appended to pg_dump_all.sh after the dump loop
rsync -a --delete /var/backups/postgres/ offsite:backups/postgres/
restic -r sftp:offsite:/backups backup /var/backups/postgres
restic -r sftp:offsite:/backups forget --keep-daily 30 --prune

Retention is a policy, so express it as one: 30 nightly copies pruned on every run, locally and off-site identically. That way the restore target never has to scan a mountain of old files to find today's archive.

Takeaway

pg_dump is dependable, but automation turns it into a backup system: per-database archives, cron with an independent smoke test, off-site copies, and retention that prunes on schedule. Practice one restore today rather than after the disk fails. When you want a clean Ubuntu 24.04 host to stage this pipeline, netbayhosts.in provisions one in under a minute.