Coolify環境のバックアップ戦略：6つのDBを自動ダンプ+復旧手順

/mnt/data1 (3.6TB HDD) ─┐
/mnt/data2 (1.8TB HDD) ─┼─ mergerfs ─→ /mnt/storage (7.2TB pool)
/mnt/data3 (1.8TB HDD) ─┘
/mnt/parity (parity disk) ─→ snapraid parity

#!/bin/bash
set -euo pipefail

# === 設定 ===
BACKUP_BASE="/mnt/storage/coolify-backups"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7
LOG_FILE="${BACKUP_BASE}/backup.log"

mkdir -p "${BACKUP_BASE}/db" "${BACKUP_BASE}/coolify-config" "${BACKUP_BASE}/system-config"

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

log "=== バックアップ開始 ==="

# === 1. PostgreSQL ダンプ ===
log "PostgreSQL: coolify-db"
docker exec coolify-db pg_dumpall -U coolify \
    | gzip > "${BACKUP_BASE}/db/coolify-db_${DATE}.sql.gz"

log "PostgreSQL: immich-database"
IMMICH_DB=$(docker ps --format '{{.Names}}' | grep immich-database)
docker exec "$IMMICH_DB" pg_dumpall -U postgres \
    | gzip > "${BACKUP_BASE}/db/immich-db_${DATE}.sql.gz"

# === 2. MySQL / MariaDB ダンプ ===
# Zabbix MySQL
ZABBIX_MYSQL=$(docker ps --format '{{.Names}}' | grep -E '^fswk')
log "MySQL: Zabbix ($ZABBIX_MYSQL)"
docker exec "$ZABBIX_MYSQL" mysqldump -u root --all-databases --single-transaction \
    | gzip > "${BACKUP_BASE}/db/zabbix-mysql_${DATE}.sql.gz"

# WordPress 共有 MariaDB
WP_SHARED_DB=$(docker ps --format '{{.Names}}' | grep -E '^fqff')
log "MariaDB: WordPress共有 ($WP_SHARED_DB)"
docker exec "$WP_SHARED_DB" mariadb-dump -u root --all-databases --single-transaction \
    | gzip > "${BACKUP_BASE}/db/wp-shared-db_${DATE}.sql.gz"

# AI Moderator MariaDB
log "MariaDB: AI Moderator"
docker exec ai-moderator-lipsync-db-1 mariadb-dump -u root --all-databases --single-transaction \
    | gzip > "${BACKUP_BASE}/db/ai-moderator-db_${DATE}.sql.gz"

# WordPress 個別 MariaDB
WP_TERA_DB=$(docker ps --format '{{.Names}}' | grep -E '^p8sg')
log "MariaDB: WordPress teraren ($WP_TERA_DB)"
docker exec "$WP_TERA_DB" mariadb-dump -u root --all-databases --single-transaction \
    | gzip > "${BACKUP_BASE}/db/wp-teraren-db_${DATE}.sql.gz"

# === 3. Coolify 設定ディレクトリ ===
log "Coolify設定: /data/coolify"
sudo rsync -a --delete /data/coolify/ "${BACKUP_BASE}/coolify-config/"

# === 4. システム設定 ===
log "システム設定のバックアップ"
sudo cp -r /etc/zabbix/ "${BACKUP_BASE}/system-config/zabbix/" 2>/dev/null || true
sudo cp -r /etc/cloudflared/ "${BACKUP_BASE}/system-config/cloudflared/" 2>/dev/null || true
crontab -l > "${BACKUP_BASE}/system-config/crontab.txt" 2>/dev/null || true

# === 5. 古いバックアップの削除 ===
log "古いバックアップの削除 (${RETENTION_DAYS}日以上)"
find "${BACKUP_BASE}/db" -name "*.sql.gz" -mtime +${RETENTION_DAYS} -delete

# === 6. サイズ確認 ===
TOTAL_SIZE=$(du -sh "${BACKUP_BASE}" | cut -f1)
log "バックアップ完了。合計サイズ: ${TOTAL_SIZE}"
log "=== バックアップ終了 ==="