Database Backup Cron Job

Back up your database every night at 2am and ship it to S3, Backblaze, or any object store.

Why 2am?

2am local time sits in the quietest window for most applications — low traffic, fewer active transactions, less risk of locking issues during the dump. It also gives you a full day of lead time to notice a failed backup before the next one runs.

PostgreSQL backup script

#!/bin/bash
DATE=$(date +%Y-%m-%d)
BACKUP_FILE="backup-$DATE.sql.gz"

pg_dump "$DATABASE_URL" | gzip > /tmp/$BACKUP_FILE

# Upload to S3
aws s3 cp /tmp/$BACKUP_FILE s3://your-bucket/backups/$BACKUP_FILE

# Keep only last 30 days locally
find /tmp -name "backup-*.sql.gz" -mtime +30 -delete

echo "Backup complete: $BACKUP_FILE"

MySQL / MariaDB backup script

#!/bin/bash
DATE=$(date +%Y-%m-%d)
mysqldump -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" \
  | gzip > /tmp/backup-$DATE.sql.gz

aws s3 cp /tmp/backup-$DATE.sql.gz \
  s3://your-bucket/backups/backup-$DATE.sql.gz

Platform snippets

0 2 * * *    /usr/local/bin/backup.sh >> /var/log/backup.log 2>&1

on:
  schedule:
    - cron: '0 2 * * *'

jobs:
  backup:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Run backup
        run: bash scripts/backup.sh
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
          AWS_ACCESS_KEY_ID: ${{ secrets.AWS_ACCESS_KEY_ID }}
          AWS_SECRET_ACCESS_KEY: ${{ secrets.AWS_SECRET_ACCESS_KEY }}

cron(0 2 * * ? *)

apiVersion: batch/v1
kind: CronJob
metadata:
  name: db-backup
spec:
  schedule: "0 2 * * *"
  jobTemplate:
    spec:
      template:
        spec:
          containers:
          - name: backup
            image: your-backup-image:latest
            envFrom:
            - secretRef:
                name: db-credentials
          restartPolicy: OnFailure

Retention strategy

A good backup retention policy: keep daily backups for 30 days, weekly backups for 3 months, monthly backups for 1 year. Use S3 Lifecycle rules or Backblaze B2 lifecycle policies to automate deletion — don't rely on the cron script to manage retention.

More guides