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
- Access the MSSQL database from PostgreSQL using TDS Foreign Data Wrapper (TDS_FDW)
- Access MySql database from PostgreSQL using MySQL Foreign Data Wrapper (MYSQL_FDW)
- Configure and monitor using pg_profile in PostgreSQL
- Monitor PostgreSQL Cluster using pgCenter
- Install and Configure pg_repack in PostgreSQL
