How to Set Up Automated Postgresql Backups with Cron Jobs and Email Notifications on Ubuntu

ByAkensai

Learning how to set up automated PostgreSQL backups with cron jobs and email notifications on Ubuntu is essential for maintaining database integrity and preventing data loss. This comprehensive guide walks you through creating a reliable backup system that runs automatically and keeps you informed about backup status.

Database backups are critical for any production environment. Manual backups are prone to human error and often forgotten during busy periods. Automated systems ensure your PostgreSQL databases are backed up consistently without requiring daily intervention. Email notifications provide immediate feedback about backup success or failure, allowing you to address issues quickly.

This tutorial covers creating backup scripts, configuring cron jobs for automation, setting up email notifications, and implementing proper backup rotation. You’ll learn to build a professional-grade backup system that protects your data while minimizing administrative overhead. The system will automatically compress backups, manage storage space, and alert you to any problems.

Prerequisites and Requirements for Setting Up Automated PostgreSQL Backups with Cron Jobs

Before implementing how to set up automated PostgreSQL backups with cron jobs and email notifications on Ubuntu, ensure you have the necessary components and access levels.

You need a Ubuntu server with PostgreSQL installed and running. Root or sudo access is required for system configuration. The PostgreSQL service should be operational with databases you want to backup. Basic knowledge of Linux command line, cron jobs, and PostgreSQL administration is assumed.

Install the required packages for email functionality:

sudo apt update
sudo apt install mailutils ssmtp -y

Verify PostgreSQL is running and accessible:

sudo systemctl status postgresql
sudo -u postgres psql -c "l"

Create a dedicated backup directory with appropriate permissions:

sudo mkdir -p /var/backups/postgresql
sudo chown postgres:postgres /var/backups/postgresql
sudo chmod 750 /var/backups/postgresql

Estimate approximately 2-3 hours to complete this setup, including testing and verification. The time varies based on your database sizes and email configuration complexity.

Step-by-Step Guide to Configure Automated PostgreSQL Backups with Cron Jobs

For more strange history, see: How to Create Custom Wordpress Gutenberg Blocks with the Block Editor Api

Step 1: Create the backup script

Create a comprehensive backup script that handles multiple databases and includes error checking. This script will be the foundation of your automated system.

sudo nano /usr/local/bin/postgresql-backup.sh

Add the following script 

#!/bin/bash

# PostgreSQL Backup Script with Email Notifications
# Configuration
BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7
EMAIL="[email protected]"
HOSTNAME=$(hostname)

# Create log file
LOG_FILE="/var/log/postgresql-backup.log"
exec 1> >(tee -a "$LOG_FILE")
exec 2>&1

echo "Starting PostgreSQL backup at $(date)"

# Function to send email notifications
send_notification() {
    local subject="$1"
    local message="$2"
    echo "$message" | mail -s "$subject" "$EMAIL"
}

# Get list of databases
DATABASES=$(sudo -u postgres psql -t -c "SELECT datname FROM pg_database WHERE NOT datistemplate AND datname != 'postgres';" | grep -v "^$")

if [ -z "$DATABASES" ]; then
    echo "No databases found to backup"
    send_notification "PostgreSQL Backup Warning - $HOSTNAME" "No databases found to backup on $HOSTNAME"
    exit 1
fi

# Backup each database
SUCCESS_COUNT=0
TOTAL_COUNT=0

for DB in $DATABASES; do
    TOTAL_COUNT=$((TOTAL_COUNT + 1))
    BACKUP_FILE="$BACKUP_DIR/${DB}_${DATE}.sql.gz"
    
    echo "Backing up database: $DB"
    
    if sudo -u postgres pg_dump "$DB" | gzip > "$BACKUP_FILE"; then
        echo "Successfully backed up $DB"
        SUCCESS_COUNT=$((SUCCESS_COUNT + 1))
        
        # Set appropriate permissions
        chmod 640 "$BACKUP_FILE"
        chown postgres:postgres "$BACKUP_FILE"
    else
        echo "Failed to backup $DB"
        send_notification "PostgreSQL Backup Failed - $HOSTNAME" "Failed to backup database $DB on $HOSTNAME at $(date)"
    fi
done

# Clean up old backups
echo "Cleaning up backups older than $RETENTION_DAYS days"
find "$BACKUP_DIR" -name ".sql.gz" -mtime +$RETENTION_DAYS -delete

# Calculate backup sizes
TOTAL_SIZE=$(du -sh "$BACKUP_DIR" | cut -f1)

echo "Backup completed at $(date)"
echo "Backed up $SUCCESS_COUNT out of $TOTAL_COUNT databases"
echo "Total backup directory size: $TOTAL_SIZE"

# Send success notification
if [ $SUCCESS_COUNT -eq $TOTAL_COUNT ]; then
    send_notification "PostgreSQL Backup Success - $HOSTNAME" "Successfully backed up all $TOTAL_COUNT databases on $HOSTNAME. Total size: $TOTAL_SIZE"
else
    send_notification "PostgreSQL Backup Partial Success - $HOSTNAME" "Backed up $SUCCESS_COUNT out of $TOTAL_COUNT databases on $HOSTNAME. Check logs for details."
fi

Step 2: Configure email settings

Set up SSMTP for sending email notifications. Edit the SSMTP configuration file:

sudo nano /etc/ssmtp/ssmtp.conf

Configure SSMTP with your email provider settings:

[email protected]
mailhub=smtp.gmail.com:587
rewriteDomain=yourdomain.com
hostname=your-server-hostname
UseSTARTTLS=YES
[email protected]
AuthPass=your-app-password
FromLineOverride=YES

For Gmail, you’ll need to generate an app-specific password. Visit the Google App Passwords documentation for detailed instructions.

Step 3: Set script permissions and test

Make the backup script executable and test it manually before automation:

sudo chmod +x /usr/local/bin/postgresql-backup.sh
sudo /usr/local/bin/postgresql-backup.sh

Verify the backup files were created and check the log output:

ls -la /var/backups/postgresql/
tail -f /var/log/postgresql-backup.log

Step 4: Configure the cron job

Create a cron job to run the backup script automatically. Edit the postgres user’s crontab:

sudo crontab -u postgres -e

Add the following line to run backups daily at 2:00 AM:

0 2    /usr/local/bin/postgresql-backup.sh

For different schedules, modify the cron expression:
– Weekly: `0 2 0` (Sundays at 2 AM)
– Twice daily: `0 2,14 ` (2 AM and 2 PM)

Step 5: Set up log rotation

Configure log rotation to prevent backup logs from consuming excessive disk space:

sudo nano /etc/logrotate.d/postgresql-backup

Add the following configuration:

/var/log/postgresql-backup.log {
    weekly
    rotate 4
    compress
    delaycompress
    missingok
    notifempty
    create 644 root root
}

Troubleshooting Common Issues with Automated PostgreSQL Backup Setup

Several issues may arise when implementing how to set up automated PostgreSQL backups with cron jobs and email notifications on Ubuntu. Understanding these problems helps ensure smooth operation.

Permission errors are common when the backup script can’t access PostgreSQL or write to backup directories. Ensure the postgres user has appropriate permissions:

sudo chown -R postgres:postgres /var/backups/postgresql
sudo chmod 750 /var/backups/postgresql

Email delivery failures often occur due to incorrect SSMTP configuration. Test email functionality separately:

echo "Test message" | mail -s "Test Subject" [email protected]

Check SSMTP logs for detailed error information:

tail -f /var/log/mail.log

Cron job execution problems may result from environment variables not being available. Add necessary PATH and environment variables to your script:

export PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
export PGPASSWORD="your_password_if_needed"

Large database backups may timeout or consume excessive resources. Consider using pg_dump options for better performance:

sudo -u postgres pg_dump -Fc "$DB" > "$BACKUP_FILE"

The `-Fc` flag creates compressed custom format backups that are typically smaller and faster to create than gzipped SQL dumps.

Disk space issues can cause backup failures. Monitor available space and adjust retention policies accordingly. The PostgreSQL backup documentation provides additional optimization strategies.

Monitoring and Maintaining Your Backup System

Regular monitoring ensures your automated backup system continues functioning reliably. Implement additional checks and maintenance procedures to catch issues early.

Create a backup verification script that tests backup integrity:

sudo nano /usr/local/bin/verify-backups.sh

Add verification logic:

#!/bin/bash


BACKUP_DIR="/var/backups/postgresql"

Similar Posts