Use and Seed SAP BTP PostgreSQL in SAP BTP, Kyma Runtime

1. Go to the kyma-runtime-samples repository. This repository contains a collection of Kyma sample applications which will be used during the tutorial.

2. Use the green Code button to choose one of the options to download the code locally, or simply run the following command using your CLI at your desired folder location:

   Shell/Bash

   Copy

   git clone https://github.com/SAP-samples/kyma-runtime-samples
   

1. Open the database-postgres directory in your desired editor.

2. Explore the content of the sample.

   Within the k8s folder you can find seed-job.yaml, which includes a ConfigMap containing the SQL and a Kubernetes Job that runs the psql client (image postgres:15). The Job expects the PostgreSQL connection details to come from a Service Binding Secret named postgres-binding. Adjust the Secret name and key names to match your binding if they differ.

1. From your subaccount overview in the SAP BTP cockpit, go to Entitlements and choose Edit.

2. Choose Add Service Plans and search for PostgreSQL, Hyperscaler Option.

3. Select free, and click Add 1 Service Plan.

   NOTE: The available service plans depend on your account configuration. If the free plan is not available, select the plan that matches your subaccount entitlements.

4. Choose Save.

1. Create the dev namespace and enable Istio:

   Shell/Bash

   Copy

   kubectl create namespace dev
   kubectl label namespaces dev istio-injection=enabled
   

   Namespaces separate objects inside a Kubernetes cluster. Choosing a different namespace requires adjustments to the provided samples.

   Adding the istio-injection=enabled label to the namespace enables Istio. Istio is the service mesh implementation used by SAP BTP, Kyma runtime.

2. Use the provided postgres-instance-binding.yaml manifest to create the PostgreSQL instance and binding:

   NOTE: The postgres-instance-binding.yaml file uses free as the servicePlanName. If the free plan is not available in your subaccount, open the file and change the servicePlanName value to match the plan you entitled in the previous step.

   Shell/Bash

   Copy

   kubectl -n dev apply -f ./k8s/postgres-instance-binding.yaml
   

3. It takes some time for the instance and binding to get created. To see if they are in the Created state, run:

   Shell/Bash

   Copy

   kubectl -n dev get serviceinstance postgres-instance
   kubectl -n dev get servicebinding postgres-binding
   

   Once ready, both resources show Created in the STATUS column:

1. Patch the ServiceInstance you applied above (namespace and name may differ) so it includes both your public IP and the Kyma NAT IP:
   Shell/Bash

   Copy

   MY_IP=$(curl -s https://api.ipify.org)
   KYMA_NAT_IPS=$(kubectl --namespace kyma-system get configmap kyma-info -o json | jq -r '.data["cloud.natGatewayIps"]')
   kubectl -n dev patch serviceinstance postgres-instance --type=merge \
     -p "{\"spec\":{\"parameters\":{\"allow_access\":\"${MY_IP},${KYMA_NAT_IPS}\"}}}"
   

2. It takes some time before the changes are applied. To see if the instance is updated, run:
   Shell/Bash

   Copy

   kubectl -n dev get serviceinstance postgres-instance
   

1. Apply the ConfigMap and Job to seed the database. Run the following commands from the database-postgresql directory using your CLI:

   Shell/Bash

   Copy

   kubectl -n dev apply -f ./k8s/seed-job.yaml
   kubectl -n dev get jobs seed-postgresql
   

2. Wait until the Job shows 1/1 in the COMPLETIONS column:

   NAME               COMPLETIONS   DURATION   AGE
   seed-postgresql    1/1           12s        30s
   

1. Run a temporary Pod that maps the Service Binding Secret keys to PGHOST, PGPORT, PGDATABASE, PGUSER, PGPASSWORD, and PGSSLMODE and executes a query. Replace the Secret name and keys if your binding differs.

   Shell/Bash

   Copy

   kubectl -n dev apply -f - <<'EOF'
   apiVersion: v1
   kind: Pod
   metadata:
     name: pg-client
   spec:
     restartPolicy: Never
     containers:
     - name: psql
       image: postgres:15
       env:
       - name: PGHOST
         valueFrom:
           secretKeyRef:
             name: postgres-binding
             key: hostname
       - name: PGPORT
         valueFrom:
           secretKeyRef:
             name: postgres-binding
             key: port
       - name: PGDATABASE
         valueFrom:
           secretKeyRef:
             name: postgres-binding
             key: dbname
       - name: PGUSER
         valueFrom:
           secretKeyRef:
             name: postgres-binding
             key: username
       - name: PGPASSWORD
         valueFrom:
           secretKeyRef:
             name: postgres-binding
             key: password
       - name: PGSSLMODE
         valueFrom:
           secretKeyRef:
             name: postgres-binding
             key: sslmode
             optional: true
       command: ["psql"]
       args: ["-v", "ON_ERROR_STOP=1", "-c", "SELECT order_id, description, created FROM orders;"]
   EOF
   

2. Check the Pod logs.

   Shell/Bash

   Copy

   kubectl -n dev logs pod/pg-client
   

   You should see a table with two sample orders:

    order_id | description  |         created
   ----------+--------------+-------------------------
    10000001 | Sample Order 1 | 2024-01-01 00:00:00+00
    10000002 | Sample Order 2 | 2024-01-01 00:00:00+00
   (2 rows)
   

3. If you want to delete the Pod, run:

   Shell/Bash

   Copy

   kubectl -n dev delete pod/pg-client
   

If you want to remove the seeding assets, run:

kubectl -n dev delete job seed-postgresql
kubectl -n dev delete configmap postgresql-sample-sql

The PostgreSQL instance itself remains running on SAP BTP and can now be consumed by your Kyma workloads.