Do not see postgresql metrics in kibana after configuring and running metricbeat

yurim · February 5, 2019, 6:11pm

Hi guys, I might be missing something here please set me straight. Using ELK 6.5.4. enabled postgresql module for metricbeat, edited metricbeat.yml and modules.d/postgresql.yml according the manual. ran metricbeat setup and then metricbeat -e. index pattern metricbeat-* was created in kibana, but when I "discover" it does not show postgresql metrics. anything but PostgreSQL data and postgresql is the only module I have enabled for metricbeat.
here is my modules.d/postgresql.yml

 - module: postgresql
  enabled: true
  metricsets:
    - database
    - bgwriter
    - activity
  period: 10s
  hosts: ["postgres://hostname:5432"]
  #username: user
  #password: pass

what am I missing? is there a way to filter out everything else and build index only for postgresql metrics? I would think there is a general approach for this and/or best practices recommendations. I am new to this and could not find my answer in documentation. Any ideas, suggestions and pointers are greatly appreciated. thanks.

Mario_Castro · February 6, 2019, 12:01pm

Hi @yurim

system module is enabled by default and it's recommended. You can however deactivate it by running metricbeat module disable system

PostgresSQL module is well tested but it might be some bug anyways. Can you please paste the output of running metricbeat -e so we can gather some more data. I'm assuming that you have checked host:port configuration and user and password to match your current instance, of course

yurim · February 6, 2019, 3:23pm

Hi Mario,
thank you for your response. I have already disabled system module for metricbeat. it works fine and I wanted to see if I could get postgres data without system enabled. I am providing the output of running metricbeat -e as you requested. Thanks again. Yuri.
I will have to split it in 2 messages since it exceeds allowed message body size.
2019-02-06T10:00:01.347-0500 INFO instance/beat.go:592 Home path: [/u01/elk/metricbeat] Config path: [/u01/elk/metricbeat] Data path:
[/u01/elk/metricbeat/data] Logs path: [/u01/elk/metricbeat/logs]
2019-02-06T10:00:01.359-0500 INFO instance/beat.go:599 Beat UUID: 7570f118-9374-4a7b-a2e1-8130403dc6f9
2019-02-06T10:00:01.359-0500 INFO [seccomp] seccomp/seccomp.go:93 Syscall filter could not be installed because the kernel does
not support seccomp
2019-02-06T10:00:01.359-0500 INFO [beat] instance/beat.go:825 Beat info {"system_info": {"beat": {"path": {"config": "/u01/elk
/metricbeat", "data": "/u01/elk/metricbeat/data", "home": "/u01/elk/metricbeat", "logs": "/u01/elk/metricbeat/logs"}, "type": "metricbeat", "u
uid": "7570f118-9374-4a7b-a2e1-8130403dc6f9"}}}
2019-02-06T10:00:01.359-0500 INFO [beat] instance/beat.go:834 Build info {"system_info": {"build": {"commit": "bd8922f1c7e93d12
b07e0b3f7d349e17107f7826", "libbeat": "6.5.4", "time": "2018-12-17T20:31:39.000Z", "version": "6.5.4"}}}
2019-02-06T10:00:01.360-0500 INFO [beat] instance/beat.go:837 Go runtime info {"system_info": {"go": {"os":"linux","arch":"amd64","m
ax_procs":4,"version":"go1.10.6"}}}
2019-02-06T10:00:01.361-0500 INFO [beat] instance/beat.go:841 Host info {"system_info": {"host": {"architecture":"x86_64","boo
t_time":"2018-11-09T16:56:26-05:00","containerized":true,"name":"itsrhv1770","ip":["127.0.0.1/8","10.10.17.70/24"],"kernel_version":"3.10.0-51
4.36.5.el7.x86_64","mac":["00:50:56:a1:47:76"],"os":{"family":"","platform":"rhel","name":"Red Hat Enterprise Linux Server","version":"7.3 (Ma
ipo)","major":7,"minor":3,"patch":0,"codename":"Maipo"},"timezone":"EST","timezone_offset_sec":-18000,"id":"a9b04fa746c9412a858c7e42e5f90d95"}
}}
2019-02-06T10:00:01.361-0500 INFO [beat] instance/beat.go:870 Process info {"system_info": {"process": {"capabilities": {"inherit
able":null,"permitted":null,"effective":null,"bounding":["chown","dac_override","dac_read_search","fowner","fsetid","kill","setgid","setuid","
setpcap","linux_immutable","net_bind_service","net_broadcast","net_admin","net_raw","ipc_lock","ipc_owner","sys_module","sys_rawio","sys_chroo
t","sys_ptrace","sys_pacct","sys_admin","sys_boot","sys_nice","sys_resource","sys_time","sys_tty_config","mknod","lease","audit_write","audit_
control","setfcap","mac_override","mac_admin","syslog","wake_alarm","block_suspend"],"ambient":null}, "cwd": "/u01/elk/metricbeat-6.5.4-linux-
x86_64", "exe": "/u01/elk/metricbeat-6.5.4-linux-x86_64/metricbeat", "name": "metricbeat", "pid": 12108, "ppid": 20127, "seccomp": {"mode":"di
sabled"}, "start_time": "2019-02-06T10:00:00.930-0500"}}}

yurim · February 6, 2019, 3:25pm

here is 2nd part of the output. Please let me know if you need anything else. Thank you.
2019-02-06T10:00:01.361-0500 INFO instance/beat.go:278 Setup Beat: metricbeat; Version: 6.5.4
2019-02-06T10:00:04.363-0500 INFO add_cloud_metadata/add_cloud_metadata.go:319 add_cloud_metadata: hosting provider type not detected
.
2019-02-06T10:00:04.363-0500 INFO elasticsearch/client.go:163 Elasticsearch url: http://itsrhv22111.it.statestr.com:7200
2019-02-06T10:00:04.363-0500 INFO elasticsearch/client.go:163 Elasticsearch url: http://itsrhv22112.it.statestr.com:7200
2019-02-06T10:00:04.364-0500 INFO [publisher] pipeline/module.go:110 Beat name: itsrhv1770
2019-02-06T10:00:04.364-0500 INFO [monitoring] log/log.go:117 Starting metrics logging every 30s
2019-02-06T10:00:04.364-0500 INFO instance/beat.go:400 metricbeat start running.
2019-02-06T10:00:04.378-0500 INFO cfgfile/reload.go:150 Config reloader started
2019-02-06T10:00:04.380-0500 INFO cfgfile/reload.go:205 Loading of config files completed.
2019-02-06T10:00:05.382-0500 INFO pipeline/output.go:95 Connecting to backoff(elasticsearch(http://itsrhv22112.it.statestr.com:7200))
2019-02-06T10:00:05.382-0500 INFO pipeline/output.go:95 Connecting to backoff(elasticsearch(http://itsrhv22111.it.statestr.com:7200))
2019-02-06T10:00:05.387-0500 INFO elasticsearch/client.go:713 Connected to Elasticsearch version 6.5.4
2019-02-06T10:00:05.388-0500 INFO elasticsearch/client.go:713 Connected to Elasticsearch version 6.5.4
2019-02-06T10:00:05.399-0500 INFO template/load.go:129 Template already exists and will not be overwritten.
2019-02-06T10:00:05.399-0500 INFO pipeline/output.go:105 Connection to backoff(elasticsearch(http://itsrhv22111.it.statestr.com:7200))
established
2019-02-06T10:00:05.411-0500 INFO template/load.go:129 Template already exists and will not be overwritten.
2019-02-06T10:00:05.411-0500 INFO pipeline/output.go:105 Connection to backoff(elasticsearch(http://itsrhv22112.it.statestr.com:7200))
established

yurim · February 6, 2019, 3:25pm

3rd part:
2019-02-06T10:00:34.367-0500 eat":{"cpu":{"system":{"tick "handles":{"limit":{"hard":4 memstats":{"gc_next":6046416 ads":1},"output":{"events":{ "clients":2,"events":{"activ failures":3},"bgwriter":{"ev postgres@/u01/elk/metricbeat$ postgres@/u01/elk/metricbeat$ 2019-02-06T10:00:01.347-0500 2019-02-06T10:00:01.359-0500 2019-02-06T10:00:01.359-0500 2019-02-06T10:00:01.359-0500 2019-02-06T10:00:01.359-0500 2019-02-06T10:00:01.360-0500 2019-02-06T10:00:01.361-0500 2019-02-06T10:00:01.361-0500 2019-02-06T10:00:01.361-0500 2019-02-06T10:00:04.363-0500 2019-02-06T10:00:04.363-0500 2019-02-06T10:00:04.363-0500 2019-02-06T10:00:04.364-0500 2019-02-06T10:00:04.364-0500 2019-02-06T10:00:04.364-0500 2019-02-06T10:00:04.378-0500 2019-02-06T10:00:04.380-0500 2019-02-06T10:00:05.382-0500 2019-02-06T10:00:05.382-0500 2019-02-06T10:00:05.387-0500 2019-02-06T10:00:05.388-0500 2019-02-06T10:00:05.399-0500 2019-02-06T10:00:05.399-0500 2019-02-06T10:00:05.411-0500 2019-02-06T10:00:05.411-0500 2019-02-06T10:00:34.367-0500 INFO [monitoring] log/log.go:144 Non-zero metrics in the last 30s {"monitoring": {"metrics": {"b
s":40,"time":{"ms":48}},"total":{"ticks":100,"time":{"ms":112},"value":100},"user":{"ticks":60,"time":{"ms":64}}},
096,"soft":1024},"open":14},"info":{"ephemeral_id":"55bc4b58-4103-452a-9f2e-eb06e5a9dbbd","uptime":{"ms":33065}},"
,"memory_alloc":3896568,"memory_total":10487360,"rss":25616384}},"libbeat":{"config":{"module":{"running":0},"relo
"acked":9,"batches":3,"total":9},"read":{"bytes":2067},"type":"elasticsearch","write":{"bytes":6147}},"pipeline":{
e":0,"published":9,"retry":6,"total":9},"queue":{"acked":9}}},"metricbeat":{"postgresql":{"activity":{"events":3,"
ents":3,"failures":3},"database":{"events":3,"failures":3}}},"system":{"cpu":{"cores":4},"load":{"1":0.01,"15":0.0
cat metricbeat.startup.output
metricbeat -e > metricbeat.startup.output
INFO instance/beat.go:592 Home path: [/u01/elk/metricbeat] Config path: [/u01/elk/metricbeat] Data path: [/u01/elk/metricbeat/data] Logs path: [/u01/elk/metricbeat/logs]
INFO instance/beat.go:599 Beat UUID: 7570f118-9374-4a7b-a2e1-8130403dc6f9
INFO [seccomp] seccomp/seccomp.go:93 Syscall filter could not be installed because the kernel does not support seccomp
INFO [beat] instance/beat.go:825 Beat info {"system_info": {"beat": {"path": {"config": "/u01/elk/metricbeat", "data": "/u01/elk/metricbeat/data", "home": "/u01/elk/metricbeat", "logs": "/u01/elk/metricbeat/logs"}, "type": "metricbeat", "uuid": "7570f118-9374-4a7b-a2e1-8130403dc6f9"}}}
INFO [beat] instance/beat.go:834 Build info {"system_info": {"build": {"commit": "bd8922f1c7e93d12b07e0b3f7d349e17107f7826", "libbeat": "6.5.4", "time": "2018-12-17T20:31:39.000Z", "version": "6.5.4"}}}
INFO [beat] instance/beat.go:837 Go runtime info {"system_info": {"go": {"os":"linux","arch":"amd64","max_procs":4,"version":"go1.10.6"}}}
INFO [beat] instance/beat.go:841 Host info {"system_info": {"host": {"architecture":"x86_64","boot_time":"2018-11-09T16:56:26-05:00","containerized":true,"name":"itsrhv1770","ip":["127.0.0.1/8","10.10.17.70/24"],"kernel_version":"3.10.0-514.36.5.el7.x86_64","mac":["00:50:56:a1:47:76"],"os":{"family":"","platform":"rhel","name":"Red Hat Enterprise Linux Server","version":"7.3 (Maipo)","major":7,"minor":3,"patch":0,"codename":"Maipo"},"timezone":"EST","timezone_offset_sec":-18000,"id":"a9b04fa746c9412a858c7e42e5f90d95"}}}
INFO [beat] instance/beat.go:870 Process info {"system_info": {"process": {"capabilities": {"inheritable":null,"permitted":null,"effective":null,"bounding":["chown","dac_override","dac_read_search","fowner","fsetid","kill","setgid","setuid","setpcap","linux_immutable","net_bind_service","net_broadcast","net_admin","net_raw","ipc_lock","ipc_owner","sys_module","sys_rawio","sys_chroot","sys_ptrace","sys_pacct","sys_admin","sys_boot","sys_nice","sys_resource","sys_time","sys_tty_config","mknod","lease","audit_write","audit_control","setfcap","mac_override","mac_admin","syslog","wake_alarm","block_suspend"],"ambient":null}, "cwd": "/u01/elk/metricbeat-6.5.4-linux-x86_64", "exe": "/u01/elk/metricbeat-6.5.4-linux-x86_64/metricbeat", "name": "metricbeat", "pid": 12108, "ppid": 20127, "seccomp": {"mode":"disabled"}, "start_time": "2019-02-06T10:00:00.930-0500"}}}
INFO instance/beat.go:278 Setup Beat: metricbeat; Version: 6.5.4
INFO add_cloud_metadata/add_cloud_metadata.go:319 add_cloud_metadata: hosting provider type not detected.
INFO elasticsearch/client.go:163 Elasticsearch url: http://itsrhv22111.it.statestr.com:7200
INFO elasticsearch/client.go:163 Elasticsearch url: http://itsrhv22112.it.statestr.com:7200
INFO [publisher] pipeline/module.go:110 Beat name: itsrhv1770
INFO [monitoring] log/log.go:117 Starting metrics logging every 30s
INFO instance/beat.go:400 metricbeat start running.
INFO cfgfile/reload.go:150 Config reloader started
INFO cfgfile/reload.go:205 Loading of config files completed.
INFO pipeline/output.go:95 Connecting to backoff(elasticsearch(http://itsrhv22112.it.statestr.com:7200))
INFO pipeline/output.go:95 Connecting to backoff(elasticsearch(http://itsrhv22111.it.statestr.com:7200))
INFO elasticsearch/client.go:713 Connected to Elasticsearch version 6.5.4
INFO elasticsearch/client.go:713 Connected to Elasticsearch version 6.5.4
INFO template/load.go:129 Template already exists and will not be overwritten.
INFO pipeline/output.go:105 Connection to backoff(elasticsearch(http://itsrhv22111.it.statestr.com:7200)) established
INFO template/load.go:129 Template already exists and will not be overwritten.
INFO pipeline/output.go:105 Connection to backoff(elasticsearch(http://itsrhv22112.it.statestr.com:7200)) established
INFO [monitoring] log/log.go:144 Non-zero metrics in the last 30s {"monitoring": {"metrics": {"beat":{"cpu":{"system":{"ticks":40,"time":{"ms":48}},"total":{"ticks":100,"time":{"ms":112},"value":100},"user":{"ticks":60,"time":{"ms":64}}},"handles":{"limit":{"hard":4096,"soft":1024},"open":14},"info":{"ephemeral_id":"55bc4b58-4103-452a-9f2e-eb06e5a9dbbd","uptime":{"ms":33065}},"memstats":{"gc_next":6046416,"memory_alloc":3896568,"memory_total":10487360,"rss":25616384}},"libbeat":{"config":{"module":{"running":0},"reloads":1},"output":{"events":{"acked":9,"batches":3,"total":9},"read":{"bytes":2067},"type":"elasticsearch","write":{"bytes":6147}},"pipeline":{"clients":2,"events":{"active":0,"published":9,"retry":6,"total":9},"queue":{"acked":9}}},"metricbeat":{"postgresql":{"activity":{"events":3,"failures":3},"bgwriter":{"events":3,"failures":3},"database":{"events":3,"failures":3}}},"system":{"cpu":{"cores":4},"load":{"1":0.01,"15":0.05,"5":0.03,"norm":{"1":0.0025,"15":0.0125,"5":0.0075}}}}}}

yurim · February 6, 2019, 3:28pm

^C2019-02-06T10:21:45.669-0500 INFO cfgfile/reload.go:208 Dynamic config reloader stopped
2019-02-06T10:21:45.669-0500 INFO [reload] cfgfile/list.go:118 Stopping 1 runners ...
2019-02-06T10:21:45.671-0500 INFO [monitoring] log/log.go:152 Total non-zero metrics {"monitoring": {"metrics": {"beat":{"cpu":{"system":{"ticks":340,"time":{"ms":346}},"total":{"ticks":830,"time":{"ms":845},"value":830},"user":{"ticks":490,"time":{"ms":499}}},"handles":{"limit":{"hard":4096,"soft":1024},"open":17},"info":{"ephemeral_id":"55bc4b58-4103-452a-9f2e-eb06e5a9dbbd","uptime":{"ms":1304369}},"memstats":{"gc_next":6258960,"memory_alloc":4260352,"memory_total":56273544,"rss":27340800}},"libbeat":{"config":{"module":{"running":0},"reloads":1},"output":{"events":{"acked":393,"batches":131,"total":393},"read":{"bytes":47349},"type":"elasticsearch","write":{"bytes":244355}},"pipeline":{"clients":1,"events":{"active":0,"published":393,"retry":6,"total":393},"queue":{"acked":393}}},"metricbeat":{"postgresql":{"activity":{"events":131,"failures":131},"bgwriter":{"events":131,"failures":131},"database":{"events":131,"failures":131}}},"system":{"cpu":{"cores":4},"load":{"1":0.03,"15":0.05,"5":0.03,"norm":{"1":0.0075,"15":0.0125,"5":0.0075}}}}}}
2019-02-06T10:21:45.671-0500 INFO [monitoring] log/log.go:153 Uptime: 21m44.37150536s
2019-02-06T10:21:45.671-0500 INFO [monitoring] log/log.go:130 Stopping metrics logging.
2019-02-06T10:21:45.671-0500 INFO instance/beat.go:410 metricbeat stopped.

yurim · February 6, 2019, 3:36pm

that is what I am getting now in kibana. yes, I didn't enable ssl on postgres, its' poc cluster and I run metricbeat on the same host as postgres.

yurim · February 6, 2019, 4:05pm

Hi Mario,
I think I figured it out and fixed my issue. I've got 2 questions if I may.

is there a way to have metricbeat use .pgpass file instead of implicitly entering postgres password in modules.d/postgresql.yml file.
is there PostgreSQL dashboard sample? There are samples for pretty much everything but PostgreSQL :-)
if those questions need to be placed in different section please let me know.
thank you.

Mario_Castro · February 7, 2019, 10:34am

Hi @yurim I'm glad that you figured out

For your questions:

We don't have this implementation but it might be a nice addition. Can you file an issue describing it? https://github.com/elastic/beats/issues/new?template=feature-request.md
PostgreSQL doesn't have a dashboard yet, I'm sorry. Again, it's good to know this things in the Beats repo so we are aware of this needs and we can prioritize and tackle them. Anyways, to create your own dashboard is pretty simple, specially with the Visual Builder. This is the tutorial about creating visualizations and the visual builder. I usually create dashboards in a matter of minutes.

yurim · February 8, 2019, 4:20pm

Thank you Mario for your help and recommendations.
I will certainly follow your suggestions.

system · March 8, 2019, 4:29pm

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