PostgreSQL Log Analysis with PgBadger

I recently mentioned PgBadger in my PostgreSQL tuning article, and it really deserves a post of its own. Pgbadger is a very capable open-source log analysis tool specifically designed for PostgreSQL databases. It parses the log files generated by PostgreSQL and PgBouncer and generates detailed reports and statistics, providing insights into the database's performance, queries, errors, and overall usage patterns. Pgbadger helps administrators and developers analyze and optimize the performance of their PostgreSQL databases by identifying potential bottlenecks, highlighting slow queries, and suggesting improvements.

Configuring PostgreSQL

PostgreSQL will need some small reconfigurations to provide logs for PgBadger.

log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0
log_error_verbosity = default

Determine which queries that you want to log. This is done by measuring the time duration. To log all queries, set the value to 0 milliseconds. Increase the value to filter out faster queries.

log_min_duration_statement = 0

Crunching The Logs

PgBadger is written in Perl, and the build in functions do not require any additional modules. To install PgBadger, simply clone the PgBadger repository from GitLab. The pgbadger command is the top level of the project repo. If necessary, adjust your path variable to include the proper path info.

git clone https://github.com/darold/pgbadger.git

The mode basic form of the command is to provide a log file path as a command line argument. This will generate the out.html file in the current directory. The script uses JavaScript to embed all of the graphs into the HTML page.

pgbadger /var/log/postgresql/postgresql.log

To send the HTML reports to another directory, just add a -o parameter.

pgbadger -o /path/to/report/directory /var/log/postgresql/postgresql.log

This command analyzes the PostgreSQL log file and saves the generated report in the specified output directory ("/path/to/report/directory"). The report file will still be named "out.html" by default.

The following command analyzes rotated and compressed PostgreSQL log files matching the pattern "/var/log/postgresql/postgresql*.log.gz". The --rotate-logs flag instructs Pgbadger to handle rotated logs, and the --gzip flag indicates that the log files are compressed using gzip. PgBadger will generate a report for each log file and save them as separate HTML files.

pgbadger --rotate-logs --gzip /var/log/postgresql/postgresql*.log.gz

PgBadger provides a wide range of options and customization features. It even works with PgBouncer logs. I recommend checking out the full documentation.