# Logstash update elastic search when any update in a table

**URL:** <https://discuss.elastic.co/t/logstash-update-elastic-search-when-any-update-in-a-table/306133>\
**Category:** Logstash\
**Created:** [June 1, 2022, 1:17pm UTC](https://discuss.elastic.co/t/logstash-update-elastic-search-when-any-update-in-a-table/306133 "2022-06-01T13:17:01Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![ABHISHEK\_KUMAR\_SINGH](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abhishek_kumar_singh/32/97675_2.png) [@ABHISHEK\_KUMAR\_SINGH](https://discuss.elastic.co/u/ABHISHEK_KUMAR_SINGH)\
**Post date:** [June 1, 2022, 1:17pm UTC](https://discuss.elastic.co/t/logstash-update-elastic-search-when-any-update-in-a-table/306133/1 "2022-06-01T13:17:01Z")

</div>

Hello, I have a Users table in my DB and I have successfully synced my logstash to update new records. But I want a way to update the existing records on Elasticsearch.  
I don't have any timestamp column in the table to maintain updated\_on.

Here's the config till now with inserting new data in elastic :

```auto
input {
  jdbc {
     jdbc_connection_string => "jdbc:postgresql://localhost:5432/litmusblox"
     jdbc_user => "postgres"
     jdbc_password => "password"
     jdbc_driver_class => "org.postgresql.Driver"
     statement => "SELECT * from users where id > :sql_last_value"
     use_column_value => true
     tracking_column => "id"
     last_run_metadata_path => "/usr/share/logstash/jdbc-lib-manual/.logstash_jdbc_last_run"
     jdbc_paging_enabled => true
     tracking_column_type => "numeric"
     schedule => "*/30 * * * * *"
 }
}
output {
  elasticsearch {
    cloud_id => "Test-deployment:xyz"
    cloud_auth => "elastic:pass"
    index => "users"
    document_id => "users_%{id}"
    doc_as_upsert => true
    #user => "elastic"
    #password =>"password"
 }
}

```

---

<div class="post-metadata">

**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\
**Post date:** [June 1, 2022, 2:51pm UTC](https://discuss.elastic.co/t/logstash-update-elastic-search-when-any-update-in-a-table/306133/2 "2022-06-01T14:51:06Z")

</div>

I can see two way to do this.  
you refresh whole table every-time you sync because you don't have anyway to track which record in postgresql is changed. "select \* from users"

or you add a date field to your table and then you can simple do  
select \* from users where updated\_date \> sysdate-1

---

<div class="post-metadata">

**Author:** ![ABHISHEK\_KUMAR\_SINGH](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abhishek_kumar_singh/32/97675_2.png) [@ABHISHEK\_KUMAR\_SINGH](https://discuss.elastic.co/u/ABHISHEK_KUMAR_SINGH)\
**Post date:** [June 1, 2022, 3:19pm UTC](https://discuss.elastic.co/t/logstash-update-elastic-search-when-any-update-in-a-table/306133/3 "2022-06-01T15:19:15Z")

</div>

refreshing the whole table won't be a good idea as the table size is huge.  
adding timestamp column could be a solution for me. Can we do REST calls through the code whenever there is changes in table? is this a right approach?

---

<div class="post-metadata">

**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\
**Post date:** [June 1, 2022, 6:51pm UTC](https://discuss.elastic.co/t/logstash-update-elastic-search-when-any-update-in-a-table/306133/4 "2022-06-01T18:51:54Z")

</div>

I don't see how you know which record changes in db.

if your table is huge that means you still have to pull all the record from db. and then all record from elasticserach and compare both set. and then update only one which are changed.

Best bet is to add date column in database.

---

<div class="post-metadata">

**Author:** ![ABHISHEK\_KUMAR\_SINGH](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abhishek_kumar_singh/32/97675_2.png) [@ABHISHEK\_KUMAR\_SINGH](https://discuss.elastic.co/u/ABHISHEK_KUMAR_SINGH)\
**Post date:** [June 2, 2022, 4:29am UTC](https://discuss.elastic.co/t/logstash-update-elastic-search-when-any-update-in-a-table/306133/5 "2022-06-02T04:29:19Z")

</div>

So, final requirement is that my Elasticsearch index should get updated with change in table records, can you suggest some other tool by which this is possible if not with logstash? any tool which can syncup with my db and Elasticsearch and replicate the data on Elasticsearch?

---

<div class="post-metadata">

**Author:** ![ABHISHEK\_KUMAR\_SINGH](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abhishek_kumar_singh/32/97675_2.png) [@ABHISHEK\_KUMAR\_SINGH](https://discuss.elastic.co/u/ABHISHEK_KUMAR_SINGH)\
**Post date:** [June 2, 2022, 4:31am UTC](https://discuss.elastic.co/t/logstash-update-elastic-search-when-any-update-in-a-table/306133/6 "2022-06-02T04:31:04Z")

</div>

BTW thank you for the quick response, you are really helping me in the POC.

---

<div class="post-metadata">

**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\
**Post date:** [June 2, 2022, 2:41pm UTC](https://discuss.elastic.co/t/logstash-update-elastic-search-when-any-update-in-a-table/306133/7 "2022-06-02T14:41:36Z")

</div>

what is your POC?

I do have good experience with pulling data from database and keeping them in sync at elk

---

<div class="post-metadata">

**Author:** ![ABHISHEK\_KUMAR\_SINGH](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abhishek_kumar_singh/32/97675_2.png) [@ABHISHEK\_KUMAR\_SINGH](https://discuss.elastic.co/u/ABHISHEK_KUMAR_SINGH)\
**Post date:** [June 2, 2022, 2:56pm UTC](https://discuss.elastic.co/t/logstash-update-elastic-search-when-any-update-in-a-table/306133/8 "2022-06-02T14:56:08Z")

</div>

My POC is to create a connection with the DB(postgres) and update the tables which I have indexed on Elastic whenever there is update, create, deletion of records in DB

---

<div class="post-metadata">

**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\
**Post date:** [June 2, 2022, 3:04pm UTC](https://discuss.elastic.co/t/logstash-update-elastic-search-when-any-update-in-a-table/306133/9 "2022-06-02T15:04:23Z")

</div>

isn't that exactly what you just did?  
first step is to fully sync table

second part is every few min you run query with updated \> sysdate- interval 'x' minute and update only that record on Elasticsearch

---

<div class="post-metadata">

**Author:** ![ABHISHEK\_KUMAR\_SINGH](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abhishek_kumar_singh/32/97675_2.png) [@ABHISHEK\_KUMAR\_SINGH](https://discuss.elastic.co/u/ABHISHEK_KUMAR_SINGH)\
**Post date:** [June 2, 2022, 3:50pm UTC](https://discuss.elastic.co/t/logstash-update-elastic-search-when-any-update-in-a-table/306133/10 "2022-06-02T15:50:14Z")

</div>

But as I mentioned earlier I want to find a way in which I don't need to add the updated\_on column in the table.

---

<div class="post-metadata">

**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\
**Post date:** [June 2, 2022, 4:05pm UTC](https://discuss.elastic.co/t/logstash-update-elastic-search-when-any-update-in-a-table/306133/11 "2022-06-02T16:05:20Z")

</div>

I don't see that is possible.

if you can't have tracking on which record is updated in database how would you only pull that record. think about it? if you find a logic to implement well and good. if not then you need updated\_on column

---

<div class="post-metadata">

**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 2, 2022, 4:32pm UTC](https://discuss.elastic.co/t/logstash-update-elastic-search-when-any-update-in-a-table/306133/12 "2022-06-02T16:32:06Z")

</div>

If your database supports REST calls from stored procedures called by a TRIGGER then you may be able to track updates that way, but that is an SQL question, not a logstash question.

---

<div class="post-metadata">

**Author:** ![ABHISHEK\_KUMAR\_SINGH](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abhishek_kumar_singh/32/97675_2.png) [@ABHISHEK\_KUMAR\_SINGH](https://discuss.elastic.co/u/ABHISHEK_KUMAR_SINGH)\
**Post date:** [June 2, 2022, 4:33pm UTC](https://discuss.elastic.co/t/logstash-update-elastic-search-when-any-update-in-a-table/306133/13 "2022-06-02T16:33:35Z")

</div>

I have read about some tools which we can use, like PGSync , which will do the syncup without keeping record of updated\_on column, it uses WAL file which get's generated by postgres DB, by processing that file it can update ELK. I will try that once.

---

<div class="post-metadata">

**Author:** ![ABHISHEK\_KUMAR\_SINGH](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abhishek_kumar_singh/32/97675_2.png) [@ABHISHEK\_KUMAR\_SINGH](https://discuss.elastic.co/u/ABHISHEK_KUMAR_SINGH)\
**Post date:** [June 2, 2022, 4:34pm UTC](https://discuss.elastic.co/t/logstash-update-elastic-search-when-any-update-in-a-table/306133/14 "2022-06-02T16:34:16Z")

</div>

Sure, will check that.

---

<div class="post-metadata">

**Author:** ![nastasiya09](https://avatars.discourse-cdn.com/v4/letter/n/ecd19e/32.png) [@nastasiya09](https://discuss.elastic.co/u/nastasiya09)\
**Post date:** [June 6, 2022, 9:19am UTC](https://discuss.elastic.co/t/logstash-update-elastic-search-when-any-update-in-a-table/306133/15 "2022-06-06T09:19:13Z")

</div>

I don't see how you know which record changes in db.[.](https://myfiosgateway.one/)[.](https://tutuappvip.co/mobdro-download)

---

<div class="post-metadata">

**Author:** ![ABHISHEK\_KUMAR\_SINGH](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abhishek_kumar_singh/32/97675_2.png) [@ABHISHEK\_KUMAR\_SINGH](https://discuss.elastic.co/u/ABHISHEK_KUMAR_SINGH)\
**Post date:** [June 6, 2022, 9:23am UTC](https://discuss.elastic.co/t/logstash-update-elastic-search-when-any-update-in-a-table/306133/16 "2022-06-06T09:23:45Z")

</div>

> **[PGSync](https://pgsync.com/)**
>
> PGSync simplifies your data pipeline by integrating Postgres into Elasticsearch.

---

<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:** [July 4, 2022, 9:24am UTC](https://discuss.elastic.co/t/logstash-update-elastic-search-when-any-update-in-a-table/306133/17 "2022-07-04T09:24:31Z")

</div>

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