Introduction to PostgreSQL log
To find out the cause and debugging the issue that is use while executing a certain command, we need to check the logs, and for doing so, it is necessary to maintain the logs. PostgreSQL comes with a brilliant log management system whereby we are provided with multiple methods to store the logs and handle them. It s essential to know where what and when the logs should be maintained. For that, we have to set many parameters provided in Postgres to define our logging system’s behaviour and mention necessary information that will help in logging. In this article, we will learn 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. That means panic is the most severe one, while debug1 to debug5 is developer-defined and has the lowest severity.
| 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 closing and abortion of all the database sessions. | ERROR | CRIT |
| FATAL | It is the error that results in 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 is aborted when the error occurs while executing it. | ERROR | WARNING |
| WARNING | It provides us with info about the things that might result in the error to take precautions earlier, such as when a commit is being done outside the transaction block. | WARNING | NOTICE |
| NOTICE | It makes us aware of the information that will prove useful as the user, such as when long identifiers are truncated. | INFORMATION | NOTICE |
| INFO | When the user explicitly demands certain kinds of information, it is used to display 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
The parameters can be set from the postgresql.conf file or on the server command line. Some of the parameters that are used for the log management system are displayed below:
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 example, if we set this parameter to csvlog, then the logs are stored in a comma-separated format. This parameter allows us to mention where the logs should be stored, and we can mention multiple parameters in comma-separated format. The default value of this parameter id stderr that is most often used.
2. Parameter logging_collector(boolean)
Logging in PostgreSQL is enabled if and only if this parameter is set to the true and logging collector is running. Logging collector works in the background to collect all the logs that are being sent to stderr that is standard error stream and redirect them to the file destination of log files. Some messages cannot be printed on the console; that’s why this method is preferred over Syslog. For example – while doing scriptings, the archieve_command() that is used does not display logs on the console when an error occurs while executing it.
3. Parameter log_directory(string)
This parameter determines where the log files should be created if the log collector is enabled. By default, this parameter has the value as pg_log and can contain an absolute or relative path to the cluster database’s location.
4. Parameter log_filename(string)
This parameter determines the name of the files that will be created for logging purposes. This field is time-varying and should be mentioned along with escape sequences %-escapes.
5. client_min_messages(enum)
This parameter defines the level up to the messages that should be sent to the client, and the sequence of the level considered here is not as mentioned above. It is PANIC, FATAL, INFO, ERROR, WARNING, NOTICE, LOG, 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 sent to the client by default.
6. Parameter log_min_messages(enum)
This parameter defines the level up to the messages that should be written to server logs, and the sequence of the level considered here 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 written to server logs by default.
7. Parameter log_min_error_statement(enum)
This parameter defines the level up to the messages that should be logged, and the sequence of the level considered here 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 name of the application is less than 64 characters and can be displayed in the view named pg_stat_activity and is also included in the CSV logs that are maintained and can be included in other logs. Note that this application name should only contain ASCII characters that are printable; otherwise, those characters will be replaced with a question mark(?).
9. Parameter log_checkpoints(boolean)
If this parameter is set to true, then checkpoints and restart points will also be logged in server logs.
Conclusion – PostgreSQL log
We can use the log management system provided in PostgreSQL for debugging the issues, finding out the causes and improving the database operations efficiency, and maintaining the logs for future reference. We can set different parameters to define the logging behaviour and pattern and mention what things should be logged when the logging should be enabled and where the logs should be stored that are generated.
Recommended Articles
This is a guide to the PostgreSQL log. Here we discuss the Introduction to PostgreSQL log and its Severity Levels of Messages and different Parameters Related to Logging. You can also go through our suggested articles to learn more –
