Connecting Elasticsearch to PostgreSQL: 2 Easy Methods

In this blog, you will see two different methods for an Elasticsearch Postgres integration. The first method involves the use of Python libraries to complete the data transfer process. The second method uses Hevo’s automated data pipeline to set up your Elasticsearch to PostgreSQL connection. Read along to decide which method suits you the best!

What is Elastisearch?

Elasticsearch is an open-source information retrieval and search-engine library that allows for horizontal scaling, near real-time search capabilities, and multi-tenancy. It has use cases for log and event data analysis, full-text search, and operational intelligence. Elasticsearch internally uses the Apache Lucene search library to provide a powerful means of indexing and searching.

Uses Cases of Elastisearch

1. Log Data Analysis and Event: Elasticsearch is one of the popular open-source projects that is widely used to analyze log data produced by various systems, including servers, applications, and network devices.
2. Elasticsearch Search Engines-Elasticsearch forms the backbone for the search feature on many websites and applications, providing fast and relevant results to its users.
3. Operational Intelligence: Elasticsearch monitors and analyzes operational data to get insight into data for making informed, data-driven decisions.

What is PostgreSQL

PostgreSQL is a popular, free, and open-source relational database that can be used for different types of workloads. Postgres provides querying abilities and windowing functions. Its versatility makes it possible to be used as a transactional database as well as a data warehouse for analytics. With its origins dating back to 1986 and the strong community support that comes with it, Postgres boasts high reliability, extensibility, and data integrity.

Use Cases of PostgreSQL

• Transactional Databases: Ideal for systems needing ACID compliance, like financial apps and e-commerce.
• Data Analytics: Great for data warehousing and geospatial analysis using extensions like PostGIS.
• App Backend Storage: A robust backend for modern apps with JSON/JSONB support for semi-structured data.

What are the Methods to Connect Elasticsearch to PostgreSQL

Below, I have shown you two easy methods using which you can connect Elasticsearch to PostgreSQL.

Method 1: Using Hevo Data to Connect Elasticsearch to PostgreSQL

Hevo Data, a No-code Data Pipeline, helps you directly establish a PostgreSQL Elasticsearch connection in a completely hassle-free & automated manner. Hevo connects to your Elasticsearch cluster using the Elasticsearch transport client and synchronizes the data available in the cluster to your destination.

• Authenticate Source: Authenticate and configure your Elasticsearch account as the data source.

• Configure Destination: Connect the PostgreSQL Database as the destination.

Move your Data to PostgreSQL seamlessly

Method 2: Using PGSync to Connect Elasticsearch to PostgreSQL

PGSync is an open-source project to sync Postgres data with Elasticsearch. It works in real-time by capturing changes from Postgres via logical replication or the WAL (Write-Ahead Log).

Features

• Real-Time Sync: Streams changes to Postgres inside Elasticsearch in near real time.
• Schema Mapping: Automatically aligns a Postgres schema with Elasticsearch indices.
• Flexible Configuration: It supports filters, transformations, and advanced mappings.

Step 1: Install PGSync

• Ensure PostgreSQL is set up with logical replication.
• Download and install PGSync.

Step 2: Configure config.yml

• Define Postgres connection, Elasticsearch connection, and mapping details.

database:
  host: localhost
  port: 5432
  name: my_database
  user: my_user
  password: my_password

elasticsearch:
  host: localhost
  port: 9200

mappings:
  - table: users
    index: users_index
    columns:
      - id
      - name
      - email

Step 3: Run PGSync

• Start the PGSync service to begin real-time syncing.

pgsync -c config.yml

Method 3: Using Python Libraries to Connect Elasticsearch to PostgreSQL

You can easily set up your Elasticsearch Postgres connection using Python Libraries with the following steps:

• Step 1: Import Python Libraries
• Step 2: Create an Elasticsearch Object
• Step 3: Connect to PostgreSQL Database
• Step 4: Get Records from Elasticsearch
• Step 5: Read Elastcsearch Data into a List
• Step 6: Load Data into PostgreSQL
• Step 7: Elasticsearch to PostgreSQL Final Code

Step 1: Import Python Libraries

First, import the required Python libraries such as elasticsearch, elasticsearch-dsl, and psycopg2. Elasticsearch is a library that provides a common ground for all Elasticsearch-related code written in Python. Elasticsearch DSL is used to write and run queries against Elasticsearch. Psycopg2 is used for connecting to the PostgreSQL database. 

import elasticsearch
from elasticsearch import Elasticsearch
import elasticsearch_dsl
from elasticsearch_dsl import Search
import psycopg2 

Step 2: Create an Elasticsearch Object

Next, create an Elasticsearch object to send a data request to Elasticsearch. You need your Elasticsearch database login credentials to set up a connection.

es= Elasticsearch(hosts= “http://user_name:your_password@localhost:9200/”)

Step 3: Connect to PostgreSQL Database

Similarly, establish a connection to your PostgreSQL database and create a cursor object to execute your INSERT command. 

conn = psycopg2.connect(host=”localhost”, database=”postgres_db_name”, user="postgres”, password= “postgres_password”)
cur = conn.cursor()

Related: Looking to replicate data from Elasticsearch to Databricks? Our blog on Elasticsearch to Databricks provides you with two simple and effective methods to achieve this seamless integration. If you’re new to Elasticsearch and want to learn how to ingest data effortlessly, check out our blog on how to ingest data to Elasticsearch.

Step 4: Get Records from Elasticsearch

Now, we will use the Elasticsearch object to get the subscriber’s names.

s= Search(index= “es_db_name”).using(es).query(“match”, status=”subscriber”)
es_result= s.execute()

Step 5: Read Elasticsearch Data into a List

Read es_result into a list, which can then be moved to PostgreSQL.

subscriber_list = []
for x in es_result:
    subscriber_list.append(x.name)

All the names in the records will be appended to the empty subsciber_list list. 

Step 6: Load Data into PostgreSQL

Now, we just have to load this list into PostgreSQL.

q= “INSERT INTO the_table VALUES (%s)”
cur.execute(q, subsciber_list)
conn.commit()
conn.close()

In the above section, we wrote a query that inserts values into a PostgreSQL table called ‘the_table’. Then, we used the cursor we created earlier to load the values in the list subscriber_list into PostgreSQL using the query. Finally, the changes are committed and the PostgreSQL connection is closed.

Step 7: Elasticsearch to PostgreSQL: Final Code

Here is the complete code to load data from Elasticsearch to PostgreSQL:

import elasticsearch
from elasticsearch import Elasticsearch
import elasticsearch_dsl
from elasticsearch_dsl import Search
import psycopg2 

# create an elasticsearch object.

es= Elasticsearch(hosts= “http://user_name:your_password@localhost:9200/”)

# connect to PostgreSQL and create a cursor object.

conn = psycopg2.connect(host=”localhost”, database=”postgres_db_name”, user=”postgres”, password= “postgres_password”)
cur = conn.cursor()

# Get the required data from the documents.

s= Search(index= “es_db_name”).using(es).query(“match”, status=”subscriber”)
es_result= s.execute()

# Load the data into a list.

subscriber_list = []
for x in es_result:
    subscriber_list.append(x.name)


# Load the list into a PostgreSQL table using the INSERT statement and close the connection.

q= “INSERT INTO the_table VALUES (%s)”
cur.execute(q, subsciber_list)
conn.commit()
conn.close()

If you are looking to streamline your PostgreSQL workflow, do read our blog on PostgreSQL import CSV, saving you time and effort. And if you’re interested in optimizing performance and achieving high availability, don’t miss our guide on setting up PostgreSQL clusters [+clustering options].

Limitations of using Manual Method to connect Elasticsearch to PostgreSQL

I have listed some of the issues you might face while following this method:

• The conversion from Elasticsearch’s unstructured or semi-structured JSON documents into relational database formats can be complex and require a lot of custom coding.
• Large volumes of data or complex queries can lead to significant performance issues, including slow data transfer and increased latency.
• Python libraries may lack built-in capabilities for handling advanced data types, ensuring transactional integrity, or supporting complex transformations.

Conclusion

The article discussed the methods of connecting Elasticsearch to PostgreSQL. The manual method is to load data from Elasticsearch to PostgreSQL using Python Libraries. But often, in reality, NoSQL data does not have a consistent structure. The data is usually semi-structured, and data needs to be transformed. There are several other complexities involved all of which require heavy engineering bandwidth to deal with. 

Instead, if you need an ETL tool that handles moving data from Elasticsearch to PostgreSQL automatically, give Hevo, a no-code data pipeline, a try.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and simplify your data integration process. Check out the pricing details to understand which plan fulfills all your business needs.

FAQ on Connecting Elasticsearch to PostgreSQL