Install and Configure pgBadger in PostgreSQL Part-II

October 23, 2021
()

Install and Configure pgBadger in PostgreSQL Part-II

This article is a continuation of the Installed pgBadger package in PostgreSQL click here to read more. In this blog, we will Configure PostgreSQL Cluster to use pgBadger.

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 is available in the PostgreSQL log file for pgbadger to read.

Change the log_min_duration_statement parameter value accordingly based on your requirement. Suppose, you want to log queries running for more than 2 seconds, then set the parameter value(in milliseconds) as ‘2000’. If you just want to report duration and number of queries and 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.

Warning: Do not enable both log_min_duration_statement, log_duration, and log_statement altogether, this will result in wrong counter values. Note that this will also increase drastically the size of your log. log_min_duration_statement should always be preferred.

Set below parameters in postgresql.conf
log_line_prefix:
Controls information prefixed to each log line.
log_checkpoints:
Logs each checkpoint.
log_connections:
Logs each successful connection.
log_disconnections:
Logs end of a session, including duration.
log_lock_waits:
Logs long lock waits.
log_temp_files:
Log the use of temporary files larger than this number of kilobytes.
log_autovacuum_min_duration:
Sets the minimum execution time above which autovacuum actions will be logged.
log_error_verbosity:
Sets the verbosity of logged messages.
log_min_duration_statement:
Sets the minimum execution time above which all statements will be logged.

[root@test-machine02 ~]#
[root@test-machine02 ~]# su - postgres
Last login: Sun Oct 17 09:13:10 +03 2021 on pts/0
[postgres@test-machine02 ~]$ psql
psql (13.4)
Type "help" for help.

postgres=#
postgres=# select name,setting,unit from pg_settings where name in ('log_checkpoints','log_connections','log_disconnections','log_lock_waits','log_temp_files','log_autovacuum_min_duration','log_error_verbosity','log_min_duration_statement','log_min_duration_statement','lc_messages');
            name             |   setting   | unit
-----------------------------+-------------+------
 lc_messages                 | en_US.UTF-8 |
 log_autovacuum_min_duration | -1          | ms
 log_checkpoints             | off         |
 log_connections             | off         |
 log_disconnections          | off         |
 log_error_verbosity         | default     |
 log_lock_waits              | off         |
 log_min_duration_statement  | -1          | ms
 log_temp_files              | -1          | kB
(9 rows)

postgres=#

postgres=# alter system set log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h';
ALTER SYSTEM
postgres=# alter system set log_checkpoints = 'on';
ALTER SYSTEM
postgres=# alter system set log_connections = 'on';
ALTER SYSTEM
postgres=# alter system set log_disconnections = 'on';
ALTER SYSTEM
postgres=# alter system set log_lock_waits = 'on';
ALTER SYSTEM
postgres=# alter system set log_temp_files = 0;
ALTER SYSTEM
postgres=# alter system set log_autovacuum_min_duration = 0;
ALTER SYSTEM
postgres=# alter system set log_error_verbosity = 'default';
ALTER SYSTEM
postgres=# alter system set log_min_duration_statement = 60000;
ALTER SYSTEM
postgres=# alter system set lc_messages='en_US.UTF-8';
ALTER SYSTEM
postgres=# alter system set lc_messages='C';
ALTER SYSTEM
postgres=#\q
[postgres@test-machine02 ~]$exit
logout
[root@test-machine02 ~]#
[root@test-machine02 ~]# systemctl restart postgresql-13
[root@test-machine02 ~]#
[root@test-machine02 ~]#
[root@test-machine02 ~]# su - postgres
Last login: Mon Oct 18 14:35:24 +03 2021 on pts/0
[postgres@test-machine02 ~]$
[postgres@test-machine02 ~]$ psql
psql (13.4)
Type "help" for help.
postgres=#
postgres=# select name,setting,unit from pg_settings where name in ('log_checkpoints','log_connections','log_disconnections','log_lock_waits','log_temp_files','log_autovacuum_min_duration','log_error_verbosity','log_min_duration_statement','log_min_duration_statement','lc_messages');
            name             | setting | unit
-----------------------------+---------+------
 lc_messages                 | C       |
 log_autovacuum_min_duration | 0       | ms
 log_checkpoints             | on      |
 log_connections             | on      |
 log_disconnections          | on      |
 log_error_verbosity         | default |
 log_lock_waits              | on      |
 log_min_duration_statement  | 60000   | ms
 log_temp_files              | 0       | kB
(9 rows)

postgres=#


Command to create pgBadger report from the specific logfile.

[postgres@test-machine02 log]$ pgbadger /var/lib/pgsql/13/data/log/postgresql-Mon.log -O /var/lib/pgsql/13/data/log/ -o pgbadger_test1_`date +\%F`.html
[========================>] Parsed 2432956 bytes of 2432956 (100.00%), queries: 0, events: 1259
LOG: Ok, generating html report...
[postgres@test-machine02 log]$
[postgres@test-machine02 log]$ ls -ltr
total 23540
-rw-------. 1 postgres postgres 3264086 Oct  5 23:59 postgresql-Tue.log
-rw-------. 1 postgres postgres 3291410 Oct  6 23:59 postgresql-Wed.log
-rw-------. 1 postgres postgres 3244446 Oct  7 23:59 postgresql-Thu.log
-rw-------. 1 postgres postgres 3248453 Oct  8 23:59 postgresql-Fri.log
-rw-------. 1 postgres postgres 3252768 Oct  9 23:59 postgresql-Sat.log
-rw-------. 1 postgres postgres 4163506 Oct 17 23:59 postgresql-Sun.log
-rw-------. 1 postgres postgres 2433896 Oct 18 16:00 postgresql-Mon.log
-rw-r--r--. 1 postgres postgres  973436 Oct 18 16:00 pgbadger_test1_2021-10-18.html
[postgres@test-machine02 log]$


Command to create Incremental pgBadger reports: pgBadger includes an automatic incremental report mode using option -I or –incremental. When running in this mode, pgBadger will generate one report per day and a cumulative report per week.

[postgres@test-machine02 log]$
[postgres@test-machine02 log]$ pgbadger --retention 2 -I -q /var/lib/pgsql/13/data/log/postgresql-*.log -O /var/lib/pgsql/13/data/log/
[postgres@test-machine02 log]$


Command to create Monthly pgBadger reports: By default pgBadger in incremental mode only computes daily and weekly reports. If you want monthly cumulative reports you will have to use a separate command to specify the report to build. For example to build a report for August 2021. This will add a link to the month name into the calendar view of incremental reports to look at the monthly reports.

[postgres@test-machine02 log]$
[postgres@test-machine02 log]$ pgbadger -X --month-report 2021-10 /var/lib/pgsql/13/data/log/postgresql-*.log -O /var/lib/pgsql/13/data/log/
LOG: Ok, generating HTML monthly report into /var/lib/pgsql/13/data/log//2021/10/index.html
LOG: Ok, generating global index to access incremental reports...
[postgres@test-machine02 log]$


Command to create Hourly pgBadger reports: 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, you can proceed by running each hour the following commands. 1st pgbadger command will generate binary files every hour. Then you can generate pgbadger report using these binary files using the 2nd command.

[postgres@test-machine02 log]$ mkdir Mon
[postgres@test-machine02 log]$ pgbadger --last-parsed .pgbadger_last_state_file -o Mon/hour1.bin /var/lib/pgsql/13/data/log/postgresql-Mon.log
[========================>] Parsed 2683017 bytes of 2683017 (100.00%), queries: 0, events: 1379
LOG: Ok, generating binary report...
[postgres@test-machine02 log]$

[postgres@test-machine02 log]$
[postgres@test-machine02 log]$ pgbadger Mon/*.bin
[========================>] Parsed 1126126 bytes of 1126126 (100.00%), queries: 0, events: 1379
LOG: Ok, generating html report...
[postgres@test-machine02 log]$


Command to create pgBadger report from specific timeframe: Use option -b for begin time and -e for the end time.

[postgres@test-machine02 log]$
[postgres@test-machine02 log]$ pgbadger -b "2021-10-18 13:38:28" -e "2021-10-18 17:38:28" /var/lib/pgsql/13/data/log/postgresql-Mon.log
[=======================> ] Parsed 2701183 bytes of 2710692 (99.65%), queries: 0, events: 321
LOG: Ok, generating html report...
[postgres@test-machine02 log]$


Reference: https://github.com/darold/pgbadger


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


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Leave a Reply

Your email address will not be published. Required fields are marked *