PostgreSQL Reporting Tools(PgBadger) Installation & Configuration

The PostgreSQL log analyzer “pgBadger” is an open source “fast PostgreSQL log analysis report” program written in Perl that takes the log output from a running PostgreSQL instance and processes it into an HTML file. 

                The report it generates shows all information found in a nice and easy to read report format. 

These reports can help shed light on errors happening in the system, checkpoint behavior, vacuum behavior, trends, and other basic but crucial information for a PostgreSQL system.

FEATURES:

 pgBadger reports everything about your SQL queries:

1.         Overall statistics.
2.         The most frequent waiting queries.
3.         Queries that waited the most.
4.         Queries generating the most temporary files.
5.         Queries generating the largest temporary files.
6.         The slowest queries.
7.         Queries that took up the most time.
8.         The most frequent queries.
9.         The most frequent errors.
10.         Histogram of query times.
11.         Histogram of sessions times.
12.         Users involved in top queries.
13.         Applications involved in top queries.
14.         Queries generating the most cancellation.
15.         Queries most cancelled.

The following reports are also available with hourly charts divided into periods of five minutes:

1.         SQL queries statistics.
2.         Temporary file statistics.
3.         Checkpoints statistics.
4.         Autovacuum and autoanalyze statistics.
5.         Cancelled queries.
6.         Error events (panic, fatal, error and warning).
7.         Error class distribution.

There are also some pie charts about distribution of:

1.         Locks statistics.
2.         Queries by type (select/insert/update/delete).
3.         Distribution of queries type per database/application
4.         Sessions per database/user/client/application.
5.         Connections per database/user/client/application.
6.         Autovacuum and autoanalyze per table.
7.         Queries per user and total duration per user.

pgBadger is also able to parse PgBouncer log files and to create the following reports:

1.         Request Throughput
2.         Bytes I/O Throughput
3.         Queries Average duration
4.         Simultaneous sessions
5.         Histogram of sessions times
6.         Sessions per database
7.         Sessions per user
8.         Sessions per host
9.         Established connections
10.         Connections per database
11.         Connections per user
12.         Connections per host
13.         Most used reserved pools
14.         Most Frequent Errors/Events

How to install pgbadger:

[root@server1 bin]# yum install pgbadger

Bin Path:

/usr/bin/pgbadger

Configuration:

vi postgresql.conf

log_destination = 'stderr' or 'csvlog' or 'syslog'

log_directory = 'log'

log_min_duration_statement = 0 

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

log_destination = ‘stderr’ or 'csvlog':

log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

log_destination = ‘syslog’:

log_line_prefix = 'user=%u,db=%d,app=%aclient=%h '

log_statement = 'all'

lc_messages='C'

PgBadger Options:

-b | --begin datetime  : start date/time for the data to be parsed in log

-e | --end datetime    : end date/time for the data to be parsed in log

-f | --format logtype  : possible values: syslog, stderr, jsonlog, cvs

-d | --dbname database : only report on entries for the given database

-h | --help            : show this message and exit.

-o | --outfile filename: define the filename for the output. Default depends

                 on the output format: out.html

Run pgbadger Tool:

[root@server1 ~]# pgbadger /var/lib/pgsql/10/data/log/* -o /zzz/report.html

[========================>] Parsed 5961992 bytes of 5961992 (100.00%), queries: 34125, events: 107

LOG: Ok, generating html report...