PostgreSQL 监控与诊断体系：pg_stat_activity、慢查询、pg_stat_statements 与 Prometheus 告警

SELECT * FROM pg_stat_database WHERE datname = current_database();

SELECT
  datname,
  round(100.0 * blks_hit / GREATEST(blks_hit + blks_read, 1), 2) AS cache_hit_ratio
FROM pg_stat_database;
-- 命中率低于 99% 通常说明 shared_buffers 偏小或全表扫描过多

-- 高回滚率往往是应用层异常/事务冲突
SELECT datname,
       xact_commit, xact_rollback,
       round(100.0 * xact_rollback / GREATEST(xact_commit + xact_rollback, 1), 2) AS rollback_pct,
       temp_files, pg_size_pretty(temp_bytes) AS temp_used
FROM pg_stat_database
WHERE datname NOT IN ('postgres', 'template0', 'template1')
ORDER BY rollback_pct DESC;

SELECT pid, usename, datname, state,
       wait_event_type, wait_event,
       query_start, xact_start,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE datname IS NOT NULL
ORDER BY query_start;

-- 按等待事件聚合，定位系统瓶颈
SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY wait_event_type, wait_event
ORDER BY count(*) DESC;

-- 1. 找出运行最久的查询
SELECT pid, now() - query_start AS dur,
       state, wait_event_type, wait_event,
       query
FROM pg_stat_activity
WHERE state = 'active' AND query_start IS NOT NULL
ORDER BY query_start ASC LIMIT 20;

-- 2. 找出 idle in transaction（见事务专题）
SELECT pid, state, now() - state_change AS idle_for,
       left(query, 50) AS query
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%'
ORDER BY state_change ASC;

-- 记录执行超过 1 秒的所有语句
ALTER SYSTEM SET log_min_duration_statement = '1000ms';
ALTER SYSTEM SET log_destination = 'stderr';
SELECT pg_reload_conf();

2026-09-27 10:00:01 UTC [12345] LOG:  duration: 2345.123 ms  statement: SELECT * FROM orders WHERE user_id = 1001 ORDER BY created_at DESC

-- 需在共享库中加载（改后重启）
ALTER SYSTEM SET shared_preload_libraries = 'auto_explain';
ALTER SYSTEM SET auto_explain.log_min_duration = '1000ms';
ALTER SYSTEM SET auto_explain.log_analyze = 'on';
ALTER SYSTEM SET auto_explain.log_buffers = 'on';
ALTER SYSTEM SET auto_explain.log_nested_statements = 'on';
-- 重启后生效

-- 当前会话手动开启（调试时非常有用）
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '100ms';
SET auto_explain.log_analyze = 'on';
-- 之后执行的慢查询会自动输出执行计划到日志

-- 需重启生效
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM SET pg_stat_statements.max = 5000;
ALTER SYSTEM SET pg_stat_statements.track = 'top';
-- 重启后
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 按总耗时排序的 Top 语句
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  calls,
  rows,
  round(100.0 * shared_blks_hit /
        GREATEST(shared_blks_hit + shared_blks_read, 1), 2) AS hit_ratio,
  left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 平均耗时最长的 Top 10
SELECT round(mean_exec_time::numeric,2) AS avg_ms,
       calls, rows,
       left(query, 80) AS query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;

-- 调用次数最多（可能是热点语句）
SELECT calls, round(total_exec_time::numeric,2) AS total_ms,
       round(mean_exec_time::numeric,2) AS avg_ms,
       left(query, 80) AS query
FROM pg_stat_statements
ORDER BY calls DESC LIMIT 10;

# docker-compose 片段
services:
  postgres_exporter:
    image: prometheuscommunity/postgres-exporter:latest
    environment:
      DATA_SOURCE_NAME: "postgresql://monitor:secret@postgres:5432/postgres?sslmode=disable"
    ports:
      - "9187:9187"
    depends_on: [postgres]

# prometheus.yml 片段
scrape_configs:
  - job_name: postgres
    static_configs:
      - targets: ["postgres_exporter:9187"]

-- 最小权限监控账号
CREATE ROLE monitor LOGIN PASSWORD 'secret';
GRANT CONNECT ON DATABASE mydb TO monitor;
GRANT pg_monitor TO monitor;  -- PG 10+ 内置监控角色

# Prometheus 告警规则（postgres_alerts.yml）
groups:
  - name: postgres-critical
    rules:
      - alert: PostgresDown
        expr: pg_up == 0
        for: 1m
        labels: { severity: critical }

      - alert: ConnectionSaturation
        expr: pg_stat_database_numbackends > (pg_settings_max_connections * 0.8)
        for: 5m
        labels: { severity: warning }

      - alert: DeadTupleHigh
        expr: pg_stat_user_tables_n_dead_tup > 10000
        for: 10m
        labels: { severity: warning }

      - alert: ReplicationLag
        expr: pg_replication_lag > 104857600   # 100MB
        for: 5m
        labels: { severity: critical }

      - alert: IdleInTransaction
        expr: pg_stat_activity_idle_in_transaction_seconds > 300
        for: 2m
        labels: { severity: warning }

      - alert: CacheHitLow
        expr: pg_stat_database_blks_hit /
              (pg_stat_database_blks_hit + pg_stat_database_blks_read) < 0.99
        for: 15m
        labels: { severity: warning }

-- 数据库级体检
SELECT
  d.datname,
  d.numbackends,
  round(100.0 * d.blks_hit / GREATEST(d.blks_hit + d.blks_read, 1), 2) AS hit_ratio,
  d.deadlocks,
  d.temp_files,
  pg_size_pretty(pg_database_size(d.datname)) AS db_size
FROM pg_stat_database d
WHERE d.datname NOT IN ('postgres', 'template0', 'template1')
ORDER BY pg_database_size(d.datname) DESC;

-- 连接打满时：找出占用连接的应用
SELECT usename, application_name, count(*)
FROM pg_stat_activity
GROUP BY usename, application_name
ORDER BY count(*) DESC;

-- 清理空闲连接（谨慎）
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state = 'idle' AND now() - state_change > INTERVAL '10 minutes';

-- 检查 WAL 写入与检查点
SELECT checkpoints_timed, checkpoints_req,
       checkpoint_write_time, checkpoint_sync_time,
       wal_bytes
FROM pg_stat_bgwriter;

-- 查看每个数据库的 IO
SELECT datname, blk_read_time, blk_write_time
FROM pg_stat_database
WHERE datname NOT IN ('postgres', 'template0', 'template1');