PostgreSQL logging: Statements splits into multiple lines



  • 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.



  • 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 https://www.postgresql.org/docs/current/file-fdw.html , it should work fine.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2