Ubuntu上PostgreSQL日志分析实操指南
一 快速定位日志文件
- 常见路径与方式：
- 系统日志目录：/var/log/postgresql/，文件名通常包含版本与实例名，如：postgresql-14-main.log。
- 服务日志：journalctl -u postgresql.service -f（实时查看）。
- 若启用了日志收集器（logging_collector），日志可能在数据目录的 pg_log 子目录，如：/var/lib/postgresql/14/main/pg_log/。
- 快速查看示例：
- 实时跟踪最新错误与告警：sudo tail -f /var/log/postgresql/postgresql-$(ls /var/log/postgresql | head -1).log
- 按时间过滤服务日志：journalctl -u postgresql.service --since “2025-12-24 00:00:00” --until “2025-12-24 23:59:59”。
二 启用并优化日志记录
- 编辑配置文件（通常位于：/etc/postgresql/14/main/postgresql.conf 或 /var/lib/postgresql/14/main/postgresql.conf），建议开启：
- 日志收集与格式：
- logging_collector = on
- log_destination = csvlog（便于机器解析；若用文本日志则为 stderr）
- log_directory = ‘pg_log’
- log_filename = ‘postgresql-%Y-%m-%d.log’
- 关键事件记录：
- log_checkpoints = on
- log_connections = on
- log_disconnections = on
- log_lock_waits = on
- log_autovacuum_min_duration = 0（记录所有 autovacuum，按需调整）
- 慢查询阈值（示例为记录超过 1 秒 的语句）：
- log_min_duration_statement = 1000
- 日志行前缀（便于 pgbadger 解析，stderr 模式时尤其重要）：
- log_line_prefix = ‘%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h’
- 日志轮转（示例：按天或达到 10MB 轮转）：
- log_rotation_age = 1440（分钟）
- log_rotation_size = 10240（KB）
- 使配置生效：
- 重新加载：sudo systemctl reload postgresql
- 或重启：sudo systemctl restart postgresql
- 提示：
- 修改前先备份配置；生产环境建议先在测试环境验证。
- 若使用非默认数据目录，请相应调整 log_directory 为绝对路径。
三 使用 pgbadger 生成可视化报告
- 安装：
- sudo apt-get install pgbadger
- 如日志为 CSV 格式，建议安装解析依赖：sudo apt-get install libtext-csv-perl
- 生成报告：
- 分析当天日志（CSV 示例）：
- pgbadger /var/lib/postgresql/14/main/pg_log/postgresql-$(date +%Y-%m-%d).csv -o /var/www/html/report.html -f csv --prefix ‘%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h’
- 分析指定时间范围（stderr 文本日志示例）：
- pgbadger --begin ‘2025-04-20 00:00:00’ --end ‘2025-04-24 23:59:59’ /var/log/postgresql/*.log -o report.html
- 报告解读要点：
- 慢查询 Top 10、高频 SQL、锁等待、检查点/连接/断开统计等，图表化展示，便于定位性能瓶颈与异常行为。
- 定时生成（每天 02:00 分析前一天日志）：
- 0 2 * * * /usr/bin/pgbadger /var/lib/postgresql/14/main/pg_log/postgresql-$(date -d “yesterday” +“%Y-%m-%d”).log -o /var/www/html/report.html。
四 进阶 解析 WAL 日志
- 说明：WAL（Write-Ahead Logging）记录的是物理/逻辑变更，并非 SQL 文本；用于回放、审计与故障定位。
- 工具与方法：
- PostgreSQL 自带工具：pg_waldump（新版本命名，旧版为 pg_xlogdump），可解析 WAL 记录类型、事务号、时间线等，用于低层问题排查。
- 第三方扩展：WalMiner（HighGo），可将 WAL 解析为近似的 SQL 语句，支持从 minimal 级别以上的 WAL 解析，并增强对系统表变更的支持；使用时常需将 wal_level = logical 并创建扩展。
- 典型场景：误删数据后的变更追溯、审计与回放验证（务必在测试环境演练）。