How to specify Schema Name in Logstash config input for postgreSQL?

[yash-bhardwaj]

For below config:

{
input {
    jdbc {
	jdbc_validate_connection => true
        jdbc_connection_string => "jdbc:postgresql://127.0.0.1:5433/Wallet_DB"
	jdbc_user => "postgres"
	jdbc_password => "postgres"
	jdbc_driver_library => "D:/softwares/logstash-7.6.0/logstash-core/lib/jars/jdbc-postgresql.jar"
	jdbc_driver_class => "org.postgresql.Driver"
	statement => "SELECT * FROM schema.customer_wallet_details"
    }
}

I'm Getting Error:

[ERROR][logstash.inputs.jdbc     ][main] Java::OrgPostgresqlUtil::PSQLException: ERROR: relation "schema.customer_wallet_details" does not exist: SELECT * FROM schema.customer_wallet_details
[WARN ][logstash.inputs.jdbc     ][main] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::OrgPostgresqlUtil::PSQLException: ERROR: relation "schema.customer_wallet_details" does not exist>}

I saw an example sql for the same in documentation, but it simply doesn't work.

@elasticsearch-bot please guide

[luishenriqueamaral]

You can add parameters => { "currentSchema" => "yourschemaname" }

refs:
https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-parameters

https://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters

[luishenriqueamaral]

It doesn't really work. Even setting the parameter, I believe that the public scheme is always used.

[jacksparrow414]

Use the currentSchema parameter

jdbc_connection_string => "jdbc:postgresql://127.0.0.1:5433/Wallet_DB?currentSchema=yourschemaname"

refs:
https://jdbc.postgresql.org/documentation/use/#connection-parameters