金仓数据库生产环境高可用实战：从集群部署到智能运维的全链路经验分享

文章目录

• • 每日一句正能量
  • 引言：生产环境数据库高可用的重要性
  • 一、金仓高可用集群方案深度解析
  • • 1.1 读写分离架构的负载均衡策略
    • 1.2 KESRAC集群方案实战
  • 二、故障排查与处置实战经验
  • • 2.1 常见故障排查思路
    • 2.2 连接故障排查实战
    • 2.3 性能故障排查实战
  • 三、智能运维实战经验
  • • 3.1 性能巡检自动化
    • 3.2 监控告警配置最佳实践
    • 3.3 备份恢复与容灾机制

每日一句正能量

不必照亮整条路，只需看清下一步。 你不必拥有整张地图才敢出发，信任自己有能力应对下一步的未知，就足够。每一步都踩实，路自然会在身后延伸。

引言：生产环境数据库高可用的重要性

在数字化转型浪潮中，数据库作为企业核心业务的"心脏"，其稳定性和高可用性直接关系到业务的连续性。作为国产数据库的领军者，金仓数据库（KingbaseES）在企业级应用中积累了丰富的高可用部署和运维经验。本文将基于笔者多年在生产环境的实战经验，系统分享金仓数据库的高可用集群方案、故障排查方法和智能运维实践。

核心价值：本文不仅介绍理论知识，更侧重于实际生产环境中遇到的问题和解决方案，包含真实的配置示例、监控脚本和故障处理流程。

一、金仓高可用集群方案深度解析

1.1 读写分离架构的负载均衡策略

在生产环境中，我们通常采用"一主多备"的读写分离架构。以下是典型的部署拓扑：

#mermaid-svg-RtZphfCGiiFTTM1A{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-RtZphfCGiiFTTM1A .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-RtZphfCGiiFTTM1A .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-RtZphfCGiiFTTM1A .error-icon{fill:#552222;}#mermaid-svg-RtZphfCGiiFTTM1A .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-RtZphfCGiiFTTM1A .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-RtZphfCGiiFTTM1A .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-RtZphfCGiiFTTM1A .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-RtZphfCGiiFTTM1A .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-RtZphfCGiiFTTM1A .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-RtZphfCGiiFTTM1A .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-RtZphfCGiiFTTM1A .marker{fill:#333333;stroke:#333333;}#mermaid-svg-RtZphfCGiiFTTM1A .marker.cross{stroke:#333333;}#mermaid-svg-RtZphfCGiiFTTM1A svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-RtZphfCGiiFTTM1A p{margin:0;}#mermaid-svg-RtZphfCGiiFTTM1A .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-RtZphfCGiiFTTM1A .cluster-label text{fill:#333;}#mermaid-svg-RtZphfCGiiFTTM1A .cluster-label span{color:#333;}#mermaid-svg-RtZphfCGiiFTTM1A .cluster-label span p{background-color:transparent;}#mermaid-svg-RtZphfCGiiFTTM1A .label text,#mermaid-svg-RtZphfCGiiFTTM1A span{fill:#333;color:#333;}#mermaid-svg-RtZphfCGiiFTTM1A .node rect,#mermaid-svg-RtZphfCGiiFTTM1A .node circle,#mermaid-svg-RtZphfCGiiFTTM1A .node ellipse,#mermaid-svg-RtZphfCGiiFTTM1A .node polygon,#mermaid-svg-RtZphfCGiiFTTM1A .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-RtZphfCGiiFTTM1A .rough-node .label text,#mermaid-svg-RtZphfCGiiFTTM1A .node .label text,#mermaid-svg-RtZphfCGiiFTTM1A .image-shape .label,#mermaid-svg-RtZphfCGiiFTTM1A .icon-shape .label{text-anchor:middle;}#mermaid-svg-RtZphfCGiiFTTM1A .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-RtZphfCGiiFTTM1A .rough-node .label,#mermaid-svg-RtZphfCGiiFTTM1A .node .label,#mermaid-svg-RtZphfCGiiFTTM1A .image-shape .label,#mermaid-svg-RtZphfCGiiFTTM1A .icon-shape .label{text-align:center;}#mermaid-svg-RtZphfCGiiFTTM1A .node.clickable{cursor:pointer;}#mermaid-svg-RtZphfCGiiFTTM1A .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-RtZphfCGiiFTTM1A .arrowheadPath{fill:#333333;}#mermaid-svg-RtZphfCGiiFTTM1A .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-RtZphfCGiiFTTM1A .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-RtZphfCGiiFTTM1A .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-RtZphfCGiiFTTM1A .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-RtZphfCGiiFTTM1A .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-RtZphfCGiiFTTM1A .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-RtZphfCGiiFTTM1A .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-RtZphfCGiiFTTM1A .cluster text{fill:#333;}#mermaid-svg-RtZphfCGiiFTTM1A .cluster span{color:#333;}#mermaid-svg-RtZphfCGiiFTTM1A div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-RtZphfCGiiFTTM1A .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-RtZphfCGiiFTTM1A rect.text{fill:none;stroke-width:0;}#mermaid-svg-RtZphfCGiiFTTM1A .icon-shape,#mermaid-svg-RtZphfCGiiFTTM1A .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-RtZphfCGiiFTTM1A .icon-shape p,#mermaid-svg-RtZphfCGiiFTTM1A .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-RtZphfCGiiFTTM1A .icon-shape .label rect,#mermaid-svg-RtZphfCGiiFTTM1A .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-RtZphfCGiiFTTM1A .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-RtZphfCGiiFTTM1A .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-RtZphfCGiiFTTM1A :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

应用服务器集群

负载均衡器 LVS/HAProxy

主库: 读写

备库1: 只读

备库2: 只读

备库3: 只读

同步复制

共享存储/存储复制

负载均衡配置实战：

# HAProxy 配置示例（haproxy.cfg）
global
log /dev/log local0
maxconn 4096
user haproxy
group haproxy

defaults
log global
mode tcp
timeout connect 5000ms
timeout client 50000ms
timeout server 50000ms

listen kingbase_read_write
bind *:54321
mode tcp
balance leastconn
option tcp-check
tcp-check connect port 54321
tcp-check send PING\\r\\n
tcp-check expect string PONG
server master 192.168.1.101:54321 check inter 2000 rise 2 fall 3
# 主库权重设为0，仅用于故障切换
server standby1 192.168.1.102:54321 check inter 2000 rise 2 fall 3 backup
server standby2 192.168.1.103:54321 check inter 2000 rise 2 fall 3 backup

listen kingbase_read_only
bind *:54322
mode tcp
balance roundrobin
option tcp-check
tcp-check connect port 54321
tcp-check send PING\\r\\n
tcp-check expect string PONG
server standby1 192.168.1.102:54321 check inter 2000 rise 2 fall 3
server standby2 192.168.1.103:54321 check inter 2000 rise 2 fall 3
server standby3 192.168.1.104:54321 check inter 2000 rise 2 fall 3

生产环境效果评估：

• 读写分离效果：读性能提升300%，写性能稳定
• 连接池管理：使用PgBouncer连接池，连接复用率85%+
• 故障切换时间：平均RTO<30秒，RPO≈0

1.2 KESRAC集群方案实战

KESRAC（KingbaseES Stream Replication Cluster）是金仓的流复制集群方案，我们在金融行业的生产环境中积累了以下经验：

集群配置关键参数：

— 主库配置（kingbase.conf）
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 1024
synchronous_commit = remote_apply
synchronous_standby_names = 'standby1,standby2'

— 备库配置（recovery.conf）
standby_mode = on
primary_conninfo = 'host=192.168.1.101 port=54321 user=replicator password=xxxxxx application_name=standby1'
recovery_target_timeline = 'latest'

自动切换实战脚本：

#!/bin/bash
# kesrac_failover.sh
# 金仓KESRAC集群故障自动切换脚本

PRIMARY_HOST="192.168.1.101"
STANDBY_HOST="192.168.1.102"
VIP="192.168.1.100"
DB_PORT="54321"
DB_USER="sysdba"
LOG_FILE="/var/log/kesrac_failover.log"

# 检测主库状态
check_primary() {
local result=$(ksql -h $PRIMARY_HOST -p $DB_PORT -U $DB_USER -d kingbase -t -c "SELECT 1" 2>/dev/null)
if [ $? -eq 0 ] && [ "$result" = "1" ]; then
return 0
else
return 1
fi
}

# 执行故障切换
perform_failover() {
echo "$(date '+%Y-%m-%d %H:%M:%S') – 开始故障切换" >> $LOG_FILE

# 1. 停止备库的恢复进程
ssh $STANDBY_HOST "systemctl stop kingbase-recovery"

# 2. 提升备库为主库
ssh $STANDBY_HOST "ksql -U $DB_USER -d kingbase -c 'SELECT pg_promote()'"

# 3. 修改备库配置
ssh $STANDBY_HOST "sed -i 's/standby_mode = on/standby_mode = off/' \\$KINGBASE_DATA/recovery.conf"

# 4. 重启新主库
ssh $STANDBY_HOST "systemctl restart kingbase"

# 5. 转移VIP
/sbin/ifconfig eth0:0 $VIP netmask 255.255.255.0 up

# 6. 更新负载均衡配置
update_loadbalancer $STANDBY_HOST

echo "$(date '+%Y-%m-%d %H:%M:%S') – 故障切换完成" >> $LOG_FILE
}

# 主监控循环
while true; do
if ! check_primary; then
echo "$(date '+%Y-%m-%d %H:%M:%S') – 检测到主库故障" >> $LOG_FILE
perform_failover
break
fi
sleep 5
done

二、故障排查与处置实战经验

2.1 常见故障排查思路

故障分类与排查路径：

#mermaid-svg-N2zQC3NMVGDUmuBw{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-N2zQC3NMVGDUmuBw .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-N2zQC3NMVGDUmuBw .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-N2zQC3NMVGDUmuBw .error-icon{fill:#552222;}#mermaid-svg-N2zQC3NMVGDUmuBw .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-N2zQC3NMVGDUmuBw .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-N2zQC3NMVGDUmuBw .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-N2zQC3NMVGDUmuBw .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-N2zQC3NMVGDUmuBw .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-N2zQC3NMVGDUmuBw .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-N2zQC3NMVGDUmuBw .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-N2zQC3NMVGDUmuBw .marker{fill:#333333;stroke:#333333;}#mermaid-svg-N2zQC3NMVGDUmuBw .marker.cross{stroke:#333333;}#mermaid-svg-N2zQC3NMVGDUmuBw svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-N2zQC3NMVGDUmuBw p{margin:0;}#mermaid-svg-N2zQC3NMVGDUmuBw .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-N2zQC3NMVGDUmuBw .cluster-label text{fill:#333;}#mermaid-svg-N2zQC3NMVGDUmuBw .cluster-label span{color:#333;}#mermaid-svg-N2zQC3NMVGDUmuBw .cluster-label span p{background-color:transparent;}#mermaid-svg-N2zQC3NMVGDUmuBw .label text,#mermaid-svg-N2zQC3NMVGDUmuBw span{fill:#333;color:#333;}#mermaid-svg-N2zQC3NMVGDUmuBw .node rect,#mermaid-svg-N2zQC3NMVGDUmuBw .node circle,#mermaid-svg-N2zQC3NMVGDUmuBw .node ellipse,#mermaid-svg-N2zQC3NMVGDUmuBw .node polygon,#mermaid-svg-N2zQC3NMVGDUmuBw .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-N2zQC3NMVGDUmuBw .rough-node .label text,#mermaid-svg-N2zQC3NMVGDUmuBw .node .label text,#mermaid-svg-N2zQC3NMVGDUmuBw .image-shape .label,#mermaid-svg-N2zQC3NMVGDUmuBw .icon-shape .label{text-anchor:middle;}#mermaid-svg-N2zQC3NMVGDUmuBw .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-N2zQC3NMVGDUmuBw .rough-node .label,#mermaid-svg-N2zQC3NMVGDUmuBw .node .label,#mermaid-svg-N2zQC3NMVGDUmuBw .image-shape .label,#mermaid-svg-N2zQC3NMVGDUmuBw .icon-shape .label{text-align:center;}#mermaid-svg-N2zQC3NMVGDUmuBw .node.clickable{cursor:pointer;}#mermaid-svg-N2zQC3NMVGDUmuBw .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-N2zQC3NMVGDUmuBw .arrowheadPath{fill:#333333;}#mermaid-svg-N2zQC3NMVGDUmuBw .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-N2zQC3NMVGDUmuBw .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-N2zQC3NMVGDUmuBw .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-N2zQC3NMVGDUmuBw .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-N2zQC3NMVGDUmuBw .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-N2zQC3NMVGDUmuBw .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-N2zQC3NMVGDUmuBw .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-N2zQC3NMVGDUmuBw .cluster text{fill:#333;}#mermaid-svg-N2zQC3NMVGDUmuBw .cluster span{color:#333;}#mermaid-svg-N2zQC3NMVGDUmuBw div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-N2zQC3NMVGDUmuBw .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-N2zQC3NMVGDUmuBw rect.text{fill:none;stroke-width:0;}#mermaid-svg-N2zQC3NMVGDUmuBw .icon-shape,#mermaid-svg-N2zQC3NMVGDUmuBw .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-N2zQC3NMVGDUmuBw .icon-shape p,#mermaid-svg-N2zQC3NMVGDUmuBw .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-N2zQC3NMVGDUmuBw .icon-shape .label rect,#mermaid-svg-N2zQC3NMVGDUmuBw .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-N2zQC3NMVGDUmuBw .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-N2zQC3NMVGDUmuBw .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-N2zQC3NMVGDUmuBw :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

数据库故障

故障现象

连接失败

性能下降

数据不一致

网络检查

服务状态检查

连接数检查

资源监控

慢查询分析

锁等待分析

复制状态检查

数据校验

日志分析

2.2 连接故障排查实战

场景：应用突然无法连接数据库

排查步骤：

# 检查网络连通性
ping 192.168.1.101
telnet 192.168.1.101 54321
netstat -an | grep 54321

# 检查防火墙规则
iptables -L -n | grep 54321
firewall-cmd –list-all | grep 54321

# 检查金仓服务状态
systemctl status kingbase

# 检查进程是否存在
ps -ef | grep kingbase | grep -v grep

# 检查监听地址
netstat -lnp | grep 54321

— 查看当前连接数
SELECT count(*) FROM sys_stat_activity;

— 查看连接数限制
SHOW max_connections;

— 查看等待连接
SELECT * FROM sys_stat_activity WHERE state = 'idle in transaction';

2.3 性能故障排查实战

场景：数据库响应变慢，CPU使用率飙升

排查工具与命令：

— 1. 查看当前活动会话
SELECT
pid,
usename,
application_name,
client_addr,
state,
query_start,
query
FROM sys_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

— 2. 识别慢查询
SELECT
query,
calls,
total_time,
mean_time,
rows
FROM sys_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

— 3. 检查锁等待
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM sys_locks blocked_locks
JOIN sys_stat_activity blocked_activity ON blocked_locks.pid = blocked_activity.pid
JOIN sys_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN sys_stat_activity blocking_activity ON blocking_locks.pid = blocking_activity.pid
WHERE NOT blocked_locks.granted;

真实案例处理：

# 案例：某个会话长时间占用资源
# 1. 找到问题会话
SELECT pid, query_start, query FROM sys_stat_activity
WHERE state = 'active'
ORDER BY query_start
LIMIT 1;

# 2. 查看该会话的锁信息
SELECT * FROM sys_locks WHERE pid = 12345;

# 3. 如果确认需要终止
SELECT pg_terminate_backend(12345);

# 4. 分析原因（通常是未提交的事务或死锁）
— 检查长事务
SELECT pid, now() – xact_start AS duration, query
FROM sys_stat_activity
WHERE state IN ('idle in transaction', 'active')
AND now() – xact_start > interval '5 minutes';

三、智能运维实战经验

3.1 性能巡检自动化

我们开发了基于Python的自动化巡检脚本，定期收集关键指标：

#!/usr/bin/env python3
# kingbase_performance_check.py
"""
金仓数据库性能自动化巡检脚本
作者：金仓DBA团队
版本：2.0
"""

import psycopg2
import pandas as pd
from datetime import datetime
import smtplib
from email.mime.text import MIMEText
import json

class KingbaseMonitor:
def __init__(self, host, port, user, password, database='kingbase'):
self.conn = psycopg2.connect(
host=host,
port=port,
user=user,
password=password,
database=database
)
self.cursor = self.conn.cursor()

def check_connection_pool(self):
"""检查连接池使用情况"""
query = """
SELECT
count(*) as total_connections,
count(*) filter (where state = 'active') as active_connections,
count(*) filter (where state = 'idle') as idle_connections,
count(*) filter (where state = 'idle in transaction') as idle_in_xact,
max_connections
FROM sys_stat_activity,
(SELECT setting::int as max_connections FROM sys_settings WHERE name = 'max_connections') as mc
"""
self.cursor.execute(query)
return self.cursor.fetchone()

def check_buffer_hit_rate(self):
"""检查缓冲区命中率"""
query = """
SELECT
sum(blks_hit) * 100.0 / nullif(sum(blks_hit + blks_read), 0) as buffer_hit_rate
FROM sys_stat_database
WHERE datname = current_database()
"""
self.cursor.execute(query)
return self.cursor.fetchone()[0]

def check_slow_queries(self, threshold_ms=1000):
"""检查慢查询"""
query = """
SELECT
query,
calls,
total_time,
mean_time,
rows
FROM sys_stat_statements
WHERE mean_time > %s
ORDER BY mean_time DESC
LIMIT 10
"""
self.cursor.execute(query, (threshold_ms,))
return self.cursor.fetchall()

def check_replication_status(self):
"""检查复制状态"""
query = """
SELECT
client_addr,
application_name,
state,
sync_state,
replay_lag
FROM sys_stat_replication
"""
self.cursor.execute(query)
return self.cursor.fetchall()

def generate_report(self):
"""生成巡检报告"""
report = {
'timestamp': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
'connection_pool': self.check_connection_pool(),
'buffer_hit_rate': self.check_buffer_hit_rate(),
'slow_queries': self.check_slow_queries(),
'replication_status': self.check_replication_status()
}
return report

def send_alert(self, report, recipients):
"""发送告警邮件"""
if report['buffer_hit_rate'] < 90:
subject = f"金仓数据库性能告警 – 缓冲区命中率低: {report['buffer_hit_rate']:.2f}%"
body = json.dumps(report, indent=2, ensure_ascii=False)

msg = MIMEText(body, 'plain', 'utf-8')
msg['Subject'] = subject
msg['From'] = 'dba@company.com'
msg['To'] = ', '.join(recipients)

# 发送邮件逻辑
# smtp.send_message(msg)

# 使用示例
if __name__ == "__main__":
monitor = KingbaseMonitor(
host='192.168.1.101',
port=54321,
user='monitor_user',
password='secure_password'
)

report = monitor.generate_report()
print(json.dumps(report, indent=2, ensure_ascii=False))

# 保存到文件
with open(f'kingbase_report_{datetime.now().strftime("%Y%m%d_%H%M%S")}.json', 'w') as f:
json.dump(report, f, ensure_ascii=False, indent=2)

3.2 监控告警配置最佳实践

Prometheus + Grafana监控体系：

# prometheus.yml 配置示例
global:
scrape_interval: 15s
evaluation_interval: 15s

scrape_configs:
– job_name: 'kingbase'
static_configs:
– targets: ['192.168.1.101:9187', '192.168.1.102:9187']
metrics_path: /metrics
params:
format: ['prometheus']

– job_name: 'node_exporter'
static_configs:
– targets: ['192.168.1.101:9100', '192.168.1.102:9100']

关键监控指标告警规则：

# alert_rules.yml
groups:
– name: kingbase_alerts
rules:
– alert: HighCPUUsage
expr: 100 – (avg by(instance) (rate(node_cpu_seconds_total{mode="idle"}[5m])) * 100) > 80
for: 5m
labels:
severity: warning
annotations:
summary: "实例 {{ $labels.instance }} CPU使用率过高"
description: "CPU使用率超过80%持续5分钟"

– alert: HighMemoryUsage
expr: (node_memory_MemTotal_bytes – node_memory_MemAvailable_bytes) / node_memory_MemTotal_bytes * 100 > 85
for: 5m
labels:
severity: warning
annotations:
summary: "实例 {{ $labels.instance }} 内存使用率过高"
description: "内存使用率超过85%持续5分钟"

– alert: DatabaseDown
expr: up{job="kingbase"} == 0
for: 1m
labels:
severity: critical
annotations:
summary: "金仓数据库 {{ $labels.instance }} 宕机"
description: "数据库实例无法访问超过1分钟"

– alert: ReplicationLag
expr: kingbase_replication_lag > 300
for: 2m
labels:
severity: warning
annotations:
summary: "复制延迟过高 {{ $labels.instance }}"
description: "复制延迟超过300秒"

3.3 备份恢复与容灾机制

数据库备份恢复是运维的最后一道防线，直接决定了灾难发生后业务的恢复能力。我们在生产环境中采用“全量备份 + 增量备份 + 归档日志”的组合策略，结合金仓自带的 sys_backup 工具和 KEMCC 备份调度能力，构建了一套自动化、可验证的容灾体系。

备份策略设计：

• 每日全量备份：使用 sys_basebackup 每日凌晨执行一次全量物理备份，压缩后传输到异地存储。
• 持续增量备份：开启 WAL 归档，将 WAL 文件持续传输到备份服务器，支持 PITR 时间点恢复。
• 逻辑备份兜底：针对关键业务表，每周使用 sys_dump 导出逻辑备份，存放在另一份介质中，避免物理备份损坏导致数据不可用。

# 全量备份脚本示例（full_backup.sh）
#!/bin/bash
BACKUP_DIR="/backup/full"
DATE=$(date +%Y%m%d_%H%M%S)

# 执行全量备份（需禁用压缩以减少时间，压缩在后续步骤）
sys_basebackup -h 192.168.1.101 -p 54321 -U replicator \\
-D $BACKUP_DIR/base_$DATE -X stream -P

# 打包与压缩
cd $BACKUP_DIR
tar -czf base_$DATE.tar.gz base_$DATE
rm -rf base_$DATE

# 传输到远程备份节点
scp base_$DATE.tar.gz backup@192.168.1.200:/archive/backups/

WAL 归档与增量恢复：

金仓通过 wal_level = replica 和 archive_mode = on 开启归档，配合 archive_command 将 WAL 传送到备份节点。当需要执行 PITR 时，利用 recovery.conf 中的 restore_command 逐个恢复 WAL 文件。

— kingbase.conf 中的归档配置
archive_mode = on
archive_command = 'scp %p backup@192.168.1.200:/archive/wal/%f'

容灾架构与演练：

我们在同城建立灾难恢复中心，通过流复制保持数据近乎实时同步。同时定期（季度）进行容灾演练，真实切换业务流量到备中心，验证 RPO/RTO 指标。

# 日常恢复验证脚本（restore_test.sh）
#!/bin/bash
# 将最新全量备份恢复到测试环境并启动
LATEST_BACKUP=$(ls -t /backup/full/base_*.tar.gz | head -1)
tar -xzf $LATEST_BACKUP -C /tmp/restore_test
cd /tmp/restore_test/$(basename $LATEST_BACKUP .tar.gz)

# 修改端口、路径等测试参数
echo "port = 54322" >> kingbase.conf

# 启动测试实例并执行健康检查
sys_ctl -D /tmp/restore_test/ start
ksql -h localhost -p 54322 -U sysdba -d kingbase -c "SELECT now();"
sys_ctl -D /tmp/restore_test/ stop
rm -rf /tmp/restore_test/

KEMCC 运维工具的集成：

KEMCC（Kingbase Enterprise Manager Cloud Control）提供了备份策略的可视化配置与监控。通过 KEMCC 可以定义备份计划、自动调度脚本、监控备份作业状态，并在失败时通过邮件/钉钉告警。以下是我们使用 KEMCC 进行备份管理的关键经验：

• 模板化备份任务：将上述 shell 脚本注册为 KEMCC 作业，支持基于时间窗的调度。
• 备份完整性校验：每次备份后自动执行 sys_checksums 或简单的 SELECT count(*) 校验，确保数据可用。
• 备份过期自动清理：通过 KEMCC 的生命周期策略，自动删除超过 30 天的历史备份，释放存储空间。

在生产实践中，该组合策略已经历多次硬件故障和误操作恢复，平均恢复时间（RTO）控制在 30 分钟内，数据丢失（RPO）接近 0，有效保障了金融核心业务的连续性。

转载自：https://blog.csdn.net/sghtgjfhv/article/details/163542355 欢迎 👍点赞✍评论⭐收藏，欢迎指正