Postgresql queries in ELK

[Charakterny]

Hi,

I installed metricbeat to provide metrics from postgresql and it works, but main goal was to analize queries in elastic and build dashboard for custom queries.
As I started to configure, any statements from database won't work.
I saw that I should install plugin like jdbc to bring database queries to ELK ?.
What is the best solution ?
Thanks in advance for help.

[ylasri (Yassine LASRI)]

Metricbeat will only collect metrics related to PostgreSQL instance health like CPU, memory, latency ... etc
if you want to analyse Business data from postgresql database with kibana you should bring your postgresql data into elasticsearch with logstash using jdbc input plugin

[warkolm (Mark Walkom)]

If you are looking to analyse the actual queries that are run against your database, then check out Packetbeat and https://www.elastic.co/guide/en/beats/packetbeat/current/packetbeat-pgsql-options.html

[Charakterny]

Ok, what is difference between jdbc with logstash and packetbeat ?

[warkolm (Mark Walkom)]

JDBC will take data from a table.
Packetbeat builds session info out of the requests and responses.

[Charakterny]

Thank you guys for help.There is any tutorial how it configure on docker ?
I have elk on https://github.com/deviantony/docker-elk#how-to-configure-logstash.
I tried to configure jdbc based on https://medium.com/@emreceylan/how-to-sync-postgresql-data-to-elasticsearch-572af15845ad
And I see in Stack Monitoring in Logstash section 1 node and 1 pipline but is "main" pipline not that I made for postgres to make test
@ylasri @warkolm

last logs from container with logstash
logstash_1 | [2020-11-24T14:34:43,203][INFO ][logstash.javapipeline ][.monitoring-logstash] Pipeline Java execution initialization time {"seconds"=>0.83}
logstash_1 | [2020-11-24T14:34:43,235][INFO ][logstash.javapipeline ][.monitoring-logstash] Pipeline started {"pipeline.id"=>".monitoring-logstash"}
logstash_1 | [2020-11-24T14:34:43,550][INFO ][logstash.agent ] Successfully started Logstash API endpoint {:port=>9600}
logstash_1 | [2020-11-24T14:34:45,494][INFO ][logstash.javapipeline ] Pipeline terminated {"pipeline.id"=>".monitoring-logstash"}
logstash_1 | [2020-11-24T14:34:45,589][INFO ][logstash.runner ] Logstash shut down.
logstash_1 | OpenJDK 64-Bit Server VM warning: Option UseConcMarkSweepGC was deprecated in version 9.0 and will likely be removed in a future release.

[Charakterny]

Any tips how to install properly jdbc ?

[Charakterny]

Ok I downloaded jdbc for postgres, wrote logstash.conf but still won't work.

My config of logstash.conf:
input {
jdbc {
jdbc_validate_connection => "true"
jdbc_connection_string => "jdbc:postgresql://x.x.x.x:xxxx/xxxx"
jdbc_driver_library => "/usr/share/postgresql-42.2.18.jar"
jdbc_user => "xxx"
jdbc_password => "xxxxx"
jdbc_driver_class => "org.postgresql.Driver"
statement => "SELECT count(1) FROM sale_or WHERE state = 'sale';"
}
}
output {
elasticsearch {
hosts => "http://elasticsearch:xxxx"
index => "jdbc"
user => "xxxx"
password => "xxxxxxx"
}
}

Logs from container:
[2020-12-02T12:37:04,859][INFO ][logstash.monitoring.internalpipelinesource] Monitoring License OK
[2020-12-02T12:37:04,861][INFO ][logstash.monitoring.internalpipelinesource] Validated license for monitoring. Enabling monitoring pipeline.
[2020-12-02T12:37:07,225][INFO ][org.reflections.Reflections] Reflections took 224 ms to scan 1 urls, producing 22 keys and 45 values
[2020-12-02T12:37:07,292][ERROR][logstash.agent ] Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:main, :exception=>"LogStash::ConfigurationError", :message=>"Expected one of [ \t\r\n], "#", [A-Za-z0-9_-], '"', "'", [A-Za-z_], "-", [0-9], "[", "{" at line 19, column 36 (byte 269) after input {\n jdbc {\n jdbc_connection_string => ", :backtrace=>["/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:32:in compile_imperative'", "org/logstash/execution/AbstractPipelineExt.java:183:in initialize'", "org/logstash/execution/JavaBasePipelineExt.java:69:in initialize'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:44:in initialize'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline_action/create.rb:52:in execute'", "/usr/share/logstash/logstash-core/lib/logstash/agent.rb:357:in block in converge_state'"]}

OpenJDK 64-Bit Server VM warning: Option UseConcMarkSweepGC was deprecated in version 9.0 and will likely be removed in a future release.

Any ideas ?