印刻数据库

外观

PostgreSQL 日志分析工具 ​

pgBadger安装与配置 ​

pgBadger是PostgreSQL最常用的日志分析工具之一，可以生成详细的HTML格式性能报告，包含慢查询、连接信息、锁等待等多种指标。

安装pgBadger ​

bash
# Debian/Ubuntu系统
apt-get install pgbadger

# CentOS/RHEL系统
yum install pgbadger

# 从源码安装
wget https://github.com/darold/pgbadger/archive/refs/tags/v12.1.tar.gz
tar zxvf v12.1.tar.gz
cd pgbadger-12.1
perl Makefile.PL
make install

配置PostgreSQL日志格式 ​

pgBadger需要特定的日志格式才能正常工作，建议在postgresql.conf中配置以下参数：

bash
# 配置log_line_prefix
echo "log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '" >> postgresql.conf

# 启用详细日志
echo "log_statement = 'ddl'" >> postgresql.conf
# 记录所有耗时超过100毫秒的语句
echo "log_min_duration_statement = 100" >> postgresql.conf
# 启用查询计划记录
echo "log_executor_stats = off" >> postgresql.conf
# 启用死锁日志
echo "log_lock_waits = on" >> postgresql.conf
# 设置日志级别
echo "log_min_messages = notice" >> postgresql.conf

# 重新加载配置
pg_ctl reload -D /var/lib/postgresql/14/main

生成pgBadger报告 ​

bash
# 基本使用
pgbadger /var/log/postgresql/postgresql-14-main.log -o pgbadger-report.html

# 分析压缩日志
pgbadger /var/log/postgresql/postgresql-14-main.log.gz -o pgbadger-report.html

# 分析多个日志文件
pgbadger /var/log/postgresql/postgresql-14-main.log* -o pgbadger-report.html

# 定时生成报告（添加到cron）
echo "0 2 * * * pgbadger /var/log/postgresql/postgresql-14-main.log -o /var/www/html/pgbadger/$(date +\%Y\%m\%d).html" >> /etc/crontab

pgFouine日志分析工具 ​

pgFouine是另一个PostgreSQL日志分析工具，专注于慢查询分析，支持多种输出格式。

安装pgFouine ​

bash
# 下载pgFouine
wget https://github.com/dalibo/pgfouine/archive/refs/tags/v1.2.tar.gz
tar zxvf v1.2.tar.gz
cd pgfouine-1.2

使用pgFouine分析日志 ​

bash
# 生成HTML报告
php pgfouine.php -f /var/log/postgresql/postgresql-14-main.log > pgfouine-report.html

# 生成文本报告
php pgfouine.php -f /var/log/postgresql/postgresql-14-main.log -t text > pgfouine-report.txt

# 仅分析慢查询
php pgfouine.php -f /var/log/postgresql/postgresql-14-main.log -s slow > slow-queries-report.html

pganalyze日志分析平台 ​

pganalyze是一个商业日志分析平台，提供实时监控和高级分析功能，支持多实例管理和告警功能。

安装pganalyze collector ​

bash
# 下载安装脚本
curl -s https://packages.pganalyze.com/install.sh | bash

# 配置pganalyze
vi /etc/pganalyze-collector.conf
# 添加API密钥和数据库连接信息

# 启动服务
systemctl enable pganalyze-collector
systemctl start pganalyze-collector

手动日志分析命令 ​

慢查询分析 ​

bash
# 查找最慢的10个查询
grep "duration:" /var/log/postgresql/postgresql.log | sort -rn -k3 | head -10

# 统计慢查询数量
grep -c "duration:" /var/log/postgresql/postgresql.log

# 计算平均查询时间
grep "duration:" /var/log/postgresql/postgresql.log | awk '{sum += $3} END {print sum/NR}'

错误日志分析 ​

bash
# 统计错误类型
grep "ERROR" /var/log/postgresql/postgresql.log | awk '{print $4}' | sort | uniq -c | sort -rn

# 查找最近的10个错误
grep "ERROR" /var/log/postgresql/postgresql.log | tail -10

# 查找特定错误
grep "ERROR:  deadlock detected" /var/log/postgresql/postgresql.log

连接与会话分析 ​

bash
# 统计连接来源IP
grep "connection authorized" /var/log/postgresql/postgresql.log | awk '{print $10}' | sort | uniq -c | sort -rn

# 统计活跃会话数
grep "statement:" /var/log/postgresql/postgresql.log | wc -l

# 查找长时间运行的查询
grep "duration:" /var/log/postgresql/postgresql.log | awk '$3 > 60000' | head -5

日志分析脚本 ​

实时监控慢查询脚本 ​

bash
#!/bin/bash
# 实时监控慢查询

tail -f /var/log/postgresql/postgresql.log | grep -i "duration:" | awk '{if ($3 > 500) print $0}'

日志分析汇总脚本 ​

bash
#!/bin/bash
# 日志分析汇总脚本

LOG_FILE="/var/log/postgresql/postgresql.log"
echo "=== PostgreSQL日志分析报告 ==="
echo "生成时间: $(date)"
echo "日志文件: $LOG_FILE"
echo ""

echo "1. 慢查询统计"
echo "----------------"
slow_count=$(grep -c "duration:" $LOG_FILE)
echo "慢查询总数: $slow_count"
if [ $slow_count -gt 0 ]; then
    avg_time=$(grep "duration:" $LOG_FILE | awk '{sum += $3} END {print sum/NR " ms"}')
    echo "平均查询时间: $avg_time"
    max_time=$(grep "duration:" $LOG_FILE | sort -rn -k3 | head -1 | awk '{print $3 " ms"}')
    echo "最长查询时间: $max_time"
fi

echo ""
echo "2. 错误统计"
echo "----------------"
error_count=$(grep -c "ERROR" $LOG_FILE)
echo "错误总数: $error_count"
grep "ERROR" $LOG_FILE | awk '{print $4}' | sort | uniq -c | sort -rn | head -5

echo ""
echo "3. 连接统计"
echo "----------------"
connection_count=$(grep -c "connection authorized" $LOG_FILE)
echo "连接总数: $connection_count"
grep "connection authorized" $LOG_FILE | awk '{print $10}' | sort | uniq -c | sort -rn | head -5

日志分析最佳实践 ​

1. 合理配置日志级别 ​

  • 生产环境建议设置log_min_duration_statement = 100（记录超过100毫秒的查询）
  • 开发环境可以设置log_min_duration_statement = 0（记录所有查询）
  • 启用log_lock_waits = on记录锁等待事件
  • 启用log_statement = 'ddl'记录DDL语句

2. 定期分析日志 ​

  • 每天生成日志分析报告
  • 关注慢查询趋势变化
  • 及时发现和处理错误日志
  • 定期清理旧日志，避免磁盘空间不足

3. 结合监控系统 ​

  • 将日志分析结果与Prometheus+Grafana监控结合
  • 设置慢查询和错误日志告警
  • 建立日志分析自动化流程

4. 优化日志存储 ​

  • 使用日志轮转机制管理日志文件
  • 考虑使用ELK Stack或Loki等集中式日志管理系统
  • 压缩归档旧日志文件

常见问题（FAQ） ​

Q1: pgBadger生成的报告没有数据怎么办？ ​

A1: 请检查以下几点：

  • 确保PostgreSQL日志格式配置正确，特别是log_line_prefix
  • 确保日志文件路径正确
  • 检查pgBadger版本是否支持当前PostgreSQL版本
  • 查看pgBadger命令输出，是否有错误信息

Q2: 如何实时监控PostgreSQL日志？ ​

A2: 可以使用以下方法：

  • 使用tail -f命令实时查看日志
  • 配置pgBadger或pganalyze等工具进行实时分析
  • 使用ELK Stack或Loki等集中式日志系统
  • 结合监控系统设置日志告警

Q3: 如何分析PostgreSQL的CSV格式日志？ ​

A3: 可以使用以下方法：

  • pgBadger支持CSV格式日志，使用-f csv参数
  • 示例：pgbadger -f csv /var/log/postgresql/postgresql-14-main.csv -o pgbadger-report.html

Q4: 如何减少PostgreSQL日志文件大小？ ​

A4: 可以通过以下方式优化：

  • 调整log_min_duration_statement参数，只记录慢查询
  • 调整log_statement参数，减少记录的语句类型
  • 启用日志压缩
  • 配置合理的日志轮转策略

Q5: 如何分析PostgreSQL的WAL日志？ ​

A5: 可以使用以下工具：

  • pg_waldump：PostgreSQL自带的WAL日志分析工具
  • pg_receivewal：用于接收和分析WAL日志
  • 示例：pg_waldump /var/lib/postgresql/14/main/pg_wal/000000010000000000000001