PostgreSQL logging: Statements splits into multiple lines

Question

In PostgreSQL v10 and above, I activated the logging of the statements using the extension pg_stat_statements. My configuration:

logging_collector = on
log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u,app=%a,client=%h '
log_destination = 'stderr,syslog'
log_statement = all

If I execute a simple query:

postgres=# select current_timestamp;

In the log it will show the prefix and and the statement, something like this:

Line 1: (prefix) statement: select current_timestamp;

However, if I have a query that is split in multiple lines, for example:

select
current_timestamp;

In the log it will show 2 lines:

Line 1: (prefix) select
Line 2: current_timestamp;

This "current_timestamp" is isolated, doesn't have the prefix and there is no way I can match the line with the "select" part.

How can I configure so that multiple line statements are shown in a single line in the log?

Like this:

Statement:

select
current_timestamp;

Log:

Line 1: (prefix) statement: select current_timestamp;

I've test changing to csvlog and I got the same result.

Why would I need this? I configured the logs to be sent to a central database for auditing purpose. These logs are exposed via Kiabana dashboards. Several lines in the log for one statement, specially if the lines (except the first one) doesn't have the prefix, it is hard to find the full statement.

Thank you.

Answer 1

The csv format is easy to parse as long as you use a proper csv parser. The embedded newline, being inside quotes, will be correctly understood as not being a record terminator.

If you send it to the central database using something like described here, it should work fine.

Answer 2

PostgreSQL won't remove the newline from the statement. With the stderr log format, you can tell such continuation lines because they start with a tab character.

But my recommendation is to use csvlog. That also won't remove the newlines, but it will properly escape them with double quotes, so that every CSV parser can read them correctly.

Answer 3

From your mention of Kibana one might assume you're using an Elastic software stack to manage logs. If that's the case, you can use the Logstash multiline codec, which is designed to handle precisely this use case.

Answer 4

filebeat multiline log parsing can send multiline logs to the central database.

Better solution would be to use nxlog to convert multiline to single line using tag in nxlog.conf file. nxlog takes care of both conversion and forwarding logs, and works with log file paths or directory paths for Postgres.