从 SQL Server 到 PostgreSQL：把统计信息和日志变成可用的监控

从 SQL Server 转向 PostgreSQL，最容易踩的坑不是 SQL 语法，而是监控思维。SQL Server DBA 习惯通过 DMV、Query Store、Extended Events 和 SSMS 在问题发生后追查原因；PostgreSQL 则要求你提前决定：哪些信息要记录、记录到哪里，以及监控账号能不能读到它们。

如果没有提前启用对应统计和日志，凌晨 3:07 出现慢查询时，你可能只能看到 CPU、磁盘和几个累计计数器，却无法知道问题何时开始、是哪条 SQL 触发了压力，更无法还原它当时使用的执行计划。

PostgreSQL 的监控数据分别在哪里

日常查询调优通常要看四类数据源：

• pg_stat_activity：当前正在运行的会话、查询、状态和等待事件。
• pg_stat_* 视图：表、索引和数据库级别的累计计数器。
• pg_stat_statements：按归一化 SQL 聚合的调用次数、执行时间、行数和缓冲区活动。
• 日志：慢语句、锁等待、临时文件、检查点、自动清理、错误和执行计划等信息。

它们和 SQL Server 的对应关系并不完全对称：

SQL Server 更像是“引擎默认持续记录，出问题后再查询”；PostgreSQL 更像是“先配置记录策略，之后才能检索”。这不是谁绝对更好，而是默认设计不同。

先分清快照、累计值和日志

pg_stat_activity 是实时快照

下面的查询可以查看当前仍在运行的客户端请求，并按执行时长排序：

SELECT
    pid,
    state,
    wait_event_type,
    wait_event,
    backend_type,
    now() - query_start AS duration,
    left(query, 120) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND state <> 'idle'
ORDER BY duration DESC;

pg_stat_activity 展示的是执行瞬间的状态，并不会像 SQL Server 的等待统计那样积累每类等待时间。你能看到一个会话此刻正在等待 Lock、IO、Client 或其他事件，但不能直接从核心系统中查询“过去一天总共等待锁多长时间”。

如果需要历史等待数据，可以让监控系统高频采样 pg_stat_activity，或者评估 pg_wait_sampling 扩展。采样频率越低，短暂但重要的等待越容易被漏掉。

大多数 pg_stat_* 是累计计数器

表和数据库统计信息通常从服务器启动或统计信息重置后持续累加。单独看一次 seq_scan 的值意义有限，比较两个时间点的差值才有用：

SELECT
    relname,
    seq_scan,
    idx_scan,
    n_live_tup,
    n_dead_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 20;

真正的监控逻辑应该保存定期快照，然后计算一段时间内的增量。例如每 5 分钟采集一次，才能回答“过去一小时哪些表发生了大量顺序扫描”。

启用 pg_stat_statements，但别把它当成完整的 Query Store

pg_stat_statements 是 PostgreSQL 中最重要的查询聚合统计扩展。它能记录归一化 SQL 的调用次数、总执行时间、平均执行时间、返回行数、共享缓冲区读写和规划时间等指标。

但它有两个关键边界：

1. 所有指标本质上都是累计值，需要定期采集并计算差值，或者使用外部工具保存历史。
2. 它记录的是聚合后的运行统计，不等同于完整的 Query Store，也不能自动提供所有历史执行计划。

启用过程需要修改 shared_preload_libraries 并重启服务器。仅执行 CREATE EXTENSION 不够，因为该模块必须在服务器启动时加载到共享内存中。

下面是一个可改造的检查和查询示例。请先将 <your-password> 替换为安全的凭据，并根据环境选择 ALTER SYSTEM、参数组或托管平台配置：

-- 需要管理员权限；修改后必须完整重启 PostgreSQL
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

-- 重启服务器后，在每个需要监控的数据库中执行一次
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 确认模块确实已经加载
SELECT setting
FROM pg_settings
WHERE name = 'shared_preload_libraries';

-- 确认视图可以返回统计数据
SELECT count(*) FROM pg_stat_statements;

-- 找出累计执行时间最高的 SQL
SELECT
    calls,
    round(total_exec_time::numeric, 2) AS total_exec_ms,
    round(mean_exec_time::numeric, 2) AS mean_exec_ms,
    rows,
    left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 为监控工具创建非超级用户账号
CREATE ROLE monitoring LOGIN PASSWORD '<your-password>';
GRANT pg_monitor TO monitoring;

pg_monitor 是比手工拼接权限更稳妥的选择。它包含读取统计信息、配置和表扫描统计所需的一组预定义权限，作用上接近 SQL Server 的 VIEW SERVER STATE，但仍应根据公司安全策略进一步限制登录来源和凭据管理方式。

需要注意权限可见性：没有超级用户、pg_monitor、pg_read_all_stats 等权限时，用户只能看到自己会话的完整查询文本，其他用户的查询文本可能显示为 NULL。监控工具如果没有足够权限，往往会表现为“指标存在，但 SQL 文本缺失”。

日志不是故障备份，而是 PostgreSQL 的主要诊断记录

PostgreSQL 中很多关键问题没有可供事后查询的 DMV。锁等待、临时文件、检查点压力和慢 SQL，如果发生时没有产生日志，事后通常无法补回。

先决定日志写到哪里

自托管环境可以从下面的基础配置开始：

logging_collector = on
log_destination = 'stderr'
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d.log'
log_file_mode = 0640
log_rotation_age = 1d
log_timezone = 'UTC'

stderr 的含义取决于 PostgreSQL 如何启动：可能进入 systemd journal，也可能被容器运行时或云平台收集。启用 logging_collector 后，PostgreSQL 会把标准错误流写入日志文件。

也可以使用结构化格式：

log_destination = 'stderr,jsonlog'

jsonlog 适合日志采集器，但需要确认现有监控系统是否支持这种格式。csvlog 便于加载到表中分析，syslog 则可以交给操作系统日志服务处理。

日志目录最好放在 $PGDATA 之外。数据目录通常要求权限为 0700 或 0750，普通监控代理无法穿过目录读取其中的日志。即使日志文件本身是 0640，代理仍可能因为无法遍历父目录而收到 permission denied。

可以这样准备目录并验证权限：

sudo mkdir -p /var/log/postgresql
sudo chown postgres:postgres /var/log/postgresql
sudo chmod 750 /var/log/postgresql

# 将监控代理加入拥有目录读取权限的组
sudo usermod -a -G postgres pganalyze

# 修改 PostgreSQL 配置后重新加载
sudo -u postgres psql -c "ALTER SYSTEM SET log_directory = '/var/log/postgresql';"
sudo -u postgres psql -c "ALTER SYSTEM SET log_file_mode = '0640';"
sudo -u postgres psql -c "SELECT pg_reload_conf();"

# 使用代理实际运行的身份检查，而不是只用 root 检查
sudo -u pganalyze test -r /var/log/postgresql/postgresql-$(date +%F).log

将用户加入新组后，通常需要重启监控代理服务。验证时不要只用 root，因为 root 能读到的文件不代表监控进程也能读到。

托管数据库的处理方式会不同：日志可能被转发到 CloudWatch、Azure Monitor、Cloud Logging 或平台自己的日志服务。此时需要同时确认两件事：日志事件是否已开启，以及监控工具是否有权限访问这些日志。

用 log_line_prefix 保留身份信息

文本日志没有固定 schema，因此每行前缀中没有写入的信息，后续工具无法凭空恢复。一个实用的起点是：

log_line_prefix = '%m [%p] %q[user=%u,db=%d,app=%a] '
log_timezone = 'UTC'

这里包含：

• %m：带毫秒的时间戳。
• %p：进程 ID。
• %u：数据库用户。
• %d：数据库名。
• %a：应用名。
• %q：让非会话进程在此处停止输出前缀。

末尾的空格很重要。很多日志解析器依赖这个空格区分前缀和消息内容。修改前缀后，如果监控系统突然无法解析日志，应该优先检查这个字符是否被复制或模板处理过程删除。

不要为了“以后可能用到”而把所有转义字段都放进去。过长的前缀会增加磁盘、网络传输和日志平台计费；启用主机名解析还可能引入反向 DNS 开销。更重要的是，前缀格式必须和日志采集工具兼容。

一套适合起步的日志策略

下面的配置偏向于保留高价值事件，同时避免把所有 SQL 都写入日志：

# 记录执行时间至少达到 1 秒的语句
log_min_duration_statement = 1000

# 对 100ms 到 1s 的语句做小比例采样
log_min_duration_sample = 100
log_statement_sample_rate = 0.05

# 锁等待、临时文件和后台活动
log_lock_waits = on
log_temp_files = 0
log_checkpoints = on
log_autovacuum_min_duration = '60s'

# 连接活动；高流量环境应评估日志量
log_connections = on
log_disconnections = on

# 只记录 DDL，不要在生产环境轻易设置为 all
log_statement = 'ddl'
log_error_verbosity = default

log_min_duration_statement 是非常有价值的起点：只有语句完成且超过阈值时才记录文本。对于扩展查询协议，绑定参数通常会出现在相邻的 DETAIL 行中，但仍应在实际环境中确认参数是否被记录，以及是否存在敏感信息泄露风险。

不要本能地把 log_statement = 'all' 或 log_duration = on 打开。繁忙服务器可能每小时产生数 GB 的噪声，既增加磁盘写入，也会淹没真正重要的慢查询和资源压力事件。用持续时间阈值配合采样，通常比记录所有语句更实用。

如果需要捕获真实生产执行计划，可以评估 auto_explain。它必须在查询执行时工作，不能事后从 PostgreSQL 核心系统中找回已经结束的计划，因此仍然需要提前配置合理的日志级别、采样率和计划捕获范围。

与 SQL Server 的差异，应该怎样取舍

SQL Server 的 Query Store、Extended Events 和 DMV 体系确实有明显优势：

• Query Store 可以持久化查询文本、执行计划和运行统计。
• Extended Events 提供结构化事件流和相对稳定的事件 schema。
• 计划缓存允许 DBA 查询已经执行过的编译计划；在特定版本和配置下，还能保留最后一次实际执行计划。
• 很多历史问题不需要事先专门部署采样器或日志策略。

PostgreSQL 的优势在于日志输出路径和采集方式更加灵活。你可以把日志同时写到本地、结构化输出和云端日志平台；log_min_duration_statement 在没有慢查询时几乎不产生额外噪声；auto_explain 还能捕获事先没有被专门关注的生产查询计划。

真正需要改变的是运维习惯：不要把日志只当成“启动失败或数据损坏时才查看的错误日志”。在 PostgreSQL 中，它是长期性能分析和事故复盘的重要数据源。

上线前检查清单

建议在生产流量进入前完成以下检查：

• 启用 pg_stat_statements，修改 shared_preload_libraries 并完成重启。
• 在每个需要监控的数据库中执行 CREATE EXTENSION。
• 验证 pg_stat_statements 能返回数据，而不是只确认扩展创建成功。
• 为监控账号授予 pg_monitor，避免直接使用超级用户。
• 设置 log_min_duration_statement，从明确的慢查询阈值开始。
• 设置带用户、数据库和应用名的 log_line_prefix，保留末尾空格。
• 打开 log_lock_waits、log_temp_files、检查点和自动清理日志。
• 将日志目录移出 $PGDATA，并以监控代理的实际身份验证读取权限。
• 让监控系统保存累计统计的时间序列，而不是只展示单次快照。
• 对日志保留周期、敏感参数、磁盘容量和采集成本做定期复核。

从 SQL Server 转到 PostgreSQL，最危险的误解是认为“数据库已经运行，所以以后总能查到发生过什么”。在 PostgreSQL 中，能否回答下一次事故的问题，往往取决于你今天是否打开了正确的统计和日志开关。