Set up pgbadger tool to analyze postgres database logs

The troubleshooting process can be accelerated with a detailed PostgreSQL log in a comprehensive format with graphs and charts. In the following article, we will explain how we can do this using pgBadger.

PgBadger can automatically detect PostgreSQL log file formats such as syslog, stderr, csvlog, and jsonlog. It can parse both compressed as well as plain text log files. Following is a comprehensive list of features:

1. All charts can be enlarged and saved as PNG images.
2. You can also limit pgBadger to only report errors or remove any part of the report using command line options.
3. pgBadger supports any custom format set into the log_line_prefix directive of your postgresql.conf file as long as it at least specifies a time escape sequence (%t, %m or %n) and the process-related escape sequence (%p or %c).
4. pgBadger allows parallel processing on a single log file and multiple files by using the -j or -J options and the number of CPUs as a value.
5. If you want to save system performance, you can also use log_duration instead of log_min_duration_statement to only have reports on duration and number of queries.
6. pgBadger supports an incremental mode that allows the construction of incremental reports after successive runs of pgbadger. It is possible to run pgbadger every day or even more frequently by hour/minutes/seconds and generate cumulative reports. A top index page will allow you to go directly to the weekly and daily reports.

Please find below the steps for the installation of the pgbadger tool.

pgbadger is written in pure Perl and uses a JavaScript library to draw graphs. Hence, you must 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

Step 1: Install pgBadger using the below steps.

Option 1: Pgdg repository

Once pgdg repository is installed from the community site, Also install the epel repository for other dependencies like perl-Text-CSV_XS and perl-UNIVERSAL-isa:

dnf install epel-release

After that, you will be able to install the last pgbadger version from the community repository :

dnf install pgbadger

dnf list installed pgbadger

Installed Packages
pgbadger.noarch 12.1-1.rhel8 @pgdg-common

Option 2: Github Darold

Download pgbadger from https://github.com/darold/pgbadger. It will provide a zip file pgbadger-master.zip which consists of the pgbadger binaries.

Once downloaded you have to unzip to extract the file using the below command:

[root@RockyB1 ~]# unzip pgbadger-master.zip

Now go to the extracted directory.

[root@RockyB1 ~]# cd pgbadger-master/

Compile the makefile.

root@RockyB1 pgbadger-master]# perl Makefile.PL

Now install the pgbadger using the below command.

[root@RockyB1 pgbadger-master]# make && sudo make install

Finally, verify the installation by running the below command which will display the pgbadger installed version.

[root@RockyB1 pgbadger-master]# pgbadger -V

Step 2: PostgreSQL Configuration

• Since pgbadger reads the PostgreSQL logs and picks up the information, it is essential to make some changes in postgresql.conf file so that necessary information is available in the PostgreSQL log file for pgbadger to read.

• Do not enable both log_min_duration_statement, log_duration, and log_statement altogether, this will result in wrong counter values and excessive log size.

• 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'

• To know more about the above parameters you may go through the below documentation:

• Logging Configuration:

• Error Reporting and Logging:

Notes:

1. The above changes require you to perform a PostgreSQL restart. systemctl restart postgresql-xx or systemctl restart edb-as-xx Replace xx with your Postgres version.
2. You need to enable the above parameters as per your business requirements.

Step 3: Generate pgbadger report using the command line pgbadger utility. There are many options, however, the most basic setup is to provide the log line prefix, the database uses the log file and the output HTML file. You can check the available options using ./pgbadger --help that can be used with pgBadger.

Step 4: Please find the Command for generating the pgbadger report.

Example:

[root@RockyB1 pgbadger-master]# ./pgbadger --prefix '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h' /var/lib/edb/as13/data/log/edb-2022-06-11_071033.log -o postgresql.html

[========================>] Parsed 2315 bytes of 2315 (100.00%), queries: 0, events: 0 LOG: Ok, generating html report...

The above command will create a graph output (postgresql.html) in HTML format. Please open it using a web browser.

Note: You can provide a list of log files with an absolute path