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 with PostgreSQL connection. Read along to decide which method suits you the best!

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].

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.