# Parse PostgreSQL CSV log

**URL:** <https://discuss.elastic.co/t/parse-postgresql-csv-log/259688>\
**Category:** Elasticsearch\
**Created:** [December 27, 2020, 2:45pm UTC](https://discuss.elastic.co/t/parse-postgresql-csv-log/259688 "2020-12-27T14:45:10Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![azlev](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/azlev/32/81434_2.png) [@azlev](https://discuss.elastic.co/u/azlev)\
**Post date:** [December 27, 2020, 2:45pm UTC](https://discuss.elastic.co/t/parse-postgresql-csv-log/259688/1 "2020-12-27T14:45:10Z")

</div>

Hello.

At work we are consuming the PostgreSQL CSV log instead of plain log. This poses some advantages, like a better formatted input file, stable format, and easy to turn it on.

To achieve it, we are using the filebeat processors, first decoding a multiline CSV then break each field.

Now I'm looking to incorporate this as a filebeat code. Is someone working on anything similar? Should I look at beats/filebeat/module and replicate this structure?

Thanks in advance.

---

<div class="post-metadata">

**Author:** ![ylasri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ylasri/32/86120_2.png) [@ylasri](https://discuss.elastic.co/u/ylasri)\
**Post date:** [December 27, 2020, 4:57pm UTC](https://discuss.elastic.co/t/parse-postgresql-csv-log/259688/2 "2020-12-27T16:57:32Z")

</div>

The default module of filebeat for postgresql export only some [fields](https://www.elastic.co/guide/en/beats/filebeat/7.10/exported-fields-postgresql.html) from the stderr default log format.  
You will need to map all csv fields of postgresql log format to the ESC schema :

| field | type | ecs mapping | example |
| --- | --- | --- | --- |
| log\_time | timestamp(3) with time zone | postgresql.log.timestamp | 2020-12-27 08:43:50.674 PST |
| user\_name | text | user.name | postgres |
| database\_name | text | postgresql.log.database | postgres |
| process\_id | integer | process.pid | 9004 |
| connection\_from | text | ? | ::1:53881 |
| session\_id | text | ? | 5fe8b9c6.232c |
| session\_line\_num | bigint | ? | 1 |
| command\_tag | text | ? | authentication |
| session\_start\_time | timestamp(3) with time zone | ? | 2020-12-27 08:43:50 PST |
| virtual\_transaction\_id | text | ? | 9/57 |
| transaction\_id | bigint | ? | 0 |
| error\_severity | text | log.level | FATAL |
| sql\_state\_code | text | postgresql.log.error.code | 28000 |
| message | text | log.message | no pg\_hba.conf entry for host "::1", user "postgres", database "postgres", SSL off |
| detail | text | ? | |
| hint | text | ? | |
| internal\_query | text | ? | |
| internal\_query\_pos | integer | ? | |
| context | text | ? | |
| query | text | postgresql.log.query | |
| query\_pos | integer | ? | |
| location | text | ? | |
| application\_name | text | ? | |

---

<div class="post-metadata">

**Author:** ![azlev](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/azlev/32/81434_2.png) [@azlev](https://discuss.elastic.co/u/azlev)\
**Post date:** [December 27, 2020, 5:51pm UTC](https://discuss.elastic.co/t/parse-postgresql-csv-log/259688/3 "2020-12-27T17:51:11Z")

</div>

That's a starting point. The other one is the PostgreSQL documentation here: [https://www.postgresql.org/docs/current/runtime-config-logging.html](https://www.postgresql.org/docs/current/runtime-config-logging.html)

Note in PostgreSQL 13 there is a new field, called `backend_type`.

Actually there are more fields than that, like `duration` and special formatted fields like `automatic vacuums` and `checkpoints`.

I'll glue this in a PR and gather distinct log lines to create tests cases.

---

<div class="post-metadata">

**Author:** ![ylasri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ylasri/32/86120_2.png) [@ylasri](https://discuss.elastic.co/u/ylasri)\
**Post date:** [December 27, 2020, 5:52pm UTC](https://discuss.elastic.co/t/parse-postgresql-csv-log/259688/4 "2020-12-27T17:52:33Z")

</div>

You can write your own module for this and send it as a PR  
Something like this may help  
Note that when you write your own module in beat, you use mostly [ingest processor](https://www.elastic.co/guide/en/elasticsearch/reference/master/ingest-processors.html) of elasticsearch instead of beat processors  
When the beat is runned for the first time, the ingest pipeline (`$BEAT_HOME/modules/${MODULE_NAME}/${STREAM_TYPE}/ingest/pipeline.yml`) is loaded into elasticsearch

```auto
description: Pipeline for parsing PostgreSQL CSV logs.
processors:
- set:
    field: event.ingested
    value: '{{_ingest.timestamp}}'
- csv:
    field: message
    separator: ","
    target_fields: ["postgresql.log.timestamp", "user.name", "postgresql.log.database", "process.pid", "temp.connection_from", "temp.session_id", "temp.session_line_num", "temp.command_tag", "temp.session_start_time", "temp.virtual_transaction_id", "temp.transaction_id", "log.level", "postgresql.log.error.code", "log.message", "temp.detail", "temp.hint", "temp.internal_query", "temp.internal_query_pos", "temp.context", "postgresql.log.query", "temp.query_pos", "temp.location", "temp.application_name"]
    ignore_missing: true
    trim: true
- date:
    field: postgresql.log.timestamp
    target_field: '@timestamp'
    formats:
    - yyyy-MM-dd HH:mm:ss.SSS zz
    - yyyy-MM-dd HH:mm:ss zz
- convert:
    field: postgresql.log.error.code
    type: integer
- script:
    lang: painless
    source: ctx.event.duration = Math.round(ctx.temp.duration * params.scale)
    params:
      scale: 1000000
    if: ctx.temp?.duration != null
- remove:
    field: temp.duration
    ignore_missing: true
- set:
    field: event.kind
    value: event
- append:
    field: event.category
    value:
      - database
- append:
    field: event.type
    value:
      - info
- append:
    field: event.type
    value:
      - error
    if: "ctx?.postgresql?.log?.error?.code != null && ctx.postgresql.log.error.code >= 02000"
- append:
    field: related.user
    value: "{{user.name}}"
    if: "ctx?.user?.name != null"
on_failure:
- set:
    field: error.message
    value: '{{ _ingest.on_failure_message }}'

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [January 24, 2021, 5:52pm UTC](https://discuss.elastic.co/t/parse-postgresql-csv-log/259688/5 "2021-01-24T17:52:35Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.