PostgreSQL 日志设置

适用版本：PostgreSQL 13 ~ 17（标注 15+/17+ 的为高版本特性）
定位：从”参数怎么配”到”日志怎么读、事故怎么查”的完整闭环。
阅读路径：① 日志体系总览 → ② 参数全景与生效级别 → ③ csvlog 字段逐列解析 → ④ SQLSTATE 与日志级别 → ⑤ 生产配置模板 → ⑥ 排查 SOP / Checklist。

1. 日志体系总览

PostgreSQL 的日志分三层，不要混淆：

1.1 核心机制（为什么 logging_collector 必须 on）

• 后端进程（backend）本身不直接写日志文件，而是把日志写到 stderr。
• logging_collector = on 时，启动一个专用 logger 后台进程，通过 匿名 pipe 收集所有 backend 写到 stderr 的内容，再按 log_filename 规则切片写盘。
• 因此：
  • 要写文件日志，logging_collector 必须 = on，且它是 postmaster 级参数，改完必须 restart（不是 reload）。
  • log_directory 是相对 data_directory 的路径（除非写绝对路径）。
  • csvlog 不是独立目的地，而是在 stderr 基础上”附加一份 CSV 结构化日志”；log_destination='stderr,csvlog' 可同时输出。
  • 高版本（15+）jsonlog 输出 JSON 行，适合直接进 ES/Loki，无需再解析 CSV。

1.2 log_destination 对比

生产常见组合：log_destination = 'csvlog'，采集端用 Filebeat/Vector 解析 CSV 后送 Loki/ES/Kafka。

2. 参数全景（按职能分组 + 生效级别）

生效级别含义：

• postmaster：改完必须 restart。
• sighup：改完 SELECT pg_reload_conf(); 或 pg_ctl reload 即生效。
• superuser / user：可在会话内 SET。

2.1 输出与轮转（文件形态）

坑：log_truncate_on_rotation = on 只有在新文件名与旧文件同名时才 truncate；log_filename 带秒级 %S 时永远不同名，truncate 永远不生效。要做”固定保留 N 天覆盖”，用 postgresql-%a.log（按星期几命名）。

2.2 日志内容开关（决定记什么）

2.3 日志行前缀 log_line_prefix（完整占位符表）

截图用的前缀：

log_line_prefix = '%m [%p]: [%l] [txid=%x] user=%u,db=%d,app=%a,client=%r '

全部占位符（务必背熟 %c %l %x %i 这几个关联排查用得到的）：

生产精简推荐前缀：

log_line_prefix = '%m [%p] %c txid=%x user=%u db=%d app=%a client=%r '

带 %c 是为了能把”某一次慢 SQL → 锁等待 → 连接断开”串成一条时间线。

3. csvlog 日志逐列解析（对照截图 15 个字段补全）

csvlog 实际有 23 列，截图是核心 15 列，完整对照表如下：

3.1 会话 ID（%c / session_id）反查活动会话

截图给的公式可直接用——把日志里的 session_id 拆出来关联 pg_stat_activity：

-- 日志里看到 session_id = '65eae855.443b'，反查它当前是否还活着、在干什么
SELECT pid, usename, datname, application_name, client_addr,
       state, wait_event_type, wait_event, query, backend_start
FROM pg_stat_activity
WHERE to_hex(trunc(EXTRACT(EPOCH FROM backend_start))::integer) = '65eae855'
  AND to_hex(pid) = '443b';

一个会话从 connect 到 disconnect 的所有日志，靠 session_id 聚合就是完整时间线；这是事故复盘的核心 join key。

3.2 在数据库里直接读日志（PG15+）

-- 列出日志目录下文件（需 pg_read_server_files 角色）
SELECT * FROM pg_ls_logdir();
-- 读取某日志内容
SELECT * FROM pg_read_logfile('postgresql-2026-11-07_020000.log');

4. 日志级别与 SQLSTATE

4.1 日志级别（从低到高）

DEBUG5 < DEBUG4 < DEBUG3 < DEBUG2 < DEBUG1 <
LOG <  INFO < NOTICE < WARNING < ERROR <
LOG(重复) < FATAL < PANIC

• client_min_messages：发给客户端的级别；log_min_messages：写日志文件的级别。
• 生产 log_min_messages = warning；error 及以上必报警。
• STATEMENT 不是 severity，而是 log_statement=all/mod/ddl 记录的语句在 message 里以 statement: ... 出现，级别标记为 LOG。

4.2 SQLSTATE 结构（5 字符）

• 前 2 位 = 错误类，后 3 位 = 子类。
• 常见速查（生产值班必背）：

5. 生产级配置模板（OLTP 16C/128G 实例参考）

5.1 postgresql.conf 生产推荐段

# ========== 日志收集与文件 ==========
logging_collector      = on
log_destination       = 'csvlog'
log_directory         = 'log'
log_filename          = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode        = 0600
log_rotation_age      = 1d
log_rotation_size     = 1GB
log_truncate_on_rotation = off

# ========== 内容粒度 ==========
log_min_messages          = warning
log_min_error_statement   = error
log_error_verbosity        = default
log_line_prefix            = '%m [%p] %c txid=%x user=%u db=%d app=%a client=%r '

# ========== 慢 SQL（核心） ==========
log_min_duration_statement = '300ms'   # 按 P95 业务响应调整；门禁高的库用 1s
log_statement              = 'ddl'      # 审计需求交给 pgaudit，别用 all
log_duration               = off

# ========== 锁 / 死锁 ==========
log_lock_waits       = on
deadlock_timeout     = '200ms'         # 默认 1s 太长，抓 200ms 以上等待更敏感

# ========== 运维信号 ==========
log_checkpoints        = on
log_connections        = off           # 高并发保持 off；安全合规 on
log_disconnections     = off
log_temp_files         = '64MB'        # 排序/哈希落盘告警
log_autovacuum_min_duration = '100ms'
log_replication_commands    = on
log_recovery_conflict_waits = on

# ========== 不要打开的 ==========
# log_statement_stats / log_executor_stats：谁开谁背锅

5.2 auto_explain（慢 SQL 自动带执行计划，强烈推荐）

# 需先在 shared_preload_libraries 加载：
shared_preload_libraries = 'auto_explain'

auto_explain.log_min_duration   = '1s'
auto_explain.log_analyze       = on     # 真实执行统计
auto_explain.log_buffers        = on    # 缓冲区命中/读盘
auto_explain.log_nested_statements = on # 函数内嵌套 SQL 也记录
auto_explain.log_timing         = on    # 高负载采样开销大时改 off
auto_explain.log_triggers       = on

效果：超过 1s 的 SQL 自动把 EXPLAIN ANALYZE 结果写进日志，不用事后 auto_explain 复现。

5.3 合规审计：pgaudit

金融/等保场景需要记录全部 DDL/DML 与登录，不要靠 log_statement=all，装 pgaudit：

shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write, ddl, role'

5.4 动态生效方式

-- 改参数后 reload（postmaster 级参数除外）
ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();

-- 验证
SHOW log_min_duration_statement;
-- 查哪些参数是 postmaster 级、当前值与来源
SELECT name, setting, sourcefile, pending_restart
FROM pg_settings WHERE name LIKE 'log_%' OR name = 'logging_collector';

6. 生产级实践要点

6.1 日志保留与磁盘治理

• pg_log/log 目录必须纳入监控：磁盘水位 > 70% 告警，> 85% 自动清理 N 天前日志（cron + find -mtime +7 -delete）。
• 保留周期建议：在线 7~14 天；合规要求的审计日志送中心化存储长期留存，本地只留热数据。
• 大日志文件排查：先 ls -lh 找最大文件，按 session_id grep 聚合。

6.2 慢日志：日志 vs pg_stat_statements，怎么选

6.3 常见”日志坑”清单

1. logging_collector 改了不重启 → 文件根本没生成，以为参数没生效。
2. log_statement=all 上生产 → 日志 GB/小时，磁盘打满，触发 53100 全站故障。
3. log_connections=on 在万级 TPS 连接池上 → 日志量压垮 I/O。
4. log_file_mode=0666 → 含敏感数据的 SQL 任何系统用户可读。
5. log_line_prefix 不带 %c → 复盘时无法把同一会话的多条日志串起来。
6. log_hostname=on → 每条连接反解 DNS，引发连接延迟毛刺。
7. log_temp_files=-1 忘开 → 排序爆内存落盘却无任何告警。
8. csvlog 直接用文本编辑器 grep → 引号转义把 SQL 拆成多行，应按 CSV 解析（filebeat 或 COPY ... WITH CSV HEADER 入库）。

6.4 日志采集上平台

• Filebeat/Vector 采集 $PGDATA/log/*.csv，用 CSV 解析器拆出 23 列，加 host/cluster/tag 后送 Loki 或 ES。
• 告警规则（PromQL / Loki）：
  • rate(log{severity=”ERROR”}[5m]) > 5 → 数据库报错风暴
  • 53300（too_many_connections）出现即 P1
  • deadlock_detected（40P01）出现即 P2
  • 出现 PANIC / XX000 → P0 立即介入

7. 事故排查 SOP（值班 Checklist）

• 确认时间窗：根据故障时间，定位对应切片日志文件。
• 按 severity 过滤：先抓 ERROR / FATAL / PANIC，看 SQLSTATE。
• 按 session_id 聚合：拿到异常会话的完整时间线（connect → SQL → wait → error → disconnect）。
• 关联活动会话：用第 3.1 节 SQL 反查 pg_stat_activity，确认该会话是否还在跑、等什么（wait_event_type/wait_event）。
• 死锁：日志里 deadlock detected 段落自带两个事务的 SQL 与持锁关系，按 detail 逐行读。
• 慢 SQL：log_min_duration_statement 记录里找 duration: xxx ms 开头的行，看是否伴随 auto_explain 的 Query Plan。
• 锁等待：log_lock_waits 记录 “process %d still waiting for …”，对比持锁进程。
• 检查点抖动：checkpoint starting: time/write 与 checkpoint complete: wrote ... buffers，看 write 耗时是否撞上业务高峰。
• 临时文件暴涨：log_temp_files 记录 temporary file: path "base/pgsql_tmp...", size xxx，定位哪个排序/哈希。
• 磁盘/连接：53100 / 53300 出现时立即查 pg_stat_activity 的 idle in transaction 与连接来源 IP。
• 备库：主从延迟先看 log_recovery_conflict_waits 与 WAL receiver 日志。

8. 附：与 Oracle 的对照（便于 Oracle DBA 快速映射）