Automated Linux Database Backups: A Guide for PostgreSQL and MySQL

How to Set Up Automated Database Backups on Linux (PostgreSQL and MySQL)

Production databases without automated backups are vulnerable to total data loss from a single bad deployment. This guide provides shell scripts for PostgreSQL and MySQL that implement retention policies and verification checks.

Why This Matters

While ideal models assume high availability and replication, technical reality often involves human error or server failure that requires point-in-time recovery. Without automated, tested backups stored off-site, local failures or corrupted deploys can lead to permanent data loss, making automated verification and off-site synchronization essential for production environments.

Key Insights

• PostgreSQL backups use pg_dumpall for global clusters or pg_dump with the -Fc flag for specific databases to ensure flexible restoration options.
• Automated retention is managed via the find command with -mtime +7 to automatically delete files older than one week.
• Security is maintained for PostgreSQL using a .pgpass file with 600 permissions, allowing for passwordless cron job execution.
• MySQL consistency is ensured during backups by using the —single-transaction flag with mysqldump to avoid locking tables.
• Backup integrity is verified using the [ ! -s ] shell test to check for empty files, triggering email alerts if failures occur.

Working Examples

Automated PostgreSQL backup script with compression and retention logic.

#!/bin/bash
set -e
BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
KEEP_DAYS=7
DB_NAME="${1:-all}"
mkdir -p "$BACKUP_DIR"
if [ "$DB_NAME" = "all" ]; then
  pg_dumpall -U postgres | gzip > "$BACKUP_DIR/all_${DATE}.sql.gz"
else
  pg_dump -U postgres -Fc "$DB_NAME" > "$BACKUP_DIR/${DB_NAME}_${DATE}.dump"
fi
find "$BACKUP_DIR" -type f -mtime +$KEEP_DAYS -delete

Creation of a dedicated MySQL backup user with minimal required privileges.

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

Cron job configuration for daily database-specific and weekly full cluster backups.

0 2 * * * /usr/local/bin/pg-backup.sh mydb >> /var/log/pg-backup.log 2>&1
0 1 * * 0 /usr/local/bin/pg-backup.sh all >> /var/log/pg-backup.log 2>&1

Practical Applications

• Use case: Production Linux environments requiring daily 2 AM local backups synced to AWS S3 using ‘aws s3 sync’ for disaster recovery.
• Pitfall: Storing backups only on the local disk; if the server’s hardware fails, both the live data and the backups are lost simultaneously.
• Use case: Testing database integrity by performing monthly trial restores using ‘pg_restore’ to ensure backup files are not corrupted.
• Pitfall: Running cron jobs without logging output; this prevents debugging when silent failures occur due to permission issues or disk space exhaustion.

References:

• https://dev.to/yash_step2dev/how-to-set-up-automated-database-backups-on-linux-postgresql-and-mysql-47ak