Back to all posts

Automating PostgreSQL Backups to MinIO with Notification Integration

January 5, 2025
nerdyslacker

@nerdyslacker

Automating PostgreSQL Backups to MinIO with Notification Integration

Backups are an essential part of maintaining database reliability and integrity. Automating this process ensures consistent backups without manual intervention. In this post, we'll explore a script that automates PostgreSQL backups, uploads them to a MinIO server, and integrates with Uptime Kuma to notify about the success or failure of the backup process.

The solution consists of two scripts:

  1. Backup Script: Automates PostgreSQL backups and uploads the backup files to MinIO.
  2. Wrapper Script: Executes the backup script and sends notifications to Uptime Kuma.

Together, these scripts form a robust, automated system for managing backups while keeping you informed.

PostgreSQL Backup and Upload to MinIO

Features

  • Database Backup: Uses pg_dump to create a compressed database dump.
  • MinIO Upload: Transfers the dump to a specified MinIO bucket using the mc (MinIO client) command.
  • Cleanup: Removes the local backup file after a successful upload.
  • Pruning Old Backups: Retains only the latest three backups by pruning older files.
#!/bin/bash
 
set -e
set -u
 
BUCKET_NAME="postgres-backups"
MINIO_ALIAS="myminio"
 
log() {
    echo "$(date +%F_%T) - $1"
}
 
decode_base64() {
    echo "$1" | base64 --decode
}
 
create_backup() {
    local temp_file="$1"
    local db_pass="$2"
    PGPASSWORD=$db_pass pg_dump -Fc -h localhost -U "$DB_USER" -d "$DB_NAME" > "$temp_file"
}
 
upload_backup() {
    local temp_file="$1"
    local s3_file="$2"
    mc cp "$temp_file" "$s3_file"
}
 
cleanup_local_file() {
    local temp_file="$1"
    rm -f "$temp_file"
}
 
prune_old_backups() {
    local backups_to_delete=$(mc ls "$MINIO_ALIAS/$BUCKET_NAME/" --recursive | sort -r | tail -n +4 | awk '{print $NF}')
    for backup in $backups_to_delete; do
        mc rm "$MINIO_ALIAS/$BUCKET_NAME/$backup"
    done
}
 
main() {
    local timestamp=$(date +%F_%T | tr ':' '-')
    local temp_file=$(mktemp tmp.XXXXXXXXXX)
    local s3_file="$MINIO_ALIAS/$BUCKET_NAME/backup-$timestamp.dump"
    local db_pass=$(decode_base64 "$DB_PASS_BASE64")
 
    create_backup "$temp_file" "$db_pass"
    upload_backup "$temp_file" "$s3_file"
    cleanup_local_file "$temp_file"
    prune_old_backups
}
 
main

Notification Integration with Uptime Kuma

This script wraps around the backup script to add status monitoring and notification capabilities. It uses Uptime Kuma to send notifications about the backup's success or failure.

#!/bin/bash
 
set -e
set -u
 
push_token_base64=$1
shift || { echo "Usage: $0 <push_token_base64> [command...]" >&2; exit 1; }
 
start_time=$(date -u +%s%3N)
status="up"
msg="BACKUP_SUCCEEDED"
 
trap 'status="down"; msg="BACKUP_INTERRUPTED"; send_status; exit 1' SIGINT SIGTERM EXIT
 
execute_command() {
    if [ $# -gt 0 ]; then
        "$@"
        return $?
    fi
    return 0
}
 
send_status() {
    local end_time=$(date -u +%s%3N)
    local duration=$((end_time - start_time))
    local push_token=$(echo "$push_token_base64" | base64 --decode)
 
    curl --fail --no-progress-meter --retry 3 \
        "https://monitor.example.am/api/push/$push_token?status=$status&msg=$msg&ping=$duration" 2>&1
}
 
execute_command "$@"
command_exit_code=$?
 
if [ $command_exit_code -ne 0 ]; then
    status="down"
    msg="BACKUP_FAILED"
fi
 
trap - SIGINT SIGTERM EXIT
 
send_status
 
exit $command_exit_code

How to Use These Scripts

  1. Prepare the Environment:

    • Install the necessary dependencies:
      • pg_dump for PostgreSQL backups.
      • mc (MinIO Client) for managing MinIO storage.
      • curl for sending notifications to Uptime Kuma.

  2. Set Environment Variables: Export the required variables before running the scripts:

    export DB_USER="your_database_username"
export DB_NAME="your_database_name"
export DB_PASS_BASE64="your_base64_encoded_database_password"

  3. Schedule Backups: Automate the process by scheduling the wrapper script using cron:

    0 2 * * * /path/to/kuma_push.sh <push_token_base64> /path/to/pgbackup_minio.sh