PostgreSQL – See X-Request-ID in Error Messages

loggingpostgresql

In my environment most PostgreSQL queries are generated from http requests.

In my case every http request has a unique header: X-Request-ID

If there is a error or warning in the PostgreSQL logs, I would like to see the related X-Request-ID to see where the sql query came from.

How to add an extra message like X-Request-ID to log messages of PostgreSQL?

Related: https://serverfault.com/a/797611/90324

Best Answer

Postgres knows nothing about how your application generates the SQL requests that are sent to the database engine. So it also has no idea that there is a HTTP request behind it, let alone the header of such a request.

The information that can be put into a log line is fixed and documented in the manual

The only "log variable" that you can dynamically change, is the application name. However that requires that your application sets that each time it processes a request.

Before running a SQL query, your application needs to run something like this: 

set application_name = 'request-42';

where 'request-42' is the request ID you extracted from the HTTP request.

Once you have that, you can add the %a to log_line_prefix and it will be shown in the log file.

Related Question