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,
From the deployment, click the kebab menu (3 vertical dots) select Additional Details, from here Copy the Endpoints.
Connection 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.
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.