Connecting Elasticsearch to PostgreSQL: 2 Easy Methods

Easily move your data from Elasticsearch to PostgreSQL to enhance your analytics capabilities. With Hevo’s intuitive pipeline setup, data flows in real-time—check out our 1-minute demo below to see the seamless integration in action!

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 with 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 multitenancy. 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, which is widely used to analyze log data produced by various systems, including servers, applications, and network devices.
2. Elasticsearch Search Engines-Elasticsearch basically forms the backbone for the search feature on many websites and applications, thereby 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.

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 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 the appropriate solemn in 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 Python libraries 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.

Learn More About:

Elasticsearch PostgreSQL Comparison

Conclusion

The article discussed 2 methods of connecting Elasticsearch to PostgreSQL. The first 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.

FAQ on Connecting Elasticsearch to PostgreSQL

Want to take Hevo for a spin? Signup for a 14-day free trial and experience the feature-rich Hevo suite firsthand. Checkout our unbeatable pricing to select the best plan for your organization.

Share your thoughts on loading data from Elasticsearch to PostgreSQL in the comments below.