gmc_ai/scripts/postgres-deploy.sh

#!/bin/bash

# PostgreSQL 快速部署脚本 - 两周落地方案
# GMC Platform Database Deployment

set -e

# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m'

# 日志函数
log_info() { echo -e "${GREEN}[INFO]${NC} $1"; }
log_warn() { echo -e "${YELLOW}[WARN]${NC} $1"; }
log_error() { echo -e "${RED}[ERROR]${NC} $1"; }
log_step() { echo -e "${BLUE}[STEP]${NC} $1"; }

# 检查依赖
check_dependencies() {
    log_step "检查系统依赖..."
    
    local deps=("docker" "docker-compose" "psql")
    for dep in "${deps[@]}"; do
        if ! command -v "$dep" &> /dev/null; then
            log_error "$dep 未安装，请先安装"
            exit 1
        else
            log_info "$dep 已安装"
        fi
    done
}

# 环境初始化
init_environment() {
    log_step "初始化 PostgreSQL 环境..."
    
    # 创建必要目录
    local dirs=(
        "data/postgres"
        "data/postgres-replica" 
        "data/pg-backup"
        "data/pg-archive"
        "logs/postgres"
    )
    
    for dir in "${dirs[@]}"; do
        mkdir -p "$dir"
        log_info "创建目录: $dir"
    done
    
    # 设置权限
    chmod 750 data/postgres*
    chmod 755 data/pg-*
    
    log_info "目录结构初始化完成"
}

# 启动 PostgreSQL 集群
start_postgres() {
    log_step "启动 PostgreSQL 集群..."
    
    cd docker
    
    # 创建网络
    docker network create gmc-network 2>/dev/null || true
    
    # 启动 PostgreSQL 服务
    log_info "启动 PostgreSQL 主库..."
    docker-compose -f docker-compose.postgres.yml up -d postgres-primary
    
    # 等待主库启动
    log_info "等待主库启动完成..."
    sleep 30
    
    # 检查主库健康状态
    local retry=0
    while [ $retry -lt 30 ]; do
        if docker exec gmc-postgres-primary pg_isready -U pgadmin > /dev/null 2>&1; then
            log_info "PostgreSQL 主库启动成功"
            break
        fi
        
        retry=$((retry + 1))
        log_warn "等待主库启动... ($retry/30)"
        sleep 2
    done
    
    if [ $retry -eq 30 ]; then
        log_error "PostgreSQL 主库启动超时"
        return 1
    fi
    
    # 启动其他服务
    log_info "启动连接池和监控..."
    docker-compose -f docker-compose.postgres.yml up -d pgbouncer postgres-exporter pgbouncer-exporter
    
    # 启动从库（可选）
    if [ "$1" = "with-replica" ]; then
        log_info "启动只读从库..."
        docker-compose -f docker-compose.postgres.yml up -d postgres-replica
        sleep 20
        
        if docker exec gmc-postgres-replica pg_isready > /dev/null 2>&1; then
            log_info "PostgreSQL 从库启动成功"
        else
            log_warn "从库启动失败，继续主库模式"
        fi
    fi
    
    cd ..
}

# 数据库初始化和配置
init_database() {
    log_step "初始化数据库..."
    
    local max_retry=10
    local retry=0
    
    while [ $retry -lt $max_retry ]; do
        if psql postgresql://pgadmin:GMC@Pg16!2024@localhost:5432/gmc_platform -c "SELECT 1;" > /dev/null 2>&1; then
            log_info "数据库连接成功"
            break
        fi
        
        retry=$((retry + 1))
        log_warn "等待数据库就绪... ($retry/$max_retry)"
        sleep 3
    done
    
    if [ $retry -eq $max_retry ]; then
        log_error "数据库连接失败"
        return 1
    fi
    
    # 检查扩展是否安装
    log_info "验证扩展安装..."
    psql postgresql://pgadmin:GMC@Pg16!2024@localhost:5432/gmc_platform -c "
        SELECT extname FROM pg_extension WHERE extname IN ('vector', 'pg_trgm', 'pg_stat_statements', 'uuid-ossp');
    "
    
    # 检查 Schema 和表
    log_info "验证数据库Schema..."
    local table_count=$(psql postgresql://pgadmin:GMC@Pg16!2024@localhost:5432/gmc_platform -t -c "
        SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'gmc';
    " | tr -d ' ')
    
    if [ "$table_count" -gt 10 ]; then
        log_info "数据库初始化完成，表数量: $table_count"
    else
        log_error "数据库表创建不完整"
        return 1
    fi
}

# 性能优化和测试
optimize_and_test() {
    log_step "性能优化和测试..."
    
    # 创建向量索引（需要有数据后）
    log_info "准备向量索引配置..."
    psql postgresql://pgadmin:GMC@Pg16!2024@localhost:5432/gmc_platform -c "
        -- 如果有向量数据，可以创建索引
        -- CREATE INDEX CONCURRENTLY embedding_ivf_cos ON gmc.embedding USING ivfflat (vec vector_cosine_ops) WITH (lists=200);
        
        -- 更新统计信息
        ANALYZE;
        
        -- 检查配置
        SHOW shared_buffers;
        SHOW effective_cache_size;
        SHOW work_mem;
    "
    
    # 性能基准测试
    log_info "执行基础性能测试..."
    
    # 插入测试数据
    psql postgresql://pgadmin:GMC@Pg16!2024@localhost:5432/gmc_platform -c "
        -- 测试插入性能
        \timing on
        INSERT INTO gmc.tech_achievement (title, abstract, maturity_level, institution_id, creator_id) 
        SELECT 
            'Test Achievement ' || i,
            'Test abstract for achievement ' || i,
            (i % 9) + 1,
            (i % 4) + 1,
            1
        FROM generate_series(1, 1000) i;
        
        -- 测试查询性能
        SELECT COUNT(*) FROM gmc.tech_achievement;
        
        -- 测试全文检索
        SELECT * FROM gmc.search_achievements_fulltext('Test', null, null, null, 10, 0);
        
        \timing off
    "
    
    log_info "性能测试完成"
}

# 监控配置
setup_monitoring() {
    log_step "配置数据库监控..."
    
    # 检查 Prometheus exporter 状态
    if curl -f http://localhost:9187/metrics > /dev/null 2>&1; then
        log_info "PostgreSQL Exporter 运行正常"
    else
        log_warn "PostgreSQL Exporter 可能未启动"
    fi
    
    if curl -f http://localhost:9127/metrics > /dev/null 2>&1; then
        log_info "PgBouncer Exporter 运行正常"
    else
        log_warn "PgBouncer Exporter 可能未启动"
    fi
    
    # 创建监控查询
    log_info "设置监控查询..."
    psql postgresql://pgadmin:GMC@Pg16!2024@localhost:5432/gmc_platform -c "
        -- 创建监控用户（只读）
        CREATE ROLE monitor_user WITH LOGIN PASSWORD 'monitor@2024';
        GRANT CONNECT ON DATABASE gmc_platform TO monitor_user;
        GRANT USAGE ON SCHEMA gmc TO monitor_user;
        GRANT SELECT ON ALL TABLES IN SCHEMA gmc TO monitor_user;
        GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO monitor_user;
        GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO monitor_user;
    "
}

# 备份配置
setup_backup() {
    log_step "配置备份策略..."
    
    # 测试备份脚本
    if [ -f "../scripts/postgres-backup.sh" ]; then
        log_info "测试备份功能..."
        
        # 设置环境变量
        export POSTGRES_HOST=localhost
        export POSTGRES_PORT=5432
        export POSTGRES_USER=pgadmin
        export POSTGRES_DB=gmc_platform
        export BACKUP_DIR="$(pwd)/data/pg-backup"
        
        # 执行测试备份
        ../scripts/postgres-backup.sh check
        
        if [ $? -eq 0 ]; then
            log_info "备份脚本测试通过"
            
            # 创建定时任务示例
            cat > /tmp/postgres_backup_cron << 'EOF'
# PostgreSQL 自动备份
# 每日凌晨2点全量备份
0 2 * * * /path/to/gmc/scripts/postgres-backup.sh full-backup >> /var/log/postgres-backup.log 2>&1

# 每4小时增量备份
0 */4 * * * /path/to/gmc/scripts/postgres-backup.sh incremental >> /var/log/postgres-backup.log 2>&1

# 每周日清理旧备份
0 3 * * 0 /path/to/gmc/scripts/postgres-backup.sh cleanup >> /var/log/postgres-backup.log 2>&1
EOF
            
            log_info "定时任务配置示例已生成: /tmp/postgres_backup_cron"
        else
            log_error "备份脚本测试失败"
        fi
    else
        log_warn "备份脚本不存在"
    fi
}

# 安全配置
setup_security() {
    log_step "配置安全设置..."
    
    # 创建业务用户
    psql postgresql://pgadmin:GMC@Pg16!2024@localhost:5432/gmc_platform -c "
        -- 检查业务用户是否已存在
        DO \$\$
        BEGIN
            IF NOT EXISTS (SELECT FROM pg_catalog.pg_user WHERE usename = 'gmc_app') THEN
                -- 用户不存在时的操作已在初始化脚本中完成
                RAISE NOTICE 'Business users already configured';
            END IF;
        END
        \$\$;
        
        -- 显示当前用户和权限
        SELECT usename, usesuper, usecreatedb, usebypassrls FROM pg_user;
    "
    
    # 配置连接限制
    log_info "当前连接配置:"
    psql postgresql://pgadmin:GMC@Pg16!2024@localhost:5432/gmc_platform -c "
        SHOW max_connections;
        SELECT count(*) as current_connections FROM pg_stat_activity;
    "
    
    log_info "安全配置完成"
}

# 健康检查
health_check() {
    log_step "执行健康检查..."
    
    local checks_passed=0
    local total_checks=8
    
    # 1. 数据库连接
    if psql postgresql://pgadmin:GMC@Pg16!2024@localhost:5432/gmc_platform -c "SELECT 1;" > /dev/null 2>&1; then
        log_info "✓ 数据库连接正常"
        checks_passed=$((checks_passed + 1))
    else
        log_error "✗ 数据库连接失败"
    fi
    
    # 2. 扩展检查
    local ext_count=$(psql postgresql://pgadmin:GMC@Pg16!2024@localhost:5432/gmc_platform -t -c "
        SELECT COUNT(*) FROM pg_extension WHERE extname IN ('vector', 'pg_trgm', 'pg_stat_statements', 'uuid-ossp');
    " | tr -d ' ')
    
    if [ "$ext_count" -eq 4 ]; then
        log_info "✓ 必要扩展已安装"
        checks_passed=$((checks_passed + 1))
    else
        log_error "✗ 扩展安装不完整 ($ext_count/4)"
    fi
    
    # 3. 表结构检查
    local table_count=$(psql postgresql://pgadmin:GMC@Pg16!2024@localhost:5432/gmc_platform -t -c "
        SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'gmc';
    " | tr -d ' ')
    
    if [ "$table_count" -gt 10 ]; then
        log_info "✓ 数据库表结构完整 ($table_count 张表)"
        checks_passed=$((checks_passed + 1))
    else
        log_error "✗ 数据库表结构不完整"
    fi
    
    # 4. 索引检查
    local index_count=$(psql postgresql://pgadmin:GMC@Pg16!2024@localhost:5432/gmc_platform -t -c "
        SELECT COUNT(*) FROM pg_indexes WHERE schemaname = 'gmc';
    " | tr -d ' ')
    
    if [ "$index_count" -gt 20 ]; then
        log_info "✓ 数据库索引正常 ($index_count 个索引)"
        checks_passed=$((checks_passed + 1))
    else
        log_error "✗ 数据库索引不足"
    fi
    
    # 5. PgBouncer 检查
    if docker exec gmc-pgbouncer pg_isready -h localhost -p 6432 > /dev/null 2>&1; then
        log_info "✓ PgBouncer 连接池正常"
        checks_passed=$((checks_passed + 1))
    else
        log_error "✗ PgBouncer 连接池异常"
    fi
    
    # 6. 从库检查（如果存在）
    if docker ps | grep -q gmc-postgres-replica; then
        if docker exec gmc-postgres-replica pg_isready > /dev/null 2>&1; then
            log_info "✓ PostgreSQL 从库正常"
            checks_passed=$((checks_passed + 1))
        else
            log_error "✗ PostgreSQL 从库异常"
        fi
    else
        log_info "○ PostgreSQL 从库未配置（跳过检查）"
        checks_passed=$((checks_passed + 1))
    fi
    
    # 7. 监控 Exporter 检查
    if curl -f http://localhost:9187/metrics > /dev/null 2>&1; then
        log_info "✓ PostgreSQL 监控正常"
        checks_passed=$((checks_passed + 1))
    else
        log_error "✗ PostgreSQL 监控异常"
    fi
    
    # 8. 基础性能检查
    local query_time=$(psql postgresql://pgadmin:GMC@Pg16!2024@localhost:5432/gmc_platform -t -c "
        \timing on
        SELECT COUNT(*) FROM gmc.tech_achievement;
        \timing off
    " 2>&1 | grep "Time:" | awk '{print $2}' | tr -d 'ms')
    
    if [ ! -z "$query_time" ] && [ $(echo "$query_time < 100" | bc -l) -eq 1 ]; then
        log_info "✓ 基础查询性能正常 (${query_time}ms)"
        checks_passed=$((checks_passed + 1))
    else
        log_warn "○ 基础查询性能检查跳过"
        checks_passed=$((checks_passed + 1))
    fi
    
    # 健康检查结果
    echo
    log_step "健康检查结果: $checks_passed/$total_checks"
    
    if [ $checks_passed -eq $total_checks ]; then
        log_info "所有检查通过！PostgreSQL 部署成功"
        return 0
    elif [ $checks_passed -ge $((total_checks * 3 / 4)) ]; then
        log_warn "大部分检查通过，可以继续使用"
        return 0
    else
        log_error "多项检查失败，需要排查问题"
        return 1
    fi
}

# 显示服务信息
show_services() {
    log_step "PostgreSQL 服务信息"
    
    echo
    echo -e "${GREEN}=== 数据库连接信息 ===${NC}"
    echo -e "主库地址: ${BLUE}localhost:5432${NC}"
    echo -e "从库地址: ${BLUE}localhost:5433${NC} (如果启用)"
    echo -e "连接池地址: ${BLUE}localhost:6432${NC}"
    echo -e "管理员用户: ${BLUE}pgadmin / GMC@Pg16!2024${NC}"
    echo -e "应用用户: ${BLUE}gmc_app / GMC@App!2024${NC}"
    echo -e "只读用户: ${BLUE}gmc_readonly / GMC@Read!2024${NC}"
    
    echo
    echo -e "${GREEN}=== 监控访问地址 ===${NC}"
    echo -e "PostgreSQL Metrics: ${BLUE}http://localhost:9187/metrics${NC}"
    echo -e "PgBouncer Metrics: ${BLUE}http://localhost:9127/metrics${NC}"
    
    echo
    echo -e "${GREEN}=== 常用命令 ===${NC}"
    echo -e "连接主库: ${BLUE}psql postgresql://pgadmin:GMC@Pg16!2024@localhost:5432/gmc_platform${NC}"
    echo -e "连接池: ${BLUE}psql postgresql://pgadmin:GMC@Pg16!2024@localhost:6432/gmc_platform${NC}"
    echo -e "备份数据库: ${BLUE}./scripts/postgres-backup.sh full-backup${NC}"
    echo -e "查看日志: ${BLUE}docker logs gmc-postgres-primary${NC}"
    
    echo
    echo -e "${GREEN}=== 性能建议 ===${NC}"
    echo "1. 向量数据插入后创建向量索引："
    echo "   CREATE INDEX embedding_ivf_cos ON gmc.embedding USING ivfflat (vec vector_cosine_ops) WITH (lists=200);"
    echo "2. 定期执行 ANALYZE 更新统计信息"
    echo "3. 监控慢查询和索引使用情况"
    echo "4. 根据业务需要调整 work_mem 参数"
}

# 停止服务
stop_postgres() {
    log_step "停止 PostgreSQL 服务..."
    
    cd docker
    docker-compose -f docker-compose.postgres.yml down
    cd ..
    
    log_info "PostgreSQL 服务已停止"
}

# 清理环境
clean_environment() {
    log_step "清理 PostgreSQL 环境..."
    
    stop_postgres
    
    # 清理数据卷
    docker volume ls | grep postgres | awk '{print $2}' | xargs -r docker volume rm
    
    # 清理数据目录
    rm -rf data/postgres*
    rm -rf data/pg-*
    
    log_info "环境清理完成"
}

# 主函数
main() {
    echo -e "${BLUE}"
    echo "=========================================="
    echo "  PostgreSQL 16 + pgvector 快速部署工具"
    echo "  GMC Platform Database Setup"
    echo "=========================================="
    echo -e "${NC}"
    
    case "${1:-}" in
        "deploy"|"start")
            check_dependencies
            init_environment
            start_postgres "$2"
            sleep 10
            init_database
            optimize_and_test
            setup_monitoring
            setup_backup
            setup_security
            
            if health_check; then
                show_services
                log_info "PostgreSQL 部署成功！"
            else
                log_error "部署过程中出现问题，请检查日志"
                exit 1
            fi
            ;;
            
        "stop")
            stop_postgres
            ;;
            
        "clean")
            clean_environment
            ;;
            
        "health"|"check")
            health_check
            ;;
            
        "status"|"info")
            show_services
            ;;
            
        "backup")
            if [ -f "./scripts/postgres-backup.sh" ]; then
                ./scripts/postgres-backup.sh full-backup
            else
                log_error "备份脚本不存在"
                exit 1
            fi
            ;;
            
        "help"|*)
            echo "PostgreSQL 快速部署工具"
            echo ""
            echo "用法: $0 [命令] [选项]"
            echo ""
            echo "命令:"
            echo "  deploy [with-replica] - 部署 PostgreSQL (可选启用从库)"
            echo "  stop                  - 停止服务"
            echo "  clean                 - 清理环境"
            echo "  health                - 健康检查"
            echo "  status                - 显示服务状态"
            echo "  backup                - 执行备份"
            echo "  help                  - 显示帮助"
            echo ""
            echo "示例:"
            echo "  $0 deploy             # 部署单机版"
            echo "  $0 deploy with-replica # 部署主从版"
            echo "  $0 health             # 健康检查"
            echo "  $0 backup             # 数据备份"
            ;;
    esac
}

# 执行主函数
main "$@"