How to connect to a PDS PostgreSQL database running on OKE

In this blog I and going to show how to connect to a PostgreSQL database created by Portworx Data Services (PDS) deployed on Oracle Cloud Infrastructure (OCI) Oracle Container Engine for Kubernetes (OKE).

I previously shared How to deploy a PostgreSQL database on the Oracle Cloud using OKE and Portworx Data Services (PDS) so if you want to follow along with this post you may want to visit the above post.

PDS Deployment

Before we can attempt to connect to a PDS PostgreSQL database we are going to need to capture the PDS endpoint details and password.

Note: the database name and username will always be pds

From the PDS webUI console navigate to PostgreSQL and select your deployment,

Deployment Details

From the deployment, click the kebab menu (3 vertical dots) select Additional Details, from here Copy the Endpoints.

Connection Details

Additional details

From the Connection tab click Copy Password and save for later use.

Database Password

An alternative method to obtain the database password is directly from within the Kubernetes environment, by getting and decoding the Kubernetes secret for the PostgreSQL deployment, for example.

Use kubectl get secrets providing the -n and the namespace name.

% kubectl get secrets -n pds-postgresql -L deployment-name=pg-pg-ron-lk3wqj
NAME                     TYPE     DATA   AGE     DEPLOYMENT-NAME=PG-PG-RON-LK3WQJ
pg-pg-ron-lk3wqj-creds   Opaque   2      5d23h 

Using the <deployment name>-creds secret get the password and decode it, if you copied the password from the PDS console, you should see the same value returned.

% export POSTGRES_PASSWORD=$(kubectl get secrets/pg-pg-ron-lk3wqj-creds -n pds-postgresql -o jsonpath='{.data.password}' | base64 --decode)

% echo ${POSTGRES_PASSWORD}
309BIeIuyYKy81DJxjmH0LypeMpGte229GF0sJlg

PostgreSQL Image

Obtain the PDS curated PostgreSQL image and version by examining .items[*].spec.containers[*].image for the Master pod and first container image, for example:

% kubectl get pods -n pds-postgresql -l role=master -o jsonpath="{.items[*].spec.containers[0].image}"
docker.io/portworx/pds-postgresql:14.5-25852e0

% export PGIMAGE=docker.io/portworx/pds-postgresql:14.5-25852e0 

Connecting to Database

The simplest way to connect to the PostgreSQL database is to spin up a pod, providing the PDS PostgreSQL image, database password and host obtained above.

% kubectl run postgresql-client --rm --tty -i --restart='Never' --namespace pds-postgresql --image ${PGIMAGE} --env="PGPASSWORD=$POSTGRES_PASSWORD" --command -- psql --host pg-pg-ron-lk3wqj-pds-postgresql -U pds -d pds -p 5432 
If you don't see a command prompt, try pressing enter.
pds=# 
pds=# \l+
                                                                   List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    |    Access privileges    |  Size   | Tablespace |                Description                 
-----------+-------+----------+-------------+-------------+-------------------------+---------+------------+--------------------------------------------
 pds       | pds   | UTF8     | en_US.utf-8 | en_US.utf-8 | =Tc/pds                +| 8609 kB | pg_default | 
           |       |          |             |             | pds=CTc/pds            +|         |            | 
           |       |          |             |             | postgres_exporter=c/pds |         |            | 
 postgres  | pds   | UTF8     | en_US.utf-8 | en_US.utf-8 |                         | 8609 kB | pg_default | default administrative connection database
 template0 | pds   | UTF8     | en_US.utf-8 | en_US.utf-8 | =c/pds                 +| 8401 kB | pg_default | unmodifiable empty database
           |       |          |             |             | pds=CTc/pds             |         |            | 
 template1 | pds   | UTF8     | en_US.utf-8 | en_US.utf-8 | =c/pds                 +| 8401 kB | pg_default | default template for new databases
           |       |          |             |             | pds=CTc/pds             |         |            | 
(4 rows)

pds=# \conninfo
You are connected to database "pds" as user "pds" on host "pg-pg-ron-lk3wqj-pds-postgresql" (address "10.96.101.99") at port "5432".

pds=# \q
pod "postgresql-client" deleted

Note when you exit out from the PostgreSQL database, the pod postgresql-client is deleted.

The PostgreSQL container also includes the PostgreSQL interactive terminal psql, so we can also use that to connect to the database, for example.

Identify the PostgreSQL master pod using kubectl get pods and filter on label role=master

% kubectl get pods -n pds-postgresql -l role=master  
NAME                 READY   STATUS    RESTARTS   AGE
pg-pg-ron-lk3wqj-0   2/2     Running   0          6d

Looking at the statefulset created, we can see it has the same as the PDS deployment name.

% kubectl get sts -n pds-postgresql -o name               
statefulset.apps/pg-pg-ron-lk3wqj

If we shell into the postgresql container, we can connect using psql using the previous syntax for example.

% kubectl exec -it pod/pg-pg-ron-lk3wqj-0 -n pds-postgresql -c postgresql -- /bin/bash
[pds@pg-pg-ron-lk3wqj-0 ~]$

[pds@pg-pg-ron-lk3wqj-0 ~]$ psql -d "host=localhost port=5432 dbname=pds user=pds password=309BIeIuyYKy81DJxjmH0LypeMpGte229GF0sJlg"
psql (14.5)
Type "help" for help.

pds=# 

Alternatively, if preferred use the URI connection string format postgresql://[user[:password]@][host][:port][/dbname] as below.

[pds@pg-pg-ron-lk3wqj-0 ~]$ psql postgresql://pds:309BIeIuyYKy81DJxjmH0LypeMpGte229GF0sJlg@localhost:5432/pds
psql (14.5)
Type "help" for help.

pds=# 

Remote Client Access

Accessing a PostgreSQL database from Kubernetes is great, but what if we want to connect from a PostgreSQL client ?

Well that’s not a problem either.

For this post I will use an OCI development compute instance which I have created in a Private subnet with the PostgreSQL client installed.

To connect to the development machine, I have created an OCI Bastion services, and time limited session to the development machine.

Create session
rekins@rekins--MacBookPro15 ~ % ssh -i ~/.ssh/id_rsa -o ProxyCommand="ssh -i ~/.ssh/id_rsa -W %h:%p -p 22 ocid1.bastionsession.oc1.uk-london-1.amaaaaaa54aw2uaa27cenhgc7igeeozzxkogo4in2nystqjnkd3r6h5fsb7q@host.bastion.uk-london-1.oci.oraclecloud.com" -p 22 opc@10.0.10.244
Activate the web console with: systemctl enable --now cockpit.socket

Last login: Wed Jan 18 16:26:52 2023 from 10.0.10.201
[opc@instance-20230118-1553 ~]$ 

Using the database password and connection details we obtained earlier we can connect directly from the development machine using psql, for example.

[opc@instance-20230118-1553 ~]$ psql postgresql://pds:309BIeIuyYKy81DJxjmH0LypeMpGte229GF0sJlg@pg-pg-ron-lk3wqj-pds.sales-prod.pds-dns.io,pg-ron-lk3wj-pds-1-vip.sales-prod.pds-dns.io:5432/pds
psql (14.6, server 14.5)
Type "help" for help.


pds-# \l+
                                                                   List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    |    Access privileges    |  Size   | Tablespace |                Description                 
-----------+-------+----------+-------------+-------------+-------------------------+---------+------------+--------------------------------------------
 demo      | pds   | UTF8     | en_US.utf-8 | en_US.utf-8 |                         | 8569 kB | pg_default | 
 pds       | pds   | UTF8     | en_US.utf-8 | en_US.utf-8 | =Tc/pds                +| 8609 kB | pg_default | 
           |       |          |             |             | pds=CTc/pds            +|         |            | 
           |       |          |             |             | postgres_exporter=c/pds |         |            | 
 postgres  | pds   | UTF8     | en_US.utf-8 | en_US.utf-8 |                         | 8609 kB | pg_default | default administrative connection database
 template0 | pds   | UTF8     | en_US.utf-8 | en_US.utf-8 | =c/pds                 +| 8401 kB | pg_default | unmodifiable empty database
           |       |          |             |             | pds=CTc/pds             |         |            | 
 template1 | pds   | UTF8     | en_US.utf-8 | en_US.utf-8 | =c/pds                 +| 8401 kB | pg_default | default template for new databases
           |       |          |             |             | pds=CTc/pds             |         |            | 
(5 rows)

pds-# Connection to host.bastion.uk-london-1.oci.oraclecloud.com closed by remote host.

Summary

In this post I have detailed how to get the PDS PostgreSQL database password, endpoint, and shared a few different ways to how connect to a PDS PostgreSQL database running on the Oracle Container Engine for Kubernetes (OKE) service.

Leave a Reply

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: