Ubuntu中PostgreSQL日志管理技巧 - 问答

Ubuntu中PostgreSQL日志管理技巧

PostgreSQL在Ubuntu上的日志默认由logging_collector参数控制，需开启后才会捕获运行日志。配置文件路径为/etc/postgresql/<version>/main/postgresql.conf（为PostgreSQL版本号，如12、14）。关键配置项包括：

开启日志收集：设置logging_collector = on（取消注释）；
设置日志目录：log_directory = 'pg_log'（相对$PGDATA路径，Ubuntu下实际路径为/var/lib/postgresql/<version>/main/pg_log）；
定义日志文件名：log_filename = 'postgresql-%Y-%m-%d.log'（按天分割，格式为“postgresql-年-月-日.log”）；
日志轮转策略：log_rotation_age = 1d（每日轮转）、log_rotation_size = 0（禁用按大小轮转，优先时间分割）；
日志覆盖方式：log_truncate_on_rotation = on（轮转时清空原文件而非追加，避免日志文件无限增大）。
修改后需重启PostgreSQL服务使配置生效：sudo systemctl restart postgresql。

日志轮转是防止日志占满磁盘的关键。Ubuntu下可通过系统logrotate工具或PostgreSQL自身配置实现：

系统logrotate配置：
Ubuntu自带PostgreSQL日志轮转规则（路径：/etc/logrotate.d/postgresql-common），默认配置如下：
```
/var/log/postgresql/*.log {
    daily
    missingok
    rotate 7
    compress
    delaycompress
    notifempty
    create 0600 postgres postgres
    sharedscripts
    postrotate
        [ -f /var/run/postgresql/*.pid ] && kill -USR1 `cat /var/run/postgresql/*.pid`
    endscript
}
```
解释：每日轮转（daily）、保留7天（rotate 7）、压缩旧日志（compress）、轮转时向PostgreSQL发送USR1信号触发日志切换（postrotate脚本）。无需额外配置，系统会自动执行。
PostgreSQL自身配置：
若需更灵活的轮转策略（如按大小轮转），可修改postgresql.conf：
```
log_rotation_age = 0  # 禁用时间轮转
log_rotation_size = 100MB  # 日志文件达到100MB时轮转
log_truncate_on_rotation = on  # 轮转时覆盖原文件
```
此配置适合日志量大的场景，避免日志文件过多。

日志分析是优化数据库性能的重要手段，常用工具为pgBadger（轻量级日志分析工具，生成HTML报告）：

安装pgBadger：
```
sudo apt install pgbadger
```
生成分析报告：
假设日志文件路径为/var/lib/postgresql/14/main/pg_log/postgresql-*.log，执行以下命令生成当天报告：
```
pgbadger /var/lib/postgresql/14/main/pg_log/postgresql-*.log -o /var/www/html/postgresql_report.html
```
报告包含慢查询Top10、高频查询、锁等待、自动vacuum等信息，用浏览器打开即可查看。
定时自动分析：
通过crontab设置每天凌晨分析前一天的日志，生成报告到Web目录：
```
0 2 * * * pgbadger /var/lib/postgresql/14/main/pg_log/postgresql-$(date -d "yesterday" +"%Y-%m-%d").log -o /var/www/html/postgresql_report.html
```
此配置会每天生成前一天的报告，方便管理员查看历史性能数据。

日常运维中，常需实时查看日志以快速定位问题（如连接失败、查询超时）：

查看实时日志：
使用tail -f命令实时输出日志内容，例如：
```
sudo tail -f /var/log/postgresql/postgresql-14-main.log
```
若日志路径不同（如自定义log_directory），需替换为实际路径。
过滤关键信息：
结合grep命令过滤特定关键字，例如查看连接失败的日志：
```
sudo grep "connection failed" /var/log/postgresql/postgresql-14-main.log
```
或查看慢查询（需提前开启log_min_duration_statement参数）：
```
sudo grep "duration: " /var/log/postgresql/postgresql-14-main.log
```
这些命令能快速定位问题根源，减少排查时间。

日志格式选择：
默认日志格式为stderr，若需结构化分析（如导入数据库），可设置为csvlog（需开启logging_collector）：
```
log_destination = 'stderr, csvlog'
log_filename = 'postgresql-%Y-%m-%d.csv'
```
CSV格式日志可直接导入数据库，方便后续分析。
日志内容细化：
通过log_statement参数控制记录的SQL语句类型：
- none：不记录SQL语句；
- ddl：记录数据定义语言（如CREATE、ALTER、DROP）；
- mod：记录修改数据的SQL（如INSERT、UPDATE、DELETE）；
- all：记录所有SQL语句（生产环境慎用，会产生大量日志）。
  例如，记录所有修改操作的SQL：
```
log_statement = 'mod'
```
日志权限控制：
为防止日志泄露敏感信息，可设置日志文件权限：
```
log_file_mode = 0600  # 仅owner（postgres用户）可读写
```
此配置需在postgresql.conf中设置，重启后生效。

0 赞

0 踩