Automated PostgreSQL Backups: A Production Bash & Cron Blueprint
Database backup failures are catastrophic when disaster strikes. Relying on manual database exports leads to data loss and unverified recovery points. Setting up an automated pipeline that dumps PostgreSQL tables, compresses the archive using gzip, and syncs the output to remote object storage guarantees data durability.
In this guide, we will build an automated shell script for PostgreSQL backups and schedule it via system cron jobs.
1. Prerequisites & AWS CLI Setup
First, ensure the AWS CLI tool and PostgreSQL client utilities are installed on your Linux host environment:
sudo apt update && sudo apt install -y postgresql-client awscli gzip
Configure credentials for an IAM user with Write permissions to your S3 bucket:
aws configure
2. Writing the Production Backup Script
Create a backup script at /usr/local/bin/pg_backup.sh:
sudo nano /usr/local/bin/pg_backup.sh
Paste the following shell automation script:
#!/usr/bin/env bash
set -euo pipefail
# Configuration parameters
DB_NAME="production_db"
DB_USER="postgres"
S3_BUCKET="s3://my-company-postgres-backups"
BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y-%m-%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz"
# Create local destination directory if not existing
mkdir -p "${BACKUP_DIR}"
echo "==> Starting database dump for: ${DB_NAME} at ${DATE}..."
# Export database dump directly into gzip stream
pg_dump -U "${DB_USER}" -h localhost -d "${DB_NAME}" -F p | gzip -9 > "${BACKUP_FILE}"
echo "==> Backup complete. Size: $(du -h "${BACKUP_FILE}" | cut -f1)"
echo "==> Uploading backup to S3..."
aws s3 cp "${BACKUP_FILE}" "${S3_BUCKET}/$(date +%Y/%m)/${DB_NAME}_${DATE}.sql.gz"
echo "==> Purging local backups older than 7 days..."
find "${BACKUP_DIR}" -type f -name "*.sql.gz" -mtime +7 -delete
echo "==> Backup process finalized successfully!"
3. Setting Executable Permissions
Make the backup script executable and secure file access permissions so non-root users cannot read operational scripts:
sudo chmod +x /usr/local/bin/pg_backup.sh
sudo chmod 700 /usr/local/bin/pg_backup.sh
4. Automating Execution with Cron
Open the system crontab editor for the root user to run the script every midnight at 00:00 AM:
sudo crontab -e
Add the following line at the bottom of the crontab file:
0 0 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1
Summary
Your PostgreSQL host now executes zero-touch automated daily backups compressed with level-9 gzip, syncs structured date archives directly to S3 cloud storage, and automatically cleans up local host disk storage after 7 days.
Comments