外观
PostgreSQL 巡检报告生成
巡检报告的重要性
PostgreSQL巡检报告是数据库运维工作的重要组成部分，它能够：
- 定期评估数据库的健康状况
- 及时发现潜在问题和性能瓶颈
- 提供数据支持，辅助决策制定
- 记录数据库的历史状态，便于趋势分析
- 满足合规性要求，提供审计依据
巡检报告核心内容
1. 基本信息
- 报告标题：PostgreSQL 数据库巡检报告
- 报告编号：XX-PG-YYYYMMDD-001
- 巡检时间：YYYY-MM-DD HH:MM:SS
- 巡检对象：数据库实例名称/IP地址
- 数据库版本：PostgreSQL X.X.X
- 巡检人员：XXX
- 报告状态：初稿/正式版
2. 硬件资源状态
| 资源类型 | 检查项 | 当前值 | 阈值 | 状态 | 备注 |
|---|---|---|---|---|---|
| CPU | 使用率 | XX% | 80% | 正常/告警 | 峰值XX% |
| 内存 | 使用率 | XX% | 85% | 正常/告警 | 已使用XXGB/总XXGB |
| 磁盘 | 数据目录使用率 | XX% | 80% | 正常/告警 | 已使用XXGB/总XXGB |
| 磁盘 | WAL目录使用率 | XX% | 80% | 正常/告警 | 已使用XXGB/总XXGB |
| 网络 | 吞吐量 | XXMB/s | 1000MB/s | 正常/告警 | - |
3. 数据库基本状态
sql
-- 数据库基本信息查询
SELECT
current_database() AS db_name,
version() AS db_version,
(SELECT count(*) FROM pg_stat_activity) AS current_connections,
(SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections,
pg_is_in_recovery() AS is_in_recovery;| 检查项 | 当前值 | 状态 | 备注 |
|---|---|---|---|
| 数据库名称 | XXX | 正常 | - |
| 数据库版本 | PostgreSQL 15.3 | 正常 | - |
| 当前连接数 | XX | 正常 | 最大连接数XXX |
| 复制状态 | 主库/从库 | 正常 | - |
| 启动时间 | YYYY-MM-DD HH:MM:SS | 正常 | 运行时长XX天 |
4. 连接状态分析
sql
-- 连接状态分布
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
-- 长时间空闲连接（>1小时）
SELECT count(*) FROM pg_stat_activity WHERE state = 'idle' AND age(now(), query_start) > interval '1 hour';| 连接状态 | 数量 | 占比 | 状态 | 备注 |
|---|---|---|---|---|
| active | XX | XX% | 正常 | - |
| idle | XX | XX% | 告警 | 存在XX个长时间空闲连接 |
| idle in transaction | XX | XX% | 正常 | - |
| others | XX | XX% | 正常 | - |
5. 性能指标
sql
-- 缓冲区命中率
SELECT
round(sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read))::numeric * 100, 2) AS heap_hit_ratio,
round(sum(idx_blks_hit) / (sum(idx_blks_hit) + sum(idx_blks_read))::numeric * 100, 2) AS idx_hit_ratio
FROM pg_statio_user_tables;
-- 事务统计
SELECT
sum(xact_commit) AS total_commits,
sum(xact_rollback) AS total_rollbacks,
round(sum(xact_rollback)::numeric / nullif(sum(xact_commit + xact_rollback), 0) * 100, 2) AS rollback_ratio
FROM pg_stat_database;| 性能指标 | 当前值 | 阈值 | 状态 | 备注 |
|---|---|---|---|---|
| 缓冲区命中率 | XX% | 95% | 正常 | - |
| 索引命中率 | XX% | 90% | 正常 | - |
| 事务回滚率 | XX% | 5% | 正常 | - |
| 慢查询数量 | XX | 10 | 告警 | 过去24小时 |
| TPS | XX | - | 正常 | 峰值XX |
| QPS | XX | - | 正常 | 峰值XX |
6. 数据库对象状态
sql
-- 表数量统计
SELECT schemaname, count(*) FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') GROUP BY schemaname;
-- 索引使用情况
SELECT
schemaname,
relname,
indexrelname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY schemaname, relname;| 检查项 | 当前值 | 状态 | 备注 |
|---|---|---|---|
| 用户表数量 | XX | 正常 | - |
| 用户索引数量 | XX | 正常 | - |
| 未使用索引数 | XX | 告警 | 建议优化 |
| 大表数量（>10GB） | XX | 正常 | - |
| 分区表数量 | XX | 正常 | - |
7. 自动真空状态
sql
-- 自动真空配置
SELECT name, setting FROM pg_settings WHERE name LIKE 'autovacuum%';
-- 需要真空的表
SELECT
schemaname,
relname,
n_dead_tup,
n_live_tup,
round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_tuple_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tup
ORDER BY n_dead_tup DESC;| 检查项 | 当前值 | 状态 | 备注 |
|---|---|---|---|
| 自动真空状态 | 开启/关闭 | 正常 | - |
| 自动真空工作进程数 | XX | 正常 | - |
| 需要真空的表数量 | XX | 告警 | 最大死元组占比XX% |
| 最近24小时真空次数 | XX | 正常 | - |
| 最近24小时分析次数 | XX | 正常 | - |
8. 复制状态（主从架构）
sql
-- 主库上查看复制状态
SELECT
usename,
application_name,
client_addr,
state,
sync_state,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag
FROM pg_stat_replication;
-- 从库上查看复制状态
SELECT
client_addr,
state,
pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS replay_lag_bytes,
pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn())) AS replay_lag,
pg_last_xact_replay_timestamp() AS last_replay_time
FROM pg_stat_wal_receiver;| 检查项 | 当前值 | 状态 | 备注 |
|---|---|---|---|
| 复制节点数量 | XX | 正常 | - |
| 同步复制节点数 | XX | 正常 | - |
| 最大复制延迟 | XX秒 | 正常 | 阈值30秒 |
| 复制槽状态 | 正常/告警 | 正常 | - |
| WAL积压量 | XXMB | 正常 | - |
9. 安全配置检查
| 检查项 | 当前配置 | 状态 | 备注 |
|---|---|---|---|
| 密码加密方式 | scram-sha-256 | 正常 | - |
| SSL配置 | 开启 | 正常 | TLS 1.2+ |
| 监听地址 | 127.0.0.1,192.168.1.0/24 | 正常 | 未监听0.0.0.0 |
| superuser远程访问 | 禁用 | 正常 | - |
| 日志审计 | 开启 | 正常 | 记录所有连接和断开 |
| 角色权限 | 符合最小权限原则 | 正常 | - |
10. 慢查询分析
sql
-- 慢查询统计（需要pg_stat_statements扩展）
SELECT
queryid,
query,
calls,
total_exec_time,
mean_exec_time,
max_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;| 排名 | 查询类型 | 总执行时间 | 平均执行时间 | 调用次数 | 状态 | 优化建议 |
|---|---|---|---|---|---|---|
| 1 | SELECT | XXms | XXms | XX | 告警 | 添加索引 |
| 2 | UPDATE | XXms | XXms | XX | 正常 | - |
| 3 | INSERT | XXms | XXms | XX | 正常 | - |
11. 日志分析
| 日志类型 | 数量 | 状态 | 备注 |
|---|---|---|---|
| ERROR | XX | 告警 | 主要错误类型：XX |
| WARNING | XX | 正常 | - |
| NOTICE | XX | 正常 | - |
| LOG | XX | 正常 | - |
12. 配置合理性检查
| 配置项 | 当前值 | 建议值 | 状态 | 备注 |
|---|---|---|---|---|
| shared_buffers | XXGB | 总内存的25% | 正常/告警 | - |
| work_mem | XXMB | 2-8MB | 正常/告警 | - |
| maintenance_work_mem | XXGB | 总内存的5% | 正常/告警 | - |
| effective_cache_size | XXGB | 总内存的75% | 正常/告警 | - |
| wal_level | replica/logical | 正常/告警 | - | |
| checkpoint_completion_target | XX | 0.9 | 正常/告警 | - |
13. 故障与问题记录
| 问题类型 | 问题描述 | 影响范围 | 处理状态 | 处理措施 | 预计解决时间 |
|---|---|---|---|---|---|
| 性能问题 | 慢查询过多 | 应用 | 已处理 | 添加索引 | YYYY-MM-DD |
| 配置问题 | 连接数不足 | 应用 | 处理中 | 调整max_connections | YYYY-MM-DD |
| 硬件问题 | 磁盘空间告警 | 数据库 | 已处理 | 清理历史数据 | YYYY-MM-DD |
14. 优化建议与行动计划
| 优先级 | 优化项 | 建议措施 | 责任人员 | 计划完成时间 | 预期效果 |
|---|---|---|---|---|---|
| 高 | 慢查询优化 | 为表XXX添加索引 | XXX | YYYY-MM-DD | 查询性能提升50% |
| 中 | 配置调整 | 调整work_mem至4MB | XXX | YYYY-MM-DD | 减少内存使用 |
| 中 | 索引优化 | 删除未使用的索引 | XXX | YYYY-MM-DD | 减少维护开销 |
| 低 | 监控增强 | 添加慢查询告警 | XXX | YYYY-MM-DD | 及时发现性能问题 |
巡检报告生成方法
1. 手动生成
- 收集数据：使用上述SQL查询收集各项指标
- 整理数据：将收集到的数据整理成报告模板格式
- 分析数据：对数据进行分析，识别问题和风险
- 生成报告：使用Word/Markdown等工具生成正式报告
- 审核发布：经过审核后发布报告
2. 使用脚本自动生成
2.1 Shell脚本示例
bash
#!/bin/bash
# PostgreSQL巡检报告自动生成脚本
# 配置信息
DB_HOST="localhost"
DB_PORT="5432"
DB_USER="postgres"
DB_NAME="postgres"
REPORT_DIR="./reports"
REPORT_FILE="pg_inspection_$(date +%Y%m%d_%H%M%S).md"
# 创建报告目录
mkdir -p $REPORT_DIR
# 生成报告头部
cat > $REPORT_DIR/$REPORT_FILE << EOF
# PostgreSQL 巡检报告
## 基本信息
- **报告时间**：$(date)
- **数据库实例**：$DB_HOST:$DB_PORT
- **数据库版本**：$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c "SELECT version()")
- **巡检人员**：自动化脚本
## 硬件资源状态
EOF
# 收集CPU使用率（示例，实际需根据系统调整）
CPU_USAGE=$(top -bn1 | grep "Cpu(s)" | awk '{print $2 + $4}')
echo "- **CPU使用率**：$CPU_USAGE%" >> $REPORT_DIR/$REPORT_FILE
# 收集内存使用率
MEM_USAGE=$(free | grep Mem | awk '{print $3/$2 * 100.0}')
echo "- **内存使用率**：$MEM_USAGE%" >> $REPORT_DIR/$REPORT_FILE
# 收集数据库连接数
CONN_COUNT=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c "SELECT count(*) FROM pg_stat_activity")
echo "- **当前连接数**：$CONN_COUNT" >> $REPORT_DIR/$REPORT_FILE
# 收集慢查询数量
SLOW_QUERIES=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c "SELECT count(*) FROM pg_stat_statements WHERE mean_exec_time > 1000")
echo "- **慢查询数量**：$SLOW_QUERIES" >> $REPORT_DIR/$REPORT_FILE
echo "\n## 详细分析\n请参考完整报告..." >> $REPORT_DIR/$REPORT_FILE
echo "巡检报告已生成：$REPORT_DIR/$REPORT_FILE"2.2 Python脚本示例
python
#!/usr/bin/env python3
# PostgreSQL巡检报告自动生成脚本
import psycopg2
import datetime
import json
import os
# 配置信息
config = {
"host": "localhost",
"port": 5432,
"user": "postgres",
"password": "your_password",
"dbname": "postgres",
"report_dir": "./reports"
}
# 创建报告目录
os.makedirs(config["report_dir"], exist_ok=True)
# 连接数据库
conn = psycopg2.connect(
host=config["host"],
port=config["port"],
user=config["user"],
password=config["password"],
dbname=config["dbname"]
)
cursor = conn.cursor()
# 收集数据
report_data = {
"report_time": datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
"database_info": {},
"resource_status": {},
"database_status": {},
"performance_metrics": {}
}
# 收集数据库基本信息
cursor.execute("SELECT current_database(), version()")
db_info = cursor.fetchone()
report_data["database_info"]["db_name"] = db_info[0]
report_data["database_info"]["version"] = db_info[1]
# 收集连接数信息
cursor.execute("SELECT count(*) FROM pg_stat_activity")
report_data["database_status"]["current_connections"] = cursor.fetchone()[0]
cursor.execute("SELECT setting::int FROM pg_settings WHERE name = 'max_connections'")
report_data["database_status"]["max_connections"] = cursor.fetchone()[0]
# 收集性能指标
cursor.execute("""
SELECT
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read))::float as heap_hit_ratio
FROM pg_statio_user_tables
""")
report_data["performance_metrics"]["heap_hit_ratio"] = cursor.fetchone()[0]
# 生成JSON报告
report_file = os.path.join(config["report_dir"], f"pg_inspection_{datetime.datetime.now().strftime('%Y%m%d_%H%M%S')}.json")
with open(report_file, "w", encoding="utf-8") as f:
json.dump(report_data, f, ensure_ascii=False, indent=2)
# 关闭连接
cursor.close()
conn.close()
print(f"巡检报告已生成：{report_file}")3. 使用监控工具生成
3.1 Prometheus + Grafana
- 安装PostgreSQL Exporter：用于收集PostgreSQL指标
- 配置Prometheus：添加PostgreSQL Exporter为数据源
- 创建Grafana Dashboard：设计巡检报告仪表盘
- 配置自动导出：使用Grafana的报告导出功能定期生成报告
3.2 Zabbix
- 配置PostgreSQL监控：使用Zabbix的PostgreSQL模板
- 创建巡检报告模板：设计报告格式和内容
- 配置自动生成：设置定期生成和发送报告
3.3 商业监控工具
- Datadog：提供PostgreSQL监控和报告功能
- New Relic：支持自动生成数据库巡检报告
- SolarWinds：提供全面的数据库监控和报告功能
巡检报告模板
Markdown模板
markdown
# PostgreSQL 巡检报告
## 1. 基本信息
| 项目 | 内容 |
|------|------|
| 报告标题 | PostgreSQL 数据库巡检报告 |
| 报告编号 | PG-INSPECT-20231015-001 |
| 巡检时间 | 2023-10-15 14:30:00 |
| 巡检对象 | 生产库主库（192.168.1.100:5432） |
| 数据库版本 | PostgreSQL 15.3 |
| 巡检人员 | 张三 |
| 报告状态 | 正式版 |
## 2. 硬件资源状态
| 资源类型 | 检查项 | 当前值 | 阈值 | 状态 | 备注 |
|---------|--------|--------|------|------|------|
| CPU | 使用率 | 45% | 80% | 正常 | 峰值65% |
| 内存 | 使用率 | 72% | 85% | 正常 | 已使用28.8GB/总40GB |
| 磁盘 | 数据目录使用率 | 65% | 80% | 正常 | 已使用130GB/总200GB |
| 磁盘 | WAL目录使用率 | 35% | 80% | 正常 | 已使用35GB/总100GB |
## 3. 数据库基本状态
| 检查项 | 当前值 | 状态 | 备注 |
|--------|--------|------|------|
| 数据库名称 | prod_db | 正常 | - |
| 当前连接数 | 128 | 正常 | 最大连接数200 |
| 复制状态 | 主库 | 正常 | 2个从库 |
| 启动时间 | 2023-09-01 08:00:00 | 正常 | 运行45天 |
## 4. 性能指标
| 性能指标 | 当前值 | 阈值 | 状态 | 备注 |
|---------|--------|------|------|------|
| 缓冲区命中率 | 99.2% | 95% | 正常 | - |
| 索引命中率 | 98.5% | 90% | 正常 | - |
| 事务回滚率 | 0.8% | 5% | 正常 | - |
| TPS | 1200 | - | 正常 | 峰值2000 |
| QPS | 8000 | - | 正常 | 峰值15000 |
## 5. 主要问题与风险
1. **未使用索引**：发现5个未使用的索引，建议删除以减少维护开销
2. **慢查询**：存在3个频繁执行的慢查询，建议优化
3. **连接数偏高**：高峰期连接数接近阈值，建议监控并考虑扩容
## 6. 优化建议
1. **索引优化**：删除未使用的索引
2. **查询优化**：为慢查询添加适当的索引
3. **连接管理**：优化应用连接池配置，减少空闲连接
4. **监控增强**：添加慢查询和连接数告警HTML模板
可以使用HTML+CSS创建更美观的巡检报告，支持样式美化和交互功能。
巡检报告自动化方案
1. 完整自动化流程
- 定时触发：使用Cron/Task Scheduler定期触发巡检
- 数据收集：自动运行SQL查询收集各项指标
- 数据存储：将收集到的数据存储到数据库或文件系统
- 报告生成：根据模板自动生成报告
- 质量检查：自动检查报告完整性和准确性
- 报告发布：通过邮件/消息系统发送报告
- 历史归档：将报告归档，便于历史查询和趋势分析
2. 工具链推荐
| 工具类型 | 推荐工具 | 用途 |
|---|---|---|
| 调度工具 | Cron/Ansible Tower | 定期触发巡检任务 |
| 数据收集 | psycopg2/PostgreSQL Exporter | 收集数据库指标 |
| 报告生成 | Jinja2/WeasyPrint | 根据模板生成报告 |
| 报告发布 | SMTP/企业微信/钉钉 | 发送报告 |
| 存储归档 | Git/MinIO | 存储和归档报告 |
常见问题及解决方案
Q1：如何确保巡检报告的准确性？
解决方案：
- 验证数据来源：确保使用的SQL查询准确可靠
- 交叉验证：使用多种方法验证同一指标
- 定期校准：定期检查和校准监控工具
- 人工审核：重要报告经过人工审核后发布
Q2：如何提高巡检报告的生成效率？
解决方案：
- 自动化：尽可能自动化数据收集和报告生成过程
- 模板化：使用标准化模板，减少重复工作
- 并行处理：并行收集多个指标，提高效率
- 增量更新：只更新变化的数据，减少处理量
Q3：如何让巡检报告更有价值？
解决方案：
- 聚焦重点：重点关注关键指标和问题
- 提供 actionable 建议：不仅仅指出问题，还提供具体的解决方案
- 趋势分析：对比历史数据，分析趋势变化
- 可视化展示：使用图表等可视化方式展示数据
- 定制化：根据不同受众定制报告内容
Q4：如何处理大量数据库实例的巡检？
解决方案：
- 批量处理：使用脚本批量处理多个实例
- 分级巡检：根据重要性分级巡检，重要实例频繁巡检
- 集中管理：使用集中式监控系统管理所有实例
- 自动告警：设置自动告警，只在出现问题时生成详细报告
Q5：如何确保巡检报告的安全性？
解决方案：
- 访问控制：限制报告的访问权限
- 加密传输：使用HTTPS/加密邮件传输报告
- 数据脱敏：对敏感数据进行脱敏处理
- 审计日志：记录报告的访问和修改记录
- 定期清理：定期清理过期报告
巡检报告最佳实践
- 定期执行：根据数据库重要性确定巡检频率，一般为每周/每月
- 标准化：使用标准化的报告模板和检查项
- 自动化：尽可能自动化报告生成过程，减少人工干预
- 完整性：确保报告包含所有关键指标和信息
- 准确性：确保报告数据准确可靠
- 及时性：及时生成和发布报告，便于及时发现和解决问题
- 可操作：提供具体的优化建议和行动计划
- 历史对比：包含历史数据对比，便于趋势分析
- 可视化：使用图表等可视化方式展示数据，提高可读性
- 持续改进：定期 review 报告模板和检查项，持续改进
常见问题（FAQ）
Q1：巡检报告的最佳频率是多少？
A1：巡检频率应根据数据库的重要性和负载情况确定：
- 核心生产库：每周一次
- 重要生产库：每两周一次
- 一般生产库：每月一次
- 测试/开发库：每季度一次
Q2：如何确保巡检报告的质量？
A2：
- 建立标准化的检查项清单
- 使用自动化工具收集数据，减少人工错误
- 对报告进行多级审核
- 定期更新报告模板，适应业务变化
- 对比历史报告，确保数据一致性
Q3：巡检报告应该包含哪些关键指标？
A3：
- 硬件资源状态（CPU、内存、磁盘、网络）
- 数据库基本状态（连接数、版本、启动时间）
- 性能指标（缓冲区命中率、TPS/QPS、慢查询）
- 数据库对象状态（表、索引、分区）
- 自动真空状态
- 复制状态（主从架构）
- 安全配置
- 日志分析
Q4：如何处理巡检中发现的问题？
A4：
- 对问题进行分级（紧急、重要、一般）
- 制定详细的修复计划和时间表
- 跟踪问题的解决进度
- 验证修复效果
- 更新知识库，避免重复问题
Q5：如何实现巡检报告的自动化生成？
A5：
- 使用脚本定期收集数据
- 利用模板引擎生成报告
- 配置自动发送机制（邮件、消息系统）
- 建立报告归档系统
- 实现异常情况自动告警