Database Backups to S3 Storage

Quick Fix

Back up a PostgreSQL database to S3 now

Create postgres_backup.sh, set your values, and run it. For MySQL and MongoDB scripts, scheduling, and retention, see the sections below.

#!/bin/bash

# Configuration
DB_NAME="your_database"
DB_USER="postgres"
DB_HOST="localhost"
DB_PORT="5432"
S3_BUCKET="database-backups"
BACKUP_DIR="/tmp/db_backups"

# S3 Configuration
export AWS_ACCESS_KEY_ID="YOUR_ACCESS_KEY"
export AWS_SECRET_ACCESS_KEY="YOUR_SECRET_KEY"

# Create backup directory
mkdir -p $BACKUP_DIR

# Create backup filename with timestamp
BACKUP_FILE="postgres_${DB_NAME}_$(date +%Y%m%d_%H%M%S).sql.gz"

# Create backup
echo "Creating PostgreSQL backup..."
pg_dump -h $DB_HOST -p $DB_PORT -U $DB_USER $DB_NAME | gzip > "$BACKUP_DIR/$BACKUP_FILE"

# Upload to S3
echo "Uploading to S3..."
aws s3 cp "$BACKUP_DIR/$BACKUP_FILE" "s3://$S3_BUCKET/postgresql/" \
    --endpoint-url https://eu-west-1.euronodes.com

# Clean up local backup
rm "$BACKUP_DIR/$BACKUP_FILE"

echo "Backup completed: $BACKUP_FILE"

Overview

Automated Database Backups

Backup your databases directly to S3 storage with automated scripts. Supports PostgreSQL, MySQL, MongoDB, and other popular databases.

Prerequisites

Before You Start

• S3 storage configured with valid credentials
• AWS CLI installed and configured
• Database access credentials
• Sufficient storage space for backups

PostgreSQL Backups

Advanced PostgreSQL Backup

Production-Ready PostgreSQL Backup

Create postgres_backup_advanced.sh:

#!/bin/bash

# Configuration
DB_NAME="your_database"
DB_USER="postgres"
DB_HOST="localhost"
DB_PORT="5432"
S3_BUCKET="database-backups"
BACKUP_DIR="/tmp/db_backups"
RETENTION_DAYS=30

# S3 Configuration
export AWS_ACCESS_KEY_ID="YOUR_ACCESS_KEY"
export AWS_SECRET_ACCESS_KEY="YOUR_SECRET_KEY"

# Create backup directory
mkdir -p $BACKUP_DIR

# Create backup filename with timestamp
BACKUP_FILE="postgres_${DB_NAME}_$(date +%Y%m%d_%H%M%S).sql.gz"

# Create backup with custom format for faster restore
echo "Creating PostgreSQL backup..."
pg_dump -h $DB_HOST -p $DB_PORT -U $DB_USER -Fc $DB_NAME | gzip > "$BACKUP_DIR/$BACKUP_FILE"

# Verify backup was created successfully
if [ $? -eq 0 ]; then
    echo "Backup created successfully"

    # Upload to S3
    echo "Uploading to S3..."
    aws s3 cp "$BACKUP_DIR/$BACKUP_FILE" "s3://$S3_BUCKET/postgresql/" \
        --endpoint-url https://eu-west-1.euronodes.com

    if [ $? -eq 0 ]; then
        echo "Upload successful"

        # Clean up old backups (keep last 30 days)
        echo "Cleaning up old backups..."
        aws s3 ls "s3://$S3_BUCKET/postgresql/" --endpoint-url https://eu-west-1.euronodes.com | \
        while read -r line; do
            createDate=$(echo $line | awk '{print $1" "$2}')
            createDate=$(date -d "$createDate" +%s)
            olderThan=$(date -d "$RETENTION_DAYS days ago" +%s)
            if [[ $createDate -lt $olderThan ]]; then
                fileName=$(echo $line | awk '{print $4}')
                if [[ $fileName != "" ]]; then
                    aws s3 rm "s3://$S3_BUCKET/postgresql/$fileName" \
                        --endpoint-url https://eu-west-1.euronodes.com
                    echo "Deleted old backup: $fileName"
                fi
            fi
        done
    else
        echo "Upload failed"
        exit 1
    fi
else
    echo "Backup failed"
    exit 1
fi

# Clean up local backup
rm "$BACKUP_DIR/$BACKUP_FILE"

echo "Backup completed: $BACKUP_FILE"

PostgreSQL Restore

Restore from S3 Backup

#!/bin/bash

# Configuration
DB_NAME="your_database"
DB_USER="postgres"
S3_BUCKET="database-backups"
BACKUP_FILE="postgres_your_database_20241201_120000.sql.gz"

# Download backup from S3
aws s3 cp "s3://$S3_BUCKET/postgresql/$BACKUP_FILE" ./ \
    --endpoint-url https://eu-west-1.euronodes.com

# Restore database
gunzip -c $BACKUP_FILE | psql -h localhost -U $DB_USER $DB_NAME

# Clean up
rm $BACKUP_FILE

MySQL Backups

Basic MySQL Backup

Simple MySQL Backup Script

Create mysql_backup.sh:

#!/bin/bash

# Configuration
DB_NAME="your_database"
DB_USER="root"
DB_PASSWORD="your_password"
DB_HOST="localhost"
S3_BUCKET="database-backups"
BACKUP_DIR="/tmp/db_backups"

# S3 Configuration
export AWS_ACCESS_KEY_ID="YOUR_ACCESS_KEY"
export AWS_SECRET_ACCESS_KEY="YOUR_SECRET_KEY"

# Create backup directory
mkdir -p $BACKUP_DIR

# Create backup filename with timestamp
BACKUP_FILE="mysql_${DB_NAME}_$(date +%Y%m%d_%H%M%S).sql.gz"

# Create backup
echo "Creating MySQL backup..."
mysqldump -h $DB_HOST -u $DB_USER -p$DB_PASSWORD $DB_NAME | gzip > "$BACKUP_DIR/$BACKUP_FILE"

# Upload to S3
echo "Uploading to S3..."
aws s3 cp "$BACKUP_DIR/$BACKUP_FILE" "s3://$S3_BUCKET/mysql/" \
    --endpoint-url https://eu-west-1.euronodes.com

# Clean up local backup
rm "$BACKUP_DIR/$BACKUP_FILE"

echo "Backup completed: $BACKUP_FILE"

MySQL All Databases Backup

Backup All MySQL Databases

Create mysql_all_backup.sh:

#!/bin/bash

# Configuration
DB_USER="root"
DB_PASSWORD="your_password"
DB_HOST="localhost"
S3_BUCKET="database-backups"
BACKUP_DIR="/tmp/db_backups"

# S3 Configuration
export AWS_ACCESS_KEY_ID="YOUR_ACCESS_KEY"
export AWS_SECRET_ACCESS_KEY="YOUR_SECRET_KEY"

# Create backup directory
mkdir -p $BACKUP_DIR

# Create backup filename with timestamp
BACKUP_FILE="mysql_all_databases_$(date +%Y%m%d_%H%M%S).sql.gz"

# Create backup of all databases
echo "Creating MySQL backup of all databases..."
mysqldump -h $DB_HOST -u $DB_USER -p$DB_PASSWORD --all-databases | gzip > "$BACKUP_DIR/$BACKUP_FILE"

# Upload to S3
echo "Uploading to S3..."
aws s3 cp "$BACKUP_DIR/$BACKUP_FILE" "s3://$S3_BUCKET/mysql/" \
    --endpoint-url https://eu-west-1.euronodes.com

# Clean up local backup
rm "$BACKUP_DIR/$BACKUP_FILE"

echo "Backup completed: $BACKUP_FILE"

MongoDB Backups

MongoDB Backup Script

MongoDB Backup to S3

Create mongodb_backup.sh:

#!/bin/bash

# Configuration
DB_NAME="your_database"
MONGO_HOST="localhost"
MONGO_PORT="27017"
MONGO_USER="admin"
MONGO_PASSWORD="your_password"
S3_BUCKET="database-backups"
BACKUP_DIR="/tmp/db_backups"

# S3 Configuration
export AWS_ACCESS_KEY_ID="YOUR_ACCESS_KEY"
export AWS_SECRET_ACCESS_KEY="YOUR_SECRET_KEY"

# Create backup directory
mkdir -p $BACKUP_DIR

# Create backup filename with timestamp
BACKUP_FILE="mongodb_${DB_NAME}_$(date +%Y%m%d_%H%M%S)"

# Create backup
echo "Creating MongoDB backup..."
mongodump --host $MONGO_HOST:$MONGO_PORT \
          --username $MONGO_USER \
          --password $MONGO_PASSWORD \
          --db $DB_NAME \
          --out "$BACKUP_DIR/$BACKUP_FILE"

# Compress backup
tar -czf "$BACKUP_DIR/$BACKUP_FILE.tar.gz" -C "$BACKUP_DIR" "$BACKUP_FILE"

# Upload to S3
echo "Uploading to S3..."
aws s3 cp "$BACKUP_DIR/$BACKUP_FILE.tar.gz" "s3://$S3_BUCKET/mongodb/" \
    --endpoint-url https://eu-west-1.euronodes.com

# Clean up local backup
rm -rf "$BACKUP_DIR/$BACKUP_FILE"
rm "$BACKUP_DIR/$BACKUP_FILE.tar.gz"

echo "Backup completed: $BACKUP_FILE.tar.gz"

Automated Scheduling

Cron Jobs

Schedule Database Backups

# Edit crontab
crontab -e

# Daily PostgreSQL backup at 2 AM
0 2 * * * /path/to/postgres_backup.sh >> /var/log/postgres_backup.log 2>&1

# Daily MySQL backup at 3 AM
0 3 * * * /path/to/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

# Weekly MongoDB backup on Sundays at 4 AM
0 4 * * 0 /path/to/mongodb_backup.sh >> /var/log/mongodb_backup.log 2>&1

Systemd Timers (Linux)

Using Systemd for Scheduling

Create /etc/systemd/system/db-backup.service:

[Unit]
Description=Database Backup Service

[Service]
Type=oneshot
ExecStart=/path/to/postgres_backup.sh
User=postgres

Create /etc/systemd/system/db-backup.timer:

[Unit]
Description=Run database backup daily
Requires=db-backup.service

[Timer]
OnCalendar=daily
Persistent=true

[Install]
WantedBy=timers.target

Enable and start:

sudo systemctl enable db-backup.timer
sudo systemctl start db-backup.timer

Monitoring and Alerts

Backup Verification

Verify Your Backups

#!/bin/bash

# Check if backup exists in S3
BACKUP_FILE="postgres_mydb_$(date +%Y%m%d)*.sql.gz"

aws s3 ls "s3://database-backups/postgresql/" \
    --endpoint-url https://eu-west-1.euronodes.com | grep "$(date +%Y%m%d)"

if [ $? -eq 0 ]; then
    echo "Backup found for today"
else
    echo "ERROR: No backup found for today"
    # Send alert email or notification
fi

Email Notifications

Send Backup Status Emails

# Add to backup script
if [ $? -eq 0 ]; then
    echo "Database backup completed successfully" | \
    mail -s "Backup Success - $(date)" [email protected]
else
    echo "Database backup failed" | \
    mail -s "Backup FAILED - $(date)" [email protected]
fi

FAQ

How often should I backup my database?

For production databases, daily backups are recommended minimum. Critical databases may need hourly backups.

How long should I keep database backups?

Common retention policies: 7 daily, 4 weekly, 12 monthly backups. Adjust based on your recovery requirements.

Can I backup large databases efficiently?

Yes, use compression and incremental backups where possible. Consider using database-specific tools for large datasets.

How do I test my backup restoration?

Regularly test restoring backups to a test environment to ensure they're valid and complete.

What about database encryption?

Encrypt sensitive data before backup or use database-native encryption features.

Contact Support

Need Help?

• Backup Issues: Open support ticket through client portal
• S3 Configuration: Include database type and error messages
• Performance Problems: Specify database size and backup duration

---

For S3 setup, see S3 Configuration | For file backups, see Restic Backups