Scheduling database backups with cron

Nightly pg_dump/mysqldump with locking, retention, and a way to notice when it stops working.

The problem

The naive version is one crontab line that calls pg_dump or mysqldump and writes to a dated file. It works the day you write it. The trouble is that cron doesn't tell you when a job fails. If the script starts erroring (disk full, credentials rotated, schema lock timeout), cron carries on as if nothing happened, and the only trace is a line in a mail spool nobody reads. Meanwhile the dump files come out empty or truncated for weeks.

The second most common failure is overlap. A slow night runs into the next scheduled run, and cron launches a second instance of the job while the first is still going.

Recommended schedule

0 2 * * *

Once daily at 02:00, during low-traffic hours. If the dump takes long enough to risk overlapping the next day's run, either widen the window or move to hourly WAL/binlog shipping instead of full dumps.

Check any expression at the cron parser, or browse more at the cron guide.

Example crontab entry

# norc: db-backup Nightly Postgres dump
0 2 * * * /usr/bin/flock -n /var/lock/db-backup.lock /opt/scripts/backup.sh >> /var/log/db-backup.log 2>&1

#!/usr/bin/env bash
set -euo pipefail
DEST="/backups/mydb-$(date +\%F).dump"
pg_dump -Fc mydb > "$DEST"
# fail loudly instead of leaving a 0-byte file behind
test -s "$DEST" || { echo "backup empty, aborting"; rm -f "$DEST"; exit 1; }
find /backups -name "mydb-*.dump" -mtime +14 -delete

Failure modes to watch for

Overlapping runs: an unlocked job that occasionally runs long gets launched twice, doubling load or corrupting an in-progress dump. Use flock (shown above) or a PID file.
Silent truncation: disk fills mid-dump and pg_dump exits non-zero, but if you're not checking exit codes or file size, the cron log entry looks identical to a healthy run.
Credential rot: a rotated database password breaks the job with no user-facing symptom until someone needs a restore and discovers three months of empty files.
Retention gaps: a naive find -delete retention line with the wrong -mtime sign deletes everything, including the backup you just made.

How norc helps

norc checks every job against its schedule, so a backup that never ran is flagged as a missed run and a failed one shows in the run history. If the machine was off at 2am, norc catches the run up when it reconnects, up to a cap. You can fix the script or the schedule from your desktop, terminal or phone without SSH.

Get early access