Postgresql Module?

[killmasta93]

Hi,
I was wondering if someone could shed some light. currently have Kibana 6 with logstash and elasticsearch. The idea is one VM which has the postgresql to send the logs using filebeat to another VM which has ELK,
I have activated the module

              filebeat modules enable postgresql

I was reading jdbc input plugin for Logstash not sure if i would need it? Im somewhat confused how i can get the correct index for it to filter.
im trying to get the pg_stat_activity

also using metricbeat to import the dashboards which i imported it but i see everything besides the postgresql

Thank you

[kvch (Noémi Ványi)]

I assume you are forwarding the events to Logstash. You don't need to use the jdbc plugin. Logstash needs to be configured to read from beats input: https://www.elastic.co/guide/en/logstash/current/plugins-inputs-beats.html

According to the command you have pasted here you enabled the module for Filebeat. You need to run metricbeat modules enable postgresql.

Metricbeat does not provide a dashboards for PostgreSQL module.

[killmasta93]

Thanks for the reply, when i was going to the dashboard of kibana i saw that postgresql had its own dashboard, not sure why it did not show up. Also the filebeat module i enabled on the VM which has the postgresql and on the metric beat i enabled on the Kibana VM.
After that on logstash this is my
02-beats-input.conf

 input {
 beats {
  port => 5443
ssl => false
 }
}

then my 10-elasticsearch-output.conf

       output {
 elasticsearch {
hosts => ["localhost:9200"]
sniffing => true
manage_template => false
index => "%{[@metadata][beat]}-%{+YYYY.MM.dd}"
document_type => "%{[@metadata][type]}"
  }
}

Then on my filebeat which is another vm which has postgresql this is my config
filebeat.yml

https://pastebin.com/tX5fY5Ga

now if i understood correctly filebeat would send all the logs from postgresql including inserts and selects?

Currently when i check on discover im getting this

Thank you

EDIT: after reading a long time i realized that metricbeat is what i need to install on the vm that has postgresql and remove the filebeat. now that i have installed metricbeat on the vm that has postgre im sending the data though logstash, the question is should i send it though logstash or elastic?

EDIT2: So after more reading i edited the postgremodule.yml

 # Module: postgresql
  # Docs: https://www.elastic.co/guide/en/beats/metricbeat/6.4/metricbeat-module-postgresql.html

  - module: postgresql
  metricsets:
     - database
     - bgwriter
      - activity
     period: 10s
  hosts: ["postgres://localhost:5432"]
   username: postgre
     password: mypassword

But i still dont see any metrics on kibana any ideas?

This is what i have im getting

     https://pastebin.com/rx1U2PFJ

[kvch (Noémi Ványi)]

Could you please share your full config? The indentation seems to be off.

Have you run metricbeat setup? It is supposed to setup everything required by ES/Kibana to show your data.

[killmasta93]

@kvch

metricbeat setup

Thanks for the reply, when i try to run

            metricbeat setup

I get this error

           Exiting: Template loading requested but the Elasticsearch output is not configured/enabled

im running the metricbeat on the vm that has the postgresql

                        ###################### Metricbeat Configuration Example #######################

    # This file is an example configuration file highlighting only the most common
  # options. The metricbeat.reference.yml file from the same directory contains all the
   # supported options with more comments. You can use it as a reference.
    #
      # You can find the full configuration reference here:
     # https://www.elastic.co/guide/en/beats/metricbeat/index.html

         #==========================  Modules configuration ============================

     metricbeat.config.modules:
    # Glob pattern for configuration loading
    path: ${path.config}/modules.d/*.yml

    # Set to true to enable config reloading
     reload.enabled: false

     # Period on which files under path should be checked for changes
    #reload.period: 10s

      #==================== Elasticsearch template setting ==========================

 setup.template.settings:
  index.number_of_shards: 1
 index.codec: best_compression
 #_source.enabled: false

 #================================ General =====================================

# The name of the shipper that publishes the network data. It can be used to group
# all the transactions sent by a single shipper in the web interface.
#name:

# The tags of the shipper are included in their own field with each
# transaction published.
#tags: ["service-X", "web-tier"]

# Optional fields that you can specify to add additional information to the
# output.
   #fields:
   #  env: staging


  #============================== Dashboards =====================================
  # These settings control loading the sample dashboards to the Kibana index. Loading
   # the dashboards is disabled by default and can be enabled either by setting the
# options here, or by using the `-setup` CLI flag or the `setup` command.
#setup.dashboards.enabled: true

# The URL from where to download the dashboards archive. By default this URL
# has a value which is computed based on the Beat name and version. For released
# versions, this URL points to the dashboard archive on the artifacts.elastic.co
 # website.
#setup.dashboards.url:

 #============================== Kibana =====================================

# Starting with Beats version 6.0.0, the dashboards are loaded via the Kibana API.
# This requires a Kibana endpoint configuration.
#setup.kibana:

  # Kibana Host
  # Scheme and port can be left out and will be set to the default (http and 5601)
 # In case you specify and additional path, the scheme is required: http://localhost:5601/path
  # IPv6 addresses should always be defined as: https://[2001:db8::1]:5601
 #host: "localhost:5601"

  #============================= Elastic Cloud ==================================

# These settings simplify using metricbeat with the Elastic Cloud (https://cloud.elastic.co/).

# The cloud.id setting overwrites the `output.elasticsearch.hosts` and
# `setup.kibana.host` options.
# You can find the `cloud.id` in the Elastic Cloud web UI.
  #cloud.id:

 # The cloud.auth setting overwrites the `output.elasticsearch.username` and
 # `output.elasticsearch.password` settings. The format is `<user>:<pass>`.
  #cloud.auth:

 #================================ Outputs =====================================

 # Configure what output to use when sending the data collected by the beat.

#-------------------------- Elasticsearch output ------------------------------
  #output.elasticsearch:
 # Array of hosts to connect to.
   # hosts: ["192.168.115.166:9200"]

  # Optional protocol and basic auth credentials.
  #protocol: "https"
 #username: "elastic"
 #password: "changeme"

#----------------------------- Logstash output --------------------------------
 output.logstash:
  # The Logstash hosts
   hosts: ["192.168.115.166:5443"]

  # Optional SSL. By default is off.
 # List of root certificates for HTTPS server verifications
  #ssl.certificate_authorities: ["/etc/pki/root/ca.pem"]

  # Certificate for SSL client authentication
 #ssl.certificate: "/etc/pki/client/cert.pem"

  # Client Certificate Key
 #ssl.key: "/etc/pki/client/cert.key"

     #================================ Logging =====================================

 # Sets log level. The default log level is info.
# Available log levels are: error, warning, info, debug
#logging.level: debug

# At debug level, you can selectively enable logging only for some components.
# To enable all selectors use ["*"]. Examples of other selectors are "beat",
# "publish", "service".
#logging.selectors: ["*"]

and this my postgresql.yml

            - module: postgresql
   metricsets:
    - database
   - bgwriter
    - activity
  period: 10s
  hosts: ["postgres://localhost:5432"]
  username: postgre
  password: mypassword

Thank you