As a DBA, we often encounter scenarios when you need to track PostgreSQL database performance by analyzing workloads and identify bottlenecks if any. There comes
pgbadger – an open source log analyzer for PostgreSQL. It parse the PostgreSQL log files and gives you a nice overview of the PostgreSQL cluster including various query metrics like number of query executions, their types and duration, session details, Locks statistics, warnings, errors and other event patterns etc. All of these details will be available in a nice HTML format on your web browser.
pgbadger is written in pure
Use the below command to install
perl if it is not already installed in your system.
$ yum install -y perl perl-devel
Either of the below two method can be followed to install
The first step is to download the latest
pgbadger installation package. The Official releases are published to the GitHub Release page of
pgBadger. While I am writing this blog post, the latest available version is 11.4.
Let’s run the below command on the Linux command line interface to download the
pgbadger version 11.4.
$ wget https://github.com/darold/pgbadger/archive/v11.4.tar.gz
Alternatively, you can clone the
pgbadger GitHub repo as well if you have Git installed in your system.
$ git clone https://github.com/dalibo/pgbadger.git
Once the download is complete, you have to extract the archive from the tarball.
$ tar xzvf v11.4.tar.gz
[user@server pgbadger_demo]$ tar xzvf v11.4.tar.gz pgbadger-11.4/ pgbadger-11.4/.editorconfig pgbadger-11.4/.gitignore pgbadger-11.4/CONTRIBUTING.md pgbadger-11.4/ChangeLog pgbadger-11.4/HACKING.md pgbadger-11.4/LICENSE pgbadger-11.4/MANIFEST pgbadger-11.4/META.yml pgbadger-11.4/Makefile.PL pgbadger-11.4/README pgbadger-11.4/README.md pgbadger-11.4/doc/ pgbadger-11.4/doc/pgBadger.pod pgbadger-11.4/pgbadger pgbadger-11.4/resources/ pgbadger-11.4/resources/.gitignore pgbadger-11.4/resources/LICENSE pgbadger-11.4/resources/README pgbadger-11.4/resources/bean.js pgbadger-11.4/resources/bootstrap.css pgbadger-11.4/resources/bootstrap.js pgbadger-11.4/resources/font/ pgbadger-11.4/resources/font/FontAwesome.otf pgbadger-11.4/resources/font/fontawesome-webfont.eot pgbadger-11.4/resources/fontawesome.css pgbadger-11.4/resources/jqplot.barRenderer.js pgbadger-11.4/resources/jqplot.canvasAxisTickRenderer.js pgbadger-11.4/resources/jqplot.canvasTextRenderer.js pgbadger-11.4/resources/jqplot.categoryAxisRenderer.js pgbadger-11.4/resources/jqplot.cursor.js pgbadger-11.4/resources/jqplot.dateAxisRenderer.js pgbadger-11.4/resources/jqplot.highlighter.js pgbadger-11.4/resources/jqplot.pieRenderer.js pgbadger-11.4/resources/jqplot.pointLabels.js pgbadger-11.4/resources/jquery.jqplot.css pgbadger-11.4/resources/jquery.jqplot.js pgbadger-11.4/resources/jquery.js pgbadger-11.4/resources/patch-jquery.jqplot.js pgbadger-11.4/resources/pgbadger.css pgbadger-11.4/resources/pgbadger.js pgbadger-11.4/resources/pgbadger_slide.js pgbadger-11.4/resources/underscore.js pgbadger-11.4/t/ pgbadger-11.4/t/01_lint.t pgbadger-11.4/t/02_basics.t pgbadger-11.4/t/03_consistency.t pgbadger-11.4/t/exp/ pgbadger-11.4/t/exp/stmt_type.out pgbadger-11.4/t/fixtures/ pgbadger-11.4/t/fixtures/cloudsql.log.gz pgbadger-11.4/t/fixtures/light.postgres.log.bz2 pgbadger-11.4/t/fixtures/logplex.gz pgbadger-11.4/t/fixtures/pg-syslog.1.bz2 pgbadger-11.4/t/fixtures/pgbouncer.log.gz pgbadger-11.4/t/fixtures/rds.log.bz2 pgbadger-11.4/t/fixtures/stmt_type.log pgbadger-11.4/tools/ pgbadger-11.4/tools/README.pgbadger_tools pgbadger-11.4/tools/README.updt_embedded_rsc pgbadger-11.4/tools/pgbadger_tools pgbadger-11.4/tools/updt_embedded_rsc.pl [user@server pgbadger_demo]$
Now go to the extracted directory.
$ cd pgbadger-11.4
Compile the makefile.
$ perl Makefile.PL
[user@server pgbadger-11.4]$ perl Makefile.PLChecking if your kit is complete... Looks good Writing Makefile for pgBadger [user@server pgbadger-11.4]$
Now install the
pgbadger using the below command.
$ make && sudo make install
[user@server pgbadger-11.4]$ make && sudo make install which: no pod2markdown in (/usr/local/rvm/gems/ruby-2.4.1/bin:/usr/local/rvm/gems/ruby-2.4.1@global/bin:/usr/local/rvm/rubies/ruby-2.4.1/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/usr/local/rvm/bin:/home/cloud_user/.local/bin:/home/user/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 [sudo] password for user: which: no pod2markdown in (/sbin:/bin:/usr/sbin:/usr/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 [user@server pgbadger-11.4]$
Finally, verify the installation by running the below command which will display the
pgbadger installed version.
$ pgbadger -V
[user@server pgbadger-11.4]$ pgbadger -V pgBadger version 11.4 [user@server pgbadger-11.4]$
pgbadger package is also available at the PostgreSQL yum repository. If you have already installed the repository configuration package from official PostgreSQL repository, then
pgbadger can be installed by running the below command.
$ sudo yum install pgbadger
[user@server ~]$ sudo yum install pgbadger Loaded plugins: amazon-id, search-disabled-repos Resolving Dependencies --> Running transaction check ---> Package pgbadger.noarch 0:11.4-1.rhel7 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================================================================================================================================================================================== Package Arch Version Repository Size ============================================================================================================================================================================================================================================== Installing: pgbadger noarch 11.4-1.rhel7 pgdg-common 346 k Transaction Summary ============================================================================================================================================================================================================================================== Install 1 Package Total download size: 346 k Installed size: 1.5 M Is this ok [y/d/N]: y Downloading packages: warning: /var/cache/yum/x86_64/7Server/pgdg-common/packages/pgbadger-11.4-1.rhel7.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY========================- ] 0.0 B/s | 224 kB --:--:-- ETA Public key for pgbadger-11.4-1.rhel7.noarch.rpm is not installed pgbadger-11.4-1.rhel7.noarch.rpm | 346 kB 00:00:01 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <email@example.com>" Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-14.noarch (@/pgdg-redhat-repo-latest.noarch) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Is this ok [y/N]: y Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : pgbadger-11.4-1.rhel7.noarch 1/1 Verifying : pgbadger-11.4-1.rhel7.noarch 1/1 Installed: pgbadger.noarch 0:11.4-1.rhel7 Complete! [user@server ~]$
pgbadger reads the PostgreSQL logs and picks up the information, it is essential to make some changes on
Postgresql.Conf file so that necessary information are available in the PostgreSQL log file for
pgbadger to read.
The first and foremost thing is to enable query logging. Change the
log_min_duration_statement parameter value accordingly on
Postgresql.Conf file. The value ‘0’ signifies that every statement will be logged which may not be ideal for a very busy server. You can change the value accordingly based on your requirement. Suppose, you want to log queries running for more that 2 seconds, then set the parameter value(in milliseconds) as ‘2000’.
** Do not enable both log_min_duration_statement, log_duration and log_statement all together, this will result in wrong counter values and excessive log size.
** Do not enable
log_statement as its log format will not be parsed by
pgBadger. If you have
log_statement set to
'all' nothing will be logged through the
log_min_duration_statement directive and
pgbadger will not be able to parse the log.
** If you don’t want all details about queries, set log_min_duration_statement to -1 to disable it and enable log_duration in your postgresql.conf file. It will only report duration and number of queries.
log_min_duration_statement should always be preferred (recommended).
log_min_duration_statement = 0
The next important parameter to look for is
log_line_prefix whose value can be specified based on the value of another parameter
log_destination . The
log_destination value tells PostgreSQL to generate logs in specified format. By default, PostgreSQL generates logs in standard error (stderr) format. In this format, each log message is prefixed with the information specified by the parameter
With ‘stderr’ log format,
log_line_prefix must be having at least a time escape sequence (%t, %m or %n) and the process related escape sequence (%p or %c) as shown below.
log_line_prefix = '%t [%p]: '
It also lets you add user, database name, application name and client ip address as well as shown below.
log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '
Another thing to remember is that your log messages should be in English with or without locale support. The below settings ensures that:
postgresql.conf configuration for
pgBadger looks like this:
log_min_duration_statement = 0 log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,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'
The above changes requires you to perform a PostgreSQL restart.
systemctl restart postgresql
Now that everything is in place, let’s proceed with generating a
The simplest way to analyze a particular log and generate the
pgbadger report is to specify the log file path to
pgbadger and it will generate the report to your present working directory.
$ pgbadger <log file>
[user@server ~]$ sudo pgbadger /var/lib/pgsql/12/data/log/postgresql-Fri.log [========================>] Parsed 1521 bytes of 1521 (100.00%), queries: 0, events: 1 LOG: Ok, generating html report... [user@server ~]$
You can also analyze all your log files at once by using wildcard(*) while specifying log names. Also, you can specify the output directory and file name as well by specifying appropriate switch as shown below.
[user@server]$ sudo pgbadger /var/lib/pgsql/12/data/log/postgresql-* -O /var/www/pgbadger -o pgbadger.html [========================>] Parsed 15914 bytes of 15914 (100.00%), queries: 0, events: 20 LOG: Ok, generating html report... [user@server]$
pgbadger report can be generated incrementally at a certain interval and you can even specify the retention period of the report files as well. This can be accomplished by setting up a
cron job. For example, the below
cron job will generate
pgbadger report daily at 01:00 AM with the retention set as one week.
$ crontab -e 0 1 * * * /usr/bin/pgbadger --R 1 -I -q /var/lib/pgsql/12/data/log/postgresql-* -O /var/www/pgbadger/
You can use the help command as mentioned below to get the complete list of options and their usage in
$ pgbadger --help
Finally, I would say it’s one of the best freely available tool for PostgreSQL log analysis and identifying performance issues. The shareable and feature-rich HTML reports & graphs has made it the go-to tool when it comes to capturing and sharing the database performance report with stakeholders.
The main author and official maintainer of the
pgBadger project is Gilles Darold. You can find the official GitHub repo here: https://github.com/darold/pgbadger and the website http://pgbadger.darold.net/ as well.