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


#1

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) #2

Hi @yurim :slightly_smiling_face:

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 :slightly_smiling_face:


#3

Hi Mario,
thank you for your response. I have already disabled system module for metricbeat. it works fine :slight_smile: 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"}}}


#4

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


#5

3rd part:
2019-02-06T10:00:34.367-0500 INFO [monitoring] log/log.go:144 Non-zero metrics in the last 30s {"monitoring": {"metrics": {"b
eat":{"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},"relo
ads":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.0
postgres@/u01/elk/metricbeat$ cat metricbeat.startup.output
postgres@/u01/elk/metricbeat$ metricbeat -e > metricbeat.startup.output
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", "uuid": "7570f118-9374-4a7b-a2e1-8130403dc6f9"}}}
2019-02-06T10:00:01.359-0500 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"}}}
2019-02-06T10:00:01.360-0500 INFO [beat] instance/beat.go:837 Go runtime info {"system_info": {"go": {"os":"linux","arch":"amd64","max_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","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"}}}
2019-02-06T10:00:01.361-0500 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"}}}
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
2019-02-06T10:00:34.367-0500 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}}}}}}


#6

^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.


#7

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.


#8

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

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

(Mario Castro) #9

Hi @yurim I'm glad that you figured out :slight_smile:

For your questions:

  1. 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
  2. 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.

#10

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