Skip to content

pgBadger — log analysis

Reference: pgBadger — a fast PostgreSQL log analysis report

Configuring the PostgreSQL cluster

pgBadger reads PostgreSQL logs, so the cluster has to log enough to be worth reading.

In Muppy, open the cluster via Databases / Database Clusters and paste this block into its postgresql.conf tab, then apply the configuration and restart the cluster.

# begin PGBadger 
log_statement = none              # required for pgBadger
log_min_duration_statement = 0    # adjust: minimum query duration to analyze
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
lc_messages='en_US.UTF-8'         # pgbadger does not parse fr_FR.UTF-8
track_activity_query_size = 1MB   # adjust: space reserved for textual repr of query
# PGBadger end

Two values to tune:

  • log_min_duration_statement — the execution time in ms above which a query is logged. At 300, only queries slower than 300 ms are recorded. Start high (say 2000) and lower it gradually.
  • track_activity_query_size — costly, and it makes pgBadger's job heavier. Valid values run from 100 bytes to 1 megabyte.

Warning

log_min_duration_statement = 0 logs every query. It is the right setting for a short analysis window, and the wrong one to leave in place.

Installing pgBadger

pgBadger is a Perl program installed from source. Perl ships with Ubuntu, so nothing else is needed. You can install it on a workstation (Linux recommended) or on a server.

As the ubuntu user:

export PGBADGER_VERSION="13.0"  # adjust to the latest release
wget https://github.com/darold/pgbadger/archive/refs/tags/v${PGBADGER_VERSION}.tar.gz -O pgbadger-v${PGBADGER_VERSION}.tar.gz
tar xzf pgbadger-v${PGBADGER_VERSION}.tar.gz
rm pgbadger-v${PGBADGER_VERSION}.tar.gz
cd pgbadger-${PGBADGER_VERSION}/
perl Makefile.PL
make && sudo make install

Running pgBadger

pgBadger runs locally on the database server, or remotely over SSH. Performance is comparable.

Locally, on the server

ubuntu@ns338066:~$ sudo pg_lsclusters 
Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

ubuntu@ns338066:~$ pgbadger /var/log/postgresql/postgresql-16-main.log*
[========================>] Parsed 2601353 bytes of 2601353 (100.00%), queries: 7519, events: 23
LOG: Ok, generating html report...

The report is written to out.html in the current directory.

Remotely, over SSH

A local pgBadger reads the log of a remote PostgreSQL server over SSH.

pgBadger must be able to connect without a password. Check it first:

ssh ubuntu@$PGHOST

Then generate the report:

pgbadger ssh://ubuntu@$PGHOST:22//var/log/postgresql/postgresql-16-main.log*

Warning

The double slash after the port is required.

Reading the report

pgBadger produces a single out.html holding everything.

Generated locally, open it in a browser.

Generated on a remote server, either copy it back with scp, or serve it over HTTP for as long as you need it:

mkdir tmp_server
cd tmp_server
pgbadger /var/log/postgresql/postgresql-XX-main.log*
# serve the report on port 8000
python3 -m http.server 
# stop with CTRL-C