Updated May 29, 2023
Introduction to PostgreSQL log
To find out the cause and debugging the issue that is used while executing a certain command, we need to check the logs and to do so, it is necessary to maintain the logs. PostgreSQL comes with a brilliant log management system, providing multiple methods to store and handle the logs. It s essential to know where and when the logs should be maintained. For that, we have to set many parameters provided in Postgres to define our logging system’s behavior and mention necessary information that will help in logging. This article will teach us about such parameters and logging in to PostgreSQL.
Severity Levels of Messages
While storing, you must be aware of the messages that may produce while executing commands and, depending on the severity, decide what kind of logs you want to maintain and store. The below table shows the severity levels of messages in decreasing order. This information suggests that the most severe level is “panic,” while developers define the levels “debug1” to “debug5” to represent the lowest severity levels.
| Level of Severity | Use of the message | Logs in event | Logs in system |
| PANIC | It is the most critical one and reports an error that leads to the closing and abortion of all the database sessions. | ERROR | CRIT |
| FATAL | The error results in the closing and abortion of the current database session. | ERROR | ERR |
| LOG | It helps in logging the information that will be helpful in administration, such as checkpoint activity. | INFORMATION | INFO |
| ERROR | Only the current command aborts when an error occurs during its execution. | ERROR | WARNING |
| WARNING | It provides information about potential causes of errors, allowing us to take precautions earlier. For instance, if a commit is executed outside the transaction block, it will raise an alert. | WARNING | NOTICE |
| NOTICE | As users, we benefit from the information it provides, such as alerting us when long identifiers are truncated. | INFORMATION | NOTICE |
| INFO | When the user explicitly requests specific types of information, it is displayed in INFO. For example, when VERBOSE is used while VACUUM or ANALYZE, then information is displayed. | INFORMATION | INFO |
| DEBUG1 to DEBUG5 | It helps in providing additional information that the developer wants to give. | INFORMATION | DEBUG |
Parameters Related to Logging in PostgreSQL
You have the option to set the parameters either in the postgresql.conf file or on the server command line. Below, we have listed some of the parameters used for the log management system:
1. Parameter log_destination(string)
PostgreSQL, a log management system, allows us to store the logs in many ways, such as stderr, csvlog, event logging(only in windows), and Syslog. For instance, setting this parameter to csvlog stores the logs in a comma-separated format. This parameter allows us to mention where the logs should be stored, and we can mention multiple parameters in a comma-separated format. By default, users commonly set the parameter to stderr, which is a commonly used value.
2. Parameter logging_collector(boolean)
To enable logging in PostgreSQL, you need to set this parameter to true and ensure that the logging collector is running. The logging collector operates in the background, actively collecting all the logs sent to stderr (the standard error stream), and redirecting them to the file destination specified for log files. We prefer using this method over Syslog because certain messages cannot be printed on the console. For example – while doing scriptings, the archieve_command() used does not display logs on the console when an error occurs while executing it.
3. Parameter log_directory(string)
When the log collector is enabled, this parameter specifies the location where the log files should be created. The default value of this parameter is pg_log, which can hold an absolute or relative path to the location of the cluster database.
4. Parameter log_filename(string)
This parameter determines the name of the files that will be created for logging purposes. It should be specified using escape sequences or %-escapes to account for time variations.
5. client_min_messages(enum)
You can use this parameter to specify the level up to which messages should be sent to the client. It’s worth noting that the sequence of considered levels for this parameter differs from the one mentioned above. It is PANIC, FATAL, INFO, ERROR, WARNING, NOTICE, LOG, DEBUG1, DEBUG2, DEBUG3, DEBUG4, AND DEBUG5. By default, the parameter is set to NOTICE, which implies that all messages before INFO will be sent to the client.
6. Parameter log_min_messages(enum)
When you set this parameter, you have the ability to determine the maximum level of messages that will be written to the server logs. It’s important to note that the sequence of considered levels for this parameter differs from the one mentioned above. It is PANIC, FATAL, LOG, ERROR, WARNING, NOTICE, INFO, DEBUG1, DEBUG2, DEBUG3, DEBUG4, AND DEBUG5. The default setting for this parameter is NOTICE. As a result, the server logs will automatically include all messages that come before INFO.
7. Parameter log_min_error_statement(enum)
This parameter determines the logging level, specifying the range of messages that should be logged. The sequence of the considered levels is not as mentioned above. It is PANIC, FATAL, LOG, ERROR, WARNING, NOTICE, INFO, DEBUG1, DEBUG2, DEBUG3, DEBUG4, AND DEBUG5. The default value of this parameter is set to NOTICE. Hence, all the messages previous to INFO will be logged by default.
8. application_name(string)
The application’s name must be less than 64 characters and can be displayed in the view called pg_stat_activity. Furthermore, the parameter is included in the maintained CSV logs and can be added to additional logs. Note that this application name should only contain printable ASCII characters; Alternatively, the system will replace those characters with a question mark (?).
9. Parameter log_checkpoints(boolean)
When you set this parameter to true, the server logs will include logging of checkpoints and restart points.
Conclusion – PostgreSQL log
We can use the log management system provided in PostgreSQL for debugging the issues, finding the causes, improving the database operations efficiency, and maintaining the logs for future reference. Users can configure various parameters to define the logging behavior and pattern. Users have the ability to specify the aspects that should be logged, enable logging at specific times, and determine the storage location for the generated logs.
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL log” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
