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.
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
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.