PostgreSQL日志分析-pgBadger
2012-08-26 14:08:26 
阿炯
pgBadger 是一个 PostgreSQL 数据库日志分析程序，采用Perl开发，可生成详细的报表和图表，旨在替换 pgFouine 脚本。可以对基于 PostgreSQL 日志文件提供详尽的性能分析报告，旨在提高性能和效率。gBadger is a PostgreSQL log analyzer build for speed with fully detailed reports from your PostgreSQL log file. It's a single and small Perl script that aims to replace and outperform the old php script pgFouine.
By the way, we would like to thank Guillaume Smet for all the work he has done on this really nice tool. We've been using it a long time, it was a really great tool!
pgBadger is written in pure Perl language. It uses a javascript library to draw graphs so that you don't need additional Perl modules or any other package to install. Furthermore, this library gives us more features such as zooming.
pgBadger is able to autodetect your log file format (syslog, stderr or csvlog). It is designed to parse huge log files as well as gzip compressed file. See a complete list of features below.
Features
pgBadger reports everything about your SQL queries:
Overall statistics.
The slowest queries.
Queries that took up the most time.
The most frequent queries.
The most frequent errors.
The following reports are also available with hourly charts:
Hourly queries statistics.
Hourly temporary file statistics.
Hourly checkpoints statistics.
Locks statistics.
Queries by type (select/insert/update/delete).
Sessions per database/user/client.
Connections per database/user/client.
All charts are zoomable and can be saved as PNG images.
最新版本：6.0
此版本解决了一些问题，同时添加了一些新特性：
Automatic cleanup of binary files in incremental mode.
Automatic handling of retention with a maximum number of weeks to keep reports.
Incremental mode improvement by allowing the use of multiprocessing with multiple log file.
Now reports the query latency percentiles on the general activity table (percentiles are 90, 95, 99).
A new output format: JSON. This format is good for sharing data with other tools.
Using the --anonymize option pgBadger will anonymize all literal values in the queries.
Add a click-to-select button in front of each query that allow to just use Ctrl+C to copy it on clipboard.
Several space usage improvement in incremental mode, the -X option allow the use of external CSS and Javascript files.
A new pgBadger logo
注意：pgBadger 的增量模式有所改变，现在总是清理所有废弃二进制文件的输出目录。如果用户使用这些废弃文件来构建报告，可以使用 --noclean 选项来阻止 pgBadger 移除那些文件。
最新版本：11.5
pgBadger 11.5 已经于2021年2月末发布。该版本是一个维护版本，修复了用户在过去三个月内报告的问题，并进行了一些优化。主要更新内容：
添加有关会话空闲时间的报告，该报告的计算方式为：“总会话时间-总查询时间/会话数。这要求已启用 log_connection 和日志断开连接，并且设置log_min_duration_statement=0(记录所有查询)，才能得出可靠的值。有助于了解丢失了多少空闲时间，以及池交易模式是否有用。此报告位于“全局统计”的“会话”标签和“常规活动”报告的“会话”标签中
添加数值的匿名化，由 4 个随机数字代替
更新基于 pgFormatter 5.0 的 SQL 美化器
修复对 Google CloudSQL 日志文件多行语句的解析
在 Heroku logplex 日志行前缀中增加对微秒的支持
在耗时准备和绑定报告中添加参数匿名化
为 jsonlog 格式的多行语句添加支持
修复被 maxlength 截断破坏的匿名化
详细内容请查看更新公告。
项目主页：http://projects.dalibo.org/pgbadger/