Synchronize PostgreSQL data with Elasticsearch

Use JDBC polling for a straightforward periodic search-index refresh. Use change data capture (CDC), such as Debezium's PostgreSQL connector, when you need a stream of committed row changes and explicit delete events. The choice depends on acceptable lag and the failure cases you must handle.

Poll with Logstash JDBC

This example assumes a PostgreSQL JDBC driver, a dedicated reader account, and a writable state directory. Supply credentials through protected environment or keystore settings. The Elasticsearch output uses HTTPS with a trusted CA; adapt authentication to your deployment.

input {
  jdbc {
    jdbc_connection_string => "jdbc:postgresql://pg:5432/shop"
    jdbc_user => "reader"
    jdbc_password => "${PG_READER_PASSWORD}"
    jdbc_driver_library => "/opt/postgresql.jar"
    jdbc_driver_class => "org.postgresql.Driver"
    statement => "SELECT id, name, updated_at FROM products WHERE updated_at >= :sql_last_value ORDER BY updated_at, id"
    schedule => "*/2 * * * *"
    tracking_column => "updated_at"
    tracking_column_type => "timestamp"
    use_column_value => true
    last_run_metadata_path => "/var/lib/logstash/products-jdbc-state.yml"
  }
}
output {
  elasticsearch {
    hosts => ["https://es:9200"]
    user => "${ES_USER}"
    password => "${ES_PASSWORD}"
    ssl_certificate_authorities => ["/etc/logstash/certs/es-ca.crt"]
    index => "products"
    document_id => "%{id}"
  }
}

The schedule polls every two minutes. A stable document ID replaces the corresponding indexed document when a row is read again. Keep a separate state file for each input and preserve it across restarts.

The inclusive >= boundary deliberately rereads rows sharing the saved timestamp. ORDER BY makes checkpoint progression predictable. This still is not a lossless change stream: a transaction can commit later with an older updated_at, and a timestamp watermark can miss it. An overlap window and periodic reconciliation reduce risk, but cannot guarantee correctness with arbitrarily late commits. Stable IDs prevent duplicate documents; they do not prevent an older event arriving last and overwriting newer content. Design ordering or version protection when that matters.

Polling also cannot observe a row after it has been physically deleted. Use retained soft-delete markers with explicit output handling, or periodically reconcile missing IDs.

Capture committed changes with Debezium

Debezium reads PostgreSQL logical decoding output and emits change events, commonly through Kafka and Kafka Connect. Configure logical WAL, replication privileges, publications, and replication slots for the deployed versions.

An Elasticsearch sink or transformation stage must map keys, values, updates, and delete events into indexing operations. Delete events being present in the stream does not mean Elasticsearch automatically deletes documents. Test the sink's tombstone and deletion behavior.

Monitor replication-slot lag and retained WAL: an unavailable consumer can retain substantial WAL. Plan initial snapshots, restarts, duplicate delivery, schema changes, and recovery. Latency depends on load and the whole pipeline, not a guaranteed number of seconds.

Compare the operating trade-offs

If the table has no update marker, add one with reliable maintenance or rebuild periodically. A trigger timestamp is still not a commit-order guarantee. For large initial loads, partition work deliberately, provision mappings, and consider temporarily increasing Elasticsearch's refresh interval. Restore the intended refresh setting and validate counts and representative records before switching readers.

See the Logstash JDBC input and Debezium PostgreSQL connector.