[PostgeSQL]: Pipeline doesn't take into account log_line_prefix config variable

[leweafan]

Integration Name

PostgreSQL [postgresql]

Dataset Name

postgresql.log

Integration Version

1.23.0

Agent Version

8.15.0

Agent Output Type

elasticsearch

Elasticsearch Version

8.15.0

OS Version and Architecture

Ubuntu 24.04.1 LTS

Software/API Version

No response

Error Message

Provided Grok expressions do not match field value

Event Original

No response

What did you do?

Changed log_line_prefix variable in postgesql.conf

What did you see?

Grok parse failure

What did you expect to see?

Option to specify log_line_prefix variable pattern

Anything else?

Current ingest pipeline uses default log_line_prefix format and does not take into account that every administrator adjust this variable. IMHO log_line_prefix and message parsing should be separated and log_line_prefix parsing should be presented like @custom ingest pipeline. Now if log_line_prefix changed then log parsing fails and there is no a good way to fix it.

cat /etc/postgresql/13/main/postgresql.conf
#log_line_prefix = '%m [%p] %q%u@%d '
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,client=%h '

Now we have logs-postgresql.log-1.23.0-pipeline-log and raw_message grok:

^(\[%{NUMBER:process.pid:long}(-%{BASE16FLOAT:postgresql.log.session_line_number:long})?\] ((\[%{USERNAME:user.name}\]@\[%{POSTGRESQL_DB_NAME:postgresql.log.database}\]|%{USERNAME:user.name}@%{POSTGRESQL_DB_NAME:postgresql.log.database}) )?)
?%{WORD:log.level}:  (?:%{POSTGRESQL_ERROR:postgresql.log.sql_state_code}|%{SPACE})(duration: %{NUMBER:temp.duration:float} ms  %{POSTGRESQL_QUERY_STEP}: %{GREEDYDATA:postgresql.log.query}|: %{GREEDYDATA:message}|%{GREEDYDATA:message})

Where log_line_prefix defined like:

^(\[%{NUMBER:process.pid:long}(-%{BASE16FLOAT:postgresql.log.session_line_number:long})?\] ((\[%{USERNAME:user.name}\]@\[%{POSTGRESQL_DB_NAME:postgresql.log.database}\]|%{USERNAME:user.name}@%{POSTGRESQL_DB_NAME:postgresql.log.database}) )?)

Please add custom ingest pipeline which every admin can modify or add several types used across different databases.

For example raw_message grok can look like:

^%{LOG_LINE_PREFIX:log_line_prefix}{%GREEDYDATA:raw_message_new}

Where

LOG_LINE_PREFIX

(\[%{NUMBER:process.pid:long}(-%{BASE16FLOAT:postgresql.log.session_line_number:long})?\] ((\[%{USERNAME:user.name}\]@\[%{POSTGRESQL_DB_NAME:postgresql.log.database}\]|%{USERNAME:user.name}@%{POSTGRESQL_DB_NAME:postgresql.log.database}) )?)

Suppose pipeline waterfall should look like

1. logs-postgresql.log-1.23.0
2. logs-postgresql.log_line_prefix-1.23.0
3. logs-postgresql.log_line_prefix-1.23.0@custom
4. logs-postgresql.log-1.23.0-pipeline-log

[leweafan]

Please find log_line_prefix variable description here