PostgreSQL on SAP BTP - Activate (create) an extension via service API using Postman

[antoniojmnunes]

Customers can activate (create) additional extensions to PostgreSQL on SAP BTP, hyperscaler option (from the list of hyperscaler supported extensions) using an extension API provided by SAP.

"These extensions are often developed by the community or third-party developers and can include functionalities such as advanced data types, indexing methods, procedural languages, or other specialized tools that extend the capabilities of PostgreSQL. Extensions can be installed and managed to tailor the database system to the needs of a particular application or use case.”

For this example, we are going to use the below PostgreSQL instance running on Cloud Foundry.

To be able to send requests to the API you need always to request before a token (for each API Call) and construct the full API URL, by adding {id} instance id {extension-name} extension name information to:

https://{base-url}/v1/postgresql-db/instances/{id}/extensions/{extension-name}

To retrieve the API server base URL {base-url} please create or check an already existent service key, example below:

To activate (create) a new extension just follow the below steps:

1️⃣  Request the  ‘bearer’ token please run the below command, after login to the API endpoint.

cf oauth-token

 Below an example on how to retrieve the token. Check also this blog post that explains the procedure step-by-step.

2️⃣  Configure the create database extension request (PUT)

We are going to use Postman REST Client for Visual Code, but you can choose any other tool or method for sending the request.

Below the complete API URL for our scenario, for enabling the pg_partman extension as an example:

https://api-backing-services.eu10-canary.data.services.cloud.sap/v1/postgresql-db/instances/fab5bd81-9292-4b6a-92ee-544d8175482a/extensions/pg_partman  

You will need to add to the request payload (JSON) the parameters: database and schema.

{“database”: “jBhasYtLuhHg”, “schema”: “public”}

NOTE: You can check the database name from the service key content via BTP cockpit instance parameters.

Add the API URL to the PUT type request. In the authorization configuration choose the type ’Bearer Token’ and add the token that you have requested before.

Add the JSON parameters to the body request.

3️⃣  Send the request using the RESP API client

After sending the PUT request you will receive a response with success of the operation.

You can check via PSQL the extension already enabled.

👉. When thinking about creating extensions, is important to check always the official support documentation from each hyperscaler.

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