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 - Architecture

-
Image
When you start PostgreSQL`s instance (via pg_ctl start) the main process of the databases is started. In the past versions it was known as the postmaster process and now days it is called postgres. When this processes is started it allocates memory for two main things : shared memory and other background processes. The shared memory is a memory that the database uses mainly for cashing. Its main goal is to reduce the I/O operations and as a result of that improve the performance of the database. The other processes that the postgres process starts are responsible for the ongoing work of the database.  PostgreSQL`s architecture is based on a client/server communication. In the server side there is a server process that manages the files, database operations, connection and so on.. On the client side there is a tool/program which can be a graphical tool (gui)  like pgAdmin or a command line tool(cli) like psql  that is repnsible for connection the database and allowing the user
Read more

PostgreSQL - Backup & Point In Time Recovery

-
Image
  In this post you will learn about how to restore a PostgreSQL database in Point in time. Open source databases are taking over other conventional Database softwares at very high speed. For me personally, I really like the way PostgreSQL is designed and it’s simple architecture. I have tried to write it down every possible steps which will help you to restore your Postgres database. Below Steps will be same, but few commands may vary with any other backup tool. PITR PostgreSQL Allow you to restore database to a specific moment in time PITR. It make use of live database files and WAL files. This method can only backup and restore the whole cluster, for individual database use pg_dump. It can be done when the DB is online. Step 1. If archive logs not enable then enable it, otherwise go to Step3. Create Directory for archive logs mkdir /postgres/postgres12.2/wal_archives Make changes in postgresql.conf Postgresql.conf is present under your data directory, in my case it is in /postgres/po
Read more