How to Load Data from Salesforce to PostgreSQL?

Even though Salesforce provides an analytics suite along with its offerings, most organizations must combine their customer data from Salesforce with data elements from various internal and external sources for decision-making. This can only be done by importing Salesforce data into a data warehouse or database.

The Salesforce Postgres integration is a powerful way to store and manage your data effectively. In addition, Salesforce Postgres sync is another way to store and organize data by extracting and transforming it. In this post, we will look at the steps involved in loading data from Salesforce to PostgreSQL.

Overview of Salesforce

For any organization, it’s very important to maintain a good relationship with customers, and CRM software makes it easier for you to achieve this. Salesforce is the world’s #1 Customer Relationship Management (CRM) platform. It has in-built sales, marketing, accounts, leads, opportunities, servicing, and marketing applications. We sometimes need to export the data from Salesforce for various reasons.

Overview of PostgreSQL

PostgreSQL is a popular object-relational database management system that offers enterprise-grade features with a strong focus on extensibility. It runs on all major operating systems, such as Unix and Windows. It is open-source, fully ACID-compliant, and fully supports foreign keys, joins, etc., in multiple languages. It is available in cloud-based deployments by most major cloud providers.

What Type of Data Can You Export From Salesforce?

• Standard & Custom Object Data – Leads, Accounts, Contacts, Opportunities, Cases, and Custom Objects.
• Metadata & Configuration Data – Users, Roles, Profiles, Permission Sets, Workflow Rules, and Apex Triggers.
• Audit & Log Data – Login History, Field History Tracking, and Event Logs.
• Reports & Dashboards – Custom Reports and Dashboard Components.
• Files & Attachments – Documents, Notes, and Email Logs.

Method 1: Using Hevo Data

Step 1: Configure Salesforce as your Source

Step 2: Configure PostgreSQL as your Destination

That’s it! You have successfully connected your Salesforce data to your PostgreSQL destination.

Method 2: Using Custom ETL Scripts

There are multiple Salesforce API versions for extracting data, including

• Sandbox & Production Environments: Used for testing and live business operations, respectively.
• REST API: Preferred for web/mobile applications.
• SOAP API: Used for strict transactional reliability and legacy applications.
• BULK API: Optimized for handling large datasets asynchronously.
• Data Loader: A desktop tool for exporting data via Bulk API.

Step 2.1: Log In to Salesforce

Login to Salesforce using the SOAP API and get the session ID. To log in, first create an XML file named login.txt in the format below.

<?xml version="1.0" encoding="utf-8" ?>

<env:Envelope xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">

<env:Body>

<n1:login xmlns:n1="urn:partner.soap.sforce.com">

<n1:username>username</n1:username>

<n1:password>password</n1:password>

</n1:login>

</env:Body>

</env:Envelope>

Execute the below command to login 

curl https://login.Salesforce.com/services/Soap/u/47.0 -H "Content-Type: text/xml; charset=UTF-8" -H 
"SOAPAction: login" -d @login.txt

From the result, XML, note the session ID. We will need the session ID for the later requests. 

Step 2.2: Create a Bulk API Job

Create a BULK API job. To create a job, a text file with details of the objects to be accessed is needed. Create the text file using the below template.

<?xml version="1.0" encoding="UTF-8"?>

<jobInfo xmlns="http://www.force.com/2009/06/asyncapi/dataload">

<operation>insert</operation>

<object>Contact</object>

<contentType>CSV</contentType>

</jobInfo>

We are attempting to pull data from the object Contact in this exercise.            

Execute the below command after creating the job.txt

curl https://instance.Salesforce.com/services/async/47.0/job -H "X-SFDC-Session: sessionId" -H 
"Content-Type: application/xml; charset=UTF-8" -d @job.txt

From the result, note the job ID. This job will be used to form the URL for subsequent requests. Please note that the URL will change according to the URL of the user’s Salesforce organization.

Step 2.3: Create SQL Query to Pull Data

Create the SQL query to pull the data and use it with CURL, as given below. 

curl https://instance_name—api.Salesforce.com/services/async/APIversion/job/jobid/batch

-H "X-SFDC-Session: sessionId" -H "Content-Type: text/csv; 

SELECT name,desc from Contact

Step 2.4: Close the Bulk API Job

The next step is to close the job. This requires a text file with details of the job status change. Create it as below with the name close_job.txt.

<?xml version="1.0" encoding="UTF-8"?>

<jobInfo xmlns="http://www.force.com/2009/06/asyncapi/dataload">

<state>Closed</state>

</jobInfo>

Use the file with the below command.

curl https://instance.Salesforce.com/services/async/47.0/job/jobId -H "X-SFDC-Session: sessionId" -H 
"Content-Type: application/xml; charset=UTF-8" -d @close_job.txt

Step 2.5: Access the Resulting API

Access the resulting API and fetch the result is of the batch. 

curl -H "X-SFDC-Session: sessionId" https://instance.Salesforce.com/services/async/47.0/job/jobId/batch/batchId/result

Step 2.6: Retrieve Results

Retrieve the actual results using the result ID that was fetched from the above step.

curl -H "X-SFDC-Session: sessionId" 
https://instance.Salesforce.com/services/async/47.0/job/jobId/batch/batchId/result/resultId

The output will be a CSV file with the required rows of data. Save it as Contacts.csv in your local file system. 

Step 2.7: Load Data to PostgreSQL

Load data to Postgres using the COPY command. Assuming the table is already created, this can be done by executing the command below.

COPY Contacts(name,desc,)

FROM 'contacts.csv' DELIMITER ',' CSV HEADER;

An alternative to using the above sequence of API calls is to use the Data Loader utility to query the data and export it to CSV. But if you need to do this programmatically, the Data Loader utility will be of little help. 

Limitations of using Custom ETL Scripts

As evident from the above steps, loading data through the manual method contains many steps that could be overwhelming if you are looking to do this regularly. You would need to configure additional scripts in case you need to bring data into real-time. 

• It is time-consuming and requires prior knowledge of coding, understanding APIs and configuring data mapping. 
• This method is unsuitable for bulk data movement, leading to slow performance, especially for large datasets.

Conclusion

This blog talks about the different methods you can use to set up a connection from Salesforce to PostgreSQL in a seamless fashion. If you want to know about PostgreSQL, read this article: Postgres to Snowflake.

Hevo is the only real-time ELT no-code data pipeline platform that cost-effectively automates flexible data pipelines to your needs. Hevo handles everything from schema management to data flow monitoring, and data rids you of any maintenance overhead. In addition to Salesforce, you can bring data from 150+ different sources into PostgreSQL in real-time, ensuring that all your data is available for analysis with you.

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.

Frequently Asked Questions (FAQs)