# PostgreSQL grok problem

**URL:** <https://discuss.elastic.co/t/postgresql-grok-problem/273284>\
**Category:** Beats\
**Tags:** filebeat\
**Created:** [May 18, 2021, 12:40pm UTC](https://discuss.elastic.co/t/postgresql-grok-problem/273284 "2021-05-18T12:40:25Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![Cihan\_Tunali](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cihan_tunali/32/73516_2.png) [@Cihan\_Tunali](https://discuss.elastic.co/u/Cihan_Tunali)\
**Post date:** [May 18, 2021, 12:40pm UTC](https://discuss.elastic.co/t/postgresql-grok-problem/273284/1 "2021-05-18T12:40:25Z")

</div>

Hello,  
I am using PostgreSQL 12 with Filebeat 7.12. I followed ([PostgreSQL module | Filebeat Reference [8.11] | Elastic](https://www.elastic.co/guide/en/beats/filebeat/current/filebeat-module-postgresql.html)) to configure filebeat. Right now Filebeat is sending the logs but I can see, there is a GROK failure and not all PostgreSQL dashboards are working.

Here is my filebeat confing:

> filebeat.inputs:
> 
> - type: log  
> enabled: false  
> paths:
> - /var/log/\*.log
> 
> - type: filestream  
> enabled: false  
> paths:
> - /var/log/_.log  
> filebeat.config.modules:  
> path: ${path.config}/modules.d/_.yml  
> reload.enabled: false  
> setup.template.settings:  
> index.number\_of\_shards: 1  
> setup.ilm.enabled: true  
> setup.ilm.rollover\_alias: "filebeat-servername"  
> setup.ilm.pattern: "{now/d}"  
> setup.dashboards.enabled: true  
> setup.kibana:  
> host: "kibana:5601"  
> output.elasticsearch:  
> hosts: ["elastic:9200"]  
> processors:
> - add\_host\_metadata:  
> when.not.contains.tags: forwarded
> - add\_cloud\_metadata: ~
> - add\_docker\_metadata: ~
> - add\_kubernetes\_metadata: ~

Here is my filebeat postgresql module config:

> - module: postgresql  
> log:  
> enabled: true  
> var.paths: ["/data/pgsql/patroni/log/\*.log"]

Here is error

> {  
> "\_index": "filebeat-servername-2021.05.17",  
> "\_type": "\_doc",  
> "\_id": "aLp7f3kBXre3GWy8Jlop",  
> "\_version": 1,  
> "\_score": null,  
> "fields": {  
> "agent.version.keyword": [  
> "7.12.1"  
> ],  
> "host.architecture.keyword": [  
> "x86\_64"  
> ],  
> "host.name.keyword": [  
> "servername"  
> ],  
> "event.dataset.keyword": [  
> "postgresql.log"  
> ],  
> "host.hostname": [  
> "servername"  
> ],  
> "host.mac": [  
> "00:50:56:9a:39:bc"  
> ],  
> "agent.hostname.keyword": [  
> "servername"  
> ],  
> "service.type": [  
> "postgresql"  
> ],  
> "ecs.version.keyword": [  
> "1.8.0"  
> ],  
> "host.ip.keyword": [  
> "11.22.33.444",  
> "fe80::7f85:b9e:ec18:70d5"  
> ],  
> "host.os.version": [  
> "7 (Core)"  
> ],  
> "host.os.name": [  
> "CentOS Linux"  
> ],  
> "agent.name": [  
> "servername"  
> ],  
> "host.id.keyword": [  
> "8e08bb3b0d484afea7697b5308c72831"  
> ],  
> "host.name": [  
> "servername"  
> ],  
> "host.os.version.keyword": [  
> "7 (Core)"  
> ],  
> "host.os.type": [  
> "linux"  
> ],  
> "agent.id.keyword": [  
> "afcf8273-3ef8-4b70-9885-c0524458fca4"  
> ],  
> "fileset.name": [  
> "log"  
> ],  
> "input.type": [  
> "log"  
> ],  
> "log.offset": [  
> 171762268  
> ],  
> "agent.hostname": [  
> "servername"  
> ],  
> "host.architecture": [  
> "x86\_64"  
> ],  
> "fileset.name.keyword": [  
> "log"  
> ],  
> "agent.id": [  
> "afcf8273-3ef8-4b70-9885-c0524458fca4"  
> ],  
> "ecs.version": [  
> "1.8.0"  
> ],  
> "host.containerized": [  
> false  
> ],  
> "event.module.keyword": [  
> "postgresql"  
> ],  
> "host.hostname.keyword": [  
> "servername"  
> ],  
> "agent.version": [  
> "7.12.1"  
> ],  
> "host.os.family": [  
> "redhat"  
> ],  
> "service.type.keyword": [  
> "postgresql"  
> ],  
> "input.type.keyword": [  
> "log"  
> ],  
> "host.ip": [  
> "11.22.33.444",  
> "fe80::7f85:b9e:ec18:70d5"  
> ],  
> "agent.type": [  
> "filebeat"  
> ],  
> "event.module": [  
> "postgresql"  
> ],  
> "host.os.kernel.keyword": [  
> "5.4.108-1.el7.elrepo.x86\_64"  
> ],  
> "host.os.kernel": [  
> "5.4.108-1.el7.elrepo.x86\_64"  
> ],  
> "host.os.name.keyword": [  
> "CentOS Linux"  
> ],  
> "host.id": [  
> "8e08bb3b0d484afea7697b5308c72831"  
> ],  
> "log.file.path.keyword": [  
> "/data/pgsql/patroni/log/postgresql-Tue.log"  
> ],  
> "agent.type.keyword": [  
> "filebeat"  
> ],  
> "agent.ephemeral\_id.keyword": [  
> "04d7fc98-524a-4916-8959-0aa5b14662b2"  
> ],  
> "host.os.codename.keyword": [  
> "Core"  
> ],  
> "host.mac.keyword": [  
> "00:50:56:9a:39:bc"  
> ],  
> "agent.name.keyword": [  
> "servername"  
> ],  
> "host.os.codename": [  
> "Core"  
> ],  
> "message": [  
> "2021-05-18 15:37:32.073 +03 [78544] postgres@postgres LOG: duration: 0.783 ms statement: SELECT pg\_catalog.to\_char(pg\_catalog.pg\_postmaster\_start\_time(), 'YYYY-MM-DD HH24:MI:SS.MS TZ'), CASE WHEN pg\_catalog.pg\_is\_in\_recovery() THEN 0 ELSE ('x' || pg\_catalog.substr(pg\_catalog.pg\_walfile\_name(pg\_catalog.pg\_current\_wal\_lsn()), 1, 8))::bit(32)::int END, CASE WHEN pg\_catalog.pg\_is\_in\_recovery() THEN 0 ELSE pg\_catalog.pg\_wal\_lsn\_diff(pg\_catalog.pg\_current\_wal\_lsn(), '0/0')::bigint END, pg\_catalog.pg\_wal\_lsn\_diff(pg\_catalog.pg\_last\_wal\_replay\_lsn(), '0/0')::bigint, pg\_catalog.pg\_wal\_lsn\_diff(COALESCE(pg\_catalog.pg\_last\_wal\_receive\_lsn(), '0/0'), '0/0')::bigint, pg\_catalog.pg\_is\_in\_recovery() AND pg\_catalog.pg\_is\_wal\_replay\_paused(), pg\_catalog.to\_char(pg\_catalog.pg\_last\_xact\_replay\_timestamp(), 'YYYY-MM-DD HH24:MI:SS.MS TZ'), pg\_catalog.array\_to\_json(pg\_catalog.array\_agg(pg\_catalog.row\_to\_json(ri))) FROM (SELECT (SELECT rolname FROM pg\_authid WHERE oid = usesysid) AS usename, application\_name, client\_addr, w.state, sync\_state, sync\_priority FROM pg\_catalog.pg\_stat\_get\_wal\_senders() w, pg\_catalog.pg\_stat\_get\_activity(pid)) AS ri"  
> ],  
> "host.os.family.keyword": [  
> "redhat"  
> ],  
> "event.ingested": [  
> "2021-05-18T12:37:27.720Z"  
> ],  
> "@timestamp": [  
> "2021-05-18T12:37:32.772Z"  
> ],  
> "host.os.type.keyword": [  
> "linux"  
> ],  
> "host.os.platform": [  
> "centos"  
> ],  
> "host.os.platform.keyword": [  
> "centos"  
> ],  
> "error.message": [  
> "Provided Grok expressions do not match field value: [2021-05-18 15:37:32.073 +03 [78544] postgres@postgres LOG: duration: 0.783 ms statement: SELECT pg\_catalog.to\_char(pg\_catalog.pg\_postmaster\_start\_time(), 'YYYY-MM-DD HH24:MI:SS.MS TZ'), CASE WHEN pg\_catalog.pg\_is\_in\_recovery() THEN 0 ELSE ('x' || pg\_catalog.substr(pg\_catalog.pg\_walfile\_name(pg\_catalog.pg\_current\_wal\_lsn()), 1, 8))::bit(32)::int END, CASE WHEN pg\_catalog.pg\_is\_in\_recovery() THEN 0 ELSE pg\_catalog.pg\_wal\_lsn\_diff(pg\_catalog.pg\_current\_wal\_lsn(), '0/0')::bigint END, pg\_catalog.pg\_wal\_lsn\_diff(pg\_catalog.pg\_last\_wal\_replay\_lsn(), '0/0')::bigint, pg\_catalog.pg\_wal\_lsn\_diff(COALESCE(pg\_catalog.pg\_last\_wal\_receive\_lsn(), '0/0'), '0/0')::bigint, pg\_catalog.pg\_is\_in\_recovery() AND pg\_catalog.pg\_is\_wal\_replay\_paused(), pg\_catalog.to\_char(pg\_catalog.pg\_last\_xact\_replay\_timestamp(), 'YYYY-MM-DD HH24:MI:SS.MS TZ'), pg\_catalog.array\_to\_json(pg\_catalog.array\_agg(pg\_catalog.row\_to\_json(ri))) FROM (SELECT (SELECT rolname FROM pg\_authid WHERE oid = usesysid) AS usename, application\_name, client\_addr, w.state, sync\_state, sync\_priority FROM pg\_catalog.pg\_stat\_get\_wal\_senders() w, pg\_catalog.pg\_stat\_get\_activity(pid)) AS ri]"  
> ],  
> "log.file.path": [  
> "/data/pgsql/patroni/log/postgresql-Tue.log"  
> ],  
> "agent.ephemeral\_id": [  
> "04d7fc98-524a-4916-8959-0aa5b14662b2"  
> ],  
> "event.dataset": [  
> "postgresql.log"  
> ]  
> },  
> "highlight": {  
> "agent.name": [  
> "@kibana-highlighted-field@servername@/kibana-highlighted-field@"  
> ]  
> },  
> "sort": [  
> 1621341452772  
> ]  
> }

What could be the problem? Thank you.

---

<div class="post-metadata">

**Author:** ![Kaiyan\_Sheng](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kaiyan_sheng/32/38247_2.png) [@Kaiyan\_Sheng](https://discuss.elastic.co/u/Kaiyan_Sheng)\
**Post date:** [May 19, 2021, 9:55pm UTC](https://discuss.elastic.co/t/postgresql-grok-problem/273284/2 "2021-05-19T21:55:05Z")

</div>

Hello! Which version of postgresql are you using? Based on the documentation, the `postgresql` module using `.log` was tested with logs from versions 9.5 on Ubuntu, 9.6 on Debian, and finally 10.11, 11.4 and 12.2 on Arch Linux 9.3. If you are using a different version, please feel free to create a [github issue](https://github.com/elastic/beats/issues/new?assignees=&labels=&template=feature-request.md) with the postgresql version, a sample log file/log line 🙂 TIA!!!

---

<div class="post-metadata">

**Author:** ![Cihan\_Tunali](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cihan_tunali/32/73516_2.png) [@Cihan\_Tunali](https://discuss.elastic.co/u/Cihan_Tunali)\
**Post date:** [May 20, 2021, 7:10am UTC](https://discuss.elastic.co/t/postgresql-grok-problem/273284/3 "2021-05-20T07:10:33Z")

</div>

Hello Kaiyan,

It is PostgreSQL 12.6 on Centos7. If this version is not supported, I will try to edit GROK pattern. To do it, do you have any guidance ? I dont know how to change filebeat postgresql GROK patterns. Thanks!

---

<div class="post-metadata">

**Author:** ![Kaiyan\_Sheng](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kaiyan_sheng/32/38247_2.png) [@Kaiyan\_Sheng](https://discuss.elastic.co/u/Kaiyan_Sheng)\
**Post date:** [May 20, 2021, 3:04pm UTC](https://discuss.elastic.co/t/postgresql-grok-problem/273284/4 "2021-05-20T15:04:22Z")

</div>

Cool! If you want, you can push a PR with your grok pattern change to add support for postgresql 12.6 🙂 We will be more than happy to review it!

Grok pattern for postgresql log is [here](https://github.com/elastic/beats/tree/master/filebeat/module/postgresql/log/ingest). Please add a [test file](https://github.com/elastic/beats/tree/master/filebeat/module/postgresql/log/test) and run command to generate the `-expected.json` file using command like `TESTING_FILEBEAT_MODULES=postgresql mage -v pythonIntegTest` to verify if the grok pattern change works.

---

<div class="post-metadata">

**Author:** ![Cihan\_Tunali](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cihan_tunali/32/73516_2.png) [@Cihan\_Tunali](https://discuss.elastic.co/u/Cihan_Tunali)\
**Post date:** [May 21, 2021, 9:36am UTC](https://discuss.elastic.co/t/postgresql-grok-problem/273284/5 "2021-05-21T09:36:11Z")

</div>

Hi Kaiyan,

I am not a developer but I created a GROK pattern based on log prefix (This prefix is more readable.)

> Log\_prefix: '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

> GROK pattern : %{TIMESTAMP\_ISO8601:timestamp}%{SPACE}%{INT:utc}%{SPACE}[%{NUMBER:pid:long}]:%{SPACE}[%{WORD:logline}-1]%{SPACE}user=%{USERNAME:user},db=%{WORD:db},app=%{NOTSPACE:application},client=%{IPV4:client}%{SPACE}%{WORD:logtype}:%{SPACE}%{GREEDYDATA:message}

I am sorry but I could not find about implementing this pattern to filebeat. Can you guide me how to test it?

Also there is still GROK failure with my pattern, if any of property has null value, I will be pleased if somebody help me to fix it as well. thanks!

---

<div class="post-metadata">

**Author:** ![Cihan\_Tunali](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cihan_tunali/32/73516_2.png) [@Cihan\_Tunali](https://discuss.elastic.co/u/Cihan_Tunali)\
**Post date:** [May 24, 2021, 2:00pm UTC](https://discuss.elastic.co/t/postgresql-grok-problem/273284/6 "2021-05-24T14:00:34Z")

</div>

I created a new pipeline as below  
 ![image](https://us1.discourse-cdn.com/elastic/original/3X/5/7/57b1b2845452ddf0d67ca760dad809fae266aa3a.png)

and added it to root pipeline

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/a/f/af2b2bd9378ef27d75ced8f3d038b2398ac21666.png)

but nothing changed ☹ I restarted Elasticsearch service, but could not make GROK pattern work ☹ Can somebody guide me? Thanks.

---

<div class="post-metadata">

**Author:** ![Cihan\_Tunali](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cihan_tunali/32/73516_2.png) [@Cihan\_Tunali](https://discuss.elastic.co/u/Cihan_Tunali)\
**Post date:** [June 1, 2021, 7:22am UTC](https://discuss.elastic.co/t/postgresql-grok-problem/273284/7 "2021-06-01T07:22:12Z")

</div>

Can anybody help me?

---

<div class="post-metadata">

**Author:** ![legoguy1000](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/legoguy1000/32/54301_2.png) [@legoguy1000](https://discuss.elastic.co/u/legoguy1000)\
**Post date:** [June 3, 2021, 1:32pm UTC](https://discuss.elastic.co/t/postgresql-grok-problem/273284/8 "2021-06-03T13:32:23Z")

</div>

looks like u have a slightly different format for the timezone. Can you submit an issue to Github with this issue, the sample log data... I think I already have the fix.

I updated the grok pattern

```auto
- grok:
    field: message
    patterns:
    - '^%{DATETIME:postgresql.log.timestamp}%{CHAR:separator}%{GREEDYDATA:raw_message}'
    pattern_definitions:
      DATETIME: '[-0-9]+ %{TIME} (%{WORD:event.timezone}|%{TIMEZONE:event.timezone})'
      TIMEZONE: '\+\d{2}'
      CHAR: .
      GREEDYDATA: |-
        (.|
        |	)*

```

And I updated the date parsing pattern

```auto
- date:
    field: postgresql.log.timestamp
    target_field: '@timestamp'
    formats:
    - yyyy-MM-dd HH:mm:ss.SSS zz
    - yyyy-MM-dd HH:mm:ss zz
    - yyyy-MM-dd HH:mm:ss X
    - yyyy-MM-dd HH:mm:ss.SSS X

```

---

<div class="post-metadata">

**Author:** ![Mario\_Castro](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mario_castro/32/35107_2.png) [@Mario\_Castro](https://discuss.elastic.co/u/Mario_Castro)\
**Post date:** [June 3, 2021, 3:24pm UTC](https://discuss.elastic.co/t/postgresql-grok-problem/273284/9 "2021-06-03T15:24:11Z")

</div>

Hi @Cihan_Tunali 🙂

I was taking a look at this now and I was wondering why or how your timestamp format is different from the default format that comes with Postgres 12.

I have just spin up a postgres instance from docker and my timestamp looks like this `2021-06-03 15:16:42.575 UTC [1] LOG: database system is ready to accept connections`.

Can you paste here your postgres config regarding your timezone format?

---

<div class="post-metadata">

**Author:** ![Cihan\_Tunali](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cihan_tunali/32/73516_2.png) [@Cihan\_Tunali](https://discuss.elastic.co/u/Cihan_Tunali)\
**Post date:** [June 4, 2021, 11:34am UTC](https://discuss.elastic.co/t/postgresql-grok-problem/273284/10 "2021-06-04T11:34:06Z")

</div>

Hello @Mario_Castro

Here is one of my log part as you requested:

> 2021-06-04 00:49:54 +03 [98853]: [87372-1] user=postgres,db=postgres,app=[unknown],client=123.123.123.123 LOG: duration: 0.222 ms statement: SELECT \* FROM pg\_stat\_database\_conflicts;  
> 2021-06-04 00:49:54 +03 [98853]: [87373-1] user=postgres,db=postgres,app=[unknown],client=123.123.123.123 LOG: duration: 0.574 ms statement: SELECT pg\_database.datname,tmp.mode,COALESCE(count,0) as count  
> FROM  
> (  
> VALUES ('accesssharelock'),  
> ('rowsharelock'),  
> ('rowexclusivelock'),  
> ('shareupdateexclusivelock'),  
> ('sharelock'),  
> ('sharerowexclusivelock'),  
> ('exclusivelock'),  
> ('accessexclusivelock')  
> ) AS tmp(mode) CROSS JOIN pg\_database  
> LEFT JOIN  
> (SELECT database, lower(mode) AS mode,count(\*) AS count  
> FROM pg\_locks WHERE database IS NOT NULL  
> GROUP BY database, lower(mode)  
> ) AS tmp2  
> ON tmp.mode=tmp2.mode and pg\_database.oid = tmp2.database ORDER BY 1  
> 2021-06-04 00:49:54 +03 [98853]: [87374-1] user=postgres,db=postgres,app=[unknown],client=123.123.123.123 LOG: duration: 0.636 ms statement:  
> SELECT \*,  
> (case pg\_is\_in\_recovery() when 't' then null else pg\_current\_wal\_lsn() end) AS pg\_current\_wal\_lsn,  
> (case pg\_is\_in\_recovery() when 't' then null else pg\_wal\_lsn\_diff(pg\_current\_wal\_lsn(), replay\_lsn)::float end) AS pg\_wal\_lsn\_diff  
> FROM pg\_stat\_replication
> 
> 2021-06-04 00:49:54 +03 [98853]: [87375-1] user=postgres,db=postgres,app=[unknown],client=123.123.123.123 LOG: duration: 0.743 ms statement:  
> SELECT  
> pg\_database.datname,  
> tmp.state,  
> COALESCE(count,0) as count,  
> COALESCE(max\_tx\_duration,0) as max\_tx\_duration  
> FROM  
> (  
> VALUES ('active'),  
> ('idle'),  
> ('idle in transaction'),  
> ('idle in transaction (aborted)'),  
> ('fastpath function call'),  
> ('disabled')  
> ) AS tmp(state) CROSS JOIN pg\_database  
> LEFT JOIN  
> (  
> SELECT  
> datname,  
> state,  
> count(\*) AS count,  
> MAX(EXTRACT(EPOCH FROM now() - xact\_start))::float AS max\_tx\_duration  
> FROM pg\_stat\_activity GROUP BY datname,state) AS tmp2  
> ON tmp.state = tmp2.state AND pg\_database.datname = tmp2.datname
> 
> 2021-06-04 00:49:54 +03 [98853]: [87376-1] user=postgres,db=postgres,app=[unknown],client=123.123.123.123 LOG: duration: 0.200 ms statement: SELECT \* FROM pg\_stat\_bgwriter;  
> 2021-06-04 00:49:54 +03 [98850]: [44781-1] user=postgres,db=postgres,app=Patroni,client=123.123.123.123 LOG: duration: 0.389 ms statement: SELECT CASE WHEN pg\_catalog.pg\_is\_in\_recovery() THEN 0 ELSE ('x' || pg\_catalog.substr(pg\_catalog.pg\_walfile\_name(pg\_catalog.pg\_current\_wal\_lsn()), 1, 8))::bit(32)::int END, CASE WHEN pg\_catalog.pg\_is\_in\_recovery() THEN 0 ELSE pg\_catalog.pg\_wal\_lsn\_diff(pg\_catalog.pg\_current\_wal\_lsn(), '0/0')::bigint END, pg\_catalog.pg\_wal\_lsn\_diff(pg\_catalog.pg\_last\_wal\_replay\_lsn(), '0/0')::bigint, pg\_catalog.pg\_wal\_lsn\_diff(COALESCE(pg\_catalog.pg\_last\_wal\_receive\_lsn(), '0/0'), '0/0')::bigint, pg\_catalog.pg\_is\_in\_recovery() AND pg\_catalog.pg\_is\_wal\_replay\_paused(), 0, CASE WHEN latest\_end\_lsn IS NULL THEN NULL ELSE received\_tli END, slot\_name, conninfo FROM pg\_catalog.pg\_stat\_get\_wal\_receiver()  
> 2021-06-04 00:49:54 +03 [98850]: [44782-1] user=postgres,db=postgres,app=Patroni,client=123.123.123.123 LOG: duration: 3.345 ms statement: SELECT name, setting, unit, vartype, context, sourcefile FROM pg\_catalog.pg\_settings WHERE pg\_catalog.lower(name) = ANY(ARRAY['listen\_addresses','port','cluster\_name','wal\_level','hot\_standby','max\_connections','max\_wal\_senders','max\_prepared\_transactions','max\_locks\_per\_transaction','track\_commit\_timestamp','max\_replication\_slots','max\_worker\_processes','wal\_log\_hints','archive\_mode','archive\_timeout','checkpoint\_completion\_target','default\_statistics\_target','effective\_cache\_size','effective\_io\_concurrency','log\_line\_prefix','log\_min\_duration\_statement','log\_rotation\_age','log\_statement','logging\_collector','maintenance\_work\_mem','max\_parallel\_maintenance\_workers','max\_parallel\_workers','max\_parallel\_workers\_per\_gather','max\_wal\_size','min\_wal\_size','random\_page\_cost','shared\_buffers','shared\_preload\_libraries','wal\_buffers','wal\_keep\_segments','work\_mem','wal\_segment\_size','wal\_block\_size'])  
> 2021-06-04 00:49:54 +03 [98850]: [44783-1] user=postgres,db=postgres,app=Patroni,client=123.123.123.123 LOG: duration: 1.223 ms statement: SELECT pg\_catalog.to\_char(pg\_catalog.pg\_postmaster\_start\_time(), 'YYYY-MM-DD HH24:MI:SS.MS TZ'), CASE WHEN pg\_catalog.pg\_is\_in\_recovery() THEN 0 ELSE ('x' || pg\_catalog.substr(pg\_catalog.pg\_walfile\_name(pg\_catalog.pg\_current\_wal\_lsn()), 1, 8))::bit(32)::int END, CASE WHEN pg\_catalog.pg\_is\_in\_recovery() THEN 0 ELSE pg\_catalog.pg\_wal\_lsn\_diff(pg\_catalog.pg\_current\_wal\_lsn(), '0/0')::bigint END, pg\_catalog.pg\_wal\_lsn\_diff(pg\_catalog.pg\_last\_wal\_replay\_lsn(), '0/0')::bigint, pg\_catalog.pg\_wal\_lsn\_diff(COALESCE(pg\_catalog.pg\_last\_wal\_receive\_lsn(), '0/0'), '0/0')::bigint, pg\_catalog.pg\_is\_in\_recovery() AND pg\_catalog.pg\_is\_wal\_replay\_paused(), pg\_catalog.to\_char(pg\_catalog.pg\_last\_xact\_replay\_timestamp(), 'YYYY-MM-DD HH24:MI:SS.MS TZ'), pg\_catalog.array\_to\_json(pg\_catalog.array\_agg(pg\_catalog.row\_to\_json(ri))) FROM (SELECT (SELECT rolname FROM pg\_authid WHERE oid = usesysid) AS usename, application\_name, client\_addr, w.state, sync\_state, sync\_priority FROM pg\_catalog.pg\_stat\_get\_wal\_senders() w, pg\_catalog.pg\_stat\_get\_activity(pid)) AS ri  
> 2021-06-04 00:49:54 +03 [98828]: [9-1] user=,db=,app=,client= LOG: received SIGHUP, reloading configuration files  
> 2021-06-04 00:49:54 +03 [98828]: [10-1] user=,db=,app=,client= LOG: parameter "log\_min\_duration\_statement" changed to "500ms"  
> 2021-06-04 00:49:54 +03 [98828]: [11-1] user=,db=,app=,client= LOG: parameter "log\_statement" changed to "ddl"

I am using Patroni for managing my Pgsql cluster. My pgsql version is

> PostgreSQL 12.4 on x86\_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180303 (Red Hat 7.3.1-5), 64-bit

@legoguy1000 after long research I could not implement my GROK to pipeline so I decided to disable filebeat's posgtresql module. After that, manually I forced filebeat to use my pipeline from filebeat.yml

> output.elasticsearch:
> 
> # Array of hosts to connect to.
> 
> hosts: ["gbzmngelk-1:9200"]  
> pipeline: "postgres"

Here is my pipeline file:

> [  
> {  
> "set": {  
> "field": "event.ingested",  
> "value": "{{\_ingest.timestamp}}"  
> }  
> },  
> {  
> "grok": {  
> "field": "message",  
> "patterns": [  
> "%{TIMESTAMP\_ISO8601:timestamp}%{SPACE}%{INT:utc}%{SPACE}\[%{NUMBER:pid:long}]:%{SPACE}\[%{NUMBER:logline}-1]%{SPACE}user=%{USERNAME:pgsql\_user},db=%{WORD:pgsql\_database},app=%{NOTSPACE:pgsql\_application},client=%{IPV4:pgsql\_client}%{SPACE}%{WORD:pgsql\_logtype}:%{SPACE}%{GREEDYDATA:pgsql\_message}"  
> ],  
> "pattern\_definitions": {  
> "DATETIME": "[-0-9]+ %{TIME} %{WORD:event.timezone}",  
> "CHAR": ".",  
> "GREEDYDATA": "(.|\n|\t)\*"  
> }  
> }  
> },  
> {  
> "date": {  
> "formats": [  
> "yyyy-MM-dd HH:mm:ss.SSS zz",  
> "yyyy-MM-dd HH:mm:ss zz"  
> ],  
> "field": "postgresql.log.timestamp",  
> "target\_field": "@timestamp"  
> }  
> },  
> {  
> "script": {  
> "params": {  
> "scale": 1000000  
> },  
> "if": "ctx.temp?.duration != null",  
> "lang": "painless",  
> "source": "ctx.event.duration = Math.round(ctx.temp.duration \* params.scale)"  
> }  
> },  
> {  
> "remove": {  
> "field": "temp.duration",  
> "ignore\_missing": true  
> }  
> },  
> {  
> "set": {  
> "field": "event.kind",  
> "value": "logtype"  
> }  
> },  
> {  
> "set": {  
> "field": "event.user",  
> "value": "user"  
> }  
> },  
> {  
> "set": {  
> "field": "event.application",  
> "value": "application"  
> }  
> },  
> {  
> "set": {  
> "field": "event.source",  
> "value": "client"  
> }  
> },  
> {  
> "append": {  
> "field": "event.category",  
> "value": [  
> "database"  
> ]  
> }  
> },  
> {  
> "remove": {  
> "field": [  
> "separator"  
> ]  
> }  
> },  
> {  
> "remove": {  
> "field": [  
> "raw\_message"  
> ]  
> }  
> }  
> ]

But still it is not parsing %100. If I open all statements logging, whoouu it gave tons of GROK parse error 😁 Right now logging enable for DDL.

With all these informations any help would be adorable 🙏

---

<div class="post-metadata">

**Author:** ![legoguy1000](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/legoguy1000/32/54301_2.png) [@legoguy1000](https://discuss.elastic.co/u/legoguy1000)\
**Post date:** [June 4, 2021, 11:57am UTC](https://discuss.elastic.co/t/postgresql-grok-problem/273284/11 "2021-06-04T11:57:08Z")

</div>

> [@Cihan\_Tunali](#):
>
> {  
> "date": {  
> "formats": [  
> "yyyy-MM-dd HH:mm:ss.SSS zz",  
> "yyyy-MM-dd HH:mm:ss zz"  
> ],  
> "field": "postgresql.log.timestamp",  
> "target\_field": "@timestamp"  
> }  
> },

In order to parse your timestamp field correctly with the +00 time zone, you need the additional date formats.

```auto
    - yyyy-MM-dd HH:mm:ss X
    - yyyy-MM-dd HH:mm:ss.SSS X

```

---

<div class="post-metadata">

**Author:** ![Cihan\_Tunali](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cihan_tunali/32/73516_2.png) [@Cihan\_Tunali](https://discuss.elastic.co/u/Cihan_Tunali)\
**Post date:** [June 4, 2021, 12:54pm UTC](https://discuss.elastic.co/t/postgresql-grok-problem/273284/12 "2021-06-04T12:54:15Z")

</div>

done.

![image](https://us1.discourse-cdn.com/elastic/original/3X/d/6/d632fc1a5cf3974df1761e1f25bd04863cb6ad53.png)

---

<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:** [July 2, 2021, 2:54pm UTC](https://discuss.elastic.co/t/postgresql-grok-problem/273284/13 "2021-07-02T14:54:56Z")

</div>

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