POSTGRESQL - PGBADGERWhat 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 . pgBadger : PostgreSQL Log Analysis tool 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 :
[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_sharedNow 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: log_directory = log lc_messages = ' en_US.UTF - 8 " lc_messages = ' C ' log_directory = logThis 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) |