Nightly PostgreSQL Backup with pg_dump

Schedule

Setup

1. 1

   Install prerequisites and choose a backup directory

   Ensure pg_dump is available on the machine running the backup (it ships with the postgresql-client package on Debian/Ubuntu: apt install postgresql-client). Create a dedicated backup directory with restricted permissions: mkdir -p /var/backups/postgres && chmod 700 /var/backups/postgres. If your PostgreSQL user requires a password, add a .pgpass entry (~/.pgpass, chmod 600) so the script can run non-interactively.

2. 2

   Create the backup script

   Save the script below to /usr/local/bin/pg-nightly-backup.sh and make it executable with chmod +x /usr/local/bin/pg-nightly-backup.sh. Edit the variables at the top (DB_NAME, DB_USER, PING_URL) to match your environment. The script dumps the database in custom compressed format, verifies the output file is non-empty, prunes backups older than 14 days, and pings your CronJobPro heartbeat URL on success or the /fail endpoint on failure.

3. 3

   Create a CronJobPro heartbeat monitor

   In your CronJobPro dashboard, create a new Heartbeat monitor. Set the period to 24 hours and the grace period to 30 minutes (this gives the backup enough time to finish before an alert fires). Copy the generated ping URL (https://cronjobpro.com/ping/<token>) and paste it into the PING_URL variable in your script. Configure your preferred alert channel: email, Slack, Discord, PagerDuty, or webhook.

4. 4

   Schedule the cron job

   Add the job to the crontab of the user who owns the PostgreSQL connection credentials. Run crontab -e and add the line shown below. The job runs at 2 AM daily, redirecting both stdout and stderr to a log file so you can inspect failures locally as well.

5. 5

   Test the setup end-to-end

   Run the script manually once: /usr/local/bin/pg-nightly-backup.sh. Confirm a .dump file appears in /var/backups/postgres, that the CronJobPro dashboard shows a successful ping, and that the backup can be restored with pg_restore --list on the output file. Fix any connection or permission errors before relying on the automated schedule.

The script

bash

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

# --- Configuration ---
DB_HOST="localhost"
DB_PORT="5432"
DB_NAME="mydb"
DB_USER="myuser"
BACKUP_DIR="/var/backups/postgres"
RETENTION_DAYS=14
PING_URL="https://cronjobpro.com/ping/YOUR_TOKEN_HERE"

# --- Derived values ---
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.dump"
LOG_PREFIX="[pg-backup ${TIMESTAMP}]"

mkdir -p "${BACKUP_DIR}"
chmod 700 "${BACKUP_DIR}"

fail() {
  echo "${LOG_PREFIX} ERROR: $1" >&2
  curl -fsS --retry 3 "${PING_URL}/fail" -o /dev/null || true
  exit 1
}

echo "${LOG_PREFIX} Starting backup of ${DB_NAME} to ${BACKUP_FILE}"

# Dump in custom compressed format (-Fc); exits non-zero on error
pg_dump \
  -h "${DB_HOST}" \
  -p "${DB_PORT}" \
  -U "${DB_USER}" \
  -Fc \
  -f "${BACKUP_FILE}" \
  "${DB_NAME}" || fail "pg_dump exited with error"

# Verify the file exists and is non-empty
[ -s "${BACKUP_FILE}" ] || fail "Backup file is empty or missing"

# Quick integrity check: list table of contents without restoring
pg_restore --list "${BACKUP_FILE}" > /dev/null 2>&1 \
  || fail "pg_restore integrity check failed"

echo "${LOG_PREFIX} Backup verified: ${BACKUP_FILE} ($(du -sh "${BACKUP_FILE}" | cut -f1))"

# Remove backups older than RETENTION_DAYS
find "${BACKUP_DIR}" -name "${DB_NAME}_*.dump" \
  -mtime +"${RETENTION_DAYS}" -delete
echo "${LOG_PREFIX} Pruned backups older than ${RETENTION_DAYS} days"

# Signal success to CronJobPro heartbeat
curl -fsS --retry 3 "${PING_URL}" -o /dev/null \
  && echo "${LOG_PREFIX} Heartbeat ping sent" \
  || echo "${LOG_PREFIX} WARNING: heartbeat ping failed (backup itself succeeded)"

echo "${LOG_PREFIX} Done."

# Crontab entry (add via crontab -e):
# 0 2 * * * /usr/local/bin/pg-nightly-backup.sh >> /var/log/pg-backup.log 2>&1

Frequently asked questions

More recipes