Ubuntu PostgreSQL日志管理技巧

Ubuntu 上高效管理 PostgreSQL 日志的实用技巧

一 日志位置与快速查看

• 常见路径与方式
  • Ubuntu 常见安装方式下，运行日志位于：/var/log/postgresql/postgresql--main.log（按版本号区分，如 14）。
  • 使用 systemd 时，可直接用 journalctl 查看服务日志：sudo journalctl -u postgresql.service -f。
  • 若采用内置日志收集器（logging_collector=on），日志可能在 $PGDATA/pg_log/（Ubuntu 常见为 /var/lib/postgresql//main/）。
• 快速排查命令
  • 实时查看：sudo tail -f /var/log/postgresql/postgresql-14-main.log
  • 按时间过滤：sudo journalctl -u postgresql.service -f --since "2025-11-19 00:00:00" --until "2025-11-19 12:00:00"。

二 核心参数与推荐配置

• 启用日志收集与结构化输出
  • logging_collector = on
  • log_destination = 'csvlog'（便于解析；也可同时保留 stderr）
  • log_directory = 'pg_log'（相对 $PGDATA）
  • log_filename = 'postgresql-%Y-%m-%d.log'
• 日志轮转与保留
  • log_rotation_age = 1d
  • log_rotation_size = 100MB
  • log_truncate_on_rotation = on（同名文件轮转时覆盖，避免追加膨胀）
• 记录关键事件与性能线索
  • log_checkpoints = on
  • log_connections = on
  • log_disconnections = on
  • log_lock_waits = on
  • log_min_duration_statement = 1000（单位毫秒，记录超过 1 秒 的慢查询）
  • log_statement = 'ddl'（按需：none/ddl/mod/all）
• 生效方式
  • 修改 postgresql.conf 后执行：sudo systemctl restart postgresql（或 pg_ctl restart）。

三 日志轮转与自动清理

• 内置轮转
  • 通过 log_rotation_age 与 log_rotation_size 控制按天或按大小切分，配合 log_truncate_on_rotation = on 避免同名文件追加。
• 外部轮转
  • 使用 logrotate（适合与系统统一日志策略集成），示例：
    • 创建配置：/etc/logrotate.d/postgresql
    • 内容示例：

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

• 定时清理旧日志
  • 以 postgres 用户添加定时任务，清理 7 天前 的日志：
    • sudo crontab -e -u postgres
    • 示例：0 1 * * * find /var/lib/postgresql/14/main/pg_log -type f \( -name '*.log' -o -name '*.csv' \) -mtime +7 -delete >/dev/null 2>&1
  • 注意：Ubuntu 常见 PGDATA 为 /var/lib/postgresql//main，请按实际版本替换。

四 日志分析与可视化

• 使用 pgBadger 生成 HTML 报告
  • 安装后验证：pgbadger --version
  • 分析当天日志：pgbadger /var/lib/postgresql/14/main/pg_log/postgresql-*.log -o /var/www/html/report.html
  • 分析指定时间段：pgbadger --begin='2025-04-20 00:00:00' --end='2025-04-24 23:59:59' *.log
  • 定时生成（每天 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
• CSV 日志入库分析
  • 建表（PostgreSQL 12+ 字段更全）：

    CREATE TABLE IF NOT EXISTS postgres_log (
      log_time timestamp(3) with time zone,
      user_name text,
      database_name text,
      process_id integer,
      connection_from text,
      session_id text,
      session_line_num bigint,
      command_tag text,
      session_start_time timestamp with time zone,
      virtual_transaction_id text,
      transaction_id bigint,
      error_severity text,
      sql_state_code text,
      message text,
      detail text,
      hint text,
      internal_query text,
      internal_query_pos integer,
      context text,
      query text,
      query_pos integer,
      location text,
      application_name text,
      backend_type text
    );
    

  • 导入 CSV：COPY postgres_log FROM '/var/lib/postgresql/14/main/pg_log/postgresql-2025-11-19.csv' WITH (FORMAT csv, HEADER true);

五 易混淆的日志与注意事项

• 区分日志类型
  • pg_log：运行/错误/慢查询等，可配置轮转与清理。
  • pg_wal：WAL（重做/事务）日志，默认 16MB 段，支持归档与流复制，不可随意删除或移动，否则可能导致无法恢复。
  • pg_clog：事务提交状态（事务元数据），强制开启，不能关闭。
• 空间与性能提示
  • 避免开启 log_statement = 'all' 与 log_duration = on 同时记录，易产生大量日志；优先使用 log_min_duration_statement 定位慢 SQL。
  • 若启用了 WAL 归档或流复制，异常时 pg_wal 可能快速增长，需监控磁盘并保障归档链路稳定。