PostgreSQL to Elasticsearch: 3 Ways to Sync Data in Real Time

PostgreSQL can be synchronized with Elasticsearch by capturing database changes and applying them to Elasticsearch indexes. The main approaches are log-based CDC using PostgreSQL’s WAL, tools such as PGSync, or query-based polling with Logstash JDBC.

For continuously changing production data, log-based CDC is usually the better fit because inserts, updates, and deletes can be propagated without repeatedly querying source tables. Estuary uses PostgreSQL logical replication to capture changes from the WAL and continuously materialize them into Elasticsearch.

In this guide, we’ll cover three popular ways to stream your data.

How does PostgreSQL to Elasticsearch synchronization work?

For real-time synchronization, the typical architecture is:

PostgreSQL → WAL → CDC connector → change stream → Elasticsearch index

1. PostgreSQL records inserts, updates, and deletes in the write-ahead log.
2. A CDC connector reads those changes through logical replication.
3. Changes are converted into events or documents.
4. The pipeline maps the relational records into Elasticsearch documents.
5. Elasticsearch indexes are updated as PostgreSQL changes.

For existing tables, most production pipelines also perform an initial backfill before switching to continuous CDC.

Which PostgreSQL to Elasticsearch Method Should You Use?

Choose based on how you want to capture changes and how much infrastructure you want to manage:

1. Estuary: best suited to managed, continuous PostgreSQL CDC with minimal pipeline infrastructure to operate.
2. PGSync: suited to teams that want an open-source, PostgreSQL-specific synchronization tool and are comfortable self-managing it.
3. Logstash JDBC: suited to periodic synchronization when you already use the Elastic Stack and polling is acceptable.
4. Debezium + Kafka: suited to teams that already operate Kafka or need PostgreSQL change events for multiple downstream consumers.

For continuously changing data, log-based CDC is generally a better fit than polling because inserts, updates, and deletes can be captured from PostgreSQL's WAL as they occur.

Method 1: Fully Managed Postgres to Elastic via Estuary

Estuary is a real-time data integration platform for CDC, streaming, and batch data movement. For PostgreSQL, Estuary uses logical replication to capture inserts, updates, and deletes from the write-ahead log (WAL) and continuously deliver those changes to destinations such as Elasticsearch.

For PostgreSQL-to-Elasticsearch pipelines, Estuary supports initial backfills followed by ongoing change data capture, so existing records can be loaded first and new changes can continue flowing without repeatedly querying source tables. Estuary supports low-latency pipelines, with end-to-end latency that can be under 100 ms depending on the source, destination, configuration, and workload.

Set Up PostgreSQL to Elasticsearch with Estuary

In the Estuary dashboard:

1. Go to Sources and select New Capture.
2. Choose the PostgreSQL connector.
3. Give the capture a unique name.
4. Enter the PostgreSQL connection details, including the database address and credentials.
5. Click Next and select the tables you want to capture.
6. Click Save and Publish.

Estuary creates collections from the selected PostgreSQL tables. These collections contain the captured data that can be sent to Elasticsearch or reused by other downstream pipelines.

Step 3: Create the Elasticsearch Materialization

Once the PostgreSQL capture is running:

1. Click Materialize from the capture, or go to Destinations and create a new materialization.
2. Select the Elasticsearch connector.
3. Give the materialization a unique name.
4. Enter your Elasticsearch cluster endpoint and authentication credentials.
5. Select the Estuary collections you want to send to Elasticsearch.
6. Map each collection to its target Elasticsearch index.
7. Click Next to test the configuration.
8. Click Save and Publish.

The Elasticsearch connector supports authentication with either a username and password or an API key. The Elasticsearch role also needs the required permissions to read, write, inspect metadata, and create the target indexes.

Step 4: Backfill Existing Data and Continue with CDC

When the pipeline starts, existing PostgreSQL data can be backfilled into Elasticsearch. After the initial load, Estuary continues capturing new PostgreSQL changes and materializing them into the corresponding Elasticsearch indexes.

This gives you one pipeline for both the initial dataset and ongoing CDC rather than maintaining separate batch and real-time synchronization processes.

Visit our documentation for more details on building pipelines in Estuary.

Estuary is a good fit when:

• Elasticsearch must stay continuously synchronized with PostgreSQL.
• You need WAL-based CDC rather than repeated source queries.
• Inserts, updates, and deletes must be propagated.
• You need an initial backfill plus ongoing changes.
• You don't want to operate Kafka, Debezium, or custom CDC infrastructure.
• You want to reuse the captured PostgreSQL stream for additional destinations.

Consider another approach when:

• synchronization is occasional rather than continuous;
• your team specifically wants to operate an entirely open-source pipeline;
• you already operate Logstash and scheduled polling meets your freshness requirements.

Method 2: PGSync – Open Source Project for Postgres to Elastic

PGSync is an open-source project for the continuous capture of data from Postgres to Elasticsearch. 

PGSync uses PostgreSQL logical decoding to capture change events from the write-ahead log.

If you modify the configuration file in Postgres to enable logical decoding, PGSync can consume the change events from the Postgres write-ahead log. 

After you define a schema file for the resulting document, your captured change events will be transformed by PGSync’s query builder from relational data into the structured document format that Elasticsearch requires.

PGSync requirements

PGSync is an open-source tool designed specifically to synchronize PostgreSQL data with Elasticsearch or OpenSearch. It uses PostgreSQL logical decoding to detect database changes and can map relational PostgreSQL data into document structures suitable for search indexes.

A typical PGSync deployment requires:

• PostgreSQL with the required logical-replication configuration
• Elasticsearch or OpenSearch as the search destination
• Redis, which PGSync uses as part of its synchronization architecture
• A supported Python environment
• A PGSync schema configuration that defines how PostgreSQL tables and relationships should be represented in the destination index

Because supported versions and dependencies can change over time, check the current PGSync documentation before deployment rather than relying on version numbers embedded in this guide.

Steps

1. Open the postgresql.conf file in the data directory, usually located in the directory etc/postgresql/[version]/main/.
   
2. Set wal_level=logical
   
3. Create a replication slot by running 
   
   SELECT * FROM pg_create_logical_replication_slot('slot_name', 'plugin');
   
4. Install PGSync pip install pgsync.
   
5. Create a schema.json that will match the expected document representation in Elasticsearch.
   
6. Run as a daemon pgsync --config schema.json -d.

When is PGSync a good fit?

PGSync can be a good option when you want an open-source, PostgreSQL-specific solution and are comfortable managing the synchronization infrastructure yourself.

Advantages of PGSync

• Open source: PGSync can be self-hosted and customized for your own environment.
• Built specifically for PostgreSQL and search indexes: It focuses on synchronizing PostgreSQL data with Elasticsearch or OpenSearch rather than supporting a broad range of unrelated sources and destinations.
• Uses PostgreSQL change data: PGSync can use PostgreSQL logical decoding to detect database changes instead of relying only on repeated full-table queries.
• Supports relational-to-document mapping: PostgreSQL tables and relationships can be mapped into the nested document structures commonly used in Elasticsearch.

Things to consider

• You operate the infrastructure: Deployment, upgrades, scaling, monitoring, and recovery are your team's responsibility.
• Additional components are required: A production deployment typically includes PostgreSQL, PGSync, Redis, and Elasticsearch or OpenSearch.
• Initial synchronization can be resource intensive: Large initial loads should be planned carefully so they do not overwhelm the destination cluster.
• It is specialized: PGSync is well suited to PostgreSQL-to-Elasticsearch/OpenSearch synchronization, but it is less appropriate when you need one pipeline platform to support many different sources and destinations.

PGSync therefore makes the most sense for teams that specifically want a self-managed PostgreSQL-to-search synchronization stack and are comfortable owning its operation.

Method 3: Logstash JDBC plugin for Postgres to Elasticsearch

Logstash can move data from PostgreSQL to Elasticsearch using its JDBC input plugin. Unlike WAL-based CDC, however, Logstash JDBC works by periodically querying PostgreSQL for new or modified rows.

Elastic documents this pattern for relational databases, including PostgreSQL-compatible JDBC sources. The JDBC input plugin can run SQL queries on a schedule and use sql_last_value to remember the last processed value between runs.

A typical architecture looks like this:

PostgreSQL → JDBC query → Logstash → Elasticsearch

This approach can work well if your team already uses the Elastic Stack and periodic synchronization is sufficient. For workloads that require continuous inserts, updates, and deletes with minimal source-database querying, WAL-based CDC is usually a better fit.

How Logstash JDBC Sync Works

The JDBC plugin periodically runs a SQL query against PostgreSQL.

To avoid rereading every row on each run, the pipeline typically tracks a timestamp or incrementing column. Logstash stores the latest processed value in sql_last_value and uses it as the starting point for the next query.

For example, a query can select only rows modified after the previous Logstash run:

sql
SELECT *
FROM customers
WHERE updated_at > :sql_last_value
ORDER BY updated_at;

The polling frequency is controlled by the Logstash schedule. For example, you might run the query every minute or every few minutes depending on your freshness requirements and database load.

Prerequisites

To use Logstash JDBC with PostgreSQL, you need:

• A running PostgreSQL database
• Logstash
• The Logstash JDBC input plugin
• The PostgreSQL JDBC driver
• An Elasticsearch deployment

The Logstash JDBC plugin does not include database JDBC drivers by default, so the PostgreSQL JDBC driver must be installed separately and referenced in the pipeline configuration.

Step 1: Install the PostgreSQL JDBC Driver

Then reference the driver in your Logstash configuration:

ruby
jdbc_driver_library => "/path/to/postgresql-driver.jar"
jdbc_driver_class => "org.postgresql.Driver"

Step 2: Add a Change-Tracking Column

For incremental synchronization, PostgreSQL rows should have a reliable field that Logstash can use to determine what changed since the last poll.

A common pattern is:

plaintext
updated_at TIMESTAMP

Whenever a row changes, updated_at should also change.

You can then configure Logstash to use this value as its tracking column.

Step 3: Create the Logstash JDBC Pipeline

A simplified configuration might look like this:

ruby
input {
  jdbc {
    jdbc_driver_library => "/path/to/postgresql-driver.jar"
    jdbc_driver_class => "org.postgresql.Driver"

    jdbc_connection_string => "jdbc:postgresql://postgres-host:5432/database"
    jdbc_user => "username"
    jdbc_password => "password"

    schedule => "* * * * *"

    use_column_value => true
    tracking_column => "updated_at"
    tracking_column_type => "timestamp"

    statement => "
      SELECT *
      FROM customers
      WHERE updated_at > :sql_last_value
      ORDER BY updated_at
    "
  }
}

filter {
  mutate {
    copy => { "id" => "[@metadata][_id]" }
  }
}

output {
  elasticsearch {
    hosts => ["<https://your-elasticsearch-host>"]
    index => "customers"
    document_id => "%{[@metadata][_id]}"
  }
}

In this example, Logstash:

1. Queries PostgreSQL on a schedule.
2. Selects records modified after the last processed value.
3. Converts each returned row into a Logstash event.
4. Sends the event to Elasticsearch.
5. Uses the PostgreSQL record ID as the Elasticsearch document ID.

Using a stable document ID is important because it allows later PostgreSQL updates to modify the same Elasticsearch document instead of creating duplicates.

Elastic recommends maintaining state between JDBC runs with sql_last_value, which is persisted in a metadata file and reused the next time the query executes.

Advantages of Logstash JDBC

• Works well with the Elastic Stack: It can be a practical choice if Logstash is already part of your infrastructure.
• Flexible SQL queries: You control exactly what PostgreSQL data is selected.
• Supports transformations: Logstash filters can transform records before indexing them into Elasticsearch.
• Configurable schedules: Pipelines can run every few seconds, every minute, hourly, or on another supported schedule.
• Incremental loading:sql_last_value allows the pipeline to resume from previously processed data rather than querying the entire table every time.

Things to Consider

• It is polling rather than WAL-based CDC: PostgreSQL must execute recurring SQL queries to discover changes.
• Deletes require extra handling: Deleted rows are no longer available to normal polling queries.
• Freshness depends on the polling interval: More frequent polling reduces latency but also increases query frequency against PostgreSQL.
• A reliable tracking field is important: Rows can be missed if the timestamp or incremental field does not change correctly.
• You operate the pipeline: Your team is responsible for Logstash deployment, monitoring, scaling, upgrades, and recovery.

When Should You Use Logstash JDBC?

Logstash JDBC is a good fit when:

• your team already uses Logstash and Elasticsearch,
• synchronization every few seconds or minutes is sufficient,
• your PostgreSQL tables have reliable change-tracking fields,
• and you are comfortable handling deletes separately.

If you need continuous PostgreSQL-to-Elasticsearch synchronization with native handling of inserts, updates, and deletes, log-based CDC is generally a better architecture.

How Are PostgreSQL Inserts, Updates, and Deletes Synced to Elasticsearch?

In a CDC-based PostgreSQL-to-Elasticsearch pipeline, database changes are captured as they happen and then applied to the corresponding Elasticsearch documents.

• Inserts: A new PostgreSQL row creates a new document in Elasticsearch.
• Updates: When a PostgreSQL row changes, the corresponding Elasticsearch document is updated or reindexed.
• Deletes: When a row is deleted in PostgreSQL, the matching Elasticsearch document should also be removed.
• Existing data: Before continuous CDC begins, an initial backfill can load the current PostgreSQL rows into Elasticsearch.

With log-based CDC, these changes are captured from PostgreSQL's write-ahead log (WAL) through logical replication. This avoids repeatedly scanning source tables to discover what changed.

The important part is maintaining a stable mapping between each PostgreSQL record and its Elasticsearch document. A consistent primary key or document ID allows the pipeline to apply later updates and deletes to the correct document.

Polling-based approaches work differently. They can identify new or modified rows by periodically querying PostgreSQL, but deletes usually require additional tracking because a deleted row is no longer available to return in the next query.

What About Debezium and Kafka?

Debezium and Kafka are another common way to move PostgreSQL changes into Elasticsearch.

A typical architecture looks like this:

PostgreSQL → Debezium → Kafka → Elasticsearch sink

Debezium captures inserts, updates, and deletes from PostgreSQL's write-ahead log (WAL) and publishes those change events to Kafka. An Elasticsearch sink connector then consumes the events and writes them into Elasticsearch.

This approach can be a strong fit when your team already uses Kafka or wants PostgreSQL changes to be available to multiple downstream systems, not just Elasticsearch.

Advantages

• Uses log-based CDC rather than table polling
• Captures inserts, updates, and deletes
• Makes PostgreSQL change events available to multiple consumers
• Fits well into an existing Kafka-based event architecture

Things to consider

• Kafka, Debezium, connectors, schemas, monitoring, and recovery all need to be operated and maintained
• The architecture introduces more infrastructure than a dedicated PostgreSQL-to-Elasticsearch pipeline
• Teams need to manage connector failures, offsets, schema changes, and destination delivery

If Kafka is already a core part of your data platform, Debezium can be a natural choice. If the main goal is simply to keep PostgreSQL and Elasticsearch synchronized, a managed CDC pipeline can reduce the amount of infrastructure your team needs to operate.

PostgreSQL to Elasticsearch: Compare the Options

The right approach depends on your latency requirements, existing infrastructure, operational preferences, and whether you need continuous CDC or periodic synchronization.

How to decide

Use Estuary when you want an initial backfill followed by continuous PostgreSQL CDC without managing the underlying replication infrastructure yourself.

Use PGSync when you want a PostgreSQL-specific open-source solution and are comfortable operating the synchronization stack.

Use Logstash JDBC when scheduled synchronization is sufficient and your PostgreSQL tables have reliable timestamp or incremental fields for detecting changes.

Use Debezium + Kafka when Kafka is already a core part of your architecture or when PostgreSQL change events need to feed multiple downstream applications and systems.

The main architectural choice is usually between log-based CDC and polling. CDC reads changes from PostgreSQL's WAL and can continuously capture inserts, updates, and deletes. Polling periodically queries PostgreSQL for changed rows and is often simpler, but freshness depends on the polling schedule and deletes typically need additional handling.

Build a PostgreSQL-to-Elasticsearch CDC pipeline with Estuary, or explore the PostgreSQL source and Elasticsearch destination documentation for configuration details.

Related PostgreSQL and Elasticsearch Guides

• MySQL to Elasticsearch
• SQL Server to Elasticsearch
• Oracle to Elasticsearch

Technical References

• Logstash JDBC Input Plugin Documentation
• PostgreSQL Logical Replication Documentation
• PGSync Documentation