Automate Database Backups on Linux

By Khimananda Oli | Last reviewed: August 2026

Data loss is rarely a dramatic catastrophe; it is usually the quiet result of a missed manual step or a silent cron failure. To reliably automate database backups on Linux, you must move beyond ad-hoc commands and implement a deterministic pipeline that handles scheduling, credential security, compression, and verification without human intervention. This guide provides the exact architecture and scripts I use in production environments to ensure recoverability for MySQL, PostgreSQL, and MongoDB workloads.

How do you architect a reliable automated database backup workflow on Linux?

A common mistake when teams first attempt to automate database backups on Linux is treating the dump command as the entire solution. In practice, the dump is only one stage in a lifecycle that must include pre-checks, secure credential handling, atomic file writes, retention management, and offsite replication. If any of these stages fail silently, you end up with a green checkmark in your monitoring dashboard and a corrupt archive in storage.

The architecture above separates concerns cleanly. The scheduler (cron or systemd timer) only triggers execution; it does not contain logic. The backup script owns the business logic: fetching credentials securely, running the vendor-specific dump tool, verifying the exit code, compressing with a consistent naming convention, and writing to a local staging directory. Only after local success does the script invoke the replication layer. This "local-first" pattern is critical because network transfers to object storage can be slow or flaky; if you stream directly to S3 and the connection drops at 90%, you have nothing. For deeper context on securing the underlying host, see my guide on Ubuntu server security best practices.

How do you write secure backup scripts for PostgreSQL, MySQL, and MongoDB?

Each database engine has distinct dump semantics, and getting them wrong produces backups that restore successfully but contain stale or partial data. Below are production-grade patterns for the three most common engines on Linux.

PostgreSQL: pg_dump with custom format

For PostgreSQL, always prefer pg_dump with the custom format (-Fc) over plain SQL. Custom format supports parallel restore, selective table recovery, and is significantly faster for large databases. Never pass passwords on the command line where they appear in /proc or shell history.

<!-- /opt/scripts/pg_backup.sh -->
#!/usr/bin/env bash
set -euo pipefail

# Load credentials from restricted env file (chmod 600)
source /etc/db-backup/pg_credentials.env

BACKUP_DIR="/var/backups/postgresql"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
FILENAME="${PGDATABASE}_${TIMESTAMP}.dump"

mkdir -p "${BACKUP_DIR}"

pg_dump -Fc -Z 6 \
  -h "${PGHOST}" -p "${PGPORT}" \
  -U "${PGUSER}" -d "${PGDATABASE}" \
  -f "${BACKUP_DIR}/${FILENAME}"

# Verify the dump is valid before considering success
pg_restore --list "${BACKUP_DIR}/${FILENAME}" > /dev/null 2>&1

echo "$(date -Iseconds) SUCCESS ${FILENAME}" >> /var/log/db-backup.log

Store credentials in /etc/db-backup/pg_credentials.env with permissions 600 owned by the backup user. For more advanced PostgreSQL administration including replication-aware backups, refer to PostgreSQL administration essentials.

MySQL/MariaDB: mysqldump with single-transaction

For InnoDB workloads, --single-transaction gives you a consistent snapshot without locking tables. Combine it with --routines --triggers --events to capture the full logical schema. Use --defaults-extra-file instead of --password to avoid credential exposure.

<!-- /opt/scripts/mysql_backup.sh -->
#!/usr/bin/env bash
set -euo pipefail

BACKUP_DIR="/var/backups/mysql"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
FILENAME="all_databases_${TIMESTAMP}.sql.gz"
MY_CNF="/etc/db-backup/mysql.cnf"  # chmod 600, contains [mysqldump] section

mkdir -p "${BACKUP_DIR}"

mysqldump --defaults-extra-file="${MY_CNF}" \
  --all-databases --single-transaction \
  --routines --triggers --events \
  --quick --lock-tables=false \
  | gzip -6 > "${BACKUP_DIR}/${FILENAME}"

# Basic sanity check: file exists and is non-empty
[ -s "${BACKUP_DIR}/${FILENAME}" ] || { echo "FAIL: empty backup"; exit 1; }

The mysql.cnf file should contain a [mysqldump] section with user and password keys. For performance tuning considerations that affect backup windows, see MySQL performance tuning guide.

MongoDB: mongodump with archive mode

Modern MongoDB deployments should use mongodump --archive which writes a single streamable file rather than a directory tree. This simplifies compression, encryption, and offsite transfer. Always include --oplog for replica sets to enable point-in-time recovery.

<!-- /opt/scripts/mongo_backup.sh -->
#!/usr/bin/env bash
set -euo pipefail

source /etc/db-backup/mongo_credentials.env

BACKUP_DIR="/var/backups/mongodb"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
FILENAME="mongo_${TIMESTAMP}.gz"

mkdir -p "${BACKUP_DIR}"

mongodump --uri="${MONGO_URI}" \
  --oplog --gzip \
  --archive="${BACKUP_DIR}/${FILENAME}"

[ -s "${BACKUP_DIR}/${FILENAME}" ] || { echo "FAIL: empty archive"; exit 1; }

How do you schedule and monitor database backups with cron or systemd timers?

Cron is the default choice for most Linux administrators, but systemd timers offer superior observability, randomized delays, and dependency management. Here is how to decide and configure each.

Cron: simple and universal

Place backup jobs in /etc/cron.d/ rather than editing crontab -e directly. This keeps backups version-controllable and auditable. Always redirect both stdout and stderr to a log file.

# /etc/cron.d/db-backups
SHELL=/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# Daily PostgreSQL backup at 02:15 AM
15 2 * * * backup-user /opt/scripts/pg_backup.sh >> /var/log/db-backup.log 2>&1

# Daily MySQL backup at 03:00 AM
0 3 * * * backup-user /opt/scripts/mysql_backup.sh >> /var/log/db-backup.log 2>&1

# Every 6 hours MongoDB backup
0 */6 * * * backup-user /opt/scripts/mongo_backup.sh >> /var/log/db-backup.log 2>&1

Systemd timers: observable and resilient

Systemd timers integrate with journald, support RandomizedDelaySec to prevent thundering herd on shared storage, and can declare dependencies on network targets for offsite replication. Create a service unit and a corresponding timer unit:

# /etc/systemd/system/db-backup-pg.service
[Unit]
Description=PostgreSQL Automated Backup
After=postgresql.service

[Service]
Type=oneshot
User=backup-user
EnvironmentFile=/etc/db-backup/pg_credentials.env
ExecStart=/opt/scripts/pg_backup.sh
StandardOutput=journal
StandardError=journal

# /etc/systemd/system/db-backup-pg.timer
[Unit]
Description=Run PostgreSQL backup daily at 02:15

[Timer]
OnCalendar=*-*-* 02:15:00
RandomizedDelaySec=300
Persistent=true

[Install]
WantedBy=timers.target

Enable with systemctl enable --now db-backup-pg.timer. Monitor with systemctl list-timers and journalctl -u db-backup-pg.service. For broader scheduling patterns on Ubuntu, see cron jobs explained on Linux.

How do you replicate backups offsite and manage retention policies?

Local backups protect against application bugs and accidental deletes. Offsite replicas protect against disk failure, ransomware, and data center outages. Both are required for any system that matters.

Replication to S3-compatible storage

Use rclone or the AWS CLI for reliable, resumable uploads. rclone supports over 40 backends including S3, Cloudflare R2, Backblaze B2, and GCS with a unified interface. Configure it once with rclone config, then add to your backup script:

# Append to backup script after successful local dump
REMOTE="s3-offsite:db-backups/${HOSTNAME}/postgresql"
rclone copy "${BACKUP_DIR}/${FILENAME}" "${REMOTE}" \
  --s3-storage-class=STANDARD_IA \
  --retries 3 --low-level-retries 10 \
  --log-file=/var/log/db-backup-rclone.log \
  --log-level INFO

# Delete local copy older than 7 days
find "${BACKUP_DIR}" -name "*.dump" -mtime +7 -delete

# Prune remote copies older than 30 days
rclone delete "${REMOTE}" --min-age 30d

In Nepal, fintech and government-adjacent projects often face data residency constraints under NRB directives. A practical approach is keeping Tier 1 and Tier 2 within a Kathmandu-based VPS provider or local data center, then replicating Tier 3 to an international region only after explicit compliance approval. Budget-conscious startups can use Cloudflare R2 for Tier 2 since it charges zero egress fees, which matters when you regularly test restores. For detailed offsite strategies, read offsite backups to S3 or R2.

How do you verify that automated backups are actually restorable?

An untested backup is a hypothesis, not an asset. Every automated backup pipeline must include a verification stage. At minimum, validate the archive integrity programmatically. Ideally, perform periodic test restores to an isolated instance.

• Checksum validation: Generate SHA-256 hashes at creation time and store them alongside the backup. Re-verify before every restore attempt.
• Archive listing: Run pg_restore --list, gunzip -t, or mongorestore --dryRun to confirm the file is structurally valid without performing a full restore.
• Scheduled test restores: Weekly, spin up a temporary container or VM, restore the latest backup, and run application-level smoke tests. Destroy the environment afterward.
• Alerting on staleness: Configure Prometheus node_exporter or a simple cron sentinel to alert if no successful backup log entry appears within the expected window. See alerting with Prometheus Alertmanager for implementation patterns.
• Size anomaly detection: Track backup file sizes over time. A sudden 80% drop usually means the dump failed silently or the schema changed unexpectedly.

I have seen too many teams discover their backup was empty during an actual incident. The cost of a weekly test restore is trivial compared to the cost of discovering corruption at 3 AM during a production outage.

Implementing Your Automated Database Backup Pipeline

Start with the script templates above, adapt them to your specific engine and topology, and deploy them today. Do not wait for a perfect solution; a verified daily backup with basic offsite replication beats a theoretical ideal that exists only in documentation. Once the foundation is solid, layer on encryption, incremental backups, and compliance-aligned retention. If your team needs help designing a backup strategy that survives audits and actual disasters, reach out to discuss your infrastructure.