How to connect postgres database with logstash from JDBC to import data?

Question

I'm trying to connect PostgreSQL database with Logstash to import data from postgres to elasticsearch. I'm using JDBC driver to connect Logstash with postgres.

But i'm getting following error

[2019-06-27T13:04:05,943][ERROR][logstash.javapipeline ] A plugin had an unrecoverable error. Will restart this plugin.
Pipeline_id:main Plugin: "postgres", jdbc_password=>, statement=>"SELECT * FROM public.\"contacts\";", jdbc_driver_library=>"postgresql-42.2.6.jar", jdbc_connection_string=>"jd bc:postgresql://localhost:5432/LogstashTest", id=>"a76a604bb9cb591dd4a19afc95e03873023e008c564101b4ac19aefe30071213", jdbc_driver_class=>"org.postgresql.Driver", enable_metric=>true, codec=>"plain_8f80bf3a-29fe-49e8-86b1-c94e9a298ffb", enable_metric=>true, charset=>"UTF-8">, jdbc_paging_enabled=>false, jdbc_page_size=>100000, jdbc_validate_connection=>false, jdbc_validation_timeout=

3600, jdbc_pool_timeout=>5, sql_log_level=>"info", connection_retry_attempts=>1, connection_retry_attempts_wait_time=>0.5, parameters=>{"sql_last_value"=>1970-01-01 00:00:00 UTC}, last_run_metadata_path=>"C :\Users\roshan/.logstash_jdbc_last_run", use_column_value=>false, tracking_column_type=>"numeric", clean_run=>false, record_last_run=>true, lowercase_column_names=>true> Error: org.postgresql.Driver not loaded. Are you sure you've included the correct jdbc driver in :jdbc_driver_library? Exception: LogStash::ConfigurationError Stack: D:/Swares/logstash-7.2.0/logstash-7.2.0/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/plugin_mixins/jdbc/jdbc.rb:163:in open_jdbc_connection' D:/Swares/logstash-7.2.0/logstash-7.2.0/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/plugin_mixins/jdbc/jdbc.rb:221:inexecute_statement' D:/Swares/logstash-7.2.0/logstash-7.2.0/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/inputs/jdbc.rb:277:in execute_query' D:/Swares/logstash-7.2.0/logstash-7.2.0/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/inputs/jdbc.rb:263:inrun' D:/Swares/logstash-7.2.0/logstash-7.2.0/logstash-core/lib/logstash/java_pipeline.rb:309:in inputworker' D:/Swares/logstash-7.2.0/logstash-7.2.0/logstash-core/lib/logstash/java_pipeline.rb:302:inblock in start_input' [2019-06-27T13:04:06,946][ERROR][logstash.inputs.jdbc ] Failed to load postgresql-42.2.6.jar {:exception=>#}

My configurations are

Java version - "1.8.0_211"
postgres (PostgreSQL) 11.0
logstash-7.2.0

And here is my logstash conf file

input {
    jdbc{
        #input configuration
        jdbc_driver_library => "postgresql-42.2.6.jar"
        jdbc_driver_class => "org.postgresql.Driver"
        jdbc_connection_string => "jdbc:postgresql://localhost:5432/LogstashTest"
        jdbc_user => "postgres"
        jdbc_password => "root"
        statement => 'SELECT  * FROM public."contacts";'
    }
}
output{
    stdout { codec => json_lines }
}

"Failed to load postgresql-42.2.6.jar", it sounds like either postgresql-42.2.6.jar is missing, or it isn't in the location expected by logstash. — Mark Rotteveel
Thanks for asking. I had an issue as was not using "" for username and password. — Raj Rajeshwar Singh Rathore

Andrey Andrey · Accepted Answer · 2020-03-30T05:52:14

The problem can occur due to corrupted PostgreSQL JDBC driver when you passing incorrect config file to Logstash. I had the same issue, you need to check your JDBC driver file.

How to connect postgres database with logstash from JDBC to import data?

1 Answers