LOG_DIR=”/opengauss/log/connection”
mkdir -p $LOG_DIR
LOG_FILE=”$LOG_DIR/connection_monitor_$(date ‘+%Y%m%d’).log”

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

check_connection_status() {
log “=== 连接状态监控 ===”

# 当前连接数
total_connections=$(gsql -h 192.168.1.10 -d fgedudb -U fgedu -t -c ”
SELECT count(*) FROM pg_stat_activity;
” | tr -d ‘ ‘)
log “当前连接数: $total_connections”

# 最大连接数
max_connections=$(gsql -h 192.168.1.10 -d fgedudb -U fgedu -t -c ”
SHOW max_connections;
” | tr -d ‘ ‘)
log “最大连接数: $max_connections”

# 连接状态分布
log “连接状态分布:
$(gsql -h 192.168.1.10 -d fgedudb -U fgedu -t -c ”
SELECT
state,
count(*)
FROM pg_stat_activity
GROUP BY state;
“)”

# 连接来源
log “连接来源:
$(gsql -h 192.168.1.10 -d fgedudb -U fgedu -t -c ”
SELECT
client_addr,
count(*)
FROM pg_stat_activity
GROUP BY client_addr
ORDER BY count(*) DESC;
“)”
}

check_idle_connections() {
log “=== 空闲连接监控 ===”

# 长时间空闲连接
idle_connections=$(gsql -h 192.168.1.10 -d fgedudb -U fgedu -t -c ”
SELECT
pid,
usename,
application_name,
client_addr,
now() – backend_start AS connected_time
FROM pg_stat_activity
WHERE state = ‘idle’ AND now() – backend_start > interval ’10 minutes’
ORDER BY connected_time DESC;
“)

if [ -n “$idle_connections” ]; then
log “发现长时间空闲连接:
$idle_connections”

# 终止长时间空闲连接
log “终止长时间空闲连接…”
gsql -h 192.168.1.10 -d fgedudb -U fgedu -t -c ”
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = ‘idle’ AND now() – backend_start > interval ’10 minutes’;
”
else
log “未发现长时间空闲连接”
fi
}

check_connection_parameters() {
log “=== 连接参数监控 ===”

# 连接相关参数
log “连接相关参数:
$(gsql -h 192.168.1.10 -d fgedudb -U fgedu -t -c ”
SHOW max_connections;
SHOW superuser_reserved_connections;
SHOW idle_in_transaction_session_timeout;
SHOW statement_timeout;
SHOW connect_timeout;
“)”
}

main() {
log “开始连接监控检查…”
check_connection_status
check_idle_connections
check_connection_parameters
log “连接监控检查完成”
}

main