Header image

Syncing PostgreSQL Data to Elasticsearch using Logstash in Ubuntu 20.04

July 17, 2020Uncategorized

If logstash/elasticsearch in not installed refer https://flapicon.io/installation-of-elasticsearch-logstash-and-kibana-elk-stack-on-ubuntu-20-04/

Step 1: Install JDBC Plugin

/usr/share/logstash/bin/logstash-plugin install logstash-input-jdbc

Step 2: Download JDBC library

cd /usr/share/logstash/logstash-core/lib/jars
wget https://jdbc.postgresql.org/download/postgresql-42.2.14.jar

Step 3: Edit config to use escape characters in our postgresql queries in logstash.

nano /etc/logstash/logstash.yml

Add/Edit the following config

config.support_escapes: true

Step 4: Create logstash pipeline file (Table name is institutes, therefore institutesync.conf)

nano /etc/logstash/conf.d/institutesync.conf

Add the following content

input {
  jdbc {
     jdbc_connection_string => "jdbc:postgresql://ip_address:port/databasename"
     jdbc_user => "username"
     jdbc_password => "password"
     jdbc_driver_class => "org.postgresql.Driver"
     statement => "SELECT * from public.\"institutes\""
output {
  elasticsearch {
    hosts => ["localhost:9200"]
    index => "institutes"
    document_id => "institutes_%{id}"
    doc_as_upsert => true
    #user => "elastic"
    #password => "Password"

Note username and password is required if xpack is enabled

Step 5: Add pipeline for above configuration

Open pipeline file

nano /etc/logstash/pipelines.yml

Add the following

- pipeline.id: institute-pipeline
  path.config: "/etc/logstash/conf.d/institutesync.conf"

Step 6: Restart Logstash

systemctl restart logstash

Step 7: Check status after some time

systemctl status logstash

Leave A Comment

Your email is safe with us.