Postgres to elasticsearch

postgres-to-elastic-search.md

Logstash Postgres to Elastic

input {
    jdbc {
        jdbc_connection_string => "jdbc:postgresql://127.0.0.1:5432/db"
        jdbc_user => "username"
        jdbc_password => "password"
        jdbc_driver_library => "/Users/user/JDBC/postgresql-42.2.2.jar"
        jdbc_driver_class => "org.postgresql.Driver"
        statement => 'SELECT id, first_name, last_name, email from users'
        schedule => "* * * * *"
    }
}
output {
    elasticsearch {
        index => "users"
        document_type => "user"
        document_id => "%{id}"
    }
}

Export as json from postgres

Convert to absolute path

copy (
  SELECT array_to_json(array_agg(t)) 
  FROM (SELECT * FROM users) t
) to '~/Downloads/users-export.json'

Convert json to put request data

Using json-to-es-bulk convert json array data to elastic search index data

node index.js -f ~/Downloads/users-export.json --index users --type user 

Outputs to request-data.txt

Pump into elastic search

curl -H 'Content-Type: application/json' -XPUT 'localhost:9200/_bulk?pretty' --data-binary @request-data.txt

Tutorial

https://www.elastic.co/blog/logstash-jdbc-input-plugin