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 line in crontab calling pg_dump or mysqldump, redirecting to a dated file. It works the day you write it, and that's exactly the problem: cron doesn't tell you when a job fails, so a backup script that starts erroring — disk full, credentials rotated, schema lock timeout — keeps "succeeding" from cron's point of view (exit code non-zero just means a silent line in a mail spool nobody reads) while producing empty or truncated dump files for weeks.

The second-most-common failure isn't the backup job at all — it's a second instance of the same job starting before the first one finished, because a slow night overlapped the next scheduled run and cron happily launched both.

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 surfaces run history and exit codes across every machine running this job, so a backup that starts failing shows up as a failure the next morning instead of at restore time.