PostgreSQL | Connect to a PostgreSQL instance on a Kyma environment

[SilviaPedroso]

This blog post is a series of 3, our aim is to walkthrough the set-up of your PostgreSQL service on a Kyma environment:

1. PostgreSQL is now available on the Kyma environment (read me first)
2. How to deploy PostgreSQL on a Kyma environment (read me second)
3. Connect to a PostgreSQL instance on a Kyma environment (current read)

In this article we will cover how to connect to your PostgreSQL instance on the Kyma environment

Before connecting to your PostgreSQL instance there are some prerequisites and steps that need to be carried out:

• Ensure you have carried out the steps described in blog 1 and blog 2.

• Install kubectl (Using SAP BTP Services in the Kyma Environment), you will need this for connecting to your Kyma environment.

• Ensure you have psql installed, you will need this to test your connection to the PostgreSQL instance, alternatively you can use a GUI tool such as pgAdmin4.

• Ensure you have your IP address authorized for connection within your instance on the Kyma Dashboard.

In order to connect to a PostgreSQL instance you need to access the instance credentials and URI (Uniform Resource Identifier), you can extract this information from your binding.

Within your Kyma dashboard you can access the ‘URI’ and ‘Secret’ of your PostgreSQL instance. Navigate to your service binding (1) and select the service binding name hyperlink (2)Select the ‘Secret Name’ (3) where you will have the ‘URI’ and credentials to access your PostgreSQL instance.Select the decode (4) button to see the values decoded, you will need them for the following steps.
Allowing your IP address to be used for connection

To connect to your PostgreSQL your IP address needs to be added as a value to the instance parameter ‘allow-access’. Using Kyma dashboard you can navigate to your ‘Service Instance’ and select ‘Edit’.Within the ‘Instance Parameters’ section add the parameter ‘allow_access’ (string format), followed by your IP address, after selecting ‘Update’ a success message should be displayed with ‘Service Instance Updated’.
If you wish to consume a PostgreSQL instance through an application using Kyma please additionally add the Cluster NAT IPS to your ‘allow_access’ parameters.

To obtain the cluster NAP IPs please follow the guidelines provided within the following Kyma documentation.

Once you have your cluster IP address add it to the allow-access parameters within your PostgreSQL instance as you did above for your own IP.

Now that you have successfully allow listed your IP you can connect to your PostgreSQL instance.

Using psql to connect to the instance, open your terminal window (psql) and execute the below command using your decoded URI information as a parameter.

Once connected successfully, test your PostgreSQL connection by verifying your PostgreSQL instance version for example.

Your PostgreSQL version will be displayed.

Alternatively, you can use the GUI tool pgAdmin 4 to test your connection, for that you need to register your server.

Once connected successfully, test your PostgreSQL connection by using the query tool and verify for example the list of all databases in your PostgreSQL instance using the below SELECT statement.

Congratulations! You have now successfully connected to your PostgreSQL instance and are ready to start using it for your development.

Relevant sources of information: 

• For detailed information on how to create instances on Kyma please refer to Kyma learning.

• For additional details on Kyma set-ups please access the SAP Help portal and also have a look at the video series provided in the following Kyma Blog.

• For more information on PostgreSQL on SAP BTP, hyperscaler option service, please check SAP Help.

• For information on PostgreSQL pricing and commercial options and regions, please check SAP Discovery Center.

Please check out the blog post below, where we've shared a list of key topics from the product team.

• PostgreSQL on SAP BTP - Are you looking for official blog posts? Blog INDEX

Thank you,

SAP BTP Backing Services – Product Management