# Logstash delta pull from postgres **URL:** \ **Category:** Logstash\ **Created:** [June 24, 2020, 2:38pm UTC](https://discuss.elastic.co/t/logstash-delta-pull-from-postgres/238501 "2020-06-24T14:38:32Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![tenet\_testuser1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tenet_testuser1/32/67526_2.png) [@tenet\_testuser1](https://discuss.elastic.co/u/tenet_testuser1)\ **Post date:** [June 24, 2020, 2:38pm UTC](https://discuss.elastic.co/t/logstash-delta-pull-from-postgres/238501/1 "2020-06-24T14:38:32Z")
I have a simple logstash pipeline job that is a SQL job that pulls data from a postgres db into ES. The initial pull was 1.5 million rows, which ran last night and took about 5 minutes. but the delta daily pull is about 4000 rows (4K) I expected the delta pull be just a few seconds.. however, today's run also took about 5 minutes. I am wondering why? I hope it's just pulling delta data... if so, why is it taking almost the same time as pull all the data? Thanks ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [June 24, 2020, 2:40pm UTC](https://discuss.elastic.co/t/logstash-delta-pull-from-postgres/238501/2 "2020-06-24T14:40:34Z")
Maybe it is pulling all of the data again. What does the configuration look like? ---
**Author:** ![tenet\_testuser1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tenet_testuser1/32/67526_2.png) [@tenet\_testuser1](https://discuss.elastic.co/u/tenet_testuser1)\ **Post date:** [June 25, 2020, 10:55pm UTC](https://discuss.elastic.co/t/logstash-delta-pull-from-postgres/238501/3 "2020-06-25T22:55:49Z")
I have pasted the sample from 7.8 doc. In my case, I have to filter for delta data based on multiple columns, this example shows just one. How do I go about setting the where clause, and the tracking\_column? input { jdbc { statement =\> "SELECT id, mycolumn1, mycolumn2 FROM my\_table WHERE id \> :sql\_last\_value" use\_column\_value =\> true tracking\_column =\> "id" # ... other configuration bits } } ---
**Author:** ![tenet\_testuser1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tenet_testuser1/32/67526_2.png) [@tenet\_testuser1](https://discuss.elastic.co/u/tenet_testuser1)\ **Post date:** [June 25, 2020, 11:34pm UTC](https://discuss.elastic.co/t/logstash-delta-pull-from-postgres/238501/4 "2020-06-25T23:34:53Z")
I tried this, but I do not think it worked well. my pipeline configuration... Note that I have commented the --'2019-04-15' portion after '?' statement=\>"select initcap(b.company\_name) company\_name, a.symbol,a.day\_close,a.day\_high,a.day\_low, a.trade\_date,a.rs,a.high\_52week,a.low\_52week from stock\_daily\_data a,company\_listing b where a.trade\_date\> '?' --'2019-04-15' and a.symbol=b.symbol" use\_column\_value =\> true tracking\_column =\> "a.trade\_date" prepared\_statement\_bind\_values =\> [":sql\_last\_value"] prepared\_statement\_name =\> "sbsectorsymbolonee" use\_prepared\_statements =\> true tracking\_column\_type =\> "timestamp" last\_run\_metadata\_path =\> "/dockermnt/logstash/lastvalues/xx.yml" logstash log snippet 2020-06-25T23:11:59.023551191Z [2020-06-25T23:11:59,021][INFO][logstash.inputs.jdbc][sector\_symbol\_one][c564e52a52c7396ffa0533a145f2e4d228e32c50e285fcef8a7f2c04cc23171c] (0.056661s) PREPARE xx: select initcap(b.company\_name) company\_name, a.symbol,a.day\_close,a.day\_high,a.day\_low, 2020-06-25T23:11:59.023648903Z a.trade\_date,a.rs,a.high\_52week,a.low\_52week 2020-06-25T23:11:59.023760102Z from stock\_daily\_data a,company\_listing b where a.trade\_date\> '?' --'2019-04-15' 2020-06-25T23:11:59.023774379Z and a.symbol=b.symbol 2020-06-25T23:11:59.204127319Z [2020-06-25T23:11:59,203][WARN][logstash.inputs.jdbc][sector\_symbol\_one][c564e52a52c7396ffa0533a145f2e4d228e32c50e285fcef8a7f2c04cc23171c] Exception when executing JDBC query {:exception=\>org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.} ---
**Author:** ![tenet\_testuser1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tenet_testuser1/32/67526_2.png) [@tenet\_testuser1](https://discuss.elastic.co/u/tenet_testuser1)\ **Post date:** [June 26, 2020, 12:41am UTC](https://discuss.elastic.co/t/logstash-delta-pull-from-postgres/238501/5 "2020-06-26T00:41:43Z")
I tried a few other changes to the cfg file, I was not successful. I am either getting the postgres index error, or logstash is running out of heap, OOM. Please let me know, how to execute a sql with multiple table join, with multiple filter predicates and perform delta pulls. ---
**Author:** ![tenet\_testuser1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tenet_testuser1/32/67526_2.png) [@tenet\_testuser1](https://discuss.elastic.co/u/tenet_testuser1)\ **Post date:** [June 27, 2020, 4:15am UTC](https://discuss.elastic.co/t/logstash-delta-pull-from-postgres/238501/6 "2020-06-27T04:15:16Z")
For anyone that might stumbe upon this issue and come here searching for answers.... I was able to get it working. Surprisingly, (after 2 days), it seems pretty simple. here is my logstash config snippet that made the difference.... 1. set the timezone to UTC 2. pay close attention to the SQL statement 3. First run, to get all the data, set clean\_run = true. Once the first run is complete, remove this directive. 4. Order by the date column is VERY IMPORTANT. jdbc\_default\_timezone=\> "UTC" statement=\>"select a.trade\_date,a.low\_52week from stock\_daily\_data a,company\_listing b where a.trade\_date\> :sql\_last\_value and a.symbol=b.symbol order by trade\_date" use\_column\_value =\> true tracking\_column =\> "trade\_date" tracking\_column\_type =\> "timestamp" ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 25, 2020, 4:15am UTC](https://discuss.elastic.co/t/logstash-delta-pull-from-postgres/238501/7 "2020-07-25T04:15:28Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.