Create PostgreSQL (Hyperscaler Option - Cloud) on SAP BTP and connect it to SAP Integration Suite

[tokleema]

Introduction

I found some good guides and explainations to connect a PostgreSQL (On-Premise) with SAP Integration Suite. But what about PostgreSQL (Cloud) on SAP BTP? 
Many customers have a SAP BTP in place and they don't want to build up a database in their own network or in another cloud provider like Azure or AWS. So, here is my first guide to setup a PostgreSQL (Hyperscaler Option) on SAP BTP and connect it to SAP Integration Suite.

Important Links
https://help.sap.com/docs/postgresql-hyperscaler-option/postgresql-on-sap-btp-hyperscaler-option/wha...
https://help.sap.com/docs/postgresql-hyperscaler-option/postgresql-on-sap-btp-hyperscaler-option/ser...

Content (Steps)

1. Setup the PostgreSQL db on SAP BTP 
2. JDBC Configuration in SAP Integration Suite
3. Connect to PostgreSQL with (local) DBeaver
4. Create IFlow to push data into the db

1. Setup the PostgreSQL db on SAP BTP 

1.1 Log in to you SAP BTP Cockpit and go to the service marketplace of your subaccount

1.2 Search for "PostgreSQL"

1.3 Create a new instance with service plan "standard"
Note: service plan "free" is not possible here because there you have less configuration options during the creation

1.4 Set Parameters

Important Parameters:

Allow Access: <your own ip address>, <ip addresses of your BTP tenant>

<your own ip address> - it's needed for the connection from your local sql editor (DBeaver)
<ip addresses of your BTP tenant> - Otherwise Integration Suite is not able to connect to the db
https://me.sap.com/notes/0002418879 - https://help.sap.com/docs/btp/sap-business-technology-platform/regions-and-api-endpoints-available-f...

Public Access: Tick the checkbox

Optional:

I've unchecked Multi AZ and I've reduced the Backup Retention Period and the Memory for the tutorial (to save some money 😉). You have to check and adjust those parameters for your project.

1.5 Create the instance (Take a coffee or tea - It will take some minutes to be ready)

1.6 Create Service Key (for Authentication of Integration Suite and DBeaver)

Copy and save dbname, hostname, port, username and password for later steps

2. JDBC Configuration in SAP Integration Suite

2.1 Add a new resource with the credentials from the step before

JDBC URL:  jdbc:postgresql://<hostname>:<port>/dbname

3. Connect to PostgreSQL with (local) DBeaver

This step is needed for the creation of a table and corresponding columns that the Integration Suite is able to insert data there.

3.1 Download DBeaver or any other sql client/ editor
3.2 Connect to the db using the credentials
3.3 Create a table (e.g. employeedata)
3.4 Create columns (e.g. emp_id and emp_name)

 

4. Create IFlow to insert Data

4.1 Go to your Integration Suite and create a new IFlow

JDBC Receiver Adapter: Enter the JDBC Alias which you created in step 2.1

Content Modifier:

<root>
<Insert_Statement1>
<dbTableName action="INSERT">
<table>employeedata</table>
<access>
<emp_id>42</emp_id>
<emp_name>Brian</emp_name>
</access>
</dbTableName>
</Insert_Statement1>
</root>

Note: Here you can find sample batch payloads to insert multiple records: https://help.sap.com/docs/cloud-integration/sap-cloud-integration/batch-payload-and-operation

4.2 Deploy it and check via DBeaver, if your data was inserted:

[Yogananda]

seems like this a blog you have written but can you change the type from Technology Q&A to Technology Blog from members

[Markus7]

Hello tokleema
Thanks for your blog. I already tried it the same way, but I cannot get it working. I have the following question regarding your solution. Is it necessary to add a JDBC Driver for PostgreSQL in "Overview/Manage JDBC Material". ChatGPT says yes and that it does not matter which of the available options (MariaDB/Oracle/DB2/MSSQL) you chose but for me it does not work.

Update: Solved. JDBC Driver was not necessary.