Pgbadger

|

It is a script developed in Perl (it also uses the JavaScript library (flotr2) for graphics).
It analyzes Postgres logs and produces detailed reports.
Automatically detects log format (syslog, stderr, csvlog, jsonlog).
It can work with large and compressed (gzip, bzip2, lz4, xz, zip, zstd) log files.
Filtering can be done so that only errors can be reported.
It can report everything related to SQL queries:

  • All statistics.
  • The least common waiting SQLs.
  • The most common waiting SQLs.
  • SQLs that produce the most temp files.
  • SQLs that produce the biggest temp file.
  • Slowest SQLs.
  • The most time-consuming SQLs.
  • The most common errors.
  • Lock statistics.
postgres@srv1:~$ sudo apt update
postgres@srv1:~$ sudo apt install pgbadger
ALTER SYSTEM SET log_min_duration_statement = 1000;
ALTER SYSTEM SET log_line_prefix = ‘%t [%p]:[%l-1]
user=%u,db=%d,app=%a,client=%h ‘;
ALTER SYSTEM SET log_checkpoints = on;
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET log_temp_files = 0;
ALTER SYSTEM SET log_autovacuum_min_duration = 0;
ALTER SYSTEM SET log_error_verbosity =

It can be downloaded from github.com/darold/pgbadger and installed from
the source code with the following steps.
tar xzf pgbadger-12.x.tar.gz
cd pgbadger-12.x/
perl Makefile.PL
make && sudo make install

Sample Report Generation

# Report of a specific range
pgbadger -b “2022-04-04 12:00:00” -e “2022-04-04 14:00:00” -f
stderr /var/lib/postgresql/14/main/log/postgresql-Mon.log -o /
tmp/mx_2022-04-04-12.00-14.00.html
# Monthly report
pgbadger -X --month-report 2919-08 /tmp/pg_reports/
pgbadger -E -X --month-report 2919-08 /tmp/pg_reports/
# Report of a remote server
pgbadger ssh://kul1@10.71.6.22:2222//var/log/postgresql/
postgresql-14.1-main.log*
pgbadger http://kul1@10.71.6.22//var/log/postgresql/postgresql-
14.1-main.log*
pgbadger ftp://kul1@10.71.6.22//var/log/postgresql/postgresql-
14.1-main.log*

With a short script, reports can be received by mail.
For more details of pgbadger, visit website github.com/darold/pgbadger, for sample
reports the link pgbadger.darold.net/#reports can be visited.