How to Monitor PostgreSQL Backup Cron Jobs and Catch Missing Backups

Your application can serve requests, pass health checks, and execute database queries while its backup pipeline has been broken for weeks. Application availability tells you nothing about whether last night’s pg_dump completed or whether its output reached storage.

To monitor PostgreSQL backup cron jobs, you need two independent controls: a script that reports success only after the backup finishes, and an external monitor that notices when that success signal never arrives.

For monitor setup, plan limits, and a downloadable PostgreSQL-to-S3 example, start with database backup monitoring. This guide goes deeper into failure handling and checking the stored backup; neither a receipt nor an upload replaces a restore drill.

1. The Deceptive “Green Dashboard”: Why Your Database Backups Fail Silently

Consider a backup scheduled for 02:00 every morning:

1. pg_dump exports the production database.
2. gzip compresses the export.
3. The AWS CLI uploads the archive to S3.
4. The script exits.

Meanwhile, your application dashboard checks HTTP responses, database connectivity, and query latency. All three remain healthy when the backup breaks.

The backup has its own dependencies:

These failures often become visible during a restore emergency. The operator opens the backup bucket and discovers that the most recent usable export predates the incident by several days.

A monitoring check against the application would never detect this. The missing condition is specific: a new backup must complete within an expected window.

This guide covers a scheduled logical backup of one PostgreSQL database. pg_dump does not include cluster-wide objects such as roles and tablespaces, and it does not replace a physical backup and WAL archiving strategy when you need point-in-time recovery. PostgreSQL documents these scope limits.

2. The Silent Culprit: How Shell Pipelines and >/dev/null 2>&1 Destroy Failure Visibility

This crontab entry discards the evidence you need:

0 2 * * * /opt/backups/postgres-backup.sh >/dev/null 2>&1

>/dev/null discards standard output. 2>&1 sends standard error to that same destination.

Authentication errors, disk errors, and upload failures disappear. Redirection does not turn a failure into success, but it removes the diagnostic output.

Now consider a second problem inside the script:

#!/bin/bash

pg_dump --dbname=app_production | gzip > /backups/backup.sql.gz

echo "Backup completed"

Without pipefail, the pipeline normally returns the exit status of its last command. If pg_dump fails before producing output, gzip can still successfully create a valid compressed representation of an empty stream.

The script then prints “Backup completed” and exits successfully.

The same problem affects this apparently cautious version:

#!/bin/bash

pg_dump --dbname=app_production | gzip > /backups/backup.sql.gz \
    && echo "Backup completed"

The && checks the pipeline status. Without pipefail, that status can still reflect only gzip.

Reproduce the false success

Run this in a disposable directory. Port 1 deliberately targets a connection that would normally be refused:

#!/bin/bash
set +e
set +o pipefail

export PGCONNECT_TIMEOUT=3

pg_dump \
    --host=127.0.0.1 \
    --port=1 \
    --username=backup_user \
    --dbname=app_production \
    --no-password \
    | gzip > broken-backup.sql.gz

pipeline_status=$?

printf 'Pipeline exit status: %s\n' "$pipeline_status"

gzip -t broken-backup.sql.gz
printf 'Gzip integrity check: %s\n' "$?"

gzip -dc broken-backup.sql.gz | wc -c

With pg_dump failing and gzip succeeding, the pipeline reports 0. The gzip integrity check can also report 0, while decompression produces zero bytes.

A nonempty .gz file is not evidence that it contains a database dump.

Enable pipeline failure propagation:

#!/bin/bash
set -eo pipefail

pg_dump --dbname=app_production --no-password \
    | gzip > backup.sql.gz

printf 'The dump and compression commands both succeeded.\n'

With pipefail, a failing command makes the pipeline fail. For this standalone pipeline, set -e stops execution before the success message. Bash has exceptions to errexit, including commands used as conditional tests, so preserve this control flow when adapting the script. Bash documents both behaviors.

Why logs and success notifications are insufficient

Logs are useful when a process starts and produces evidence. A removed crontab entry, stopped scheduler, or powered-off host may produce no new backup log at all.

Likewise, a success email proves only what the sender actually checked. If the script reports success after a broken pipeline, the notification repeats the mistake.

Even correctly implemented success notifications need an absence check. Someone or something must notice that today’s notification never arrived. An external monitor can perform that check automatically.

3. Building a Bulletproof Postgres Backup Wrapper Script

The wrapper below makes completion explicit:

• The dump pipeline must succeed.
• The resulting archive must contain bytes and pass gzip validation.
• The S3 upload must succeed.
• S3 must report an object with the expected size.
• Only then may the script send its success heartbeat.

It also prevents overlapping executions, records the last completed stage, and separates backup success from heartbeat delivery.

Prepare the execution environment

The example targets Linux with Bash, GNU coreutils, gzip, curl, flock, PostgreSQL client tools, and AWS CLI v2.

Run it under a dedicated account such as pgbackup. Before scheduling it:

• Create /var/lib/pgbackup and /var/log/pgbackup, writable by that account.
• Provision /etc/pgbackup/pgpass with mode 0600 and ownership allowing the backup account to read it.
• Install the database CA certificate at /etc/pgbackup/root.crt.
• Configure noninteractive AWS authentication, preferably using the host or workload’s IAM role.
• Configure the destination bucket’s encryption, retention, and access policies.
• Ensure the selected pg_dump version supports the database server.

The password file uses this format:

db.internal.example.com:5432:app_production:backup_user:REPLACE_WITH_DATABASE_PASSWORD

Use your secret provisioning process to supply the real password. PostgreSQL ignores password files with permissive group or world access on Unix systems. PostgreSQL password-file documentation

The AWS identity needs permission to upload to the selected prefix and read object metadata. HeadObject requires s3:GetObject; multipart uploads and your encryption configuration may require additional permissions. AWS HeadObject documentation

The complete wrapper

Save this as /usr/local/sbin/postgres-backup.sh and restrict access because it contains the monitor’s private URL.

Replace PINGCRON_URL="https://pingcron.io" with the unique success URL copied from your PingCron monitor. The website homepage is a placeholder, not a heartbeat endpoint. The script rejects it.

#!/bin/bash
set -eo pipefail
set -u
umask 077

# Configure these for the backup account and environment.
export PATH="/usr/local/bin:/usr/bin:/bin"
export PGHOST="db.internal.example.com"
export PGPORT="5432"
export PGDATABASE="app_production"
export PGUSER="backup_user"
export PGPASSFILE="/etc/pgbackup/pgpass"
export PGCONNECT_TIMEOUT="10"
export PGSSLMODE="verify-full"
export PGSSLROOTCERT="/etc/pgbackup/root.crt"
export AWS_DEFAULT_REGION="us-east-1"
export AWS_PAGER=""
export AWS_RETRY_MODE="standard"
export AWS_MAX_ATTEMPTS="3"

BACKUP_DIR="/var/lib/pgbackup"
S3_BUCKET="replace-with-your-private-backup-bucket"
S3_PREFIX="postgres/app-production"

# Replace with the PRIVATE success URL copied from your monitor.
# The website URL below is deliberately rejected by this script.
PINGCRON_URL="https://pingcron.io"

stage="preflight"
dump_created=0
file_verified=0
upload_completed=0
remote_verified=0
heartbeat_delivered=0
work_dir=""

log() { printf '%s\n' "$*" >&2; }
die() { log "ERROR: $*"; exit 1; }

finish() {
    rc=$?
    trap - EXIT
    set +e

    log "exit=$rc stage=$stage dump_created=$dump_created file_verified=$file_verified upload_completed=$upload_completed remote_verified=$remote_verified heartbeat_delivered=$heartbeat_delivered"

    if [[ -n "$work_dir" ]]; then
        rm -f -- "$work_dir/backup.sql.gz"
        rmdir -- "$work_dir"
    fi

    exit "$rc"
}

trap finish EXIT
trap 'exit 143' TERM
trap 'exit 130' INT

for tool in pg_dump gzip aws curl flock mktemp stat date rm rmdir; do
    command -v "$tool" >/dev/null || die "Missing executable: $tool"
done

case "$PINGCRON_URL" in
    "https://pingcron.io"|"https://pingcron.io/"|"")
        die "Replace PINGCRON_URL with your monitor's private success URL"
        ;;
    https://*) ;;
    *) die "PINGCRON_URL must use HTTPS" ;;
esac

[[ -r "$PGPASSFILE" ]] || die "Password file is not readable"
[[ -r "$PGSSLROOTCERT" ]] || die "Database CA file is not readable"
[[ -d "$BACKUP_DIR" && -w "$BACKUP_DIR" ]] \
    || die "Backup directory is not writable"

# Local filesystem lock. A skipped overlapping run sends no heartbeat.
exec 9>"$BACKUP_DIR/.backup.lock"
flock -n 9 || die "Another backup holds the lock"

run_id="$(date -u +%Y%m%dT%H%M%SZ)"
work_dir="$(mktemp -d "$BACKUP_DIR/run.XXXXXXXX")"
archive="$work_dir/backup.sql.gz"
object_key="$S3_PREFIX/$run_id-${work_dir##*/}.sql.gz"

stage="dump"

pg_dump --no-password --format=plain \
    | gzip -c > "$archive"

dump_created=1

stage="validate_local"

[[ -s "$archive" ]] || die "Backup archive is empty"
gzip -t -- "$archive"

local_bytes="$(stat -c %s -- "$archive")"
[[ "$local_bytes" =~ ^[0-9]+$ && "$local_bytes" -gt 0 ]] \
    || die "Invalid local size"

file_verified=1

stage="upload"

aws s3 cp "$archive" "s3://$S3_BUCKET/$object_key" \
    --only-show-errors \
    --no-progress \
    --cli-connect-timeout 10 \
    --cli-read-timeout 60

upload_completed=1

stage="verify_remote"

remote_bytes="$(aws s3api head-object \
    --bucket "$S3_BUCKET" \
    --key "$object_key" \
    --query ContentLength \
    --output text \
    --cli-connect-timeout 10 \
    --cli-read-timeout 60)"

[[ "$remote_bytes" == "$local_bytes" ]] \
    || die "Remote object size does not match"

remote_verified=1

[[ "$dump_created$file_verified$upload_completed$remote_verified" == "1111" ]] \
    || die "Backup completion checks did not pass"

log "Backup verified: key=$object_key bytes=$local_bytes"

stage="heartbeat"

# Suppress curl diagnostics to avoid disclosing the private URL.
# Retry delivery only, never the database dump or upload.
if http_code="$(curl \
    --silent \
    --fail \
    --output /dev/null \
    --write-out '%{http_code}' \
    --proto '=https' \
    --connect-timeout 5 \
    --max-time 10 \
    --retry 2 \
    --retry-delay 2 \
    --retry-max-time 30 \
    "$PINGCRON_URL" 2>/dev/null)"; then

    case "$http_code" in
        2??)
            heartbeat_delivered=1
            ;;
        *)
            log "WARNING: Backup verified; heartbeat returned HTTP $http_code"
            ;;
    esac
else
    log "WARNING: Backup verified; heartbeat delivery failed"
fi

# Reporting failure must not turn a completed backup into a failed backup.
stage="complete"
exit 0

What the wrapper guarantees

The heartbeat is reachable only after every required backup command and validation succeeds. An error during dumping, compression, upload, or remote verification stops execution before curl.

The stage flags make the failure location visible. For example:

exit=1 stage=upload dump_created=1 file_verified=1 upload_completed=0 remote_verified=0 heartbeat_delivered=0

The upload uses a unique object key rather than repeatedly overwriting latest.sql.gz. Configure S3 lifecycle rules to manage retention. Local temporary archives are removed when the script exits normally or through a handled signal; a forced kill or host crash can leave temporary files requiring cleanup.

The remote size comparison checks that the expected object exists with the expected length. It is not a content checksum or a restore test.

Similarly, gzip -t checks compression integrity. Its value comes from combining it with a successful pg_dump exit status, not from treating it as database validation.

If heartbeat delivery fails after a verified upload, the script logs that condition and preserves the successful backup exit status. The missing check-in remains actionable, but it should not automatically cause another database dump.

Schedule the wrapper with a runtime limit

Install this in the backup account’s user crontab:

SHELL=/bin/bash
PATH=/usr/local/bin:/usr/bin:/bin
CRON_TZ=UTC

0 2 * * * /usr/bin/timeout --kill-after=30s 40m /bin/bash /usr/local/sbin/postgres-backup.sh >> /var/log/pgbackup/backup.log 2>&1

This example starts at 02:00 UTC and limits the entire process to approximately 40 minutes, followed by a short termination allowance. Adjust that budget to the database’s measured runtime.

Use a cron implementation that supports CRON_TZ. Where it is unsupported, configure the scheduler’s actual timezone and verify it. Setting TZ inside the script does not necessarily change when cron launches it. The Cronie crontab manual documents CRON_TZ.

Retain and rotate the log file. Ensure both the log directory and backup directory are writable before scheduling. A failed log redirection can prevent the script from starting, which is another reason the absence check must be external.

4. Implementing the External Dead-Man’s Switch with PingCron

The architecture is straightforward:

Cron starts the wrapper
        |
        v
pg_dump and compression succeed
        |
        v
Local archive checks pass
        |
        v
S3 upload and object-size check pass
        |
        v
Send the PingCron success heartbeat

PingCron independently watches for that expected heartbeat. If no check-in arrives within the configured schedule and grace period, it can alert through Email, Slack, or Discord. PingCron describes this completion-monitoring model.

Create a monitor such as production-postgres-nightly-backup and configure it to match the crontab:

Copy its private success URL into the wrapper. Keep it out of source control and public logs. Anyone who obtains a success URL may be able to send misleading check-ins.

Size the grace period around completed work

The expected start time is 02:00, but the heartbeat represents completion. The grace period must accommodate the work between those two events:

Grace period >=
    scheduler delay
  + dump runtime
  + compression and validation time
  + upload and remote verification time
  + heartbeat delivery allowance
  + operating margin

For example, if a measured slow but acceptable execution takes 30 minutes, a 45-minute grace period may be reasonable. The example’s 40-minute execution limit leaves additional time before the missed-check-in threshold.

These values are illustrative. Measure your own workload, including periods of database growth and reduced upload bandwidth.

A two-minute grace period for a twenty-minute backup produces routine false alarms. A twelve-hour grace period for that same job delays useful detection.

With the example configuration, the missing check-in becomes eligible for detection after approximately 02:45 UTC. Processing and notification delivery can add delay. Do not promise that a Slack message will arrive at exactly 02:45.

An alert establishes that the expected signal was absent. It does not establish whether the cause was a failed dump, stopped scheduler, unavailable host, failed upload, or blocked heartbeat request.

5. How to Safely Test and Validate Your Alerts

Test the entire chain before relying on it. A successful manual curl proves neither that cron is configured correctly nor that the backup workflow reaches the heartbeat.

Use a separate test database, backup prefix, script copy, and PingCron monitor.

First, establish a successful baseline

Configure a short test schedule, such as every five minutes, with a grace period appropriate for a small fixture database.

Run the test script as the same account that owns the test crontab. Confirm:

1. The dump finishes without errors.
2. The new object appears in the test S3 prefix.
3. Its recorded size matches the local archive size logged by the script.
4. PingCron records the check-in.
5. A later automatic cron execution also produces a check-in.
6. An alert-destination test reaches the intended Email, Slack, or Discord destination.

The destination test verifies routing. It does not replace a missed-check-in test.

Test a failure before completion

In the test copy of the script, change the backup directory to a nonexistent path:

BACKUP_DIR="/nonexistent/pgbackup-alert-test"

Run it:

/bin/bash /usr/local/sbin/postgres-backup-test.sh
printf 'Exit status: %s\n' "$?"

The script should exit nonzero during preflight. It must not send a heartbeat.

Leave the test monitor active. Wait until its next expected check-in and grace period have passed, then confirm that the external notification arrives and identifies the correct monitor.

You can separately test a failed S3 upload using a test identity or prefix without write permission. The expected outcome is a failure at stage=upload, with no success heartbeat.

Test a job that never starts

Comment out only the test cron entry. Do not pause the PingCron monitor.

The test script now produces no output, no exception, and no notification of its own. The external monitor should still detect the missing check-in after the expected window.

This is the failure mode that log scraping for error messages cannot cover by itself.

Restore the test cron entry afterward and verify that subsequent successful check-ins appear.

Test restoration separately

Monitoring completion and validating recoverability answer different questions.

Periodically download an uploaded object and restore it into a disposable database. For this article’s plain SQL format, the restore command should stop on SQL errors:

#!/bin/bash
set -eo pipefail

# Configure PostgreSQL connection variables and authentication
# for an isolated restore environment before running this.
ARCHIVE="/path/to/downloaded-backup.sql.gz"
RESTORE_DATABASE="backup_restore_test"

gzip -t "$ARCHIVE"

gzip -dc "$ARCHIVE" \
    | psql \
        --no-password \
        --set=ON_ERROR_STOP=on \
        --dbname="$RESTORE_DATABASE"

The target database must already exist in the isolated environment, with any required roles and extensions available. Never point this validation at production.

After restoration, check the tables and application invariants that matter to recovery. Measure how long restoration takes. A backup that restores successfully but misses your recovery-time objective still needs operational work.

6. Conclusion: Monitor One Completed Backup

A scheduler cannot reliably report its own disappearance. Neither can a host that has lost power or network access.

Make backup success explicit: require a successful dump, validate the archive, complete the upload, verify the remote object, and only then send the heartbeat. Keep logs for diagnosis and restore drills for recovery assurance.

Start with one nightly backup. Create a free PingCron monitor, match its schedule and timezone, and verify both a successful scheduled run and a deliberately missing test check-in.

Related monitoring guides

For configuration and setup, see the database backup monitoring guide.

• GitHub Actions Scheduled Workflow Not Running?
• Kubernetes CronJob Not Running? Diagnose Skips
• Laravel Scheduled Job Not Running? Monitor Workers
• Self Hosted vs Hosted Cron Monitoring: Tool Comparison