Skip to main content

Installing pgbadger on Linux for PostgreSQL log analysis

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.

Installation:

Prerequisite:

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. Charts are rendered using a JavaScript library and Your web browser will do all the work. Nothing additional is required here.

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 pgbadger.

Method to Follow :

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.

Step 1 :$ wget https://github.com/darold/pgbadger/archive/v11.4.tar.gz


Once the download is complete, you have to extract the archive from the tarball.


Step 2 : $ 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

$ perl Makefile.PL


$ 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]$

[user@server pgbadger-11.4]$ pgbadger -V

pgBadger version 11.4

[user@server pgbadger-11.4]$


************PostgreSQL Configuration*******************


Since 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.


The 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 log_line_prefix.


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:


 lc_messages='en_US.UTF-8'

 lc_messages='C'

The usual 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

Generating pgbadger report:


Now that everything is in place, let’s proceed with generating a pgbadger report.


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>

For Example:


[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 ~]$



Comments

Post a Comment

Popular posts from this blog

PostgreSQL Vacuum and Vacuum full are not two different processes

  PostgreSQL’s   VACUUM   and   VACUUM FULL   are not separate processes but rather different operational modes of the same maintenance command. Here’s why: Core Implementation Both commands share the same underlying codebase and are executed through the  vacuum_rel()  function in PostgreSQL’s source code ( src/backend/commands/vacuum.c ). The key distinction lies in the  FULL  option, which triggers additional steps: Standard  VACUUM : Removes dead tuples (obsolete rows) and marks space reusable  within PostgreSQL Updates the visibility map to optimize future queries Runs concurrently with read/write operations VACUUM FULL : Rewrites the entire table into a new disk file, compressing it and reclaiming space for the  operating system Rebuilds all indexes and requires an  ACCESS EXCLUSIVE  lock, blocking other operations Key Differences in Behavior Aspect Standard VACUUM VACUUM FULL Space Reclamation Internal reuse onl...
1 comment
Read more

Job scheduler for PostgreSQL "pg_cron"

What is pg_cron   : -   pg_cron is a simple cron-based job scheduler for   PostgreSQL (9.5 or higher)   that runs inside the database as an extension. It uses the same syntax as regular cron, but it allows you to schedule PostgreSQL commands directly from the database . Why We need it ? Running periodic maintenance jobs or removing old data is a common requirement in PostgreSQL. A simple way to achieve this is to configure cron or another external daemon to periodically connect to the database and run a command. Let's see how it's works  Step 1 :-  For implementing/Installation of pg_cron you need to download source code from git Dowload link  export PATH=/usr/local/pgsql/bin:$PATH wget https://github.com/citusdata/pg_cron/archive/master.zip unzip master cd pg_cron-master/ make make install    Step 2 : - To start the pg_cron background worker when PostgreSQL starts, you need to add pg_cron to  shared_preload_libraries   in post...
Post a Comment
Read more

All about pg_hba.conf(authentication methods- Postgresql)

  pg_hba.conf is the PostgreSQL access policy configuration file, which is located in the /var/lib/pgsql/10/data/ directory (PostgreSQL10) by default. The configuration file has 5 parameters, namely: TYPE (host type), DATABASE (database name), USER (user name), ADDRESS (IP address and mask), METHOD (encryption method) host all all 192.168.109.103/22 md5 host dbName user 192.168.109.106/22 trust Modify the server-side pg_hba.conf file Make the shell can connect to the postgres database secretly: Modify the authentication file $PGDATA/pg_hba.conf, add the following lines, and reload to make the configuration take effect immediately. host pankajconnect postgresql 192.168.8.103/32 trust Reload to take effect: pg_ctl reload -D $PGDATA Examples: 1. Allow local login to the database using PGAdmin3, database address  localhost, user user1, database user1db: host user1db user1 127.0.0.1/32 md5 2. Allow 10.1.1.0~10.1.1.255 network segments to log in to the database: host all all 10.1.1....
Post a Comment
Read more