Logging mechanism in PostgreSQL:
Logging is the process of recording events, actions, and error messages in a systematic manner for future analysis and reference.
In PostgreSQL, the logging mechanism helps track various activities like SQL statements executed, connection attempts, query performance, and errors encountered during database operations.
These logs help in understanding database behaviors, identifying potential issues, and making informed decisions to optimize database performance.
Default logging mechanism:
Prior to PostgreSQL version 12, the default logging mechanism was not enabled by default. This meant that when a new PostgreSQL instance was set up, it would not automatically log any information by default. As a result, administrators and developers had to manually configure the logging parameters to enable logging.
Starting from PostgreSQL version 12, a significant change was introduced in the logging mechanism. The default behavior was altered, and a minimal logging mechanism was enabled by default. By utilizing a component known as the “logger” to handle the generation and writing of log messages. The logger is responsible for capturing crucial events and activities within the database and saving them to log files.
Types of logging formats in PostgreSQL:
1. Plain Format (Default)
2. CSV Format
3. Syslog Format
4. Json Format
Enable Logging:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Here are list of parameters regarding to logging in PostgreSQL. logging_collector = on # It enables the logging mechanism log_destination = 'stderr|csvlog' # it is extension of the log file i.e., 'stderr,csvlog'==>postgresql.log /postgresql.csv log_line_prefix = '%p,%t,%d,%u,%a' # recorded the info based on the values. log_directory = 'log'|'/u01/log' # directory where log files are written, if you want to use other location you can. log_rotation_age=1440 # control when a new log file is created #ex: it creates a new log file for every 1440 minutes(1day). log_rotation_size=10000 # create a new log file when the previous one reach 10000KB log_truncate_on_rotation=on # it will overwrite any log file that has the same name as a newly created log file. log_filename = 'postgresql-%a.log' # log file name pattern # (every log file has the first 3 letters of the weekday in its name)ex:postgresql-FRI.log 'postgresql-%Y-%m-%d.log # postgresql-2021-05-25.log log_duration='on|off' # Recorded the duration of every completed statement. log_file_mode = 0600 # creation mode for log files, log_timezone = 'Asia/Kolkata' log_connections = on|off # Recorded for every connection Info log_disconnections = on|off # Recorded for every disconnection Info log_statement = 'off' | 'ddl' | 'mod' | 'all' # Decides what type of statements will be recorded log_min_messages = warning # Recorded any statement that gives 'Warning' messages. log_min_error_statement = error # Recorded any statement that gives 'error' messages. log_min_duration_statement = 10000 # It recorded any statement which is running more than 10 seconds.(10000ms) log_hostname = on # Recorded the host details where the connections are coming. |
1. Firstly, we should configure the parameters in postgresql.conf file. Run the following command:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[postgres@postgres1 data1]$ vi postgresql.conf change the parameters as shown below: logging_collector = on log_destination = 'stderr' log_line_prefix = '%t,%p,%d,%u,%a' log_directory = 'pg_log'|'/u01/logs/pg_log' log_rotation_age=1440 log_rotation_size=0MB log_truncate_on_rotation=on log_filename = 'postgresql-%Y-%m-%d.log' log_duration= on log_file_mode = 0600 , log_timezone = 'Asia/Kolkata' log_connections = on log_disconnections = on log_statement = 'all' log_min_messages = warning log_min_error_statement = error log_min_duration_statement = 10000 log_hostname = on |
Note – Remove hash infront of every parameter.
2. Restart the cluster to apply changes. Run the following command
|
1 |
[postgres@postgres1 ~]$ /usr/local/pgsql/bin/pg_ctl -D /u01/data restart |
3. Check the parameters by running the following command
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
[postgres@postgres1 ~]$ psql psql (11.0) Type "help" for help. postgres # select name, setting from pg_settings where name in ('log_destinati on', 'log_connections', 'log_directory', 'log_disconnections', 'log_duration', 'lo g_file_mode', 'log filename', 'log_hostname', 'log_rotation_age', 'log_line_prefi x', 'log_rotation_age', 'log_rotation_size', 'log_statement', 'log_timezone', 'log truncate_on_rotation', 'logging collector', 'log_min_messages', 'log_min_error_statement" 'log_min_duration_statement', 'log_truncate_on_rotation=on', 'log_checkpoints', 'log_temp_files', 'log_error_verbosity'); name | setting ----------------------------+------------------------- log_checkpoints | on log_connections | on log_destination | stderr,csvlog log_directory | pg_log log_disconnections | on log_duration | on log_error_verbosity | default log_file_mode | 0600 log_filename | postgresql-%Y-%m-%d.log log_hostname | on log_line_prefix | %m,%p,%u,%d,%a,%h log_min_duration_statement | 0 log_min_error_statement | error log_min_messages | warning log_rotation_age | 1440 log_rotation_size | 0 log_statement | all log_temp_files | -1 log_timezone | Asia/Kolkata logging_collector | on (20 rows) Accessing the log file: -bash-4.1$ cd /usr/data1 -bash-4.1$ cd log/ -bash-4.1$ ll -rw-------. 1 postgres postgres 1428 Jul 24 11:12 postgresql-2023-06-24.log |
Here, each day a log file will be created with that particular date.
To open log file Run the following command:
|
1 |
[postgres@postgres1 ~]$ cat postgresql-2021-07-24.log |
Author : Prudhvi Teja |
LinkedIn : http://linkedin.com/in/prudhvi-teja-nagabhyru-715052224
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTExperts is always active on social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Note: Please test scripts in Non Prod before trying in Production.
Loading...
