# Jdbc plugin for postgresql issue

**URL:** <https://discuss.elastic.co/t/jdbc-plugin-for-postgresql-issue/141065>\
**Category:** Logstash\
**Created:** [July 23, 2018, 3:15am UTC](https://discuss.elastic.co/t/jdbc-plugin-for-postgresql-issue/141065 "2018-07-23T03:15:10Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Harman\_Gurm](https://avatars.discourse-cdn.com/v4/letter/h/6de8d8/32.png) [@Harman\_Gurm](https://discuss.elastic.co/u/Harman_Gurm)\
**Post date:** [July 23, 2018, 3:15am UTC](https://discuss.elastic.co/t/jdbc-plugin-for-postgresql-issue/141065/1 "2018-07-23T03:15:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\
**Post date:** [July 23, 2018, 10:38am UTC](https://discuss.elastic.co/t/jdbc-plugin-for-postgresql-issue/141065/2 "2018-07-23T10:38:26Z")

</div>

> clean\_run =\> true

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

---

<div class="post-metadata">

**Author:** ![Harman\_Gurm](https://avatars.discourse-cdn.com/v4/letter/h/6de8d8/32.png) [@Harman\_Gurm](https://discuss.elastic.co/u/Harman_Gurm)\
**Post date:** [July 23, 2018, 1:50pm UTC](https://discuss.elastic.co/t/jdbc-plugin-for-postgresql-issue/141065/3 "2018-07-23T13:50:34Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\
**Post date:** [July 23, 2018, 8:34pm UTC](https://discuss.elastic.co/t/jdbc-plugin-for-postgresql-issue/141065/4 "2018-07-23T20:34:29Z")

</div>

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

```auto
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.

```auto
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'

```

---

<div class="post-metadata">

**Author:** ![Harman\_Gurm](https://avatars.discourse-cdn.com/v4/letter/h/6de8d8/32.png) [@Harman\_Gurm](https://discuss.elastic.co/u/Harman_Gurm)\
**Post date:** [July 23, 2018, 8:36pm UTC](https://discuss.elastic.co/t/jdbc-plugin-for-postgresql-issue/141065/5 "2018-07-23T20:36:55Z")

</div>

Thanks for help, Yes it worked. Appreciate your time

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 20, 2018, 8:36pm UTC](https://discuss.elastic.co/t/jdbc-plugin-for-postgresql-issue/141065/6 "2018-08-20T20:36:59Z")

</div>

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