数据库巡检
监控告警回答的是「现在有没有着火」，巡检回答的是「有没有还没着火的隐患」。两者互补：告警依赖阈值，只能捕捉越线的异常；而很多问题——一个从未被使用却每天维护的大索引、一个统计信息过期三个月的表、一个连续三周没成功过的归档任务——在越线之前毫无征兆，等阈值响了往往已经积重难返。巡检的本质是用一套固定清单定期主动体检，把问题消灭在阈值之前。
一份可持续的巡检体系有三个构成要素：清单（查什么、多频次查、健康标准是什么）、脚本（把清单固化为可重复执行的 SQL 与 bash）、报告（把结果变成人能读、能归档、能对比趋势的产物）。本页就按这三层展开：先设计一份按每日 / 每周 / 每月分频次的巡检清单，再逐项给出可直接执行的检查 SQL，然后提供一个完整的 bash 巡检脚本与报告模板，最后讨论用 cron 与 CI 把巡检自动化落地。
本页同时是前两节成果的「集大成者」：连接状态分析（上一页）与表膨胀测量（上一页）都会作为巡检项出现，后续的 WAL 积压监控也会在巡检项中露出。掌握本页之后，你就拥有了把前几页的单一技能编排成例行运维流程的能力。
前置知识
建议先阅读：表与索引膨胀检查
基础概念
巡检、监控与变更管理的边界
| 维度 | 监控告警 | 定期巡检 | 变更管理 |
|---|---|---|---|
| 触发方式 | 阈值/事件驱动，实时 | 时间驱动，周期执行 | 需求驱动，人工发起 |
| 关注对象 | 当前异常 | 潜在隐患 + 趋势 | 对象的修改过程 |
| 典型产物 | 告警通知 | 巡检报告 | 变更单 |
| 响应动作 | 立即处置 | 排期整改 | 审批执行 |
三者不是替代关系。巡检发现的问题通常会转化为：临时告警规则（同类问题再次发生要能实时捕获）、变更单（整改动作）或架构复盘（根因治理）。
巡检清单的设计原则
设计巡检清单时遵循四条原则：
- 按风险分级定频次：磁盘、备份、复制这类「一坏就是事故」的项每日查；膨胀、索引健康这类慢性项每周查；权限、扩展版本这类几乎不变的项每月查。
- 每项都有明确判据：不是「看看复制延迟」，而是「
replay_lsn差值 > 100 MB 告警」——没有判据的巡检项执行两周就会沦为形式。 - 结果可量化、可趋势化：巡检报告要能和上一次对比，数字型结果（连接数、膨胀率、WAL 日增量）优先于文字描述。
- 自动化优先，人工复核兜底：脚本能产出的部分全部脚本化，人只看汇总与异常项。
一份按频次分层的巡检清单
| 频次 | 巡检项 | 核心判据（示例阈值） |
|---|---|---|
| 每日 | 连接水位 | client backend 数 < max_connections 的 70% |
| 每日 | 空闲事务 | idle in transaction = 0 个持续超 5 分钟者 |
| 每日 | 锁等待 | pg_blocking_pids() 阻塞链无长于 5 分钟者 |
| 每日 | 长事务 | 最长 xact_start < 1 小时 |
| 每日 | 复制延迟 | 字节延迟 < 100 MB 且时间延迟 < 60 秒 |
| 每日 | WAL 目录大小 | pg_wal < 规定上限（如 20 GB） |
| 每日 | 归档成功 | pg_stat_archiver.failed_count 无新增 |
| 每日 | 备份成功 | 最近一次备份任务成功且文件大小合理 |
| 每日 | 磁盘空间 | 数据/日志分区使用率 < 80% |
| 每日 | 日志错误扫描 | FATAL/ERROR 无异常突增 |
| 每周 | 表膨胀 Top N | 统计估算 + 抽样 pgstattuple_approx |
| 每周 | 索引膨胀抽样 | 核心表索引 avg_leaf_density > 70% |
| 每周 | 未使用索引 | idx_scan = 0 清单复核 |
| 每周 | 无效索引 | pg_index.indisvalid = false 应为空 |
| 每周 | 异常增长表 | 表大小周环比突增复核 |
| 每周 | autovacuum 健康 | last_autovacuum 覆盖所有活跃表 |
| 每周 | deadlocks 计数 | pg_stat_database.deadlocks 无新增 |
| 每月 | 主键缺失表 | 无主键的业务表清单 |
| 每月 | 外键约束复核 | 关键关系是否显式声明（不强制的除外） |
| 每月 | 扩展与版本 | 扩展版本、已安装扩展与需求一致 |
| 每月 | 权限抽查 | 超级用户清单、可登录角色最小化 |
| 每月 | 备份恢复演练 | 抽取一次恢复验证（配合灾备演练页） |
语法与用法
本节把清单里可 SQL 化的项逐一落地。所有查询都设计为「输出就是结论」：结果为空或满足判据即健康，异常行直接可见。
连接与事务类（每日）
sql
-- 1. 连接水位（阈值 70%）
SELECT count(*) FILTER (WHERE backend_type = 'client backend') AS used,
current_setting('max_connections')::int AS max_conn,
round(count(*) FILTER (WHERE backend_type = 'client backend') * 100.0
/ current_setting('max_connections')::int, 1) AS usage_pct
FROM pg_stat_activity;sql
-- 2. 空闲事务与长事务（合并为一张异常表）
SELECT pid,
usename,
application_name,
state,
now() - xact_start AS xact_age,
now() - state_change AS idle_age,
left(query, 60) AS last_query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
AND (
(state = 'idle in transaction' AND now() - state_change > interval '5 minutes')
OR (xact_start IS NOT NULL AND now() - xact_start > interval '1 hour')
);sql
-- 3. 锁等待（被阻塞超过 1 分钟的会话及其阻塞者）
SELECT blocked.pid AS blocked_pid,
now() - blocked.query_start AS wait_time,
left(blocked.query, 50) AS blocked_query,
blocking.pid AS blocking_pid,
blocking.state AS blocking_state,
now() - blocking.xact_start AS blocking_xact_age
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
ON blocking.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE now() - blocked.query_start > interval '1 minute';复制与 WAL 类（每日）
sql
-- 4. 复制延迟（字节）
SELECT application_name,
client_addr,
state,
sync_state,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes,
write_lag,
flush_lag,
replay_lag
FROM pg_stat_replication;sql
-- 5. 复制槽健康（包括失联槽与保留 WAL）
SELECT slot_name,
slot_type,
active,
wal_status,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
COALESCE(restart_lsn, '0/0'))) AS retained_wal
FROM pg_replication_slots
ORDER BY retained_wal DESC;sql
-- 6. 归档统计（pg_stat_archiver）
SELECT archived_count,
last_archived_wal,
last_archived_time,
failed_count,
last_failed_wal,
last_failed_time
FROM pg_stat_archiver;sql
-- 7. pg_wal 目录实际大小（需超级用户；PG10+ 提供 pg_ls_waldir）
SELECT pg_size_pretty(sum(size)) AS wal_dir_size
FROM pg_ls_waldir();数据库级健康（每日/每周）
sql
-- 8. 各库统计快照（重点看 deadlocks、冲突与回滚）
SELECT datname,
numbackends,
xact_commit,
xact_rollback,
round(blks_hit_ratio, 2),
deadlocks,
conflicts,
temp_files,
pg_size_pretty(temp_bytes) AS temp_written
FROM (
SELECT datname, numbackends, xact_commit, xact_rollback,
CASE WHEN blks_hit + blks_read > 0
THEN blks_hit * 100.0 / (blks_hit + blks_read) END AS blks_hit_ratio,
deadlocks, conflicts, temp_files, temp_bytes
FROM pg_stat_database
WHERE datname NOT LIKE 'template%'
) t;sql
-- 9. 日志错误扫描：当日 FATAL/ERROR 分布（从日志文件侧统计，bash 部分）表与索引健康类（每周）
sql
-- 10. 表膨胀 Top 20（统计估算，快速扫描）
SELECT relname,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
n_live_tup,
n_dead_tup,
round(n_dead_tup * 100.0 / greatest(n_live_tup + n_dead_tup, 1), 1) AS dead_pct,
last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;sql
-- 11. autovacuum 健康检查：活跃表却从未被 autovacuum 触达
SELECT relname,
n_live_tup,
n_dead_tup,
last_autovacuum,
autovacuum_count
FROM pg_stat_user_tables
WHERE n_live_tup > 100000
AND (last_autovacuum IS NULL OR last_autovacuum < now() - interval '7 days')
ORDER BY n_dead_tup DESC;sql
-- 12. 无效索引（任何输出都是事故级隐患：计划器会绕开它，且维护成本照付）
SELECT n.nspname AS schema,
c.relname AS index_name,
t.relname AS table_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT i.indisvalid;sql
-- 13. 未使用索引（结合读写统计，慎重评估后删除）
SELECT schemaname,
relname AS table_name,
indexrelname AS index_name,
idx_scan,
idx_tup_read,
idx_tup_fetch,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND pg_relation_size(indexrelid) > 10 * 1024 * 1024 -- 大于 10MB 才值得看
ORDER BY pg_relation_size(indexrelid) DESC;sql
-- 14. 表大小周环比（需要历史快照表配合，见实战示例）结构治理类（每月）
sql
-- 15. 主键缺失的用户表
SELECT n.nspname AS schema,
c.relname AS table_name,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
AND NOT EXISTS (
SELECT 1 FROM pg_constraint k
WHERE k.conrelid = c.oid AND k.contype = 'p'
)
ORDER BY pg_total_relation_size(c.oid) DESC;sql
-- 16. 外键缺失（参考性检查：大表/日志表常有意不加外键，只报告不强制）
SELECT c.relname AS table_name,
a.attname AS column_name,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped
WHERE c.relkind = 'r'
AND n.nspname = 'public'
AND a.attname LIKE '%\_id' -- 命名约定启发式，按需调整
AND NOT EXISTS (
SELECT 1 FROM pg_constraint fk
WHERE fk.conrelid = c.oid
AND fk.contype = 'f'
AND a.attname = ANY (SELECT unnest(fk.conkey)::text)
);sql
-- 17. 已安装扩展清单
SELECT e.extname, e.extversion, n.nspname AS schema
FROM pg_extension e
JOIN pg_namespace n ON n.oid = e.extnamespace
ORDER BY e.extname;详细说明
每日项的优先级排序
每日巡检项按「出事后的爆炸半径」排序：
- 磁盘空间是第一优先级：磁盘满会让 PostgreSQL 直接崩溃（WAL 写不进去、临时文件失败），且恢复动作（清理、扩容）耗时最长。除了数据目录，还要盯 WAL 目录、日志目录、表空间所在挂载点。
- 备份成功第二：备份失败意味着 RPO 正在恶化，且备份失败往往是磁盘/权限/存储的下游症状，值得追根。
- 复制延迟第三：延迟意味着高可用能力降级，字节延迟（
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)）看堆积量，时间延迟（replay_lag列）看追平能力。 - 连接与事务类第四：它们影响可用性但不至于立刻宕库。
- 日志扫描是收尾：当日 FATAL/ERROR 的分布能提前暴露连接问题、权限问题与 OOM 征兆。
备份成功的判据设计
「备份成功」不能只看任务退出码，还要看产物完整性：
- 逻辑备份：产物文件存在、大小与近 7 日中位数偏差 < 30%、（可选）
pg_restore --list能解析； - 物理备份：
backup_label存在、pg_verifybackup通过（见备份章节的验证页）、WAL 归档覆盖到备份结束 LSN； - 时间窗口：备份完成时间落在预期窗口内，避免「跑了 12 小时的备份其实从第 2 小时就开始失败」。
备份策略本身的分层设计（每日全量/增量、WAL 归档频率、保留策略）属于备份章节主题，参见 复制槽与备份策略。
未使用索引与无效索引的区别
这两个都是「索引健康」巡检项，但性质完全不同：
- 未使用索引（
idx_scan = 0）：功能正常，只是没人用。它白吃写入成本（每次 UPDATE 都要维护）。处置是先加监控窗口再删：确认覆盖读多写少的场景、主备两端都统计（备库查询不走主库的 idx_scan）、跨过季度末/年末等特殊查询周期后，用DROP INDEX CONCURRENTLY下线。 - 无效索引（
indisvalid = false）：创建中途失败（如CREATE INDEX CONCURRENTLY被中断）或REINDEX CONCURRENTLY过程中的产物。计划器不会使用它，但它仍随表更新而维护，纯负资产。处置是直接删除后重建。
sql
-- 一条查询同时统计两类
SELECT c.relname AS index_name,
i.indisvalid,
s.idx_scan,
pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
LEFT JOIN pg_stat_user_indexes s ON s.indexrelid = c.oid
WHERE NOT i.indisvalid OR COALESCE(s.idx_scan, 0) = 0;deadlocks 与冲突的解读
pg_stat_database.deadlocks 是自统计重置以来的累计值，它的价值不在绝对值而在增量：巡检记录每次快照值，周环比有新增就说明这一周发生过死锁。发生死锁时 PostgreSQL 会主动回滚其中一个事务并在日志中留下完整细节，因此该指标的意义是「提醒你去翻日志」而不是「量化损失」。
conflicts 列只在备库上有意义：它统计备库恢复与查询冲突（如 cleanup 过快导致查询取消）的次数，主库上恒为 0。备库巡检时要单独看。
巡检脚本的设计要点
把清单变成脚本时有几条工程经验：
- psql 单连接批量执行：为每条检查 SQL 用
-At -c或 heredoc 输出机器可解析格式（-A去表格边框、-t去表头按需），避免每条查询一次认证开销。 - 失败隔离：单条检查失败不应终止整个巡检，逐条 try 并在报告里标注执行失败。
- 可静默、可汇报：正常时输出一行摘要，异常时输出完整明细；
--report模式产出完整报告。 - 阈值外置：阈值写成脚本头部变量或配置文件，不同环境（开发/生产）用不同配置。
巡检自动化的两级落地
- cron 级：数据库主机上用 cron 每日跑巡检脚本，结果写入日志目录并邮件/IM 通知。简单直接，但结果分散在各主机。
- CI / 调度平台级：把巡检脚本纳入 Git 管理，由 CI 定时任务（或 Jenkins/Airflow 类调度）在跳板机执行并归档报告，报告版本化、可追溯、可对比。结构治理类月检尤其适合放在 CI——它天然带有「对变更做回归检查」的性质。
巡检报告的信息结构
一份好的巡检报告自上而下分四层：结论层（整体健康度 + 3 条以内最重要的发现）、异常明细层（每项检查的异常输出）、趋势层（与历史快照的对比数字）、附录层（完整原始数据 + 执行环境说明）。运维 Leader 只读第一层，值班只看第二层，复盘才用到第三四层。
实战示例
示例一：一个完整的 bash 巡检脚本
下面是一个可落库使用的单机巡检脚本骨架，覆盖每日项并输出 Markdown 报告：
bash
#!/usr/bin/env bash
# pg_daily_check.sh —— PostgreSQL 每日巡检
# 用法: ./pg_daily_check.sh [port] > report.md
set -u
PGPORT="${1:-5432}"
PGUSER="postgres"
PSQL=(psql -U "$PGUSER" -p "$PGPORT" -d postgres -v ON_ERROR_STOP=0 -qAt)
REPORT_DATE="$(date '+%F %T')"
echo "# PostgreSQL 每日巡检报告 — $REPORT_DATE (port=$PGPORT)"
echo
run_check() {
local title="$1" sql="$2"
echo "## $title"
echo
echo '```text'
# 单条检查失败不中断巡检
if ! out="$("${PSQL[@]}" -c "$sql" 2>&1)"; then
echo "执行失败: $out"
else
echo "$out"
fi
echo '```'
echo
}
run_check "1. 连接水位（阈值 70%）" "
SELECT 'used=' || count(*) FILTER (WHERE backend_type='client backend')
|| ' / max=' || current_setting('max_connections')
FROM pg_stat_activity;"
run_check "2. 空闲事务 > 5 分钟" "
SELECT pid || ' | ' || usename || ' | ' || coalesce(application_name,'')
|| ' | ' || state || ' | idle_age=' || (now()-state_change)
|| ' | ' || left(query,50)
FROM pg_stat_activity
WHERE backend_type='client backend'
AND state='idle in transaction'
AND now()-state_change > interval '5 minutes';"
run_check "3. 长事务 > 1 小时" "
SELECT pid || ' | ' || usename || ' | xact_age=' || (now()-xact_start)
|| ' | ' || left(query,50)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
AND now()-xact_start > interval '1 hour';"
run_check "4. 复制延迟（字节）" "
SELECT application_name || ' | state=' || state
|| ' | lag=' || pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn))
|| ' | replay_lag=' || coalesce(replay_lag::text,'-')
FROM pg_stat_replication;"
run_check "5. 失联复制槽（含保留 WAL）" "
SELECT slot_name || ' | type=' || slot_type || ' | active=' || active
|| ' | wal_status=' || coalesce(wal_status,'-')
|| ' | retained=' || pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
FROM pg_replication_slots
WHERE NOT active OR wal_status IN ('unreserved','lost');"
run_check "6. 归档失败统计" "
SELECT 'archived=' || archived_count
|| ' failed=' || failed_count
|| ' last_archived=' || coalesce(last_archived_time::text,'-')
|| ' last_failed=' || coalesce(last_failed_time::text,'-')
FROM pg_stat_archiver;"
run_check "7. pg_wal 目录大小" "
SELECT 'wal_dir=' || pg_size_pretty(coalesce(sum(size),0))
FROM pg_ls_waldir();"
run_check "8. 数据库 deadlocks 累计" "
SELECT datname || ' | deadlocks=' || deadlocks || ' | conflicts=' || conflicts
FROM pg_stat_database
WHERE datname NOT LIKE 'template%';"
run_check "9. 表膨胀 Top 10" "
SELECT relname
|| ' | ' || pg_size_pretty(pg_total_relation_size(relid))
|| ' | dead_pct=' || round(n_dead_tup*100.0/greatest(n_live_tup+n_dead_tup,1),1)
|| ' | last_autovacuum=' || coalesce(last_autovacuum::text,'-')
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;"
run_check "10. 无效索引" "
SELECT n.nspname || '.' || c.relname || ' on ' || t.relname
FROM pg_index i
JOIN pg_class c ON c.oid=i.indexrelid
JOIN pg_class t ON t.oid=i.indrelid
JOIN pg_namespace n ON n.oid=c.relnamespace
WHERE NOT i.indisvalid;"
echo "## 11. 今日日志错误扫描"
echo
echo '```text'
LOGDIR="/var/log/postgresql" # 按实际日志目录调整
TODAY="$(date '+%F')"
if [ -d "$LOGDIR" ]; then
grep -h "FATAL\|ERROR" "$LOGDIR"/*"$TODAY"* 2>/dev/null \
| awk -F': ' '{print $3}' | sort | uniq -c | sort -rn | head -10
else
echo "日志目录 $LOGDIR 不可访问，跳过"
fi
echo '```'
echo
echo "---"
echo "巡检完成于 $(date '+%F %T')，请复核以上异常项。"执行与通知：
bash
chmod +x pg_daily_check.sh
./pg_daily_check.sh 5432 > /var/log/pg_inspection/daily_$(date +%F).md
# 异常检测 + IM 通知（以是否存在特定关键词为例）
if grep -qE 'idle in transaction|unreserved|lost|FATAL' /var/log/pg_inspection/daily_$(date +%F).md; then
echo "巡检发现异常项，请查看报告" | mail -s "[PG] 巡检异常 $(date +%F)" dba-team@example.com
fi示例二：巡检报告模板
markdown
# PostgreSQL 巡检报告 — 2026-09-30（生产集群 A）
## 一、结论
- 整体健康度：⚠️ 黄色（1 项需要整改，2 项持续观察）
- 重要发现：
1. user_events 表膨胀 27%，建议本周内安排 pg_repack；
2. idx_orders_created_at 已 90 天无扫描（1.2 GB），建议评估下线；
3. 备库 B 复制延迟趋势从 10 MB 恶化到 260 MB，观察复现。
## 二、异常明细
（各检查项的异常输出，见脚本报告原文）
## 三、趋势对比（与上周）
| 指标 | 上周 | 本周 | 变化 |
| --- | --- | --- | --- |
| 数据目录大小 | 512 GB | 519 GB | +1.4% |
| pg_wal 峰值 | 11 GB | 9 GB | -2 GB |
| 最大连接水位 | 62% | 58% | -4pp |
| Top1 表膨胀 | 22% | 27% | +5pp ↑ |
## 四、执行环境
- 脚本版本：pg_daily_check.sh v1.6（git: a1b2c3d）
- 阈值配置：prod.yaml
- 执行主机：jumper-01示例三：表大小快照与周环比
「异常增长表」巡检需要历史快照支撑。建一张巡检快照表，每次巡检写入当天全量表大小，查询周环比：
sql
-- 一次性初始化（放在巡检库或 postgres 库）
CREATE TABLE IF NOT EXISTS inspection.table_size_snapshot (
snap_date date NOT NULL,
schemaname text NOT NULL,
relname text NOT NULL,
total_bytes bigint NOT NULL,
PRIMARY KEY (snap_date, schemaname, relname)
);sql
-- 每次巡检写入快照
INSERT INTO inspection.table_size_snapshot
SELECT current_date, schemaname, relname, pg_total_relation_size('"' || schemaname || '"."' || relname || '"')
FROM pg_stat_user_tables
ON CONFLICT (snap_date, schemaname, relname) DO NOTHING;
-- 周环比突增（>50% 且绝对增量 > 1GB）的表
SELECT cur.schemaname,
cur.relname,
pg_size_pretty(prev.total_bytes) AS last_week,
pg_size_pretty(cur.total_bytes) AS this_week,
round((cur.total_bytes - prev.total_bytes) * 100.0 / greatest(prev.total_bytes,1), 1) AS growth_pct
FROM inspection.table_size_snapshot cur
JOIN inspection.table_size_snapshot prev
ON prev.schemaname = cur.schemaname
AND prev.relname = cur.relname
AND prev.snap_date = cur.snap_date - 7
WHERE cur.snap_date = current_date
AND cur.total_bytes > prev.total_bytes * 1.5
AND cur.total_bytes - prev.total_bytes > 1024*1024*1024
ORDER BY cur.total_bytes - prev.total_bytes DESC;示例四：用 cron 与 CI 落地自动化
cron 方案（数据库主机本地）：
bash
# /etc/cron.d/pg-inspection
# 每日 07:30 每日巡检
30 7 * * * postgres /opt/pgsql/inspection/pg_daily_check.sh 5432 > /var/log/pg_inspection/daily_$(date +\%F).md 2>&1
# 每周一 08:00 周检（含膨胀与索引项）
0 8 * * 1 postgres /opt/pgsql/inspection/pg_weekly_check.sh 5432 > /var/log/pg_inspection/weekly_$(date +\%F).md 2>&1
# 每月 1 日 09:00 月检（结构治理类）
0 9 1 * * postgres /opt/pgsql/inspection/pg_monthly_check.sh 5432 > /var/log/pg_inspection/monthly_$(date +\%F).md 2>&1CI 方案（脚本入库，定时流水线执行）：
text
仓库结构
inspection/
├── pg_daily_check.sh
├── pg_weekly_check.sh
├── pg_monthly_check.sh
├── thresholds/
│ ├── prod.yaml
│ └── staging.yaml
└── reports/ # 巡检报告按日期归档入库
└── 2026-09-30.mdCI 定时任务在跳板机/堡垒机环境执行脚本，把产出的报告以 commit 形式归档——报告天然版本化，趋势对比可以直接 diff 两个日期的文件。配合 MR 评审，巡检发现的整改项（如删除未使用索引）可以立刻转成变更单。
示例五：把巡检与统计扩展联动
如果实例上已部署 pg_stat_statements（扩展与部署详见 常用扩展总览），巡检报告可以增加一个「Top 耗时查询」小节，把巡检从「基础设施视角」推进到「负载视角」：
sql
SELECT round(total_exec_time::numeric / 1000, 1) AS total_s,
calls,
round(mean_exec_time::numeric, 1) AS avg_ms,
left(query, 60) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;text
total_s | calls | avg_ms | query
---------+--------+--------+------------------------------------------------------
84120.3 | 412031 | 204.2 | SELECT * FROM orders WHERE user_id = $1 AND ...
40211.9 | 3942 | 10202.1| SELECT date_trunc('day', ...) FROM user_events ...
(2 rows)周巡检把 Top 5 的 total_s 变化记录进趋势表，突然跃升的查询往往就是「表膨胀」「统计过期」「数据倾斜」三个病因的显影剂——与表膨胀巡检形成交叉验证。
注意事项
巡检账号要走最小权限。 巡检查询大多是只读的，但 pg_ls_waldir()、pg_stat_archiver 等部分视图/函数需要超级用户或 pg_monitor 角色。用专用巡检账号并授予 pg_monitor 预置角色，避免巡检本身成为高危操作。
阈值要按环境分级。 开发库与生产库的合理阈值不同（连接水位、WAL 上限、延迟容忍），把阈值外置成配置并在脚本里加载，否则巡检在非生产环境会「假警报」淹没真问题。
统计视图的数据在实例重启后部分归零。 pg_stat_database、pg_stat_user_tables 等基于统计收集的视图在崩溃恢复后可能清零（PG15 起统计持久化改善明显），deadlocks 累计值、idx_scan 等指标要结合快照表看增量，巡检快照因此更加必要。
pg_blocking_pids() 有一定成本。 它需要遍历锁表，在锁竞争极重的实例上调用本身有开销；巡检每日跑一次可以接受，但不要把它塞进高频采集。
巡检脚本对大表的查询要有超时保护。 给 psql 加 statement_timeout（例如 PGOPTIONS="-c statement_timeout=30s"），防止某条巡检 SQL 自己变成慢查询拖垮巡检任务。
未使用索引的删除必须走变更流程。 idx_scan = 0 只是嫌疑，删除前要核对：备库上的读流量、报表/批量任务在非高峰时段的使用、以及该索引是否被约束（主键/唯一约束的支撑索引不能删）依赖。
巡检报告要归档，不能只在 IM 里闪现。 IM 消息会被淹没，报告必须落在文件系统或版本库中（如示例四的 reports/ 目录），季度复盘与容量规划都依赖这份历史档案。
最佳实践
- 把巡检清单固化为代码，而不是文档。 清单放在与脚本同一个仓库里（README 或 thresholds 注释），每次加巡检项都是一次 MR 评审，清单才会真的更新。
- 每日项全部脚本化 + 异常触发通知；人工只复核异常项。 健康日不需要任何人阅读报告，这是巡检能长期坚持的前提。
- 为巡检建立快照表（表大小、deadlocks、idx_scan 等）。 统计视图大多只有累计值，「增量」与「趋势」必须靠自己的快照体系计算，示例三的模式可以直接复用。
- 巡检账号独立并授予
pg_monitor角色。 与监控采集器账号隔离，避免巡检与采集互相影响，也便于审计。 - 每条巡检 SQL 都带
statement_timeout与失败隔离。 巡检脚本自己的健壮性决定了它能不能在问题最严重的时候正常工作。 - 周检必查四类慢性病：表膨胀、索引密度、未使用索引、autovacuum 覆盖率。 这四项是「离事故最远的隐患」，也是巡检最有性价比的部分。
- 月检聚焦结构与配置治理：主键缺失、扩展版本、权限、备份恢复演练。 把低频高价值的检查固定在月初，配合变更窗口消化整改项。
- 报告四层结构：结论 → 异常明细 → 趋势 → 附录。 让不同角色各取所需，巡检报告才会被持续阅读。
- 巡检发现的问题要闭环。 每个异常项要么转化为告警规则（下次实时捕获）、要么转变更单（明确 owner 与 deadline），报告里维护一张「遗留问题跟踪表」。
- 把
pg_stat_statements的 Top 查询纳入周检。 负载视角与资源视角交叉验证，能提前发现「还没慢到告警但已经劣化」的查询。 - 巡检脚本与阈值配置纳入 Git，报告按日期归档。 可追溯是巡检体系能持续演进的基础，也是新同事接手值班的第一份教材。
下一节
继续学习：WAL 积压监控
参考链接
- PostgreSQL 官方文档：pg_stat_activity / pg_stat_database 等统计视图
- PostgreSQL 官方文档：pg_stat_replication 视图
- PostgreSQL 官方文档：pg_replication_slots 视图
- PostgreSQL 官方文档：pg_stat_archiver 视图
- PostgreSQL 官方文档：预置角色（pg_monitor）
- PostgreSQL 官方文档：锁等待与 pg_blocking_pids
- PostgreSQL 官方文档：pg_stat_statements 扩展
- PostgreSQL 官方文档：pg_ls_waldir 函数
- PostgreSQL Wiki：Monitoring 相关工具与最佳实践
- PostgreSQL 官方文档：Backup and Restore 总览