PostgreSQL on SAP BTP - Activate (create) an extension using pgAdmin tool

[antoniojmnunes]

The PostgreSQL instances are delivered by default with a set of extensions pre-enabled that are used to support features that are delivered by SAP or are needed to manage the service. To enable customers to activate (create) additional extensions (from the list of hyperscaler supported extensions) SAP provides also an extension API that can be used.

What is a PostgreSQL extension?

“A PostgreSQL extension is a modular and optional component that enhances the functionality of the PostgreSQL database system. Extensions provide additional features, functions, or capabilities that can be added to the core PostgreSQL database to meet specific requirements"

The procedure of using the extension API is documented in the SAP Help Portal service documentation, in this post we will address how to activate (create) an extension by connecting to the instance and use the pgAdmin tool and run PSQL/SQL commands with a user session with the necessary authorizations.

For this example we will use the below sub-account and PostgreSQL instance running on Cloud Foundry (CF).
☝️Be aware that you need to have the right permissions to be able to access your sub-account and the instance (admin or developer type authorizations).

Take note of the API endpoint and the region where the instance is deployed, you will need it to connect.

In this blog post we focus on the below topics:

1. Request an admin user access valid for x days
2. Connect to the PostgreSQL instance from your local PC via SSH tunnel
3. Check available extensions and compatible versions using pgAdmin
4. Create and update database extensions using pgAdmin

1️⃣. Request an admin user access valid for x days

To be able to run the necessary steps to activate (create) an extension, using pgAdmin, you need to connect to the PostgreSQL instance using a user with the proper authorizations. SAP provides a service API for customers to request a temporary user access (valid between 1-29 days) with some admin rights (user: instance_monitoring_admin).

The process of requesting the 'instance_monitoring_admin' user is described in the below blog post, and you should follow the process before moving to next topic (2).

• PostgreSQL on SAP BTP - Request an 'admin' user access valid for 'x' days

2️⃣ Connect to the PostgreSQL instance from your local PC via SSH tunnel

You need to have Cloud Foundry Command Line Interface (CF CLI) installed in your local computer (for more instructions, see Installing the cf CLI).

To support the setup of a SSH tunnel to access your PostgreSQL instance you need to deploy a sample app and use it as SSH host and port forwarding. You can download the following example app available at https://github.com/cloudfoundry-samples/test-app to a local folder.

Use the cf push command to deploy the app that will be used to support the creation of the SSH tunnel.

First you need to login to the BTP Cloud Foundry using the API endpoint. 

cf login -a https://api.cf.sap.hana.ondemand.com

After a successful login, you will be prompt to select the target <org> and <space>.

Alternatively, you can use the cf target command to specify the target organization and space.

cf target -o <org> -s <space>

Be aware that you need to select as target the <org> and <space> where you have your PostgreSQL instance running.

As next step deploy the sample app using the cf push command from your local folder.

cf push my-test-app -p /Users/User123/Downloads/test-app-master

Enable SSH for your app by running the cf enable command:

cf enable-ssh my-test-app

After a successful app enable, bind the application to the DB instance and create a service key via the BTP Cockpit create service key option for the selected instance.

 Note: The name of the service key is not relevant, only the credentials and parameters are needed to connect to your instance.

Check that the app is enable and without any error.

To establish the SSH tunnel to your app as a host and to be able to connect to the instance, you need to know the hostname and port, this can be done from the service key, as show below:

Configure an SSH tunnel to your service instance using cf ssh command using the parameters from the service key.

cf ssh -L 63306:<hostname>:<port> my-test-app

By running the command you should be able to establish the ssh tunnel as shown below:

Now you can setup and test the connection to the instance via pgAdmin tool.

Open the tool and setup your server connection using the local host configuration to redirect your connection via SSH tunnel (⚠️ do not close the terminal window used for SSH connection). Use the credentials from the requested instance_monitoring_admin user. 

Connect to the instance, open the query editor and run for example the below SQL command to test the successful access.

You can also run the above query using PSQL command line tool:

3️⃣  Check available extensions and compatible versions

Now that you have access to your instance via SSH tunnel, your can use pgAdmin query tool to check the extensions available, supported and already registered in the database via the below pg_* system catalogs views:

• The catalog pg_extension view shows information about the installed extensions
• The pg_available_extensions view lists the extensions that are available for installation
• The pg_available_extension_versions view lists the specific extension versions that are available for installation

Using the query editor, you can run the below SQL commands:

SELECT * FROM pg_extension;
SELECT * FROM pg_available_extensions;
SELECT * FROM pg_available_extension_versions;

In our case the below SQL output shows all installed and active extensions:

IMPORTANT:
Please check always to the hyperscaler documentation regarding extensions and versions supported for each PostgreSQL major version. You can also check what are the allowed extensions by running the below command, in case of an AWS PostgreSQL instance type.

SHOW rds.allowed_extensions;

❗BE AWARE that SAP can restrict the activation (creation) of some extensions, even they are supported by the hyperscalers.❗

4️⃣  Create and update database extensions

Using pgAdmin query tool you can run the SQL statements with the user session with the proper authorizations (in our case the user: instance_monitoring_admin).

The 3 most relevant SQL commands are:

• CREATE EXTENSION (enables the extension)
• ALTER EXTENSION UPDATE (update the extension)
• DROP EXTENSION (removes/delete the extension)

The below SQL command will create (enable) the vector extension (aka pg_vector).

For more information you can refer to the official PostgreSQL documentation regarding CREATE EXTENSION SQL command.

The below output shows the installed and active extension version.

 

When thinking about creating extensions, is important to check always the official support documentation from each hyperscaler. Please be aware that by installing specific extensions they will not be automatically upgraded when the engine version is upgrade. You will need to plan running some manual actions before and/or after the upgrade to avoid any disruption of the service that you are providing for your use cases or consumed by bound applications.

Please refer always to the product official documentation, regarding extension APIs, at SAP Help Portal.

Check also other interesting blogs regarding PostgreSQL:

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

Thanks for your reading,

SAP BTP Backing Services – Product Management

[rskumarv80]

Getting the below error in 2nd step.

For application 'my-test-app': Routes cannot be mapped to destinations in different spaces.

Thanks 

Ravi

[antoniojmnunes]

Hi @rskumarv80,

When deleting the app please make sure you delete also the routes, using cf command.

Regards, Antonio 

[rskumarv80]

Hi 

Not sure, no applications available in my trial account and deleted apps and routes from trial account. I am using latest cf8.

Thanks 

Ravi