Parse PostgreSQL logs → regex, Grok, Wazuh & rsyslog

What a PostgreSQL line looks like

The freeform sample below is fed verbatim into the engine to produce every parser on this page.

2026-07-03 14:22:15.123 UTC [1234] jdoe@onber LOG:  duration: 1201.334 ms  statement: SELECT * FROM orders WHERE id = 42
2026-07-03 14:22:19.884 UTC [1290] admin@onber LOG:  duration: 843.201 ms  statement: SELECT * FROM users WHERE id = 7

Detected fields

The engine classified this sample as freeform and consolidated 18 fields across 2 lines. Fields marked literal were identical on every sample line, so they are baked into the pattern as anchors rather than captured.

• timestamp : timestamp · literal
• timestamp2 : timestamp
• _lit1 : literal · literal
• number : number
• literal : literal
• _lit2 : literal · literal
• _lit3 : literal · literal
• number2 : number
• _lit4 : literal · literal
• _lit5 : literal · literal
• _lit6 : literal · literal
• _lit7 : literal · literal
• _lit8 : literal · literal
• literal2 : literal
• _lit9 : literal · literal
• _lit10 : literal · literal
• _lit11 : literal · literal
• number3 : number

Regex (named capture groups)

# sample: 2026-07-03 14:22:15.123 UTC [1234] jdoe@onber LOG:  duration: 1201.334 ms  statement: SELECT * FROM orders WHERE id = 42
# groups: timestamp2=14:22:15.123, number=1234, literal=jdoe@onber, number2=1201.334, literal2=orders, number3=42
^2026-07-03 (?<timestamp2>\d+:\d+:\d+\.\d+) UTC \[(?<number>-?\d+(?:\.\d+)?)\] (?<literal>[A-Za-z]+@[A-Za-z]+) LOG:  duration: (?<number2>-?\d+(?:\.\d+)?) ms  statement: SELECT \* FROM (?<literal2>[A-Za-z]+) WHERE id = (?<number3>-?\d+(?:\.\d+)?)$

Grok pattern (Logstash / Elastic)

2026-07-03 %{TIME:timestamp2} UTC \[%{NUMBER:number}\] %{NOTSPACE:literal} LOG:  duration: %{NUMBER:number2} ms  statement: SELECT \* FROM %{NOTSPACE:literal2} WHERE id = %{NUMBER:number3}

• note constant field "timestamp" embedded as literal anchor "2026-07-03" (varying=false)

Wazuh decoder (OS_Regex XML)

<!--
  Generated by LogForge - Wazuh decoder (OS_Regex dialect, not PCRE)
  sample: 2026-07-03 14:22:15.123 UTC [1234] jdoe@onber LOG:  duration: 1201.334 ms  statement: SELECT * FROM orders WHERE id = 42
  test with: /var/ossec/bin/wazuh-logtest
-->

<decoder name="postgresql-freeform">
  <prematch>^\d+-\d+-\d+ </prematch>
</decoder>

<decoder name="postgresql-freeform">
  <parent>postgresql-freeform</parent>
  <regex>^2026-07-03 (\S+) UTC [(\d+)] (\w+) LOG:  duration: (\d+.\d+) ms  statement: SELECT \p FROM (\w+) WHERE id = (\d+)</regex>
  <order>timestamp2, number, literal, number2, literal2, number3</order>
</decoder>

<!-- ============================================================
     ALERT RULE (starter) — put this in a RULES file, e.g.
     /var/ossec/etc/rules/local_rules.xml. Decoders and rules live
     in SEPARATE files. The rule matches the decoder above through
     <decoded_as>; set <level> and add <field>/<match> conditions so
     it alerts only on the events you care about. Rule ids 100000+
     are the user range — change them if they collide with yours.
     ============================================================ -->
<group name="postgresql,">
  <rule id="100000" level="3">
    <decoded_as>postgresql-freeform</decoded_as>
    <description>postgresql: timestamp2=$(timestamp2) number=$(number)</description>
  </rule>

  <!-- Example — a higher-level alert gated on one field (uncomment and edit):
  <rule id="100001" level="10">
    <if_sid>100000</if_sid>
    <field name="timestamp2">^CHANGE_ME$</field>
    <description>postgresql: a value you care about from $(timestamp2)</description>
  </rule>
  -->
</group>

• note literal '*' in the log text has no exact OS_Regex representation — matched with \p (any punctuation character)
• note constant field "timestamp" embedded as literal anchor "2026-07-03"
• note added a starter alert <rule> (level 3, matched to the decoder via <decoded_as>) — put it in a RULES file (not the decoders file), set the level, and add <field>/<match> conditions; the commented example child rule shows the pattern
• note decoder order and prematch specificity may need site-specific tuning (other decoders in your ruleset can shadow these) — validate with /var/ossec/bin/wazuh-logtest

Wazuh's OS_Regex is not PCRE — a bare . is a literal dot and \. matches any character. Test Wazuh OS_Regex patterns →

rsyslog template / liblognorm rulebase

version=2
# postgresql — liblognorm v2 rulebase (generated by LogForge)
# Usage with rsyslog (mmnormalize runs liblognorm):
#   module(load="mmnormalize")
#   action(type="mmnormalize" rulebase="/etc/rsyslog.d/postgresql.rb" useRawMsg="on")
# Literal "%" is escaped as "%%"; raw tabs are written as \x09.
rule=postgresql:2026-07-03 %timestamp2:word% UTC [%number:number%] %literal:word% LOG:  duration: %number2:float% ms  statement: SELECT * FROM %literal2:word% WHERE id = %number3:number%

• note field "timestamp2": samples do not uniformly match engine type "timestamp"; using a generic parser
• note chosen parser types: timestamp2=word, number=number, literal=word, number2=float, literal2=word, number3=number

Splunk

# props.conf  (search-time extraction)
[<REPLACE_WITH_SOURCETYPE>]
EXTRACT-logforge = 2026-07-03 (?<timestamp2>\d+:\d+:\d+\.\d+) UTC \[(?<number>-?\d+(?:\.\d+)?)\] (?<literal>[A-Za-z]+@[A-Za-z]+) LOG:  duration: (?<number2>-?\d+(?:\.\d+)?) ms  statement: SELECT \* FROM (?<literal2>[A-Za-z]+) WHERE id = (?<number3>-?\d+(?:\.\d+)?)

# Quick search-time test in SPL:
# | rex field=_raw "2026-07-03 (?<timestamp2>\\d+:\\d+:\\d+\\.\\d+) UTC \\[(?<number>-?\\d+(?:\\.\\d+)?)\\] (?<literal>[A-Za-z]+@[A-Za-z]+) LOG:  duration: (?<number2>-?\\d+(?:\\.\\d+)?) ms  statement: SELECT \\* FROM (?<literal2>[A-Za-z]+) WHERE id = (?<number3>-?\\d+(?:\\.\\d+)?)"

• note EXTRACT-<class> names must be unique within a sourcetype stanza — rename EXTRACT-logforge if you already use that class for this sourcetype
• note a timestamp field was detected: this EXTRACT only makes it a searchable field. To set the event _time at index time, add TIME_PREFIX and TIME_FORMAT to this props.conf stanza (TIME_FORMAT uses Splunk strptime, e.g. %Y-%m-%dT%H:%M:%S) — this generator does not guess the strptime format.

ES ingest

PUT _ingest/pipeline/postgresql
{
  "description": "LogForge-generated ingest pipeline for postgresql",
  "processors": [
    {
      "grok": {
        "field": "message",
        "patterns": [
          "2026-07-03 %{TIME:timestamp2} UTC \\[%{NUMBER:number}\\] %{NOTSPACE:literal} LOG:  duration: %{NUMBER:number2} ms  statement: SELECT \\* FROM %{NOTSPACE:literal2} WHERE id = %{NUMBER:number3}"
        ]
      }
    }
  ]
}

• note grok: constant field "timestamp" embedded as literal anchor "2026-07-03" (varying=false)
• note test in Kibana Dev Tools with: POST _ingest/pipeline/postgresql/_simulate (supply a docs[] array whose _source.message holds a sample line)

Graylog

# --- Graylog processing pipeline rule (primary) ---
# Paste under System > Pipelines > Manage rules, then attach the rule to a pipeline stage.
rule "postgresql-parse"
when
  has_field("message")
then
  let gp = grok(pattern: "2026-07-03 %{TIME:timestamp2} UTC \\[%{NUMBER:number}\\] %{NOTSPACE:literal} LOG:  duration: %{NUMBER:number2} ms  statement: SELECT \\* FROM %{NOTSPACE:literal2} WHERE id = %{NUMBER:number3}", value: to_string($message.message), only_named_captures: true);
  set_fields(gp);
end

# --- Graylog import-ready extractor JSON (secondary) ---
# Save as a .json file and import under System > Inputs > (input) > Manage extractors > Actions > Import extractors.
{
  "extractors": [
    {
      "title": "postgresql",
      "extractor_type": "grok",
      "converters": [],
      "order": 0,
      "cursor_strategy": "copy",
      "source_field": "message",
      "target_field": "",
      "extractor_config": {
        "grok_pattern": "2026-07-03 %{TIME:timestamp2} UTC \\[%{NUMBER:number}\\] %{NOTSPACE:literal} LOG:  duration: %{NUMBER:number2} ms  statement: SELECT \\* FROM %{NOTSPACE:literal2} WHERE id = %{NUMBER:number3}",
        "named_captures_only": true
      },
      "condition_type": "none",
      "condition_value": ""
    }
  ],
  "version": "5.0.0"
}

• note grok: constant field "timestamp" embedded as literal anchor "2026-07-03" (varying=false)
• note primary artifact is the processing-pipeline rule; the extractor JSON is an equivalent import-ready alternative for the classic extractor UI

Datadog

logforge_rule 2026-07-03 %{data:timestamp2} UTC \[%{number:number}\] %{notSpace:literal} LOG:  duration: %{number:number2} ms  statement: SELECT \* FROM %{notSpace:literal2} WHERE id = %{number:number3}

• note emitted rule name is "logforge_rule"; rename it to match your "postgresql" convention if desired
• note constant field "timestamp" embedded as literal anchor "2026-07-03" (varying=false)
• note field "timestamp2" (timestamp): could not derive a Joda/Java date format from the sample shape; using %{data} — add a date("…") format by hand if you need a parsed timestamp
• note paste this line into a Grok Parser processor in a Datadog Log Pipeline; matchers are anchored left-to-right and rule whitespace matches log whitespace. Complex or multi-shape logs may need Helper Rules.

Fluent Bit

[PARSER]
    Name        postgresql
    Format      regex
    Regex       ^2026-07-03 (?<timestamp2>\d+:\d+:\d+\.\d+) UTC \[(?<number>-?\d+(?:\.\d+)?)\] (?<literal>[A-Za-z]+@[A-Za-z]+) LOG:  duration: (?<number2>-?\d+(?:\.\d+)?) ms  statement: SELECT \* FROM (?<literal2>[A-Za-z]+) WHERE id = (?<number3>-?\d+(?:\.\d+)?)$
    Time_Key    timestamp2
    Time_Format %H:%M:%S.%L
# Fluentd <parse> block:
#   <parse>
#     @type regexp
#     expression /^2026-07-03 (?<timestamp2>\d+:\d+:\d+\.\d+) UTC \[(?<number>-?\d+(?:\.\d+)?)\] (?<literal>[A-Za-z]+@[A-Za-z]+) LOG:  duration: (?<number2>-?\d+(?:\.\d+)?) ms  statement: SELECT \* FROM (?<literal2>[A-Za-z]+) WHERE id = (?<number3>-?\d+(?:\.\d+)?)$/
#     time_key timestamp2
#     time_format %H:%M:%S.%L
#   </parse>

• note Time_Key set to "timestamp2"; Time_Format "%H:%M:%S.%L" is a best-effort strptime derived from the sample shape — verify it against your data (Fluent Bit uses %L for fractional seconds and %z for numeric offsets)

Vector

[transforms.postgresql_parse]
type = "remap"
inputs = ["REPLACE_WITH_SOURCE"]
source = '''
. |= parse_regex!(.message, r'2026-07-03 (?P<timestamp2>\d+:\d+:\d+\.\d+) UTC \[(?P<number>-?\d+(?:\.\d+)?)\] (?P<literal>[A-Za-z]+@[A-Za-z]+) LOG:  duration: (?P<number2>-?\d+(?:\.\d+)?) ms  statement: SELECT \* FROM (?P<literal2>[A-Za-z]+) WHERE id = (?P<number3>-?\d+(?:\.\d+)?)')
'''

Loki

# promtail pipeline for "postgresql" (generated by LogForge)
# Add these stages under a scrape_config in your promtail config:
#   scrape_configs:
#     - job_name: postgresql
#       pipeline_stages:
# (the stages below are indented to sit under pipeline_stages)
pipeline_stages:
  - regex:
      expression: '^2026-07-03 (?P<timestamp2>\d+:\d+:\d+\.\d+) UTC \[(?P<number>-?\d+(?:\.\d+)?)\] (?P<literal>[A-Za-z]+@[A-Za-z]+) LOG:  duration: (?P<number2>-?\d+(?:\.\d+)?) ms  statement: SELECT \* FROM (?P<literal2>[A-Za-z]+) WHERE id = (?P<number3>-?\d+(?:\.\d+)?)$'

• note no low-cardinality field found to promote to a Loki label — omitted the `- labels:` stage; every captured field stays in the extracted map for later stages
• note left in the extracted map (NOT promoted to labels — high cardinality would explode Loki streams): timestamp2, number, literal, number2, literal2, number3

syslog-ng

parser p_postgresql {
    regexp-parser(
        prefix(".postgresql.")
        patterns("2026-07-03 (?<timestamp2>\\d+:\\d+:\\d+\\.\\d+) UTC \\[(?<number>-?\\d+(?:\\.\\d+)?)\\] (?<literal>[A-Za-z]+@[A-Za-z]+) LOG:  duration: (?<number2>-?\\d+(?:\\.\\d+)?) ms  statement: SELECT \\* FROM (?<literal2>[A-Za-z]+) WHERE id = (?<number3>-?\\d+(?:\\.\\d+)?)")
    );
};

• note captured fields are stored as name-value pairs under the prefix ".postgresql." (e.g. a group (?<srcip>…) becomes ".postgresql.srcip")

FAQ

Why does no single regex parse all PostgreSQL logs?

Because the line header is defined by the log_line_prefix setting, which each DBA configures differently (%m, %p, %u, %d, %h, %a, and more, in any order). Two Postgres servers can produce structurally different headers. A parser must be matched to the specific deployment's log_line_prefix; there is no universal Postgres pattern.

What do the LOG, ERROR, FATAL, and PANIC levels mean?

They are severity levels after the prefix. LOG is routine operational info, WARNING is a concern, ERROR aborts the current statement, FATAL aborts the session (e.g. failed authentication), and PANIC aborts all sessions and restarts the server. ERROR/FATAL/PANIC are what you alert on; a burst of FATAL "password authentication failed" is a brute-force signal.

Where do the "duration:" slow-query lines come from?

From log_min_duration_statement. When a statement runs longer than the configured threshold, Postgres logs a LOG line like "duration: 42.318 ms statement: SELECT …". Everything before "duration:" is the configured prefix; the duration and statement text are what you extract for slow-query analysis.

How do I handle multi-line PostgreSQL error events?

An ERROR or FATAL line is often followed by continuation lines tagged DETAIL:, HINT:, STATEMENT:, and CONTEXT: that belong to the same event — the STATEMENT: line echoes the offending SQL. Correlate these back to their parent by proximity and shared PID/session, rather than treating each line as an independent event.