Setting up access to EDB Postgres service instances

You can connect to the EDB Postgres service from a Kubernetes cluster.

Follow these steps to connect to the EDB Postgres service:

1. Get the list of EDB Postgres services that are running on this instance of IBM® Software Hub:

   oc get services \
   -n ${PROJECT_CPD_INST_OPERANDS} \
   -o name \
   | grep edb-db

   Each service instance will have the following services associated with it:

   Service Description	Example
   A service for read-only workloads	service/<instance-name>-edb-db-r
   A service for all hot-standby replicas of the cluster	service/<instance-name>-edb-db-ro
   A service for the primary database instance	service/<instance-name>-edb-db-rw

2. Set the EDB_INSTANCE_NAME environment variable to the name of the service instance that you want to connect to:

   export EDB_INSTANCE_NAME=<instance-name>

3. Get the credentials that you want to use to connect to the instance. Generally, there are two different credentials:

   Application credentials

   Use these credentials for applications that need to connect to the PostgreSQL cluster. Application credentials correspond to the user who owns the database. Perform these steps to get the application credentials:

   1. Set the APP_USER environment variable to the username:

      export APP_USER=$(echo $(oc get secret ${EDB_INSTANCE_NAME}-edb-db-app -n ${PROJECT_CPD_INST_OPERANDS} -o jsonpath='{.data.username}{"\n"}') | base64 -d && echo)

   2. Set the APP_PASSWORD environment variable to the password:

      export APP_PASSWORD=$(echo $(oc get secret ${EDB_INSTANCE_NAME}-edb-db-app -n ${PROJECT_CPD_INST_OPERANDS} -o jsonpath='{.data.password}{"\n"}') | base64 -d && echo)

   Superuser credentials

   Use these credentials to access the PostgreSQL cluster as an administrative user. Superuser credentials correspond to the postgres user. Perform these steps to get the superuser credentials:

   1. Set the ADMIN_USER environment variable to the username:

      export ADMIN_USER=$(echo $(oc get secret ${EDB_INSTANCE_NAME}-edb-db-superuser -n ${PROJECT_CPD_INST_OPERANDS} -o jsonpath='{.data.username}{"\n"}') | base64 -d && echo)

   2. Set the ADMIN_PASSWORD environment variable to the password:

      export ADMIN_PASSWORD=$(echo $(oc get secret ${EDB_INSTANCE_NAME}-edb-db-superuser -n ${PROJECT_CPD_INST_OPERANDS} -o jsonpath='{.data.password}{"\n"}') | base64 -d && echo)

4. To connect to the instance from another application on the cluster, use the PostgreSQL connection string. The format of the connection string depends on whether you are connecting to the instance from an application in the same project (namespace) or from an application in a different project.
   • To connect to the instance from an application in the same project:
     Application credentials

     postgresql://${APP_USER}:${APP_PASSOWRD}@${EDB_INSTANCE_NAME}-rw.db.svc.cluster.local:5432/<db-name>

     Replace <db-name> with the name of the database that you want to connect to.
     Superuser credentials

     postgresql://${ADMIN_USER}:${ADMIN_PASSOWRD}@${EDB_INSTANCE_NAME}-rw.db.svc.cluster.local:5432/<db-name>

     Replace <db-name> with the name of the database that you want to connect to.
   • To connect to the instance from an application in a different project:
     Application credentials

     postgresql://${APP_USER}:${APP_PASSOWRD}@${EDB_INSTANCE_NAME}.${PROJECT_CPD_INST_OPERANDS}-rw.db.svc.cluster.local:5432/<db-name>

     Replace <db-name> with the name of the database that you want to connect to.
     Superuser credentials

     postgresql://${ADMIN_USER}:${ADMIN_PASSOWRD}@${EDB_INSTANCE_NAME}.${PROJECT_CPD_INST_OPERANDS}-rw.db.svc.cluster.local:5432/<db-name>

     Replace <db-name> with the name of the database that you want to connect to.
5. Optional: Enable access to the service instance from outside of the cluster.
   Important: Consult with your network administrator to determine which method you should use to enable access from outside of the cluster.

   By default, EDB Postgres listens for incoming connections from client applications on port 5432.

   Example: This example uses port forwarding to enables access to the service instance from your local machine:

   oc port-forward service/${EDB_INSTANCE_NAME}-edb-db-rw 5454:5432 \
   -n ${PROJECT_CPD_INST_OPERANDS}

   The preceding command enables:

   • The service instance to listen on port 5454
   • Your local machine to access the service instance on port 5432

   Result: To connect from your local workstation:

   1. Leave the current terminal open.
   2. Open a new terminal and run the appropriate command:
      Application credentials

      psql -h 127.0.0.1 -p 5454 -U ${APP_USER} 

      When prompted provide the password: ${APP_PASSWORD}.
      Superuser credentials

      psql -h 127.0.0.1 -p 5454 -U ${ADMIN_USER}

      When prompted provide the password: ${ADMIN_PASSWORD}.