Jdbc plugin for postgresql issue

[Harman_Gurm]

Hi,

I am using jdbc plugin to fetch data from postgresql db, it seems to be work fine for first time and i am able to pull the data, but it is not working according to saved state, everytime all of data is queried and there are lot of duplicates.

I checked the .logstash_jdbc_last_run. The metadata state is updated as required, still plugin is importing entire data from table on every run. If any thing wrong in config.

input {
jdbc {
jdbc_connection_string => "jdbc:postgresql://x.x.x.x:5432/dodb"
jdbc_user => "myuser"
jdbc_password => "passsword"
jdbc_validate_connection => true
jdbc_driver_library => "/opt/postgresql-9.4.1207.jar"
jdbc_driver_class => "org.postgresql.Driver"
statement => "select id,timestamp,distributed_query_id,distributed_query_task_id, "columns"->>'uid' as uid, "columns"->>'name' as name from distributed_query_result;"
schedule => "* * * * *"
use_column_value => true
tracking_column => "id"
tracking_column_type => "numeric"
clean_run => true

}
}
output {
kafka{
topic_id => "psql-logs"
bootstrap_servers => "x.x.x.x:9092"
codec => "json"
}
}

Any help !! Thanks in advance

[guyboertje]

clean_run => true

This always ignores any state saved from the previous run. Remove the line or set it to false.

[Harman_Gurm]

Thanks for update @guyboertje I removed the clean_run => true, but still it is not running according to the saved state. i have checked again .logstash_jdbc_last_run is saving the last run from specified column.

[guyboertje]

Now you actually have to use the id in your sql statement.
Something like...

statement => "select id,timestamp,distributed_query_id,distributed_query_task_id, \"columns\"->>'uid' as uid, \"columns\"->>'name' as name from distributed_query_result where id > :sql_last_value order by id asc"

I am not sure about the double quotes around columns though.
Maybe use single quotes in the LS config and double quotes around the columns.

statement => 'select id,timestamp,distributed_query_id,distributed_query_task_id, "columns"->>"uid" as uid, "columns"->>"name" as name from distributed_query_result where id > :sql_last_value order by id asc'

[Harman_Gurm]

Thanks for help, Yes it worked. Appreciate your time