gmc_ai/scripts/postgres-backup.sh

#!/bin/bash

# PostgreSQL 备份与恢复脚本
# 用于 GMC Platform 数据库维护

set -e

# 配置变量
POSTGRES_HOST=${POSTGRES_HOST:-localhost}
POSTGRES_PORT=${POSTGRES_PORT:-5432}
POSTGRES_USER=${POSTGRES_USER:-pgadmin}
POSTGRES_DB=${POSTGRES_DB:-gmc_platform}
BACKUP_DIR=${BACKUP_DIR:-/data/pg-backup}
ARCHIVE_DIR=${ARCHIVE_DIR:-/data/pg-archive}
RETENTION_DAYS=${RETENTION_DAYS:-30}

# 日志函数
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1"
}

error() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] ERROR: $1" >&2
}

# 创建备份目录
ensure_backup_dir() {
    if [ ! -d "$BACKUP_DIR" ]; then
        mkdir -p "$BACKUP_DIR"
        log "创建备份目录: $BACKUP_DIR"
    fi
}

# 全量备份
full_backup() {
    local backup_date=$(date +%Y%m%d_%H%M%S)
    local backup_file="$BACKUP_DIR/gmc_platform_full_$backup_date.sql"
    local backup_compressed="$backup_file.gz"
    
    log "开始全量备份..."
    
    # 执行 pg_dump
    if pg_dump -h "$POSTGRES_HOST" -p "$POSTGRES_PORT" -U "$POSTGRES_USER" \
        --verbose --clean --if-exists --no-owner --no-privileges \
        --format=custom --compress=9 \
        --file="$backup_file.custom" "$POSTGRES_DB"; then
        
        log "全量备份完成: $backup_file.custom"
        
        # 同时生成SQL格式备份
        pg_dump -h "$POSTGRES_HOST" -p "$POSTGRES_PORT" -U "$POSTGRES_USER" \
            --verbose --clean --if-exists --no-owner --no-privileges \
            "$POSTGRES_DB" | gzip > "$backup_compressed"
        
        log "SQL格式备份完成: $backup_compressed"
        
        # 验证备份文件
        if [ -f "$backup_file.custom" ] && [ -s "$backup_file.custom" ]; then
            log "备份文件验证通过"
            return 0
        else
            error "备份文件验证失败"
            return 1
        fi
    else
        error "全量备份失败"
        return 1
    fi
}

# 数据库备份验证
verify_backup() {
    local backup_file="$1"
    
    if [ ! -f "$backup_file" ]; then
        error "备份文件不存在: $backup_file"
        return 1
    fi
    
    log "验证备份文件: $backup_file"
    
    # 验证备份文件完整性
    if pg_restore --list "$backup_file" > /dev/null 2>&1; then
        log "备份文件验证通过"
        return 0
    else
        error "备份文件验证失败"
        return 1
    fi
}

# 增量备份 (WAL 归档)
incremental_backup() {
    local backup_date=$(date +%Y%m%d_%H%M%S)
    local wal_backup_dir="$BACKUP_DIR/wal_$backup_date"
    
    log "开始 WAL 增量备份..."
    
    # 创建 WAL 备份目录
    mkdir -p "$wal_backup_dir"
    
    # 强制切换 WAL 文件
    psql -h "$POSTGRES_HOST" -p "$POSTGRES_PORT" -U "$POSTGRES_USER" -d "$POSTGRES_DB" \
        -c "SELECT pg_switch_wal();" > /dev/null
    
    # 复制 WAL 文件
    if [ -d "$ARCHIVE_DIR" ]; then
        find "$ARCHIVE_DIR" -name "*.wal" -newer "$BACKUP_DIR/.last_wal_backup" 2>/dev/null | \
        while read wal_file; do
            cp "$wal_file" "$wal_backup_dir/"
        done
        
        # 更新时间戳
        touch "$BACKUP_DIR/.last_wal_backup"
        
        log "WAL 增量备份完成: $wal_backup_dir"
    else
        error "WAL 归档目录不存在: $ARCHIVE_DIR"
        return 1
    fi
}

# 恢复数据库
restore_database() {
    local backup_file="$1"
    local target_db="${2:-${POSTGRES_DB}_restore}"
    
    if [ ! -f "$backup_file" ]; then
        error "备份文件不存在: $backup_file"
        return 1
    fi
    
    log "开始恢复数据库到: $target_db"
    
    # 创建目标数据库
    createdb -h "$POSTGRES_HOST" -p "$POSTGRES_PORT" -U "$POSTGRES_USER" \
        "$target_db" || log "数据库已存在，继续恢复..."
    
    # 恢复数据
    if pg_restore -h "$POSTGRES_HOST" -p "$POSTGRES_PORT" -U "$POSTGRES_USER" \
        --verbose --clean --if-exists --no-owner --no-privileges \
        --dbname="$target_db" "$backup_file"; then
        
        log "数据库恢复完成: $target_db"
        return 0
    else
        error "数据库恢复失败"
        return 1
    fi
}

# 时间点恢复 (PITR)
point_in_time_recovery() {
    local base_backup="$1"
    local target_time="$2"
    local recovery_dir="$3"
    
    if [ ! -f "$base_backup" ]; then
        error "基础备份文件不存在: $base_backup"
        return 1
    fi
    
    log "开始时间点恢复到: $target_time"
    
    # 创建恢复目录
    mkdir -p "$recovery_dir"
    
    # 解压基础备份
    tar -xzf "$base_backup" -C "$recovery_dir"
    
    # 创建 recovery.conf
    cat > "$recovery_dir/recovery.conf" << EOF
restore_command = 'cp $ARCHIVE_DIR/%f %p'
recovery_target_time = '$target_time'
recovery_target_timeline = 'latest'
EOF
    
    log "恢复配置已创建，请手动启动 PostgreSQL 实例进行恢复"
    log "数据目录: $recovery_dir"
}

# 清理旧备份
cleanup_old_backups() {
    log "清理 $RETENTION_DAYS 天前的备份文件..."
    
    # 清理全量备份
    find "$BACKUP_DIR" -name "gmc_platform_full_*.sql*" -mtime +$RETENTION_DAYS -delete
    find "$BACKUP_DIR" -name "gmc_platform_full_*.custom" -mtime +$RETENTION_DAYS -delete
    
    # 清理 WAL 备份
    find "$BACKUP_DIR" -name "wal_*" -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \;
    
    log "清理完成"
}

# 检查数据库连接
check_connection() {
    if pg_isready -h "$POSTGRES_HOST" -p "$POSTGRES_PORT" -U "$POSTGRES_USER" > /dev/null 2>&1; then
        log "数据库连接正常"
        return 0
    else
        error "无法连接到数据库"
        return 1
    fi
}

# 数据库性能统计
db_stats() {
    log "生成数据库性能统计报告..."
    
    local stats_file="$BACKUP_DIR/db_stats_$(date +%Y%m%d).txt"
    
    {
        echo "数据库性能统计报告 - $(date)"
        echo "================================="
        echo
        
        echo "数据库大小:"
        psql -h "$POSTGRES_HOST" -p "$POSTGRES_PORT" -U "$POSTGRES_USER" -d "$POSTGRES_DB" \
            -c "SELECT pg_size_pretty(pg_database_size('$POSTGRES_DB'));"
        echo
        
        echo "表大小 Top 10:"
        psql -h "$POSTGRES_HOST" -p "$POSTGRES_PORT" -U "$POSTGRES_USER" -d "$POSTGRES_DB" \
            -c "SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size FROM pg_tables WHERE schemaname = 'gmc' ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC LIMIT 10;"
        echo
        
        echo "连接状态:"
        psql -h "$POSTGRES_HOST" -p "$POSTGRES_PORT" -U "$POSTGRES_USER" -d "$POSTGRES_DB" \
            -c "SELECT state, count(*) FROM pg_stat_activity GROUP BY state;"
        echo
        
        echo "慢查询 Top 10:"
        psql -h "$POSTGRES_HOST" -p "$POSTGRES_PORT" -U "$POSTGRES_USER" -d "$POSTGRES_DB" \
            -c "SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;"
        echo
        
        echo "缓存命中率:"
        psql -h "$POSTGRES_HOST" -p "$POSTGRES_PORT" -U "$POSTGRES_USER" -d "$POSTGRES_DB" \
            -c "SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio FROM pg_statio_user_tables;"
        
    } > "$stats_file"
    
    log "统计报告已生成: $stats_file"
}

# 主函数
main() {
    case "${1:-}" in
        "full-backup"|"full")
            ensure_backup_dir
            check_connection
            full_backup
            cleanup_old_backups
            ;;
            
        "incremental"|"inc")
            ensure_backup_dir
            check_connection
            incremental_backup
            ;;
            
        "restore")
            if [ -z "$2" ]; then
                error "请指定备份文件路径"
                exit 1
            fi
            check_connection
            restore_database "$2" "$3"
            ;;
            
        "verify")
            if [ -z "$2" ]; then
                error "请指定备份文件路径"
                exit 1
            fi
            verify_backup "$2"
            ;;
            
        "pitr")
            if [ -z "$2" ] || [ -z "$3" ] || [ -z "$4" ]; then
                error "用法: $0 pitr <base_backup> <target_time> <recovery_dir>"
                exit 1
            fi
            point_in_time_recovery "$2" "$3" "$4"
            ;;
            
        "cleanup")
            cleanup_old_backups
            ;;
            
        "stats")
            check_connection
            db_stats
            ;;
            
        "check")
            check_connection
            ;;
            
        "help"|*)
            echo "PostgreSQL 备份恢复工具"
            echo ""
            echo "用法: $0 [命令] [参数]"
            echo ""
            echo "命令:"
            echo "  full-backup    - 执行全量备份"
            echo "  incremental    - 执行增量备份(WAL)"
            echo "  restore <file> [target_db] - 恢复数据库"
            echo "  verify <file>  - 验证备份文件"
            echo "  pitr <base> <time> <dir> - 时间点恢复"
            echo "  cleanup        - 清理旧备份"
            echo "  stats          - 生成性能统计"
            echo "  check          - 检查数据库连接"
            echo "  help           - 显示帮助信息"
            echo ""
            echo "环境变量:"
            echo "  POSTGRES_HOST     - PostgreSQL 主机 (默认: localhost)"
            echo "  POSTGRES_PORT     - PostgreSQL 端口 (默认: 5432)"
            echo "  POSTGRES_USER     - PostgreSQL 用户 (默认: pgadmin)"
            echo "  POSTGRES_DB       - 数据库名称 (默认: gmc_platform)"
            echo "  BACKUP_DIR        - 备份目录 (默认: /data/pg-backup)"
            echo "  RETENTION_DAYS    - 备份保留天数 (默认: 30)"
            echo ""
            echo "示例:"
            echo "  $0 full-backup                     # 全量备份"
            echo "  $0 restore backup.custom           # 恢复到新数据库"
            echo "  $0 pitr base.tar.gz '2024-01-01 12:00:00' /tmp/recovery"
            ;;
    esac
}

# 执行主函数
main "$@"