How to Set Up Automated Postgresql Backups with Cron and Pg_dump

ByAkensai

Learning how to set up automated PostgreSQL backups with cron and pg_dump is essential for maintaining data integrity and disaster recovery. This comprehensive tutorial will guide you through creating reliable, scheduled database backups that run automatically without manual intervention.

PostgreSQL databases contain critical business data that requires regular backup protection. Manual backups are prone to human error and inconsistent scheduling. Automated backups ensure your data stays protected even when you’re not actively monitoring your systems.

This tutorial covers creating backup scripts, configuring cron jobs, implementing rotation policies, and monitoring backup success. You’ll learn to build a complete automated backup solution that handles multiple databases, manages disk space efficiently, and provides reliable recovery options for your PostgreSQL installations.

By the end of this guide, you’ll have a production-ready backup system running automatically on your server. The techniques shown here work on Ubuntu, CentOS, and other Linux distributions commonly used for PostgreSQL hosting.

Prerequisites and Requirements for Automated PostgreSQL Backups

Before implementing how to set up automated PostgreSQL backups with cron and pg_dump, ensure your system meets these requirements:

You need root or sudo access on your Linux server. PostgreSQL must be installed and running with at least one database to backup. The pg_dump utility comes bundled with PostgreSQL installations, so no additional software installation is required.

Your system should have sufficient disk space for backup storage. Calculate approximately 1.5 times your largest database size for compressed backups. Consider using a separate partition or external storage for backup files to prevent filling your root filesystem.

Basic Linux command-line knowledge is assumed, including file editing with nano or vim. You should understand cron syntax and file permissions. Familiarity with PostgreSQL user management and authentication methods will be helpful.

Estimated completion time is 30-45 minutes, including testing and verification steps. Have your PostgreSQL database names, usernames, and passwords ready before starting.

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

For more strange history, see: How to Containerize a Node.js Web Application with Docker

Step 1: Create a dedicated backup user in PostgreSQL

First, create a PostgreSQL user specifically for backups. This follows security best practices by limiting privileges to only what’s needed for backup operations.

sudo -u postgres psql
CREATE USER backupuser WITH PASSWORD 'your_secure_password';
GRANT CONNECT ON DATABASE your_database_name TO backupuser;
GRANT USAGE ON SCHEMA public TO backupuser;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backupuser;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backupuser;
q

Replace ‘your_secure_password’ with a strong password and ‘your_database_name’ with your actual database name. This user can read all tables but cannot modify data, making it perfect for backup operations.

Step 2: Configure PostgreSQL authentication

Create a .pgpass file to store authentication credentials securely. This eliminates the need to include passwords in backup scripts.

nano ~/.pgpass

Add the following line, replacing values with your actual connection details:

localhost:5432:your_database_name:backupuser:your_secure_password

Set proper permissions on the .pgpass file:

chmod 600 ~/.pgpass

This file format allows pg_dump to authenticate automatically without exposing passwords in process lists or script files.

Step 3: Create the backup directory structure

Organize your backup files with a logical directory structure that supports rotation and easy management.

sudo mkdir -p /var/backups/postgresql/{daily,weekly,monthly}
sudo chown -R $(whoami):$(whoami) /var/backups/postgresql
chmod 755 /var/backups/postgresql

This structure separates backups by frequency, making it easier to implement different retention policies for daily, weekly, and monthly backups.

Step 4: Write the backup script

Create a comprehensive backup script that handles multiple databases, compression, and error logging. This script forms the foundation of your automated backup system.

nano /usr/local/bin/postgresql_backup.sh

Add the following script 

#!/bin/bash

# PostgreSQL Backup Script
# Configuration
BACKUP_DIR="/var/backups/postgresql"
DB_USER="backupuser"
DB_HOST="localhost"
DB_PORT="5432"
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="$BACKUP_DIR/backup.log"

# Database list (space-separated)
DATABASES="your_database_name another_database"

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

# Create backup
for DB in $DATABASES; do
    log_message "Starting backup for database: $DB"
    
    BACKUP_FILE="$BACKUP_DIR/daily/${DB}_${DATE}.sql.gz"
    
    if pg_dump -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB" | gzip > "$BACKUP_FILE"; then
        log_message "Backup completed successfully: $BACKUP_FILE"
    else
        log_message "ERROR: Backup failed for database: $DB"
        exit 1
    fi
done

# Cleanup old backups (keep last 7 days)
find "$BACKUP_DIR/daily" -name ".sql.gz" -mtime +7 -delete
log_message "Cleanup completed - removed backups older than 7 days"

Make the script executable:

chmod +x /usr/local/bin/postgresql_backup.sh

Step 5: Test the backup script manually

Before scheduling the backup, test it manually to ensure everything works correctly.

/usr/local/bin/postgresql_backup.sh

Check if backup files were created:

ls -la /var/backups/postgresql/daily/

Verify the backup log for any errors:

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

If you encounter authentication errors, double-check your .pgpass file format and permissions. The PostgreSQL documentation provides detailed information about password file configuration.

Step 6: Configure cron for automated execution

Set up cron jobs to run your backup script automatically. This implements the automation component of how to set up automated PostgreSQL backups with cron and pg_dump.

crontab -e

Add the following cron entries:

# Daily PostgreSQL backup at 2:00 AM
0 2    /usr/local/bin/postgresql_backup.sh

# Weekly backup on Sundays at 3:00 AM
0 3   0 cp /var/backups/postgresql/daily/$(ls -1t /var/backups/postgresql/daily/ | head -1) /var/backups/postgresql/weekly/

# Monthly backup on the 1st at 4:00 AM
0 4 1   cp /var/backups/postgresql/daily/$(ls -1t /var/backups/postgresql/daily/ | head -1) /var/backups/postgresql/monthly/

This schedule creates daily backups at 2 AM, copies the most recent backup to weekly storage on Sundays, and creates monthly archives on the first day of each month.

Step 7: Implement backup monitoring

Create a simple monitoring script to verify backup success and send alerts if backups fail.

nano /usr/local/bin/backup_monitor.sh
#!/bin/bash

BACKUP_DIR="/var/backups/postgresql/daily"
LOG_FILE="/var/backups/postgresql/backup.log"
ALERT_EMAIL="[email protected]"

# Check if backup ran in last 25 hours
LATEST_BACKUP=$(find "$BACKUP_DIR" -name ".sql.gz" -mtime -1 | wc -l)

if [ "$LATEST_BACKUP" -eq 0 ]; then
    echo "ALERT: No PostgreSQL backups found in last 24 hours" | mail -s "Backup Alert" "$ALERT_EMAIL"
    echo "$(date) - ALERT: No recent backups found" >> "$LOG_FILE"
fi

Make it executable and add to cron:

chmod +x /usr/local/bin/backup_monitor.sh
crontab -e

Add this line to run monitoring at 6 AM daily:

0 6    /usr/local/bin/backup_monitor.sh

Troubleshooting Common PostgreSQL Backup Issues

When implementing automated PostgreSQL backups with cron and pg_dump, several common issues may arise that require troubleshooting.

Authentication failures are the most frequent problem. If pg_dump cannot connect to PostgreSQL, verify your .pgpass file contains the correct hostname, port, database name, username, and password. Ensure the file has 600 permissions and is owned by the user running the backup script.

Permission denied errors often occur when the backup directory lacks proper ownership or permissions. Use chown and chmod commands to fix directory permissions. The backup user needs write access to the backup directory and all subdirectories.

Disk space issues can cause backup failures. Monitor available space in your backup directory with df -h /var/backups. Implement more aggressive cleanup policies if backups consume too much space. Consider compressing backups further or moving older backups to external storage.

Cron jobs failing silently is another common issue. Cron runs with a minimal environment, so specify full paths to all commands in your scripts. Add logging to your backup scripts and check /var/log/syslog for cron execution messages. The <a href="

Similar Posts