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

Viewed 5014

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:in execute_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:in block 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 }
}
1 Answers

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.

Related