serverhabit.com

Practical server tutorials and cost-conscious self-hosting for indie hackers

How to Write a PostgreSQL Backup Automation Script

by David Park
How to Write a PostgreSQL Backup Automation Script

What You'll End Up With

By the end of this guide you'll have a production-ready PostgreSQL backup automation script that runs on Ubuntu 24.04. It uses pg_dump to create compressed .sql.gz archives, stores them in a dedicated directory, rotates files older than 7 days, and sends an email alert on failure. A systemd timer (not cron) triggers it nightly. No third-party backup SaaS required — this runs on any $6/month VPS.

Prerequisites

  • Ubuntu 24.04 server with PostgreSQL 16 installed (postgresql-16 package)
  • A Unix user named postgres (created automatically by the package)
  • mailutils installed if you want failure email alerts
  • Root or sudo access
  • Basic familiarity with systemd and bash

1. Create the Backup Directory

Run the following to create a dedicated backup directory owned by the postgres user:

sudo mkdir -p /var/backups/postgresql
sudo chown postgres:postgres /var/backups/postgresql
sudo chmod 750 /var/backups/postgresql

This keeps backup files readable only by postgres and root, preventing other system users from accessing raw database dumps.

2. Write the Backup Script

Create the script at /usr/local/bin/pg_backup.sh:

sudo nano /usr/local/bin/pg_backup.sh

Paste the following content exactly:

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

# ── Configuration ────────────────────────────────────────────
BACKUP_DIR="/var/backups/postgresql"
RETENTION_DAYS=7
DATE=$(date +%Y-%m-%d_%H-%M-%S)
ALERT_EMAIL="ops@example.com"   # set to "" to disable alerts
LOG_FILE="/var/log/pg_backup.log"

# ── Helpers ──────────────────────────────────────────────────
log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" | tee -a "$LOG_FILE"; }

fail() {
  log "ERROR: $*"
  if [[ -n "$ALERT_EMAIL" ]]; then
    echo "PostgreSQL backup failed on $(hostname): $*" \
      | mail -s "[ALERT] pg_backup failed" "$ALERT_EMAIL"
  fi
  exit 1
}

# ── Trap unexpected errors ────────────────────────────────────
trap 'fail "Unexpected error on line $LINENO"' ERR

# ── Fetch database list ───────────────────────────────────────
DATABASES=$(psql -U postgres -At -c \
  "SELECT datname FROM pg_database WHERE datistemplate = false AND datname <> 'postgres';")

if [[ -z "$DATABASES" ]]; then
  log "No user databases found. Exiting."
  exit 0
fi

# ── Dump each database ───────────────────────────────────────
for DB in $DATABASES; do
  OUTFILE="${BACKUP_DIR}/${DB}_${DATE}.sql.gz"
  log "Backing up database: $DB → $OUTFILE"
  pg_dump -U postgres -Fp "$DB" | gzip -9 > "$OUTFILE"
  log "Completed: $OUTFILE ($(du -sh "$OUTFILE" | cut -f1))"
done

# ── Rotate old backups ────────────────────────────────────────
log "Rotating backups older than ${RETENTION_DAYS} days..."
find "$BACKUP_DIR" -name '*.sql.gz' -mtime +"$RETENTION_DAYS" -delete
log "Rotation complete."

log "All backups finished successfully."

Save and close (Ctrl+O, Enter, Ctrl+X).

What each section does:

  • set -euo pipefail — aborts on any unhandled error, undefined variable, or pipe failure.
  • fail() — logs the error and optionally emails you before exiting non-zero.
  • trap ... ERR — catches unexpected failures that set -e triggers.
  • The psql query excludes template databases and the default postgres DB so you only back up your actual application databases.
  • pg_dump -Fp produces plain SQL piped directly into gzip -9 — no intermediate uncompressed file touches disk.
  • find ... -mtime +7 -delete removes archives older than 7 days.

3. Set Permissions on the Script

Make the script executable and restrict ownership:

sudo chown postgres:postgres /usr/local/bin/pg_backup.sh
sudo chmod 750 /usr/local/bin/pg_backup.sh

Create the log file with correct ownership:

sudo touch /var/log/pg_backup.log
sudo chown postgres:postgres /var/log/pg_backup.log

The script runs as the postgres user, which already has peer-authenticated access to PostgreSQL — no password needed.

4. Test the Script Manually

Run it once as the postgres user to confirm it works before wiring up the timer:

sudo -u postgres /usr/local/bin/pg_backup.sh

Expected output (example with a database named myapp):

[2025-01-15 14:32:01] Backing up database: myapp → /var/backups/postgresql/myapp_2025-01-15_14-32-01.sql.gz
[2025-01-15 14:32:03] Completed: /var/backups/postgresql/myapp_2025-01-15_14-32-01.sql.gz (4.2M)
[2025-01-15 14:32:03] Rotating backups older than 7 days...
[2025-01-15 14:32:03] Rotation complete.
[2025-01-15 14:32:03] All backups finished successfully.

Confirm the file exists:

ls -lh /var/backups/postgresql/

-rw-r--r-- 1 postgres postgres 4.2M Jan 15 14:32 myapp_2025-01-15_14-32-01.sql.gz

5. Create the systemd Service and Timer

Using a systemd timer instead of cron gives you dependency handling, logging via journald, and missed-run tracking.

Create the service unit at /etc/systemd/system/pg-backup.service:

sudo nano /etc/systemd/system/pg-backup.service

[Unit]
Description=PostgreSQL Backup Automation Script
After=postgresql.service
Requires=postgresql.service

[Service]
Type=oneshot
User=postgres
ExecStart=/usr/local/bin/pg_backup.sh
StandardOutput=journal
StandardError=journal

Create the timer unit at /etc/systemd/system/pg-backup.timer:

sudo nano /etc/systemd/system/pg-backup.timer

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

[Timer]
OnCalendar=*-*-* 02:30:00
Persistent=true

[Install]
WantedBy=timers.target

Persistent=true means if the server was off at 02:30, the backup runs at next boot — critical for VPS instances that get rebooted.

Reload systemd and enable the timer:

sudo systemctl daemon-reload
sudo systemctl enable --now pg-backup.timer

Expected output:

Created symlink /etc/systemd/system/timers.target.wants/pg-backup.timer → /etc/systemd/system/pg-backup.timer.

6. Verify It Works

Check the timer is active and shows the next trigger time:

systemctl status pg-backup.timer

● pg-backup.timer - Run PostgreSQL backup nightly at 02:30
     Loaded: loaded (/etc/systemd/system/pg-backup.timer; enabled; preset: enabled)
     Active: active (waiting) since Wed 2025-01-15 14:35:00 UTC; 5s ago
    Trigger: Thu 2025-01-16 02:30:00 UTC; 11h left

Trigger the service immediately to confirm end-to-end:

sudo systemctl start pg-backup.service

Check the journal for output:

journalctl -u pg-backup.service -n 30 --no-pager

List all backup files:

ls -lht /var/backups/postgresql/

Verify a dump is restorable (run on a test server, not production):

gunzip -c /var/backups/postgresql/myapp_2025-01-15_14-32-01.sql.gz | psql -U postgres myapp_restore

Always test restores. A backup you've never restored is a backup you don't have.

Troubleshooting

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed PostgreSQL is not running. Run sudo systemctl start postgresql@16-main and retry.

permission denied when writing to /var/backups/postgresql/ The script is not running as postgres. Confirm User=postgres in the service unit and re-run sudo systemctl daemon-reload.

gzip: stdout: No space left on device The backup directory is full. Check disk usage with df -h /var/backups. Reduce RETENTION_DAYS or mount a larger volume.

No email alert received Confirm mailutils is installed (dpkg -l mailutils) and that your server's MTA is configured. Test with echo test | mail -s test ops@example.com.

Timer never fires Run systemctl list-timers pg-backup.timer and confirm the NEXT column shows a future time. If the unit shows failed, inspect with journalctl -u pg-backup.timer.

Backup file is 0 bytes pg_dump failed silently before the pipe. Remove set -o pipefail temporarily and run manually to see the raw error, then fix the underlying issue and restore the flag.

Next Steps

This PostgreSQL backup automation script covers the core loop: dump, compress, rotate, alert. From here, consider:

  • Off-site replication — sync /var/backups/postgresql/ to an S3-compatible bucket with rclone or aws s3 sync after each run. Add the sync command at the end of pg_backup.sh before the final log line.
  • Backup encryption — pipe pg_dump output through gpg --symmetric --cipher-algo AES256 before gzip if the backup directory is on shared storage.
  • Slack or PagerDuty alerts — replace the mail command in fail() with a curl POST to a webhook for tighter incident response.
  • Custom format dumps — switch pg_dump -Fp to pg_dump -Fc (custom format) for faster selective restores with pg_restore, at the cost of losing human-readable SQL.
  • Monitoring — push a heartbeat to a dead-man's-switch service (e.g., Healthchecks.io) at the end of a successful run so you get paged if the backup stops running at all.