用这个SKILL分分钟巡检一万个PG，DBA真的要下岗了

一些 PostgreSQL Agent Skill，健康巡检的、经营分析的。

参考了 deepwiki 以及之前写的 《PostgreSQL DBA最常用SQL》《PostgreSQL Freeze 风暴预测》 等文章。
SKILL 代码已放到 https://github.com/digoal/postgres_skill/ 拿走不谢, 以后可基于这个 skill 再进行迭代.

这个以经营分析为目标的 SKILL , 先获取并分析元数据, 采样数据, 生成经营分析相关的SQL和结果分析代码.

# 经营分析 SKILL 使用举例 :
以经营分析的视角, 重新分析这个 postgresql 数据库: host 127.0.0.1 port 1922 user digoal dbname postgres password 123456

以日常巡检的 Skill 为例，介绍如下。

1、PostgreSQL Daily Check 文件结构与用途

postgres-daily-check/  
├── assets/  
│   └── db_config.env          # 数据库连接配置（PGHOST, PGPORT, PGUSER, PGDATABASE）  
├── scripts/  
│   ├── run_postgres_check.sh  # 核心脚本：执行各 skill 的 SQL 查询  
│   └── postgres_agent.py      # Agent：调度执行、分析结果、生成报告  
└── SKILL.md                   # Skill 定义文档  

2、文件用途

3、Skill 用法

1. 独立执行单个 skill

./run_postgres_check.sh <skill_name> [参数]  
  
# 示例  
./run_postgres_check.sh get_long_running_queries 5    # 查询超过5分钟的长查询  
./run_postgres_check.sh get_idle_in_transaction_sessions 2  # 查询超过2分钟的idle事务  
./run_postgres_check.sh get_cache_hit_rate  
./run_postgres_check.sh get_table_bloat  

2. 完整巡检（推荐）

使用方式

cd postgres-daily-check  
  
# 运行巡检  
# 生成 daily_health_report.md  
python3 scripts/postgres_agent.py  

说明：postgres_agent.py 只使用 Python 标准库（json, subprocess, datetime, os），无需额外依赖。

3. 目前可用 Skill 列表如下

PostgreSQL 巡检报告总结 (按 SKILL.md 分类)

1. Core Health & Availability ✅/❌

关键问题: truncate test 阻塞了所有对 test 表的查询，已持续1小时16分钟。

1. Session & Connection Monitoring ✅/⚠️

1. Performance & Activity ⚠️

1. Replication & Archiving ✅

1. Maintenance & Storage ⚠️

1. Freeze & Wraparound Protection ✅

✅ SKILL.md 覆盖率

4、使用注意

需要提前配置 PostgreSQL pg_stat_statements 插件.

由于 PG 采用类似 PDB 的架构, 不同的 database 需要不同的连接, 所以如果你的实例中有多个数据库, 记得告诉 AI 进行多次巡检.

ai agent 端需要安装了 psql 客户端.

例子

支持 SKILL 的 AI Agent 都可以使用它了.

直接在 opencode 中进行巡检.

巡检这个 PostgreSQL 实例: host 127.0.0.1 port 1922 user digoal dbname postgres password 123456 , 巡检完成后根据报告给出详细的建议.  

AI会自动根据 SKILL.md 指引进行巡检, 包括自动配置 db_config.env, 输出如下

巡检完成！报告时间：2026-02-04 14:53:46  
---  
PostgreSQL 巡检报告详细分析  
整体状态: ❌ ERROR  
---  

🔴 严重问题  

1. 阻塞锁 (Blocking Locks)  
问题描述: 发现 5 个锁阻塞场景  
- PID 23716 (truncate test) 持有 AccessExclusiveLock，阻塞了所有其他会话  
- 多个 select * from test 查询在等待锁  

建议:  
-- 1. 查看阻塞会话  
SELECT pid, usename, state, query, wait_event_type, wait_event   
FROM pg_stat_activity   
WHERE pid IN (23716, 23718, 23636, 23637, 23757, 23828);  
-- 2. 终止阻塞会话（如果确认可以终止）  
SELECT pg_terminate_backend(23716);  
-- 3. 或者等待会话自行释放锁  
根因分析: truncate test 命令持有排它锁，而多个 SELECT 查询需要访问该表。长时间不提交事务导致锁持有时间过长。  

---  

2. 长事务 (Idle-in-Transaction)  
问题描述:   
- PID 23716 idle in transaction 30分钟45秒  
建议:  
-- 查找长时间 idle in transaction 的会话  
SELECT pid, usename, state, age(clock_timestamp(), xact_start) AS xact_age,  
       query, xact_start  
FROM pg_stat_activity  
WHERE state = 'idle in transaction'
  AND age(clock_timestamp(), xact_start) > interval '5 minutes'
ORDER BY xact_start;  
-- 终止问题会话  
SELECT pg_terminate_backend(23716);  
建议配置:  
-- 在 postgresql.conf 中添加  
idle_in_transaction_session_timeout = 60000  -- 60秒后自动终止  

---  

🟠 警告问题  

3. 长查询 (Long-Running Queries)  
问题描述: 5 个查询运行超过 5 分钟  
| PID | 用户 | 时长 | 查询 |  
|-----|------|------|------|  
| 23718 | postgres | 30:34 | truncate test |  
| 23636 | postgres | 30:30 | select * from test |  
| 23637 | postgres | 30:27 | select * from test |  
建议:  
-- 查看详细查询内容  
SELECT pid, now() - query_start AS duration, usename, query  
FROM pg_stat_activity  
WHERE state = 'active'
ORDER BY duration DESC LIMIT 10;  

---  

4. 缓存命中率低 (Low Cache Hit Rate)  
问题描述:   
- 当前: 51.4%  
- 正常值: >99%  
建议:  
-- 检查共享内存大小  
SHOW shared_buffers;  
-- 检查实际缓存使用  
SELECT   
    sum(heap_blks_read) AS heap_read,  
    sum(heap_blks_hit) AS heap_hit,  
    round(sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) * 100, 2) AS hit_ratio  
FROM pg_statio_user_tables;  
-- 调优建议  
-- 1. 增加 shared_buffers (建议为物理内存的 25%)  
-- 2. 考虑增加 work_mem  
-- 3. 优化查询减少全表扫描  
postgresql.conf 调优建议:  
shared_buffers = 4GB                    # 物理内存的 25%  
effective_cache_size = 12GB            # 物理内存的 75%  
work_mem = 64MB                        # 每个排序操作  
maintenance_work_mem = 1GB              # VACUUM 等维护操作  

---  

5. Background Writer Maxwritten  
问题描述: bgwriter 达到 max pages 限制 12590 次  
建议:  
-- 检查当前 bgwriter 参数  
SHOW bgwriter_lru_maxpages;  
SHOW bgwriter_flush_after;  
SHOW bgwriter_lru_multiplier;  
-- postgresql.conf 调优建议  
bgwriter_lru_maxpages = 1000           # 提高到 1000  
bgwriter_lru_multiplier = 2.0           # 保持合理的预写比例  
bgwriter_flush_after = 512kB           # 批量刷盘  

---  

6. 表膨胀严重 (Table Bloat)  
问题描述:  
| 表名 | 大小 | 膨胀率 | 浪费空间 |  
|------|------|--------|----------|  
| pgbench_accounts | 2.29 GB | 88.65% | 2.03 GB |  
| pgbench_history | 598 MB | 54% | 314 MB |  
建议:  
-- 1. 手动执行 VACUUM (不会锁表，但可能不完全回收空间)  
VACUUM VERBOSE ANALYZE pgbench_accounts;  
-- 2. 执行 VACUUM FULL (会锁表，完全回收空间)  
VACUUM FULL pgbench_accounts;  
-- 3. 或者使用 pg_repack (在线回收，不阻塞读写)  
-- 需要安装 pg_repack 扩展  
预防措施:  
-- 检查 autovacuum 设置  
SHOW autovacuum;  
SHOW autovacuum_vacuum_threshold;  
SHOW autovacuum_vacuum_scale_factor;  
-- postgresql.conf 调优  
autovacuum = on  
autovacuum_naptime = 30s  
autovacuum_vacuum_scale_factor = 0.05  
autovacuum_vacuum_cost_delay = 2ms  

---  

7. 索引膨胀 (Index Bloat)  
问题描述:  
| 索引名 | 大小 | 膨胀率 |  
|--------|------|--------|  
| pgbench_tellers_pkey | 31.79 MB | 99.94% |  
| pgbench_branches_pkey | 6.42 MB | 99.97% |  
建议:  
-- 重建膨胀的索引  
REINDEX INDEX pgbench_tellers_pkey;  
REINDEX INDEX pgbench_branches_pkey;  
-- 或者使用 CONCURRENTLY (不阻塞读写)  
REINDEX INDEX CONCURRENTLY pgbench_tellers_pkey;  

---  

✅ 正常项目  

| 检查项 | 状态 | 说明 |  
|--------|------|------|  
| 无效索引 | ✅ OK | 无损坏索引 |  
| XID 回绕 | ✅ OK | 风险低 |  
| 死锁 | ✅ OK | 无死锁发生 |  
| 连接数 | ✅ OK | 仅使用 8% |  
| 回滚率 | ✅ OK | 正常范围 |  
| WAL 归档 | ✅ OK | 正常 |  

---  

📊 存储使用统计  
| 数据库 | 大小 |  
|--------|------|  
| postgres | 3501 MB |  
| gemini_test | 8 MB |  
| contrib_regression | 8 MB |  
| test | 7 MB |  

---  

🔧 立即行动建议  

紧急 (立即处理)  

1. 终止阻塞会话  
SELECT pg_terminate_backend(23716);  -- 终止 idle in transaction 会话  
2. 重建膨胀索引  
REINDEX INDEX pgbench_tellers_pkey;  
REINDEX INDEX pgbench_branches_pkey;  

短期 (本周内)  

3. 执行表清理  
VACUUM FULL pgbench_accounts;  
VACUUM FULL pgbench_history;  
4. 调优 bgwriter 参数  

长期 (配置优化)  

5. 优化 postgresql.conf  
shared_buffers = 4GB  
effective_cache_size = 12GB  
work_mem = 64MB  
maintenance_work_mem = 1GB  
bgwriter_lru_maxpages = 1000  
idle_in_transaction_session_timeout = 60000  
6. 启用自动清理监控  
-- 创建监控视图  
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;  
-- 设置合理的 autovacuum 参数  

---  

注意: 该实例正在运行 pgbench 测试负载，以上问题主要是测试场景下的正常现象。如果是生产环境，建议根据实际业务负载调整参数。