Logstash : jdbc - Unable to connect to database

Elastic Stack Logstash
1

this is my file : contact.conf
input {

     jdbc {

                jdbc_driver_library => "/tmp/postgresql-42.2.12.jar"

                jdbc_driver_class => "org.postgresql.Driver"

                jdbc_connection_string => "jdbc:postgresql://34.241.251.3:5432/demo_elastic_connector_v1"

                jdbc_user => "odoo"

                jdbc_password => "xxxx"

                statement => "select * from res_partner"

                jdbc_paging_enabled => "true"

                jdbc_page_size => 50000
            }
        }

output {stdout { codec => json_lines }}

when I try to run this cmd:
/usr/share/logstash/bin/logstash -f contact.conf

OpenJDK 64-Bit Server VM warning: Option UseConcMarkSweepGC was deprecated in version 9.0 and will likely be removed in a future release.
WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by com.headius.backport9.modules.Modules (file:/usr/share/logstash/logstash-core/lib/jars/jruby-complete-9.2.11.1.jar) to method sun.nio.ch.NativeThread.signal(long)
WARNING: Please consider reporting this to the maintainers of com.headius.backport9.modules.Modules
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
WARNING: Could not find logstash.yml which is typically located in $LS_HOME/config or /etc/logstash. You can specify the path using --path.settings. Continuing using the defaults
Could not find log4j2 configuration at path /usr/share/logstash/config/log4j2.properties. Using default config which logs errors to the console
[WARN ] 2020-07-04 19:59:54.415 [LogStash::Runner] multilocal - Ignoring the 'pipelines.yml' file because modules or command line options are specified
[INFO ] 2020-07-04 19:59:54.427 [LogStash::Runner] runner - Starting Logstash {"logstash.version"=>"7.8.0", "jruby.version"=>"jruby 9.2.11.1 (2.5.7) 2020-03-25 b1f55b1a40 OpenJDK 64-Bit Server VM 11.0.7+10-post-Ubuntu-2ubuntu218.04 on 11.0.7+10-post-Ubuntu-2ubuntu218.04 +indy +jit [linux-x86_64]"}
[INFO ] 2020-07-04 19:59:57.058 [Converge PipelineAction::Create<main>] Reflections - Reflections took 72 ms to scan 1 urls, producing 21 keys and 41 values 
[INFO ] 2020-07-04 19:59:57.785 [[main]-pipeline-manager] javapipeline - Starting pipeline {:pipeline_id=>"main", "pipeline.workers"=>2, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>50, "pipeline.max_inflight"=>250, "pipeline.sources"=>["/tmp/contact.conf"], :thread=>"#<Thread:0x47361bf4 run>"}
[INFO ] 2020-07-04 19:59:59.277 [[main]-pipeline-manager] javapipeline - Pipeline started {"pipeline.id"=>"main"}
[INFO ] 2020-07-04 19:59:59.391 [Agent thread] agent - Pipelines running {:count=>1, :running_pipelines=>[:main], :non_running_pipelines=>[]}
[INFO ] 2020-07-04 19:59:59.886 [Api Webserver] agent - Successfully started Logstash API endpoint {:port=>9601}
[ERROR] 2020-07-04 20:00:20.887 [[main]<jdbc] jdbc - Unable to connect to database. Tried 1 times {:error_message=>"Java::OrgPostgresqlUtil::PSQLException: The connection attempt failed."}
[ERROR] 2020-07-04 20:00:20.915 [[main]<jdbc] javapipeline - A plugin had an unrecoverable error. Will restart this plugin.
  Pipeline_id:main
  Plugin: <LogStash::Inputs::Jdbc jdbc_user=>"odoo", jdbc_paging_enabled=>true, jdbc_password=><password>, statement=>"select * from res_partner", jdbc_page_size=>50000, jdbc_driver_library=>"/tmp/postgresql-42.2.12.jar", jdbc_connection_string=>"jdbc:postgresql://34.241.251.3:5432/demo_elastic_connector_v1", id=>"3e5554691d331d2578de4178d1420977bb25cee05b01ec00fd24d89425919117", jdbc_driver_class=>"org.postgresql.Driver", enable_metric=>true, codec=><LogStash::Codecs::Plain id=>"plain_36f92cbc-d45d-474f-9f79-a6cf9f3f6053", enable_metric=>true, charset=>"UTF-8">, 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, plugin_timezone=>"utc", last_run_metadata_path=>"/home/cloud_user/.logstash_jdbc_last_run", use_column_value=>false, tracking_column_type=>"numeric", clean_run=>false, record_last_run=>true, lowercase_column_names=>true, use_prepared_statements=>false>
  Error: Java::OrgPostgresqlUtil::PSQLException: The connection attempt failed.
  Exception: Sequel::DatabaseConnectionError
  Stack: org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(org/postgresql/core/v3/ConnectionFactoryImpl.java:297)
org.postgresql.core.ConnectionFactory.openConnection(org/postgresql/core/ConnectionFactory.java:49)
org.postgresql.jdbc.PgConnection.<init>(org/postgresql/jdbc/PgConnection.java:211)
org.postgresql.Driver.makeConnection(org/postgresql/Driver.java:459)
org.postgresql.Driver.connect(org/postgresql/Driver.java:261)

NB:
when i run this file in my locolhost ,
Logstash and mypsotgress db in localhost it works,
but i have the issue when they are in different servers
i update the config file in postgress

    postgresql.conf
    listen_addresses = '*'
    pg_hba.conf
    host all all 172.17.0.0/32 trust
    host all all 10.0.2.15/32 trust

But i have the same issue.

2

I have connected to an oracle database.

input {
jdbc {
jdbc_validate_connection => true
jdbc_connection_string => "jdbc:oracle:thin:@dboracle:1521/DBTEST"
jdbc_user => "user_monitoring"
jdbc_password => "test.monitoring"
jdbc_driver_library => "/home/oracle/product/11.2.0/dbhome_1/jdbc/lib/ojdbc6.jar"
jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
statement => "select os_username,username,sql_bind from dba_audit_trail where timestamp > :sql_last_value"
schedule => "*/2 * * * *"
type => "oracle_audit"
}
}

Note:

1.- check the path of the driver.
2.- check the permissions of the user who will connect to the database (the USER must be created in your database), to make queries for example a SELECT
3.- check user credentials

3

@xllauca, Thanks for help,
I check all the points, but i always have the same pb.

root@salhi093c:/tmp# ls -al |grep postgresql-42.2.12.jar 
-rwxrwxrwx  1 logstash   logstash   930498 Jul  6 18:14 postgresql-42.2.12.jar
root@salhi093c:/tmp#

  1. && 3)

             sudo -u odoo  psql demo_elastic_connector_v1
         psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
         Type "help" for help.

         demo_elastic_connector_v1=# select count(*) from res_partner;
          count 
         -------
             40
         (1 row)

 demo_elastic_connector_v1=# \list
                                       List of databases
            Name            |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
 ---------------------------+----------+----------+---------+---------+-----------------------
  demo_elastic_connector_v1 | odoo     | UTF8     | C       | C.UTF-8 |
4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.