PostgreSQL 在 Linux 上的日志分析方法
一 日志位置与快速查看
- 定位日志目录与文件名
- 在数据库中执行:SHOW log_directory; SHOW log_filename; 获取实际路径与命名规则。
- 常见路径示例
- RHEL/CentOS 常见:/var/log/postgresql/postgresql-<版本>-main.log
- Debian/Ubuntu 常见:/var/log/postgresql/
- 自定义路径:由 log_directory 指定(可为绝对路径,通常在 $PGDATA 下,如 $PGDATA/pg_log)
- 常用查看命令
- 实时查看:tail -f /var/log/postgresql/postgresql-12-main.log
- 分页查看:less /var/log/postgresql/postgresql-12-main.log
- 关键字过滤:grep “ERROR” /var/log/postgresql/postgresql-12-main.log
- 图形化查看
- 使用 pgAdmin 的“日志”选项卡可实时查看并按时间/级别筛选。
二 日志采集与关键配置
- 启用日志收集与目录
- logging_collector = on
- log_destination = ‘stderr’(文本)或 ‘csvlog’(结构化,便于解析)
- log_directory = ‘pg_log’(或自定义绝对路径)
- log_filename = ‘postgresql-%Y-%m-%d.log’
- 日志轮转
- log_rotation_age = 1d
- log_rotation_size = 10MB
- log_truncate_on_rotation = off
- 行前缀与可读性
- log_line_prefix = '%t [%p] %r %d %u '(包含时间、进程、客户端、库、用户)
- 记录策略
- log_min_duration_statement = 1000(单位毫秒,记录超过阈值的慢 SQL)
- log_checkpoints = on
- log_connections = on
- log_disconnections = on
- log_lock_waits = on
- log_statement = ddl(或 mod;生产不建议 all,避免日志爆炸)
- 生效方式
- 修改 postgresql.conf 后执行:pg_ctl reload;若涉及 logging_collector 等变更需 restart。
三 使用 pgBadger 生成分析报告
- 安装
- 方式一:发行版包管理器安装(如 yum/apt)
- 方式二:源码安装(perl 环境),完成后执行 pgbadger –version 验证
- 基本用法
- 文本日志:pgbadger /var/lib/pgsql/data/pg_log/postgresql-*.log -o report.html
- CSV 日志:pgbadger -f csvlog /path/*.csv -o report.html
- 指定时间窗:pgbadger --begin=‘2025-04-20 00:00:00’ --end=‘2025-04-24 23:59:59’ *.log
- 报告内容
- 慢查询 Top 10、高频查询、锁等待、检查点、连接/断开等图表与统计
- 定时任务
- 每天凌晨分析前一天日志并覆盖输出:
- 0 2 * * * /usr/bin/pgbadger /var/lib/pgsql/data/pg_log/postgresql-$(date -d “yesterday” +“%Y-%m-%d”).log -o /var/www/html/report.html
四 命令行与 SQL 辅助分析
- 实时排查
- 跟踪错误与慢 SQL:tail -f /var/log/postgresql/*.log | egrep “ERROR|duration:”
- 当前活动会话
- SELECT pid, usename, application_name, client_addr, query_start, state, query FROM pg_stat_activity;
- 语句级统计(需预加载扩展)
- 配置:shared_preload_libraries = ‘pg_stat_statements’(重启)
- 查询:SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 20;
- 图形化辅助
- pgAdmin 日志页可实时查看并按时间/级别筛选,便于快速定位问题。
五 日志轮转与归档实践
- 按时间与大小切割
- 使用 log_rotation_age / log_rotation_size 控制切割,避免单文件过大
- 长期保存与集中化
- 将切割后的日志压缩归档,并通过 Logstash / Fluentd / rsync 等工具集中到远程存储或备份系统
- 注意事项
- 不要与 WAL(pg_xlog/pg_wal) 和 事务提交日志(pg_clog/pg_xact) 混淆,这些不是运行日志,涉及恢复与复制,严禁随意删除或移动。