PostgreSQL 14 improves logging with libpq
The libpq function has been enhanced in PG14 to track the availability of application server/client communication. It adds new options to control the output format.
background
libpq is one of the client libraries, users can run the PQtrace function to record the client-server communication. This communication is a kind of protocol information used by PG, including: the identifier of the message type, the length of the message, and the message content of the exchange information.
Application developers can use this log to determine whether the communication is performing as expected. Use the following statement as an example:
CREATE TABLESPACE regress＿tblspacewith
LOCATION ＇／home／postgres／src／test／regress／testtablespace＇
WITH （random＿page＿cost ＝ 3．0）；
The application that calls PQtrace in PG13 will output the following types of logs to the specified file:
Logs generated by libpq in PG13
It should be noted that the PQtrace log output of the current PG version does not contain a timestamp, so it cannot be used as a reference to analyze slow queries. In addition, because the message identifier, server/client message length, and output content are on a separate line, the reliability is relatively low, and the analysis of protocol messages is more difficult. Z and C marked 1 are the identifiers of the protocol message.To understand the meaning of each identifier, refer to the Message Formats section of the manual
Overview of feature improvements
In PG14, the PQtrace function has been improved to make the output log more readable and include a timestamp. A new function PQsetTraceFlags has been added to control the output of the timestamp.
Improve log output
The improved trace function in PG14 produces output similar to the following:
Logs generated by libpq in PG 14
1) Contains a timestamp
2) The code of the message direction is more intuitive: F means front end, B means back end
3) Output the official message name instead of the identifier of the protocol message
4) Meaningful protocol messages are output in one line
Log retrieval method
As before, start logging by calling libpq’s PQtrace function. If you do not need to output the timestamp, you can control it through the PQsetTraceFlags function.
Influence
PQtrace outputs timestamps, which can help users identify slow queries. If the application suddenly slows down, you can check the timestamp difference in the log to determine whether the server or client took longer. Significant protocol messages are output in one line, so that people who are not familiar with lipq logs can easily understand the communication sent between the server and the client. By using the PQsetTraceFlags function to control whether to output the timestamp, you can use this log for regression testing. By not outputting a timestamp, you can fill the log with the results of the expected test run and easily compare it with the log obtained during the test run.
future
PG14’s libpq log will contain time stamps and more readable text. In subsequent versions, we will further improve usability in the following aspects:
1) The current function writes the log to the file assigned to the PQtrace function. In some cases, the log file will expand very greatly, thus affecting the file operation. To solve this problem, we hope to provide a function to specify the maximum size of the file.
2) I hope to add environment variables and connection parameters to set the directory location of the log output and the log file name to adapt to the environment without modifying the application.
Links: