Automating DB Backups with Docker, Cron & LocalStack

Introduction

In this tutorial, we’ll walk through how to automate database backups, upload them to an S3 bucket (emulated by LocalStack), and automatically clean up old backups both locally and in the S3 bucket. We’ll be using Docker, cron jobs, bash scripts, and LocalStack to mimic the behavior of AWS S3.

1. Setting Up Docker Compose Services

We’ll be using Docker Compose to manage multiple services, including a PostgreSQL database, Redis, Directus CMS, and LocalStack. Here’s how our docker-compose.yml is configured:

services:
  database:
    image: postgis/postgis:13-master
    volumes:
      - ./data/database:/var/lib/postgresql/data
    environment:
      POSTGRES_USER: "directus"
      POSTGRES_PASSWORD: "directus"
      POSTGRES_DB: "directus"

  cache:
    image: redis:6

  mailhog:
    image: mailhog/mailhog
    ports:
      - "1025:1025"
      - "8025:8025"

  adminer:
    image: adminer
    ports:
      - 8080:8080
    environment:
      ADMINER_DEFAULT_SERVER: database

  directus:
    image: directus/directus:10.10.4
    ports:
      - 8055:8055
    depends_on:
      - cache
      - database
    environment:
      DB_CLIENT: "pg"
      DB_HOST: "database"
      DB_PORT: "5432"
      DB_DATABASE: "directus"
      DB_USER: "directus"
      DB_PASSWORD: "directus"

  localstack:
    image: localstack/localstack
    ports:
      - "4566:4566"
    environment:
      - SERVICES=s3

  backup:
    build: ./backup
    volumes:
      - ./backup:/backup
    environment:
      - AWS_ACCESS_KEY_ID=test
      - AWS_SECRET_ACCESS_KEY=test
      - DB_HOST=database
      - DB_PORT=5432
      - DB_USER=directus
      - DB_PASSWORD=directus
      - DB_NAME=directus
    depends_on:
      - database
      - localstack

Breakdown of Key Services:

• Database: PostgreSQL with PostGIS extensions to store Directus data.
• Directus: CMS that connects to the database and Redis for caching.
• LocalStack: Simulates AWS services, including S3, where we’ll store our backups.
• Backup: A custom service that runs our backup and cleanup scripts using cron jobs.

2. Writing the Backup Script

Our main goal is to create a backup of the database, upload it to LocalStack S3, and clean up old backups. We’ll split the logic into smaller, reusable scripts.

2.1 backup.sh

This is the entry point for the backup process. It invokes different scripts to handle the database dump, upload to S3, and cleanup of old backups.

#!/bin/bash

LOG_DIR="/backup/logs"
LOG_FILE="$LOG_DIR/backup_log.log"
DATE=$(date +"%Y-%m-%d_%H-%M-%S")

# Ensure log directory exists
mkdir -p $LOG_DIR

log_message() {
    echo "$DATE - $1" | tee -a $LOG_FILE
}

log_message "Starting backup process..."

# Step 1: Backup the database
log_message "Backing up database..."
/scripts/db.sh

# Step 2: Upload to S3
log_message "Uploading backup to S3..."
/scripts/s3.sh

# Step 3: Clean up old backups
log_message "Cleaning up old backups..."
/scripts/clean.sh

log_message "Backup process completed."

2.2 db.sh

This script creates a backup of the database and saves it locally.

#!/bin/bash

BACKUP_DIR="/backup/dumps"
DATE=$(date +"%Y-%m-%d_%H-%M-%S")
BACKUP_FILE="$BACKUP_DIR/db_backup_$DATE.sql"

# Ensure backup directory exists
mkdir -p $BACKUP_DIR

# Create the database dump
pg_dump -h "$DB_HOST" -U "$DB_USER" "$DB_NAME" > "$BACKUP_FILE" 2>> /backup/logs/backup_log.log

if [ $? -eq 0 ]; then
    echo "Backup created successfully: $BACKUP_FILE"
else
    echo "Failed to create backup!"
    exit 1
fi

2.3 s3.sh

This script uploads the generated backup to the LocalStack S3 bucket.

#!/bin/bash

AWS_REGION="us-east-1"
S3_BUCKET="backups-bucket"
BACKUP_DIR="/backup/dumps"
DATE=$(date +"%Y-%m-%d_%H-%M-%S")
BACKUP_FILE="$BACKUP_DIR/db_backup_$DATE.sql"

# Ensure the S3 bucket exists
aws --endpoint-url=http://localstack:4566 --region $AWS_REGION s3 mb s3://$S3_BUCKET 2>> /backup/logs/backup_log.log

# Upload the backup to S3
aws --endpoint-url=http://localstack:4566 --region $AWS_REGION s3 cp "$BACKUP_FILE" s3://$S3_BUCKET/ 2>> /backup/logs/backup_log.log

if [ $? -eq 0 ]; then
    echo "Backup uploaded successfully to s3://$S3_BUCKET"
else
    echo "Failed to upload backup to S3!"
    exit 1
fi

2.4 clean.sh

This script removes backups older than week from both the local file system and the S3 bucket.

#!/bin/bash

LOG_FILE="/backup/logs/backup_log.log"
AWS_REGION="us-east-1"
S3_BUCKET="backups-bucket"
BACKUP_DIR="/backup/dumps"

# Remove local backups older than 7 days
find "$BACKUP_DIR"/* -mtime +7 -exec rm {} \; 2>> "$LOG_FILE"

# Get the current timestamp in seconds
CURRENT_TIMESTAMP=$(date +%s)

# Remove S3 backups older than 7 days (1 week)
aws --endpoint-url=http://localstack:4566 --region $AWS_REGION s3 ls s3://$S3_BUCKET/ --recursive | while read -r line; do
    FILE_DATE=$(echo "$line" | awk '{print $1" "$2}')
    FILE_NAME=$(echo "$line" | awk '{print $4}')
    FILE_TIMESTAMP=$(date -d "$FILE_DATE" +%s)
    FILE_AGE=$(( (CURRENT_TIMESTAMP - FILE_TIMESTAMP) / 86400 ))  # Calculate age in days (86400 seconds = 1 day)

    if [ $FILE_AGE -gt 7 ]; then  # If file is older than 7 days
        aws --endpoint-url=http://localstack:4566 --region $AWS_REGION s3 rm s3://$S3_BUCKET/$FILE_NAME 2>> "$LOG_FILE"
        echo "Removed S3 backup: $FILE_NAME (Age: $FILE_AGE days)" >> "$LOG_FILE"
    fi
done

3. Dockerfile for the Backup Service

This Dockerfile defines how the backup service is built, installing necessary tools like PostgreSQL client and AWS CLI, and setting up cron to run our backup.sh script every midnght.

# Use the latest Ubuntu image
FROM ubuntu:latest

# Install cron, curl, unzip, and PostgreSQL client
RUN apt-get update && apt-get install -y \
    cron \
    curl \
    unzip \
    postgresql-client \
    && rm -rf /var/lib/apt/lists/*

# Install AWS CLI
RUN curl "https://awscli.amazonaws.com/awscli-exe-linux-aarch64.zip" -o "awscliv2.zip" && \
    unzip awscliv2.zip && \
    ./aws/install && \
    rm -rf awscliv2.zip aws

# Ensure AWS CLI is available in the PATH
ENV PATH="/usr/local/bin:/usr/local/aws-cli/v2/current/bin:$PATH"

# Copy backup scripts into the container
COPY scripts/ /scripts/

# Make all scripts executable
RUN chmod +x /scripts/*.sh

# Add cron job to run every day at midnight
RUN echo "0 0 * * * /scripts/backup.sh >> /var/log/cron.log 2>&1" > /etc/cron.d/backup-cron

# Set the cron job
RUN chmod 0644 /etc/cron.d/backup-cron && crontab /etc/cron.d/backup-cron

# Start cron in the foreground to keep the container running
CMD ["cron", "-f"]

4. Running the Backup Service

To bring up the entire system, use:

docker-compose up

This command will start all services, including the backup service that runs the backup.sh script every midnight, backs up the PostgreSQL database, uploads the backup to S3, and cleans up backups older than week.

5. Conclusion

By using Docker Compose, cron, and LocalStack, we’ve set up a fully automated backup system that not only creates database backups but also handles S3 uploads and periodic cleanup. This setup can easily be adapted to a production environment by replacing LocalStack with actual AWS services.

Feel free to customize the backup and cleanup times, S3 configuration, and scripts according to your project’s needs!