Debian系统下PostgreSQL日志管理技巧

Debian下PostgreSQL日志管理实用技巧

一 快速定位与查看日志

• 常见日志路径为：/var/log/postgresql/，文件名通常为postgresql-{版本}-main.log或postgresql-{版本}-{实例名}.log。若不确定，可在配置中查看log_directory与log_filename的实际值。实时查看可用：tail -f /var/log/postgresql/postgresql-12-main.log；按关键字过滤可用：grep ERROR /var/log/postgresql/postgresql-12-main.log。以上路径与命令适用于Debian环境。

二 核心配置与生效方式

• 编辑配置文件：/etc/postgresql/{version}/main/postgresql.conf。常用关键参数与作用如下（按需调整）：
  • 启用与路径
    • logging_collector = on：开启日志收集器（文件日志）
    • log_directory = 'pg_log'：日志目录（相对数据目录，Debian常见为/var/log/postgresql）
    • log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'：按时间命名，便于轮转与检索
  • 记录内容
    • log_statement = 'mod'：记录DDL与数据修改；可选值：none/ddl/mod/all
    • log_min_duration_statement = 1000：记录执行时间超过1000 ms的慢SQL
    • log_checkpoints = on、log_connections = on、log_disconnections = on、log_lock_waits = on：记录检查点、连接/断开、锁等待等关键信息
  • 输出格式
    • log_destination = 'csvlog'：CSV格式便于解析与导入分析工具
    • log_line_prefix = '%m %p %u %d %r'：前缀含时间、进程、用户、数据库、客户端地址，便于过滤
  • 生效方式：修改后执行systemctl restart postgresql（或service postgresql restart）使配置生效。

三 日志轮转与清理

• 内置按时间与大小轮转（推荐优先使用）
  • log_rotation_age = 1d：单个日志保留1天
  • log_rotation_size = 10MB：达到10MB即轮转
  • log_truncate_on_rotation = on：同名文件轮转时覆盖而非追加
• 系统级logrotate兜底（Debian默认使用rsyslog与logrotate）
  • 示例（/etc/logrotate.d/postgresql）：

    /var/log/postgresql/*.log {
        daily
        rotate 7
        compress
        missingok
        notifempty
        create 0644 postgres postgres
        sharedscripts
        postrotate
            systemctl reload postgresql >/dev/null 2>&1 || true
        endscript
    }
    

  • 要点：保留周期与压缩策略按磁盘与合规要求设定；postrotate中执行reload可确保文件句柄正常切换。

四 日志分析与可视化

• 使用pgBadger生成HTML报告（支持CSV与文本日志）
  • 分析当天日志：pgbadger /var/log/postgresql/*.log -o /var/www/html/pg-report.html
  • 分析时间范围：pgbadger --begin='2025-04-20 00:00:00' --end='2025-04-24 23:59:59' *.log
  • 报告包含：慢查询Top、高频SQL、锁等待、错误统计等，便于定位性能与稳定性问题
• 定时报告（每日02:00分析前一日日志）
  • 0 2 * * * /usr/bin/pgbadger /var/log/postgresql/postgresql-$(date -d "yesterday" +"%Y-%m-%d").log -o /var/www/html/report.html

五 故障排查与运行时诊断

• 实时会话与阻塞查询
  • 当前活动会话：SELECT pid, usename, application_name, client_addr, query_start, state, query FROM pg_stat_activity;
• 查询性能分析
  • 执行计划：EXPLAIN SELECT ...; 识别全表扫描、缺少索引等问题
• 语句级统计
  • 启用扩展：在postgresql.conf中设置shared_preload_libraries = 'pg_stat_statements'，重启后创建扩展并查询：SELECT * FROM pg_stat_statements;
• 日志与配置核对
  • 快速确认日志目录与命名：grep -E 'log_filename|log_directory' /etc/postgresql/{version}/main/postgresql.conf