Installing and Configuring PgBadger in Linux

PgBadger is one of the best open source tool for postgres to have a track of slow queries, most frequent queries, users analysis and many more. Therefore, I am making this blog for detailed steps to set up pgbadger in Linux and viewing report in web.

As pgbadger reads the log file of postgres, therefore we need to make changes in the log file so that correct information are logged and pgbadger can read the log file appropriately. Below are some of the changes needs to make in postgres log file -

• Log connection - On
• Log disconnection - On
• Log Duration - off
• Log statement - none
• Log hostname - On
• log_line_prefix = '%t [%p]: [%l-1] db=%d, %u , app=%a, %h '  
• Log Min Duration Statement - 0
• Log Checkpoint - on
• lc_messages = ‘C’

Below is the configuration change for the log file size, format and rotation -

• log_truncate_on_rotation = off
• log_rotation_age = 1d 
• log_rotation_size = 0

After making all the above changes, verify the generation of log file with the changes made in configuration file. After verifying it time to download the pgbadger and configure it from the below official site - 

Link - https://github.com/dalibo/pgbadger/releases ( Version 9.2)

Steps for installing pgbadger in linux( RHEL in my case):

• Download the latest pgbadger using wget command - wget https://github.com/dalibo/pgbadger/archive/v9.2.tar.gz
• Untar the tar Ball  - tar xvf pgbadger-9.2.tar
• Install the source code using the below steps- 
• A. cd pgbadger-9.2  
• B. perl Makefile.PL 
• C. Make 
• D. Make Install

Once we install the pgbadger, we can view the report in text format or we can view the report in HTML format in web. We will see both the process in details - 

Text Format - 

In this process we simply parse the log file and generate a report in text format. And the text format report is generated in linux only, and we can view the report using less, more or VI command. Below is the command for it - 

"pgbadger /data/postgres/data/pg_log/postgresql-2018-07-02_075351.log --outfile /tmp/queries_4.txt"

HTML FORMAT - 

For this format, we have multiple steps to achieve this. We need to make sure we have apache or httpd installed and have all the report generated in the root folder so that we can view the reports over web. Below are the details steps -

• Firstly, we need to install apache in the linux system using yum or rpm command. Along with it, there are few more dependencies we need to install. They are - GCC, apr-util and apr.
• If we do not have apache in repository, instead we can install httpd and the above dependencies.
• Lastly, we need to create the report in the apache root directory i.e. "/var/www/html" so that apache can read the html files.
• Below is the command to generate html report - pgbadger --incremental --outdir /var/www/html /data/postgres/data/pg_log/postgresql-2018-07-02_075351.log -o out.html
• Most important is the cron the above steps and generate the report everyday. 
• You can view the reports online at http://127.0.0.1:80 if it is localhost or the IP where pgbadger is installed.

I have installed pgbadger many times and have good hands on experience. So, please comment below if you face any issue and I will be there to help you.