Salesforce to PostgreSQL: 2 Easy Methods

Salesforce provides customer relationship management software as a subscription-based cloud service, thereby eliminating the need for organizations to have their own infrastructure and software deployment. Even though Salesforce provides an analytics suite along with its offerings, most organizations will need to combine their customer data from Salesforce to 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. In this post, we will look at the steps involved in loading data from Salesforce to PostgreSQL, one of the most famous relational databases.

Introduction to Salesforce

Salesforce serves as an integrated CRM platform that provides a single, shared view across all your departments including Sales, Service, Commerce, and Marketing. Salesforce can help you empower and develop your Sales teams with integrated Lead Management, Forecasting Tools, and the ability to Quote, Price, and Configure every deal. Salesforce provides a comprehensive Cloud platform (Sales Cloud) that gives you the Customer Management Solution that is the right fit for every growth phase of your company.

Sales Cloud is an all-in-one Sales tool that also doubles as CRM software. Sales Cloud is flexible enough to accommodate businesses of any industry or size and very scalable. It allows you to access data on the go through its mobile app. It can also help maximize Sales with AppExchange integrations, thus boosting business growth.     

Here are a few salient Salesforce features:

• It allows you to build mobile-friendly Salesforce apps for every employee. This helps boost productivity and build a more connected workforce. Apart from this, it also helps you streamline the employee helpdesk for faster self-service. 
• Salesforce offers a boost on its core services with additional functionalities such as Application Integration and Artificial Intelligence. This includes building custom-branded mobile apps; easy integration of data from ERP, IoT, and databases; and addition of predictive capabilities for every service and Sales application.
• You can make your apps more personalized and smart to meet your needs in real-time engagement, service, and purchase.
• With Experience Cloud, you can evolve on a fast, flexible platform and maximize your return on experience. It allows you to build stronger relationships with a Single Source of Truth.
• Salesforce provides a highly curated library of industry-specific expert help. This includes process flows, apps, templates, and components built to tackle every single issue faced by the user.    

Introduction to PostgreSQL

Postgres is a popular relational database that uses a variant of SQL in its operations. Postgres can store and scale different workloads, making it a very versatile relational database. This versatility makes Postgres very useful in many instances, from use for small applications to large-scale data warehousing. Postgres also has an architecture that encourages extensibility. Thus, enabling you to incorporate code from other languages without recompiling the database. Its extensibility also enables you to define your own data types. Postgres is also ACID-compliant and able to run on all the major operating systems.

Key features of Postgres

• ACID-Compliant: Postgres is ACID-compliant and so ensures that your transactions are handled in a timely and efficient manner.
• Open Source: Postgres is fully open-source and has an active community to help with efficiency tips, bug resolution, etc.
• Extensibility: Postgres is very customizable and so can more readily incorporate new functionality into its system.

This blog describes method 1 in great detail. It also highlights the challenges involved in implementing this approach. Toward the end, you will also find more details on Method 2 so that you explore all your options and choose wisely.

Methods to Connect Salesforce to PostgreSQL

Here are the methods you can use to set up a connection from Salesforce to PostgreSQL in a seamless fashion:

• Method 1: Using Custom ETL Scripts to Connect Salesforce to PostgreSQL
• Method 2: Using Hevo Data to Connect Salesforce to PostgreSQL

Method 1: Using Custom ETL Scripts to Connect Salesforce to PostgreSQL

The best way to interact with Salesforce is to use the different APIs provided by Salesforce itself. It also provides some utilities to deal with the data. The following section attempts to provide an overview of these APIs and utilities. 

• Salesforce REST APIs: Salesforce REST APIs are a set of web services that help to insert/delete, update and query Salesforce objects. To implement a custom application using Salesforce in mobile or web ecosystem, these REST APIs are the preferred method. 
• Salesforce SOAP APIs: SOAP APIs can establish formal contracts of API behaviour through the use of WSDL. Typically Salesforce SOAP APIs are when there is a requirement for stateful APS or in case of strict transactional reliability requirement. SOAP APIs are also sometimes used when the organization’s legacy applications mandate the protocol to be SOAP.  
• Salesforce BULK APIs: Salesforce BULK APIs are optimized for dealing with a large amount of data ranging up to GBs. These APIs can run in a batch mode and can work asynchronously. They provide facilities for checking the status of batch runs and retrieving the results as large text files. BULK APIs can insert, update, delete or query records just like the other two types of APIs.
• Salesforce Data Loader: Data Loader is a Salesforce utility that can be installed on the desktop computer. It has functionalities to query and export the data to CSV files. Internally this is accomplished using the bulk APIs. 

Here are the steps involved in using Custom ETL Scripts to connect Salesforce to PostgreSQL:

• Step 1: Log In to Salesforce
• Step 2: Create a Bulk API Job
• Step 3: Create SQL Query to Pull Data
• Step 4: Close the Bulk API Job
• Step 5: Access the Resulting API
• Step 6: Retrieve Results
• Step 7: Load Data to PostgreSQL

Step 1: Log In to Salesforce

Login to Salesforce using the SOAP API and get the session id. For logging in first create an XML file named login.txt in the below format.

<?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: Create a Bulk API Job

Create a BULK API job. For creating a job, a text file with details of the objects that are 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-id will be used to form the URL for subsequent requests. Please note the URL will change according to the URL of the user’s Salesforce organization.

Step 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 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 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 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 filesystem. 

Step 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 below command.

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 in case you need to do this programmatically, Data Loader utility will be of little help. 

Limitations of using Custom ETL Scripts to Connect Salesforce to PostgreSQL

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

Method 2: Using Hevo Data to Connect Salesforce to PostgreSQL

An easier way to accomplish the same result is to use a code-free data pipeline platform like Hevo Data that can implement sync in a couple of clicks. 

Hevo does all heavy lifting and masks all the data migration complexities to securely and reliably deliver the data from Salesforce into your PostgreSQL database in real-time and for free. By providing analysis-ready data in PostgreSQL, Hevo helps you stop worrying about your data and start uncovering insights in real time.

Sign up here for a 14-day Free Trial!

With Hevo, you could move data from Salesforce to PostgreSQL in just 2 steps:

• Step 1: Connect Hevo to Salesforce by entering the Pipeline Name.

• Step 2: Load data from Salesforce to PostgreSQL by providing your Postgresql databases credentials like Database Host, Port, Username, Password, Schema, and Name along with the destination name.

Check out what makes Hevo amazing:

• Real-Time Data Transfer: Hevo with its strong Integration with 100+ sources, allows you to transfer data quickly & efficiently. This ensures efficient utilization of bandwidth on both ends.
• Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
• Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
• Tremendous Connector Availability: Hevo houses a large variety of connectors and lets you bring in data from numerous Marketing & SaaS applications, databases, etc. such as Google Analytics 4, Google Firebase, Airflow, HubSpot, Marketo, MongoDB, Oracle, Salesforce, Redshift, etc. in an integrated and analysis-ready form.
• Simplicity: Using Hevo is easy and intuitive, ensuring that your data is exported in just a few clicks. 
• Completely Managed Platform: Hevo is fully managed. You need not invest time and effort to maintain or monitor the infrastructure involved in executing codes.
• Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

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 wants to know about PostgreSQL, then read this article: Postgres to Snowflake.

Hevo handles everything from schema management to data flow monitoring data rids you of any maintenance overhead. In addition to Salesforce, you can bring data from 100s of different sources into PostgreSQL in real-time, ensuring that all your data is available for analysis with you.

Visit our Website to Explore Hevo

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

What are your thoughts on the two approaches to move data from Salesforce to PostgreSQL? Let us know in the comments.