数据一致性故障处理脚本示例

#!/bin/bash
# data_consistency_check.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

# 定义变量
PRIMARY_HOST="192.168.1.10"
STANDBY_HOST="192.168.2.10"
DB_USER="fgedu"
DB_PASS="Fgedu@123"
DB_NAME="fgedudb"

# 检查复制状态
check_replication() {
    echo "检查复制状态..."
    ssh $DB_USER@$PRIMARY_HOST "gsql -U $DB_USER -d $DB_NAME -c 'SELECT application_name, state, sync_state, pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) as lag FROM pg_stat_replication; 
'"
}

# 验证表数据一致性
verify_table_consistency() {
    local table_name=$1
    echo "验证表 $table_name 数据一致性..."
    
    # 获取主库数据量
    primary_count=$(ssh $DB_USER@$PRIMARY_HOST "gsql -U $DB_USER -d $DB_NAME -t -c "SELECT COUNT(*) FROM fgedu.$table_name; 
")
    
    # 获取备库数据量
    standby_count=$(ssh $DB_USER@$STANDBY_HOST "gsql -U $DB_USER -d $DB_NAME -t -c "SELECT COUNT(*) FROM fgedu.$table_name; 
")
    
    # 比较数据量
    if [ "$primary_count" == "$standby_count" ]; then
        echo "表 $table_name 数据一致"
        return 0
    else
        echo "表 $table_name 数据不一致：主库 $primary_count，备库 $standby_count"
        return 1
    fi
}

# 验证关键表数据一致性
verify_all_tables() {
    echo "验证所有关键表数据一致性..."
    local tables=("users" "orders" "products" "inventory" "transactions")
    local inconsistent=0
    
    for table in "${tables[@]}"; do
        verify_table_consistency $table
        if [ $? -ne 0 ]; then
            inconsistent=1
        fi
    done
    
    if [ $inconsistent -eq 0 ]; then
        echo "所有关键表数据一致"
    else
        echo "存在数据不一致的表"
    fi
}

# 检查WAL日志应用状态
check_wal_apply() {
    echo "检查WAL日志应用状态..."
    ssh $DB_USER@$STANDBY_HOST "gsql -U $DB_USER -d $DB_NAME -c 'SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) as replay_lag; 
'"
}

# 主流程
echo "=== 数据一致性检查开始 ==="

# 检查复制状态
check_replication

# 验证表数据一致性
verify_all_tables

# 检查WAL日志应用状态
check_wal_apply

echo "=== 数据一致性检查完成 ==="