Database backups, restore: Ubuntu, Rails, Kamal, PostgreSQL, Wasabi (S3-compatible storage)

db_backup

#!/bin/bash
set -e

PROJECT_NAME="your_project"
RETENTION_DAYS=30  # Number of days to keep backups

DB_NAME="${PROJECT_NAME}_${RAILS_ENV}"

BACKUP_DATE=$(date '+%Y%m%d_%H%M%S')
FILENAME="${DB_NAME}_${BACKUP_DATE}.sql"

if [ "$RAILS_ENV" = "production" ]; then
    DB_USER=$PROJECT_NAME
    DB_PASSWORD=$POSTGRES_PASSWORD
    DB_HOST="prod_db_host"
else
    DB_USER="postgres"
    DB_PASSWORD="password"
    DB_HOST="db"
fi

WASABI_BUCKET_NAME="database-storage"
WASABI_REGION="eu-central-1"
WASABI_ENDPOINT="https://s3.$WASABI_REGION.wasabisys.com"

# Set the values from Rails credentials and export them
export AWS_ACCESS_KEY_ID=$(rails runner "puts Rails.application.credentials.wasabi.access_key_id")
export AWS_SECRET_ACCESS_KEY=$(rails runner "puts Rails.application.credentials.wasabi.secret_access_key")
export AWS_DEFAULT_REGION=$WASABI_REGION

if [ -z "$AWS_ACCESS_KEY_ID" ] || [ -z "$AWS_SECRET_ACCESS_KEY" ]; then
    echo "Error: WASABI credentials not set"
    exit 1
fi

# Create backup with built-in compression
echo "Creating database backup"
PGPASSWORD=$DB_PASSWORD pg_dump -h $DB_HOST -U $DB_USER -d $DB_NAME --format=c -Z9 -f /tmp/$FILENAME

# Upload to Wasabi using AWS CLI
echo "Uploading to Wasabi..."
aws s3 cp /tmp/$FILENAME s3://$WASABI_BUCKET_NAME/$PROJECT_NAME/ --endpoint-url=$WASABI_ENDPOINT

# Cleanup old backups
echo "Cleaning up old backups (keeping last $RETENTION_DAYS days)..."

# Calculate cutoff date (handle different date command versions)
if date -v-1d > /dev/null 2>&1; then
    # BSD date (macOS)
    CUTOFF_DATE=$(date -v-${RETENTION_DAYS}d '+%Y%m%d')
else
    # GNU date (Linux)
    CUTOFF_DATE=$(date -d "$RETENTION_DAYS days ago" '+%Y%m%d')
fi

echo "Cutoff date: $CUTOFF_DATE (files older than this will be deleted)"

# List all backup files and filter by date
aws s3 ls s3://$WASABI_BUCKET_NAME/$PROJECT_NAME/ --endpoint-url=$WASABI_ENDPOINT | \
while read -r line; do
    # Extract filename from aws s3 ls output (4th column)
    backup_file=$(echo $line | awk '{print $4}')

    # Skip if not a backup file for this project (flexible pattern)
    if [[ ! $backup_file =~ ^${PROJECT_NAME}.*[0-9]{8}_[0-9]{6}\.sql$ ]]; then
        continue
    fi

    # Extract date from filename (flexible pattern to handle different naming)
    backup_date=$(echo $backup_file | sed -n 's/^.*_\([0-9]\{8\}\)_[0-9]\{6\}\.sql$/\1/p')

    echo "Checking backup: $backup_file (date: $backup_date)"

    # Compare with cutoff date
    if [ "$backup_date" -lt "$CUTOFF_DATE" ]; then
        echo "Deleting old backup: $backup_file (date: $backup_date)"
        aws s3 rm s3://$WASABI_BUCKET_NAME/$PROJECT_NAME/$backup_file --endpoint-url=$WASABI_ENDPOINT
    else
        echo "Keeping backup: $backup_file (date: $backup_date)"
    fi
done

# Cleanup local file
rm /tmp/$FILENAME

echo "Backup completed successfully at $(date)"

db_restore

#!/bin/bash
set -e

PROJECT_NAME="your_project"
DB_NAME="${PROJECT_NAME}_${RAILS_ENV}"

if [ "$RAILS_ENV" = "production" ]; then
    DB_USER=$PROJECT_NAME
    DB_PASSWORD=$POSTGRES_PASSWORD
    DB_HOST="prod_db_host"
else
    DB_USER="postgres"
    DB_PASSWORD="password"
    DB_HOST="db"
fi

WASABI_BUCKET_NAME="database-storage"
WASABI_REGION="eu-central-1"
WASABI_ENDPOINT="https://s3.$WASABI_REGION.wasabisys.com"

# Safety check - only allow restore in development
if [ "$RAILS_ENV" != "development" ]; then
    echo "ERROR: Database restore is only allowed in development environment!"
    echo "Current RAILS_ENV: $RAILS_ENV"
    exit 1
fi

# Set the values from Rails credentials
export AWS_ACCESS_KEY_ID=$(rails runner "puts Rails.application.credentials.wasabi.access_key_id")
export AWS_SECRET_ACCESS_KEY=$(rails runner "puts Rails.application.credentials.wasabi.secret_access_key")
export AWS_DEFAULT_REGION=$WASABI_REGION

if [ -z "$AWS_ACCESS_KEY_ID" ] || [ -z "$AWS_SECRET_ACCESS_KEY" ]; then
    echo "Error: WASABI credentials not set"
    exit 1
fi

# If no backup file specified, find the most recent one
if [ -z "$1" ]; then
    echo "No backup file specified. Finding the most recent backup..."

    # Get the most recent backup file
    BACKUP_FILE=$(aws s3 ls s3://$WASABI_BUCKET_NAME/$PROJECT_NAME/ --endpoint-url=$WASABI_ENDPOINT | grep '\.sql$' | sort -k1,2 | tail -n 1 | awk '{print $4}')

    if [ -z "$BACKUP_FILE" ]; then
        echo "Error: No backup files found in bucket"
        echo "Available files:"
        aws s3 ls s3://$WASABI_BUCKET_NAME/$PROJECT_NAME/ --endpoint-url=$WASABI_ENDPOINT
        exit 1
    fi

    echo "Using most recent backup: $BACKUP_FILE"
else
    BACKUP_FILE=$1
fi

# Show available backups if requested file doesn't exist or if listing
if [ "$1" = "list" ]; then
    echo "Available backups:"
    aws s3 ls s3://$WASABI_BUCKET_NAME/$PROJECT_NAME/ --endpoint-url=$WASABI_ENDPOINT
    exit 0
fi

# Download backup
echo "Downloading backup: $BACKUP_FILE"
aws s3 cp s3://$WASABI_BUCKET_NAME/$PROJECT_NAME/$BACKUP_FILE /tmp/ --endpoint-url=$WASABI_ENDPOINT

# Restore backup
echo "Restoring backup to $DB_NAME..."
PGPASSWORD=$DB_PASSWORD pg_restore -h $DB_HOST -U $DB_USER -d $DB_NAME --clean --if-exists --no-owner --no-privileges /tmp/$BACKUP_FILE

# Cleanup
rm /tmp/$BACKUP_FILE

echo "Restore completed successfully!"