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:

   Code Copy

   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

   Show all 45 lines Show less

2. Check the Pod logs.

   Shell/Bash Copy

   kubectl -n dev logs pod/pg-client

   You should see a table with two sample orders:

   Code Copy

    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.