PostgreSQL在Linux上的日志分析方法

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） 混淆，这些不是运行日志，涉及恢复与复制，严禁随意删除或移动。