Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will talk about pgBadger Installation and what it is.
What is pgBadger?
PostgreSQL log analyzer “pgBadger” is an open source PostgreSQL log analysis program written in Perl that takes log output from PostgreSQL instance and processes it into an HTML file.
pgBadger Installation
Operating System : CentOS 7 — PostgreSQL Version : 13
1 2 | [root@postgresql ~]# yum -y install libjson-xs-perl [root@postgresql ~]# yum -y install pgbadger |
pgBadger Configuration
We make some configuration settings in the postgresql.conf file.
First we set the log_min_duration_statement parameter.
The parameter value is given in milliseconds. A value of “0” ensures that all SQLs are logged.
1 2 | [root@postgresql ~]# vi postgresql.conf log_min_duration_statement = 0 |
The log_line_prefix parameter allows detailed information to be written to the log file.
Here, the database name, user name, application and client ip address are also written to the log file.
1 | log_line_prefix = ‘%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ‘ |
To get more information in the logs, we need to set the following parameters on.
1 2 3 4 5 6 7 | log_checkpoints = on log_connections = on log_disconnections = on log_error_verbosity = default log_temp_files = 0 log_lock_waits = on log_autovacuum_min_duration = 0 |
After the configuration adjustments are completed, we create an index for the reports.
1 2 3 4 | -bash-4.2$ mkdir report -bash-4.2$ cd report/ -bash-4.2$ pwd /var/lib/pgsql/13/report |
We get the report as follows.
1 2 | -bash-4.2$ pgbadger /var/lib/pgsql/13/data/log/postgresql-Fri.log -o /var/lib/pgsql/13/report/pgbadger-2022-05-27.html |
Example: The slowest running queries
