What is our objective which will be solved by this tool called as pgbadger?

 

pgBadger is a PostgreSQL performance analyser , built for speed with fully detailed reports based on your PostgreSQL log file

To get detailed view of PostgreSQL database workload ,

pgbadger is a great tool to explore the detailed dimesion .

It is open source and easy to install and configure and explore .

You can also schedule pgbadger reports via available scheduler like Cron ,

Analyze the pgbadger report on dialy basis for review and plan for continuous improvements on database performance area .

pgbadger detects log format automatically and charts can be saved as image directly in presentable mode .

pgBadger : PostgreSQL Log Analysis tool

            ************

” pgBadger ” tool .

it is an open source ” fast PostgreSQL log analysis report ” program .

it is written in Perl and is a tool for The Posteresql lop analyzer .

The report has the information in a readble report format .

These reports can help analyze errors in postgresql , can share details around checkpoint , vacuum and other basic

but crucial information for a PostgreSQL instance .

FEATURE of pgBadger

Reports everything about your SQL queries :

       Overall statistics .

       The most frequent waiting queries .

       Queries that waited the most .

      Queries generating the most temporary files .

      Queries generating the largest temporary files .

       The slowest queries .

       Queries that took up the most time .

       The most frequent queries .

       The most frequent errors .

      Histogram of query times .

       Histogram of sessions times .

      Users involved in top queries .

      Applications involved in top queries .

       Queries generating the most cancellation .                                             

 

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

        Request Throughput

        Bytes 1/0 Throughput

        Queries Average duration

        Simultaneous sessions

        Histogram of sessions times

        Sessions per database

        Sessions per user

        Sessions per host

        Established connections

        Connections per database

        Connections per user

        Connections per host

        Most used reserved pools

        Most Ecequent Ennons / Events

 

Steps to install PGBADGER:

 

[root@krishna ~]# yum install -y perl perl-devel

 

[root@krishna ~]# yum install wget

 

[root@krishna ~]# yum install git

 

[root@krishna ~]# wget https://github.com/darold/pgbadger/archive/v11.6.tar.gz

 

[root@krishna ~]# tar -xzvf v11.6.tar.gz

 

[root@krishna ~]# ls -lrt

 

[root@krishna ~]# cd pgbadger-11.6

 

[root@krishna pgbadger-11.6]# perl Makefile.PL

 

[root@krishna pgbadger-11.6]# make

 

[root@krishna pgbadger-11.6]# pgbadger -V

 

CONFIGURATION SETTINGS IN POSTGRESQL.CONF file

log_min_duration statement e

log_line_prefix = % t [ Xp ] : user – Xu , db- % d , app – Xa , client = h

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 “

lc messages = ‘ C ‘

 

RESTART POSTGRES

[root@krishna ~]# su – postgres

-bash-4.2$ /usr/pgsql-11/bin/pg_ctl -D /var/lib/pgsql/11/data/ restart

-bash-4.2$ pgbadger /var/lib/pgsql/11/data/log/postgresql-* -f /var/lib/pgsql/11/data/log -o pgbadger.html

[root@krishna ~]# cd /var/lib/pgsql/11/data/log

[root@krishna log]# ls

pgbadger.html  postgresql-Tue.log  postgresql-Wed.log

[root@krishna log]# cp -r pgbadger.html /media/sf_shared

 

Now we can view the report as HTML page in a browser