# Update deleted RDBMS entries in Elasticsearch using Logstash jdbc input plugin

**URL:** <https://discuss.elastic.co/t/update-deleted-rdbms-entries-in-elasticsearch-using-logstash-jdbc-input-plugin/282803>\
**Category:** Logstash\
**Created:** [August 30, 2021, 11:18am UTC](https://discuss.elastic.co/t/update-deleted-rdbms-entries-in-elasticsearch-using-logstash-jdbc-input-plugin/282803 "2021-08-30T11:18:44Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![RRSR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rrsr/32/27766_2.png) [@RRSR](https://discuss.elastic.co/u/RRSR)\
**Post date:** [August 30, 2021, 11:18am UTC](https://discuss.elastic.co/t/update-deleted-rdbms-entries-in-elasticsearch-using-logstash-jdbc-input-plugin/282803/1 "2021-08-30T11:18:44Z")

</div>

I am trying to sync the RDBMS (`postgres`) data to `Elasticsearch` using `Logstash` and it works fine.  
`Logstash` configuration file:

```auto
input {
  jdbc {
    jdbc_driver_library => "/home/raj/Downloads/postgresql-42.2.23.jar"
     jdbc_driver_class => "org.postgresql.Driver"
    jdbc_connection_string => "jdbc:postgresql://localhost:5432/local_test"
    jdbc_user => "postgres"
    jdbc_password => ""
    jdbc_paging_enabled => true
    tracking_column => "unix_ts_in_secs"
    use_column_value => true
    tracking_column_type => "numeric"
    schedule => "*/5 * * * * *"
    statement => "SELECT *, extract(epoch FROM modification_time) AS unix_ts_in_secs FROM es.es_table WHERE (extract(epoch FROM modification_time)) > :sql_last_value AND modification_time < NOW() ORDER BY modification_time ASC"
  }
}
filter {
  mutate {
    copy => { "id" => "[@metadata][_id]"}
    remove_field => ["id", "@version", "unix_ts_in_secs"]
  }
}
output {
  # stdout { codec => "rubydebug"}
  elasticsearch {
      index => "rdbms_sync_idx"
      document_id => "%{[@metadata][_id]}"
  }
}

```

Now if any record is deleted in `postgres` then to update the same in `Elasticsearch` trying to follow the first approach:

> ## What about deleted documents?
> 
> The astute reader may have noticed that if a document is deleted from MySQL, then that deletion will not be propagated to Elasticsearch. The following approaches may be considered to address this issue:
> 
> 1. MySQL records could include an "is\_deleted" field, which indicates that they are no longer valid. This is known as a "soft delete". As with any other update to a record in MySQL, the "is\_deleted" field will be propagated to Elasticsearch through Logstash. If this approach is implemented, then Elasticsearch and MySQL queries would need to be written so as to exclude records/documents where "is\_deleted" is true. Eventually, background jobs can remove such documents from both MySQL and Elastic.
> 2. Another alternative is to ensure that any system that is responsible for deletion of records in MySQL must also subsequently execute a command to directly delete the corresponding documents from Elasticsearch.

Link: [How to keep Elasticsearch synchronized with a relational database using Logstash and JDBC](https://www.elastic.co/blog/how-to-keep-elasticsearch-synchronized-with-a-relational-database-using-logstash)

Now how to check if the record was deleted in `postgres` or not and how to get the updated value for `is_deleted` and update the same in `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:** [September 27, 2021, 11:19am UTC](https://discuss.elastic.co/t/update-deleted-rdbms-entries-in-elasticsearch-using-logstash-jdbc-input-plugin/282803/2 "2021-09-27T11:19:21Z")

</div>

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