How to Set Up Automated Postgresql Backups with Cron Jobs and Retention Policies on Linux

ByAkensai

How to set up automated PostgreSQL backups with cron jobs and retention policies on Linux is essential for maintaining database integrity and preventing data loss. Database backups form the backbone of any disaster recovery strategy, especially for production PostgreSQL environments. Manual backups are prone to human error and often forgotten during busy periods. Automated backup systems ensure consistent, reliable database protection without requiring daily intervention.

This comprehensive tutorial covers creating automated PostgreSQL backup scripts, configuring cron jobs for scheduled execution, and implementing retention policies to manage storage space efficiently. You’ll learn to create custom backup scripts that compress and timestamp your database dumps, set up automated cleanup procedures, and monitor backup success through logging mechanisms.

By the end of this guide, you’ll have a fully automated PostgreSQL backup system running on your Linux server. This system will create regular database backups, automatically remove old backup files based on your retention policy, and provide detailed logs for monitoring backup operations. Whether you’re managing a single database or multiple PostgreSQL instances, these techniques will help you maintain reliable database backups.

Prerequisites and Requirements for Automated PostgreSQL Backups with Cron Jobs

Before implementing how to set up automated PostgreSQL backups with cron jobs and retention policies on Linux, ensure your system meets these requirements. You’ll need root or sudo access to your Linux server, along with a functioning PostgreSQL installation. Your PostgreSQL service should be running and accessible with appropriate user credentials.

Your Linux system should have sufficient disk space for storing backup files. Calculate your database size and multiply by your desired retention period to estimate storage requirements. For example, a 1GB database with 30-day retention needs approximately 30GB of backup storage space.

You’ll also need basic knowledge of Linux command-line operations, cron job syntax, and PostgreSQL administration. Familiarity with bash scripting will help you customize the backup scripts for your specific needs. The official PostgreSQL backup documentation provides additional context for backup strategies.

Estimated completion time for this tutorial is 45-60 minutes, including testing and verification steps. Ensure you have a test database available for initial testing before implementing these procedures on production systems.

Step-by-Step Guide to Setting Up Automated PostgreSQL Backups

Another fascinating historical case is: How to Configure Opnsense Firewall Rules for Network Segmentation

Let’s create a comprehensive backup system that handles multiple aspects of database protection. This process involves creating backup scripts, configuring cron jobs, and implementing automated cleanup procedures.

Step 1: Create the backup directory structure
First, establish a organized directory structure for storing your PostgreSQL backups. This organization makes backup management easier and supports automated cleanup procedures.

sudo mkdir -p /var/backups/postgresql/daily
sudo mkdir -p /var/backups/postgresql/logs
sudo chown postgres:postgres /var/backups/postgresql -R
sudo chmod 755 /var/backups/postgresql -R

Step 2: Create the main backup script
Create a comprehensive backup script that handles database dumping, compression, and logging. This script will serve as the foundation for your automated backup system.

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

Add the following script 

#!/bin/bash

# PostgreSQL Automated Backup Script
# Configuration variables
BACKUP_DIR="/var/backups/postgresql/daily"
LOG_FILE="/var/backups/postgresql/logs/backup.log"
DB_USER="postgres"
DB_HOST="localhost"
DB_PORT="5432"
RETENTION_DAYS=30

# Create timestamp for backup files
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")

# Function to log messages
log_message() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" >> "$LOG_FILE"
}

# Function to backup a single database
backup_database() {
    local db_name=$1
    local backup_file="${BACKUP_DIR}/${db_name}_${TIMESTAMP}.sql.gz"
    
    log_message "Starting backup for database: $db_name"
    
    # Create database dump and compress
    if pg_dump -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$db_name" | gzip > "$backup_file"; then
        log_message "Backup completed successfully: $backup_file"
        # Set appropriate permissions
        chmod 640 "$backup_file"
        return 0
    else
        log_message "ERROR: Backup failed for database: $db_name"
        return 1
    fi
}

# Function to clean old backups based on retention policy
cleanup_old_backups() {
    log_message "Starting cleanup of backups older than $RETENTION_DAYS days"
    
    find "$BACKUP_DIR" -name ".sql.gz" -type f -mtime +$RETENTION_DAYS -delete
    
    if [ $? -eq 0 ]; then
        log_message "Cleanup completed successfully"
    else
        log_message "ERROR: Cleanup process encountered errors"
    fi
}

# Main backup process
main() {
    log_message "=== PostgreSQL Backup Process Started ==="
    
    # Get list of all databases (excluding system databases)
    DATABASES=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname NOT IN ('postgres', 'template0', 'template1');" | grep -v '^$')
    
    backup_count=0
    failed_count=0
    
    # Backup each database
    for db in $DATABASES; do
        db_clean=$(echo "$db" | xargs)  # Remove whitespace
        if backup_database "$db_clean"; then
            ((backup_count++))
        else
            ((failed_count++))
        fi
    done
    
    # Clean up old backups
    cleanup_old_backups
    
    log_message "Backup process completed. Success: $backup_count, Failed: $failed_count"
    log_message "=== PostgreSQL Backup Process Finished ==="
}

# Execute main function
main

Step 3: Make the backup script executable
Set appropriate permissions for the backup script to ensure it can be executed by the system and accessed by the PostgreSQL user.

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

Step 4: Configure PostgreSQL authentication
Set up password-less authentication for the backup script by configuring the PostgreSQL password file. This prevents interactive password prompts during automated backups.

sudo -u postgres nano ~/.pgpass

Add your database connection details:

localhost:5432::postgres:your_postgres_password

Secure the password file:

sudo -u postgres chmod 600 ~/.pgpass

Step 5: Test the backup script manually
Before automating the backup process, test the script manually to ensure it works correctly with your PostgreSQL setup.

sudo -u postgres /usr/local/bin/postgresql-backup.sh

Verify the backup files were created:

ls -la /var/backups/postgresql/daily/
cat /var/backups/postgresql/logs/backup.log

Step 6: Create the cron job for automated backups
Configure a cron job to run the backup script automatically. This example schedules daily backups at 2:00 AM, but you can adjust the timing based on your requirements.

sudo -u postgres crontab -e

Add the following line for daily backups at 2:00 AM:

0 2    /usr/local/bin/postgresql-backup.sh >/dev/null 2>&1

For different scheduling options, consider these alternatives:
– Weekly backups (Sundays at 3:00 AM): 0 3 0 /usr/local/bin/postgresql-backup.sh >/dev/null 2>&1
– Twice daily backups (2:00 AM and 2:00 PM): 0 2,14 /usr/local/bin/postgresql-backup.sh >/dev/null 2>&1

Implementing Retention Policies and Monitoring for PostgreSQL Backup Systems

Effective retention policies prevent backup directories from consuming excessive disk space while maintaining adequate recovery options. The backup script includes automatic cleanup functionality, but you may need to customize retention settings based on your specific requirements.

Step 7: Customize retention policies
Modify the retention settings in your backup script to match your organization’s data retention requirements. Edit the script to adjust the RETENTION_DAYS variable:

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

Consider implementing tiered retention policies for different backup frequencies:

# Tiered retention example
DAILY_RETENTION=7    # Keep daily backups for 1 week
WEEKLY_RETENTION=30  # Keep weekly backups for 1 month
MONTHLY_RETENTION=365 # Keep monthly backups for 1 year

Step 8: Create a backup monitoring script
Develop a monitoring script that checks backup success and sends notifications when issues occur. This proactive approach helps identify backup failures quickly.

sudo nano /usr/local/bin/backup-monitor.sh
#!/bin/bash


LOG_FILE="/var/backups/postgresql/logs/backup.log"

EMAIL_RECIPIENT="[email protected]"

BACKUP_DIR="/var/backups/postgresql/daily"


# Check if backup ran today

TODAY=$(date +"%Y-%m-%d")

if grep -q "$TODAY.Backup Process Finished" "$LOG_FILE"; then

    echo "PostgreSQL backup completed successfully for $TODAY"


    # Check for any errors in today

Similar Posts