POSTGRESQL - PGBADGER

pgBadger PostgreSQL Log Analysis tool :
What is our objective which will be solved by this tool called as pgbadger?
pgBadger is a PostgreSQL performance analyser , built for speed with fully detailed reports based on your PostgreSQL log file.

To get detailed view of PostgreSQL database workload, pgbadger is a great tool to explore the detailed dimesion .

It is open source and easy to install and configure and explore.

You can also schedule pgbadger reports via available scheduler like Cron.

Analyze the pgbadger report on dialy basis for review and plan for continuous improvements on database performance area .

pgbadger detects log format automatically and charts can be saved as image directly in presentable mode.

pgBadger : PostgreSQL Log Analysis tool

It is an open source " fast PostgreSQL log analysis report " program.

It is written in Perl and is a tool for The Posteresql lop analyzer.

The report has the information in a readble report format.
These reports can help analyze errors in postgresql, can share details around checkpoint, vacuum and other basic but crucial information for a PostgreSQL instance.

FEATURE of PgBadger:
Reports everything about your SQL queries :

Overall statistics .
The most frequent waiting queries .
Queries that waited the most .
Queries generating the most temporary files .
The slowest queries .
Queries that took up the most time .
The most frequent queries .
The most frequent errors .
Histogram of query times .
Histogram of sessions times .
Users involved in top queries .
Applications involved in top queries .
Queries generating the most cancellation .

pgBadger is also able to parse PgBouncer log files and to create the following reports :

Request Throughput
Bytes 1/0 Throughput
Queries Average duration
Simultaneous sessions
Histogram of sessions times
Sessions per database
Sessions per user
Sessions per host
Established connections
Connections per database
Connections per user
Connections per host
Most used reserved pools
Most Ecequent Ennons / Events

Steps to install PGBADGER :

    [root@krishna ~]# yum install  -y perl perl-devel
    [root@krishna ~]#  	
    [root@krishna ~]# yum install git
    [root@krishna ~]# wget https://github.com/darold/pgbadger/archive/v11.6.tar.gz
    [root@krishna ~]# tar -xzvf v11.6.tar.gz
    [root@krishna ~]# ls -lrt

    [root@krishna ~]# cd pgbadger-11.6
    [root@krishna pgbadger-11.6]# perl Makefile.PL
    [root@krishna pgbadger-11.6]# make
    [root@krishna pgbadger-11.6]# make install
    [root@krishna pgbadger-11.6]# pgbadger -V

    CONFIGURATION SETTINGS IN POSTGRESQL.CONF file
    log_min_duration_statement= 0
    log_line_prefix =’ % t [ %p ] : [%l-1]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 . 
    log_destination=’stderr’
    log_directory = log
    lc_messages = ' en_US.UTF - 8 "
    lc_messages = ' C '

RESTART POSTGRES

    [root@krishna ~]# su - postgres
    -bash-4.2$ /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ restart
    [root@sdbt pgsql] pgbadger /var/lib/pgsql/13/data/log/postgresql-* -f /var/lib/pgsql/log -o pgbadger.html   
    [root@krishna ~]# cd /var/lib/pgsql/log 
    [root@krishna log]# ls
    pgbadger.html  postgresql-Tue.log  postgresql-Wed.log
    [root@krishna log]# cp -r pgbadger.html /media/sf_shared

Now we can view the report as HTML page in a browser

log_min_duration_statement :
This setting causes PostgreSQL to log all statements which run for longer than the given value in milliseconds, where a value of 0 will log everything. Make sure that the "log_statement" is set to "none", otherwise the "log_min_duration_statement" setting will not log anything ("none" is the default value anyway).

log_checkpoints :
When enabled, PostgreSQL writes information about checkpoint operations to the server log. This information includes the start time of the checkpoint, the duration of the checkpoint, and other relevant details. 2024-05-16 15:30:00.123 UTC [database]: LOG: checkpoint complete: wrote 1000 buffers (10.0%); 0 transaction log file(s) added, 0 removed, 2 recycled; write=5.123 s, sync=0.456 s, total=6.789 s; sync files=3, longest=0.123 s, average=0.045 s

log_connections
When enabled, PostgreSQL writes information about client connections to the server log. This information includes details such as the timestamp of the connection, the username of the connecting user, the IP address or hostname of the client, and other relevant details. 2024-05-16 15:30:00.123 UTC [database]: LOG: connection received: host=192.168.1.100 port=54321 pid=12345

log_disconnections
When enabled, PostgreSQL writes information about client disconnections to the server log. This information typically includes details such as the timestamp of the disconnection event, the username of the disconnected user, and other relevant details. 2024-05-16 15:35:00.123 UTC [database]: LOG: disconnection: session time: 0:02:30

log_lock_waits
When enabled, PostgreSQL writes information about processes waiting for locks to the server log. This information typically includes details such as the timestamp of the log message, the PID of the waiting process, the PID of the process holding the lock, the lock type, and other relevant details.
Eg: 2024-05-16 15:40:00.123 UTC [database]: LOG: process PID 12345 still waiting for ShareLock on transaction 6789 after 1000.0 ms

log_temp_files= 0
This parameter determines the threshold at which PostgreSQL logs information about temporary file usage. If set to a non-negative integer value, PostgreSQL logs information about temporary files exceeding that size (in kilobytes) to the server log. If set to -1 (the default), temporary file logging is disabled. 2024-05-16 15:45:00.123 UTC [database]: LOG: temporary file: path "/pg_tmp/pgsql_tmp1234.0", size 2048KB

log_autovacuum_min_duration = 0
This parameter specifies the minimum duration threshold (in milliseconds) for automatic vacuum operations to be logged. If an autovacuum operation exceeds this duration, PostgreSQL logs information about it to the server log. If set to -1 (the default), logging of autovacuum operations based on duration is disabled. 2024-05-16 15:50:00.123 UTC [database]: LOG: automatic vacuum of table "public.example_table" took 3000 ms

log_error_verbosity= default
When set to default, PostgreSQL logs error messages with a default level of verbosity. This level of verbosity provides essential information about the error, including severity, SQL state, error code, message text, and context. It strikes a balance between providing useful information for troubleshooting and maintaining a manageable log file size. 2024-05-16 16:00:00.123 UTC [database]: ERROR: division by zero

log_destination=’stderr’
This parameter specifies one or more destinations for PostgreSQL log messages. The available options are:

stderr : Log messages are sent to the standard error output of the PostgreSQL server process.

csvlog : Log messages are written to CSV format log files in the logging directory (log_directory). This format allows for easy parsing and analysis with external tools.

yslog : Log messages are sent to the system syslog facility. The destination facility can be configured with the syslog_facility parameter.

eventlog (Windows only) : Log messages are written to the Windows Event Log.

eventlog_stderr (Windows only) : Log messages are written to both the Windows Event Log and the standard error output

    log_directory = log
    lc_messages = ' en_US.UTF - 8 "
    lc_messages = ' C '
    log_directory = log

This parameter specifies the directory path where log files should be stored. If not set (empty string), log files are written to the default location determined by the operating system or logging mechanism.

    lc_messages = ' en_US.UTF - 8 "

This setting specifies that PostgreSQL should use the English language messages with the United States locale conventions and UTF-8 character encoding.

    lc_messages = ' C '

The lc_messages parameter allows administrators to customize the language and cultural conventions used for PostgreSQL messages to match the preferences of users or the requirements of specific applications. By setting lc_messages to a specific locale, administrators can ensure that messages are displayed in the desired language and format, providing a consistent user experience across different environments and languages.

« Previous Next Topic » (pg_audit)