Install and Configure pgBadger in PostgreSQL Part-I

October 22, 2021
()

Install and Configure pgBadger in PostgreSQL Part-I


pgBadger
is a PostgreSQL log analyzer built for speed providing fully detailed reports based on your PostgreSQL log files.

                                                                   
Below are the features of
pgBadger 

1. pgBadger is able to autodetect your log file format (syslog, stderr or csvlog). It is designed to parse huge log files as well as gzip compressed files.
2. You can also limit pgBadger to only report errors or remove any part of the report using command-line options.
3. pgBadger allows parallel processing of a single log file or multiple files through the use of the -j option specifying the number of CPUs.
4. By default pgBadger in incremental mode only computes daily and weekly reports. But if you want monthly cumulative reports can also be generated.
5. Using the binary format, it is possible to create custom incremental and cumulative reports. For example, if you  want  to refresh a pgBadger report each hour from a daily PostgreSQL log file.
6. pgBadger can parse a remote log file using a passwordless ssh connection.
7. pgBadger is also able to parse PgBouncer log files 
8. pgBadger can also be used in a central place to parse remote log files using a passwordless SSH connection.
9. You can also schedule pgBadger reports via an available scheduler like cron.


Method-1: Install pgBadger using the yum command


Step 1. Install pgBadger using yum: Try to install pgBadger package using OS yum command. Please note below command will work if you have postgreSQL repository already configured. Click here to configure PostgreSQL repository. If you receive the below error continue to Step 2.

[root@test-machine01 ~]#
[root@test-machine01 ~]#  yum install pgbadger
Loaded plugins: langpacks, ulninfo
pgdg-common                                                                                                                                           | 2.9 kB  00:00:00
pgdg10                                                                                                                                                | 3.6 kB  00:00:00
pgdg11                                                                                                                                                | 3.6 kB  00:00:00
pgdg12                                                                                                                                                | 3.6 kB  00:00:00
pgdg13                                                                                                                                                | 3.6 kB  00:00:00
pgdg95                                                                                                                                                | 3.6 kB  00:00:00
pgdg96                                                                                                                                                | 3.6 kB  00:00:00
Resolving Dependencies
--> Running transaction check
---> Package pgbadger.noarch 0:11.6-1.rhel7 will be installed
--> Processing Dependency: perl-Text-CSV_XS for package: pgbadger-11.6-1.rhel7.noarch
--> Finished Dependency Resolution
Error: Package: pgbadger-11.6-1.rhel7.noarch (pgdg-common)
           Requires: perl-Text-CSV_XS
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest
[root@test-machine01 ~]#
[root@test-machine01 ~]#


Step 2. Install dependent packages: The pgBadger is written in pure Perl and uses a JavaScript library (flotr2) to draw graphs. Hence, you need to ensure that a modern Perl distribution is available in your system. Use the below command to install perl if it is not already installed in your system. Also download rpm: perl-Text-CSV_XS-1.00-3.el7.x86_64.rpm using wget command. Use command yum localinstall to install this rpm.

[root@test-machine01 ~]#
[root@test-machine01 ~]# yum install perl perl-devel
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package perl.x86_64 4:5.16.3-297.el7 will be updated
---> Package perl.x86_64 4:5.16.3-299.el7_9 will be an update
--> Processing Dependency: perl-libs = 4:5.16.3-299.el7_9 for package: 4:perl-5.16.3-299.el7_9.x86_64
---> Package perl-devel.x86_64 4:5.16.3-299.el7_9 will be installed
--> Processing Dependency: gdbm-devel for package: 4:perl-devel-5.16.3-299.el7_9.x86_64
--> Processing Dependency: libdb-devel for package: 4:perl-devel-5.16.3-299.el7_9.x86_64
--> Processing Dependency: perl(ExtUtils::Installed) for package: 4:perl-devel-5.16.3-299.el7_9.x86_64
--> Processing Dependency: perl(ExtUtils::MakeMaker) for package: 4:perl-devel-5.16.3-299.el7_9.x86_64
--> Processing Dependency: perl(ExtUtils::ParseXS) for package: 4:perl-devel-5.16.3-299.el7_9.x86_64
--> Processing Dependency: systemtap-sdt-devel for package: 4:perl-devel-5.16.3-299.el7_9.x86_64
--> Running transaction check
  Verifying  : 4:perl-libs-5.16.3-299.el7_9.x86_64                                                                                                                      8/14
  Verifying  : gdbm-devel-1.10-8.el7.x86_64                                                                                                                             9/14
  Verifying  : perl-ExtUtils-MakeMaker-6.68-3.el7.noarch                                                                                                               10/14
  Verifying  : pyparsing-1.5.6-9.el7.noarch                                                                                                                            11/14
  Verifying  : 4:perl-devel-5.16.3-299.el7_9.x86_64                                                                                                                    12/14
  Verifying  : 4:perl-libs-5.16.3-297.el7.x86_64                                                                                                                       13/14
  Verifying  : 4:perl-5.16.3-297.el7.x86_64                                                                                                                            14/14

Installed:
  perl-devel.x86_64 4:5.16.3-299.el7_9

Dependency Installed:
  gdbm-devel.x86_64 0:1.10-8.el7                          libdb-devel.x86_64 0:5.3.21-25.el7                       perl-ExtUtils-Install.noarch 0:1.58-299.el7_9
  perl-ExtUtils-MakeMaker.noarch 0:6.68-3.el7             perl-ExtUtils-Manifest.noarch 0:1.61-244.el7             perl-ExtUtils-ParseXS.noarch 1:3.18-3.el7
  perl-Test-Harness.noarch 0:3.28-3.el7                   pyparsing.noarch 0:1.5.6-9.el7                           systemtap-sdt-devel.x86_64 0:4.0-13.0.1.el7

Updated:
  perl.x86_64 4:5.16.3-299.el7_9

Dependency Updated:
  perl-libs.x86_64 4:5.16.3-299.el7_9

Complete!
[root@test-machine01 ~]#

[root@test-machine01 tmp]#
[root@test-machine01 tmp]# wget  --no-check-certificate http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-Text-CSV_XS-1.00-3.el7.x86_64.rpm
--2021-10-15 18:50:02--  http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-Text-CSV_XS-1.00-3.el7.x86_64.rpm
Resolving mirror.centos.org (mirror.centos.org)... 103.232.121.196, 2406:da1a:fcb:2f01:b6e2:c6:795:b503
Connecting to mirror.centos.org (mirror.centos.org)|103.232.121.196|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 79520 (78K) [application/x-rpm]
Saving to: âperl-Text-CSV_XS-1.00-3.el7.x86_64.rpmâ

100%[===================================================================================================================================>] 79,520      --.-K/s   in 0.02s

2021-10-15 18:50:04 (4.67 MB/s) - âperl-Text-CSV_XS-1.00-3.el7.x86_64.rpmâ saved [79520/79520]

[root@test-machine01 tmp]#


[root@test-machine01 tmp]#
[root@test-machine01 tmp]# ls -ltr
total 92
-rw-r--r--. 1 root root 79520 Jul  4  2014 perl-Text-CSV_XS-1.00-3.el7.x86_64.rpm
[root@test-machine01 tmp]#
[root@test-machine01 tmp]# yum localinstall perl-Text-CSV_XS-1.00-3.el7.x86_64.rpm
Loaded plugins: langpacks, ulninfo
Examining perl-Text-CSV_XS-1.00-3.el7.x86_64.rpm: perl-Text-CSV_XS-1.00-3.el7.x86_64
Marking perl-Text-CSV_XS-1.00-3.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package perl-Text-CSV_XS.x86_64 0:1.00-3.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================================
 Package                                 Arch                          Version                              Repository                                                  Size
=============================================================================================================================================================================
Installing:
 perl-Text-CSV_XS                        x86_64                        1.00-3.el7                           /perl-Text-CSV_XS-1.00-3.el7.x86_64                        162 k

Transaction Summary
=============================================================================================================================================================================
Install  1 Package

Total size: 162 k
Installed size: 162 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : perl-Text-CSV_XS-1.00-3.el7.x86_64                                                                                                                        1/1
  Verifying  : perl-Text-CSV_XS-1.00-3.el7.x86_64                                                                                                                        1/1

Installed:
  perl-Text-CSV_XS.x86_64 0:1.00-3.el7

Complete!
[root@test-machine01 tmp]#


Step 3: Install pgBadger: Once both the rpm and packages are installed try to install pgBadger package using yum command. Once pgBadger is installed use the command pgbadger -V to check pgBadger version and pgbadger –help command to get a complete help list.

[root@test-machine01 tmp]# 
[root@test-machine01 tmp]# yum install pgbadger
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package pgbadger.noarch 0:11.6-1.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================================
 Package                                 Arch                                  Version                                      Repository                                  Size
=============================================================================================================================================================================
Installing:
 pgbadger                                noarch                                11.6-1.rhel7                                 pgdg-common                                354 k

Transaction Summary
=============================================================================================================================================================================
Install  1 Package

Total download size: 354 k
Installed size: 1.5 M
Is this ok [y/d/N]: y
Downloading packages:
pgbadger-11.6-1.rhel7.noarch.rpm                                                                                                                      | 354 kB  00:00:02
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pgbadger-11.6-1.rhel7.noarch                                                                                                                              1/1
  Verifying  : pgbadger-11.6-1.rhel7.noarch                                                                                                                              1/1

Installed:
  pgbadger.noarch 0:11.6-1.rhel7

Complete!
[root@test-machine01 tmp]#

[root@test-machine01 tmp]#  pgbadger -V
pgBadger version 11.6
[root@test-machine01 tmp]#

[postgres@test-machine01 tmp]$ pgbadger --help

Usage: pgbadger [options] logfile [...]

    PostgreSQL log analyzer with fully detailed reports and graphs.

Arguments:

    logfile can be a single log file, a list of files, or a shell command
    returning a list of files. If you want to pass log content from stdin
    use - as filename. Note that input from stdin will not work with csvlog.

Options:

    -a | --average minutes : number of minutes to build the average graphs of
                             queries and connections. Default 5 minutes.
    -A | --histo-average min: number of minutes to build the histogram graphs
                             of queries. Default 60 minutes.
    -b | --begin datetime  : start date/time for the data to be parsed in log
                             (either a timestamp or a time)
    -c | --dbclient host   : only report on entries for the given client host.
    -C | --nocomment       : remove comments like /* ... */ from queries.


Method-2: Install pgBadger using manual method


Step 1. Install Perl Packages: The pgBadger is written in pure Perl and uses a JavaScript library (flotr2) to draw graphs. Hence, you need to ensure that a modern Perl distribution is available in your system. Use the below command to install perl if it is not already installed in your system.

[root@test-machine02 tmp]#
[root@test-machine02 tmp]# yum install perl perl-devel
Loaded plugins: langpacks, ulninfo
pgdg10                                                                                                                                                | 3.6 kB  00:00:00
pgdg11                                                                                                                                                | 3.6 kB  00:00:00
pgdg12                                                                                                                                                | 3.6 kB  00:00:00
pgdg13                                                                                                                                                | 3.6 kB  00:00:00
pgdg95                                                                                                                                                | 3.6 kB  00:00:00
pgdg96                                                                                                                                                | 3.6 kB  00:00:00
vault.centos.org_centos_7.4.1708_extras_x86_64_                                                                                                       | 3.4 kB  00:00:00
(1/11): mongodb-org-4.4/7Server/primary_db                                                                                                            |  70 kB  00:00:00
(2/11): ol7_UEKR6/x86_64/updateinfo                                                                                                                   | 328 kB  00:00:01
(3/11): pgdg-common/7Server/x86_64/primary_db                                                                                                         | 141 kB  00:00:00
(4/11): pgdg11/7Server/x86_64/primary_db                                                                                                              | 371 kB  00:00:00
(5/11): pgdg10/7Server/x86_64/primary_db                                                                                                              | 348 kB  00:00:01
(6/11): pgdg12/7Server/x86_64/primary_db                                                                                                              | 226 kB  00:00:00
(7/11): pgdg13/7Server/x86_64/primary_db                                                                                                              | 142 kB  00:00:00
(8/11): ol7_latest/x86_64/updateinfo                                                                                                                  | 3.3 MB  00:00:03
(9/11): pgdg96/7Server/x86_64/primary_db                                                                                                              | 338 kB  00:00:00
(10/11): ol7_UEKR6/x86_64/primary_db                                                                                                                  |  23 MB  00:00:10
(11/11): ol7_latest/x86_64/primary_db                                                                                                                 |  36 MB  00:00:13
Resolving Dependencies
  Verifying  : perl-ExtUtils-Install-1.58-299.el7_9.noarch                                                                                                              6/14
  Verifying  : perl-ExtUtils-Manifest-1.61-244.el7.noarch                                                                                                               7/14
  Verifying  : 4:perl-libs-5.16.3-299.el7_9.x86_64                                                                                                                      8/14
  Verifying  : gdbm-devel-1.10-8.el7.x86_64                                                                                                                             9/14
  Verifying  : perl-ExtUtils-MakeMaker-6.68-3.el7.noarch                                                                                                               10/14
  Verifying  : pyparsing-1.5.6-9.el7.noarch                                                                                                                            11/14
  Verifying  : 4:perl-devel-5.16.3-299.el7_9.x86_64                                                                                                                    12/14
  Verifying  : 4:perl-libs-5.16.3-297.el7.x86_64                                                                                                                       13/14
  Verifying  : 4:perl-5.16.3-297.el7.x86_64                                                                                                                            14/14

Installed:
  perl-devel.x86_64 4:5.16.3-299.el7_9

Dependency Installed:
  gdbm-devel.x86_64 0:1.10-8.el7                          libdb-devel.x86_64 0:5.3.21-25.el7                       perl-ExtUtils-Install.noarch 0:1.58-299.el7_9
  perl-ExtUtils-MakeMaker.noarch 0:6.68-3.el7             perl-ExtUtils-Manifest.noarch 0:1.61-244.el7             perl-ExtUtils-ParseXS.noarch 1:3.18-3.el7
  perl-Test-Harness.noarch 0:3.28-3.el7                   pyparsing.noarch 0:1.5.6-9.el7                           systemtap-sdt-devel.x86_64 0:4.0-13.0.1.el7

Updated:
  perl.x86_64 4:5.16.3-299.el7_9

Dependency Updated:
  perl-libs.x86_64 4:5.16.3-299.el7_9

Complete!
[root@test-machine02 tmp]#


Step 2. Download the latest pgBadger: Download the latest pgBadger installation package using wget command.

[root@test-machine02 tmp]# wget https://github.com/darold/pgbadger/archive/v11.6.tar.gz
--2021-10-15 19:15:36--  https://github.com/darold/pgbadger/archive/v11.6.tar.gz
Resolving github.com (github.com)... 140.82.121.4
Connecting to github.com (github.com)|140.82.121.4|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://codeload.github.com/darold/pgbadger/tar.gz/v11.6 [following]
--2021-10-15 19:15:37--  https://codeload.github.com/darold/pgbadger/tar.gz/v11.6
Resolving codeload.github.com (codeload.github.com)... 140.82.121.10
Connecting to codeload.github.com (codeload.github.com)|140.82.121.10|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [application/x-gzip]
Saving to: âv11.6.tar.gzâ

    [      <=>                                                                                                                           ] 2,974,369   2.31MB/s   in 1.2s

2021-10-15 19:15:39 (2.31 MB/s) - âv11.6.tar.gzâ saved [2974369]

[root@test-machine02 tmp]#


Step 3. Extract the archive: Once the download is complete, you have to extract the archive from the tarball.

[root@test-machine02 tmp]#
[root@test-machine02 tmp]# ls -ltr
total 5600
-rw-r--r--. 1 root   root   2755155 Mar 30  2018 llvm5.0-5.0.1-7.el7.x86_64.rpm
srwx------. 1 mongod mongod       0 Oct  5 18:55 mongodb-27017.sock
-rw-r--r--. 1 root   root   2974369 Oct 15 19:15 v11.6.tar.gz
[root@test-machine02 tmp]#
[root@test-machine02 tmp]# tar -zxvf v11.6.tar.gz
pgbadger-11.6/
pgbadger-11.6/.editorconfig
pgbadger-11.6/.gitignore
pgbadger-11.6/CONTRIBUTING.md
pgbadger-11.6/ChangeLog
pgbadger-11.6/HACKING.md
pgbadger-11.6/LICENSE
pgbadger-11.6/MANIFEST
pgbadger-11.6/META.yml
pgbadger-11.6/t/fixtures/weeknumber.log
pgbadger-11.6/tools/
pgbadger-11.6/tools/README.pgbadger_tools
pgbadger-11.6/tools/README.updt_embedded_rsc
pgbadger-11.6/tools/pgbadger_tools
pgbadger-11.6/tools/updt_embedded_rsc.pl
[root@test-machine02 tmp]#

[root@test-machine02 tmp]#
[root@test-machine02 tmp]# ls -ltr
total 5604
-rw-r--r--. 1 root   root   2755155 Mar 30  2018 llvm5.0-5.0.1-7.el7.x86_64.rpm
drwxrwxr-x. 6 root   root      4096 Sep  4 11:45 pgbadger-11.6
srwx------. 1 mongod mongod       0 Oct  5 18:55 mongodb-27017.sock
-rw-r--r--. 1 root   root   2974369 Oct 15 19:15 v11.6.tar.gz
[root@test-machine02 tmp]#


Step 4. Install the pgBadger: Go to the extracted directory and compile the makefile using command perl Makefile.PL. And then install the pgBadger using the make && make install command. Once pgBadger is installed use the command pgbadger -V to check pgBadger version and pgbadger –help command to get a complete help list.

[root@test-machine02 tmp]#
[root@test-machine02 tmp]# cd pgbadger-11.6
[root@test-machine02 pgbadger-11.6]#

[root@test-machine02 pgbadger-11.6]#
[root@test-machine02 pgbadger-11.6]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for pgBadger
[root@test-machine02 pgbadger-11.6]#

[root@test-machine02 pgbadger-11.6]#
[root@test-machine02 pgbadger-11.6]# make && make install
which: no pod2markdown in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/u01/mysql-8/bin)
cp pgbadger blib/script/pgbadger
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pgbadger
echo "=head1 SYNOPSIS" > doc/synopsis.pod
./pgbadger --help >> doc/synopsis.pod
echo "=head1 DESCRIPTION" >> doc/synopsis.pod
sed -i.bak 's/ +$//g' doc/synopsis.pod
rm doc/synopsis.pod.bak
sed -i.bak '/^=head1 SYNOPSIS/,/^=head1 DESCRIPTION/d' doc/pgBadger.pod
sed -i.bak '4r doc/synopsis.pod' doc/pgBadger.pod
rm doc/pgBadger.pod.bak
Manifying blib/man1/pgbadger.1p
rm doc/synopsis.pod
which: no pod2markdown in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/u01/mysql-8/bin)
echo "=head1 SYNOPSIS" > doc/synopsis.pod
./pgbadger --help >> doc/synopsis.pod
echo "=head1 DESCRIPTION" >> doc/synopsis.pod
sed -i.bak 's/ +$//g' doc/synopsis.pod
rm doc/synopsis.pod.bak
sed -i.bak '/^=head1 SYNOPSIS/,/^=head1 DESCRIPTION/d' doc/pgBadger.pod
sed -i.bak '4r doc/synopsis.pod' doc/pgBadger.pod
rm doc/pgBadger.pod.bak
Manifying blib/man1/pgbadger.1p
Installing /usr/local/share/man/man1/pgbadger.1p
Installing /usr/local/bin/pgbadger
Appending installation info to /usr/lib64/perl5/perllocal.pod
rm doc/synopsis.pod
[root@test-machine02 pgbadger-11.6]#
[root@test-machine02 pgbadger-11.6]# pgbadger -V
pgBadger version 11.6
[root@test-machine02 pgbadger-11.6]#

[postgres@test-machine01 tmp]$ pgbadger --help

Usage: pgbadger [options] logfile [...]

    PostgreSQL log analyzer with fully detailed reports and graphs.

Arguments:

    logfile can be a single log file, a list of files, or a shell command
    returning a list of files. If you want to pass log content from stdin
    use - as filename. Note that input from stdin will not work with csvlog.

Options:

    -a | --average minutes : number of minutes to build the average graphs of
                             queries and connections. Default 5 minutes.
    -A | --histo-average min: number of minutes to build the histogram graphs
                             of queries. Default 60 minutes.
    -b | --begin datetime  : start date/time for the data to be parsed in log
                             (either a timestamp or a time)
    -c | --dbclient host   : only report on entries for the given client host.
    -C | --nocomment       : remove comments like /* ... */ from queries.


Reference : https://github.com/darold/pgbadger

Part II : Install and Configure pgBadger in PostgreSQL Part-II

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article
!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. 
Click here to understand more about our pursuit.

 

 

Related Articles


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Leave a Reply

Your email address will not be published. Required fields are marked *