Introduction
In this blog post I will introduce Portworx Data Services (PDS) and demonstrate how it an be used to also deliver PostgreSQL databases.
I have previously shared how we can use the EDB Kubernetes Database Operator to deliver a PostgreSQL database, and also the YugabyteDB Kubernetes Database Operator to deliver a PostgreSQL compatible Yugabyte database. Now it’s the turn of PDS.
What is Portworx Data Services (PDS)
Portworx Data Services (PDS) is a Database-as-a-Service (DBaaS) platform for Kubernetes.
PDS enables DevOps engineers and data professionals to deploy SQL and NoSQL databases, search and streaming solutions from a broad catalog of data services using curated templates with a single click of a button.
Kubernetes Environment
Ok, let’s get started, within my lab I have a 7 node Kubernetes cluster, running Kubernetes v1.21.3, we can see this using kubectl get nodes
[root@master-1 ~]# kubectl get nodes NAME STATUS ROLES AGE VERSION master-1 Ready control-plane,master 8m19s v1.21.3 node-1-1 Ready <none> 7m50s v1.21.3 node-1-2 Ready <none> 7m51s v1.21.3 node-1-3 Ready <none> 7m52s v1.21.3 node-1-4 Ready <none> 7m51s v1.21.3 node-1-5 Ready <none> 7m51s v1.21.3 node-1-6 Ready <none> 7m51s v1.21.3 node-1-7 Ready <none> 7m53s v1.21.3
Portworx
To check the version of Portworx, identify a Portworx pod and set-up an alias for pxctl, for example
% export PX_POD=$(kubectl get pods -l name=portworx -n kube-system -o jsonpath='{.items[0].metadata.name}')
% alias pxctl='kubectl exec -n kube-system ${PX_POD} -it -- /opt/pwx/bin/pxctl'
% pxctl -v
Defaulted container "portworx" out of: portworx, csi-node-driver-registrar
pxctl version 2.10.1-abc4f69
Portworx Data Services (PDS)
Before we can deploy a PostgreSQL database we need to register our Kubernetes cluster with PDS, using the deployment wizard.
Deployment Targets
Logon to PDS, and click on the Settings (Gear icon) on the left hand panel, then click + Add Deployment Target and click the Copy icon.
Return to the Kubernetes cluster and paste the helm install command, for example
[root@master-1 ~]# helm install --create-namespace --namespace=pds-system pds pds-target ... NAME: pds LAST DEPLOYED: Tue May 10 11:28:48 2022 NAMESPACE: pds-system STATUS: deployed REVISION: 1 TEST SUITE: None
PDS will create a new Storage Class (sc) called pds-db and a 8GB Persistent Volume Claim (pvc) called pds-prometheus-server as well as pods, services and deployments, for example.
[root@master-1 ~]# kubectl get po,svc,deploy -n pds-system NAME READY STATUS RESTARTS AGE pod/pds-agent-5cf77f574f-rlxdb 1/1 Running 0 58m pod/pds-backup-controller-manager-f9979b695-vnv77 2/2 Running 0 58m pod/pds-deployment-controller-manager-bd448f45b-h6hh2 2/2 Running 0 58m pod/pds-external-dns-858464bc78-mrvfw 1/1 Running 0 58m pod/pds-kube-state-metrics-d674c5b78-652l9 1/1 Running 0 58m pod/pds-prometheus-node-exporter-4fx42 1/1 Running 0 58m pod/pds-prometheus-node-exporter-5n48f 1/1 Running 0 58m pod/pds-prometheus-node-exporter-9czs5 1/1 Running 0 58m pod/pds-prometheus-node-exporter-mtrr7 1/1 Running 0 59m pod/pds-prometheus-node-exporter-pssjl 1/1 Running 0 58m pod/pds-prometheus-node-exporter-rztnn 1/1 Running 0 58m pod/pds-prometheus-node-exporter-vlq27 1/1 Running 0 58m pod/pds-prometheus-server-6446bcbdf9-mxz8b 2/2 Running 0 58m pod/pds-teleport-58b7dbf9fd-tdthp 1/1 Running 0 58m pod/pds-teleport-58b7dbf9fd-x4l7x 1/1 Running 0 58m NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE service/pds-backup-controller-manager-metrics-service ClusterIP 10.109.227.207 <none> 8443/TCP 59m service/pds-deployment-controller-manager-metrics-service ClusterIP 10.108.109.116 <none> 8443/TCP 59m service/pds-external-dns ClusterIP 10.98.85.251 <none> 7979/TCP 59m service/pds-kube-state-metrics ClusterIP 10.98.106.151 <none> 8080/TCP 59m service/pds-prometheus-node-exporter ClusterIP None <none> 9100/TCP 59m service/pds-prometheus-server ClusterIP 10.97.12.239 <none> 80/TCP 59m NAME READY UP-TO-DATE AVAILABLE AGE deployment.apps/pds-agent 1/1 1 1 59m deployment.apps/pds-backup-controller-manager 1/1 1 1 59m deployment.apps/pds-deployment-controller-manager 1/1 1 1 59m deployment.apps/pds-external-dns 1/1 1 1 58m deployment.apps/pds-kube-state-metrics 1/1 1 1 59m deployment.apps/pds-prometheus-server 1/1 1 1 58m deployment.apps/pds-teleport 2/2 2 2 59m
Create a dedicated PostgreSQL Kubernetes namespace for PDS and label it with pds.portworx.com/available=true
[root@master-1 ~]# kubectl create namespace pds-postgresql namespace/pds-postgresql created [root@master-1 ~]# kubectl label namespace pds-postgresql pds.portworx.com/available=true namespace/pds-postgresql labeled [root@master-1 ~]# kubectl get namespace pds-postgresql --show-labels NAME STATUS AGE LABELS pds-postgresql Active 117s kubernetes.io/metadata.name=pds-postgresql,pds.portworx.com/available=true
Once the helm chart has been installed and namespace labeled the Kubernetes cluster should automatically appear in PDS.
Now, click on edit (pencil icon) to provide a more meaning full name.
PDS PostgreSQL Deployment
From the PDS dashboard, click the Deployment (Disk icon) on the left hand panel to see existing deployments and to deploy new data services.
Before we attempt to deploy a new PostgreSQL database it’s advisable to confirm there is adequate space within our Portworx cluster, using pxctl status or pxctl cluster provision-status
[root@master-1 ~]# pxctl cluster provision-status Defaulted container "portworx" out of: portworx, csi-node-driver-registrar NODE NODE STATUS POOL POOL STATUS IO_PRIORITY SIZE AVAILABLUSED PROVISIONED ZONE REGION RACK 42bedec0-7d0c-467a-a444-c90154c90117 Up 0 ( 648c14f5-226a-45c1-afd5-1f099c3a596c ) Online HIGH 250 GiB 245 GiB 5.0 GiB 0 B default default default 449b8e08-0b4b-461d-8524-d66dd71368e1 Up 0 ( 44a23d6b-d631-4af2-bd03-15d7bf37b1f6 ) Online HIGH 250 GiB 245 GiB 5.0 GiB 0 B default default default 590d2e25-4766-4214-8cc0-513b3b180c34 Up 0 ( e8238468-7ad4-4d43-b193-76d84e4404fb ) Online HIGH 250 GiB 245 GiB 5.0 GiB 0 B default default default 6a6ac24d-ece6-406f-9feb-96340c4eb96e Up 0 ( 25667d59-fa0d-45d1-9848-4f7b20f39de4 ) Online HIGH 250 GiB 245 GiB 5.0 GiB 8.0 GiB default default default 8ec17055-11a1-46bb-90fc-0f918ee63aa9 Up 0 ( af1603b9-cd4c-4969-9a09-c8975f253b6a ) Online HIGH 250 GiB 245 GiB 5.0 GiB 0 B default default default bc30c1c7-9fd6-491d-9935-e51702df47e2 Up 0 ( 2c0fc8b4-eaf8-4a67-b98e-31226f673f29 ) Online HIGH 250 GiB 245 GiB 5.0 GiB 8.0 GiB default default default cafe5658-5a6c-4a27-a653-a2e03c5acd99 Up 0 ( 399f34c1-1f16-4d4f-8a98-e84db3e1bd19 ) Online HIGH 250 GiB 245 GiB 5.0 GiB 8.0 GiB default default default
From the Deploy Data Service panel select PostgreSQL.
Provide a Name, select the Target and Namespace previously created.
As I am deploying PostgreSQL within my on-premises lab and do not have a load balancer I will uncheck the Automatically provision external load balancer. For Cloud deployments use the default setting.
From Application Configuration, select Custom and supply PG_DATABASE (if required), will default to pds otherwise.
Select required templates for Size, Storage Options and Backup, then click Deploy.
After a few minutes we should see the PostgreSQL database provisioned.
Once provisioned, click on view connection to obtain username, password and endpoints.
Returning to the Kubernetes cluster we can see the PostgreSQL Kubernetes Pods (po), Statefulsets (sts), Services (svc) and Persistent Volume Claims (pvc)
[root@master-1 ~]# kubectl get po,sts,svc,pvc -n pds-postgresql NAME READY STATUS RESTARTS AGE pod/pg-pg-ron-hzng45-0 2/2 Running 0 9m17s pod/pg-pg-ron-hzng45-1 2/2 Running 0 7m38s pod/pg-pg-ron-hzng45-2 2/2 Running 0 6m51s pod/pg-pg-ron-hzng45-cluster-init-hw7vv 0/1 Completed 0 6m12s pod/pg-pg-ron-hzng45-node-init-pqqbz 0/1 Completed 0 6m12s NAME READY AGE statefulset.apps/pg-pg-ron-hzng45 3/3 9m17s NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE service/pg-pg-ron-hzng45-pds-postgresql ClusterIP 10.105.203.97 <none> 5432/TCP 9m18s service/pg-pg-ron-hzng45-pds-postgresql-0 ClusterIP None <none> 5432/TCP,8009/TCP,2022/TCP 9m21s service/pg-pg-ron-hzng45-pds-postgresql-0-vip ClusterIP 10.96.91.238 <none> 5432/TCP,8009/TCP,2022/TCP 9m21s service/pg-pg-ron-hzng45-pds-postgresql-1 ClusterIP None <none> 5432/TCP,8009/TCP,2022/TCP 9m20s service/pg-pg-ron-hzng45-pds-postgresql-1-vip ClusterIP 10.97.105.205 <none> 5432/TCP,8009/TCP,2022/TCP 9m20s service/pg-pg-ron-hzng45-pds-postgresql-2 ClusterIP None <none> 5432/TCP,8009/TCP,2022/TCP 9m19s service/pg-pg-ron-hzng45-pds-postgresql-2-vip ClusterIP 10.104.156.68 <none> 5432/TCP,8009/TCP,2022/TCP 9m19s service/pg-pg-ron-hzng45-pds-postgresql-config ClusterIP None <none> 5432/TCP,8009/TCP,2022/TCP 9m17s NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE persistentvolumeclaim/datadir-pg-pg-ron-571y4k-0 Bound pvc-74829a42-5636-4201-b7ff-82d633039444 94Gi RWO pg-pg-ron-571y4k-pds-postgresql 18h persistentvolumeclaim/datadir-pg-pg-ron-571y4k-1 Bound pvc-4e2d7a26-b274-42d2-885a-b084e06e044c 94Gi RWO pg-pg-ron-571y4k-pds-postgresql 18h persistentvolumeclaim/datadir-pg-pg-ron-571y4k-2 Bound pvc-b1e183d8-239d-419f-a928-a476715e8b06 94Gi RWO pg-pg-ron-571y4k-pds-postgresql 18h persistentvolumeclaim/datadir-pg-pg-ron-hzng45-0 Bound pvc-3a611fb7-7254-402d-8771-bea2b7f3c9f3 94Gi RWO pg-pg-ron-hzng45-pds-postgresql 9m17s persistentvolumeclaim/datadir-pg-pg-ron-hzng45-1 Bound pvc-a9016138-2c94-446f-abe2-f220f47d81bb 94Gi RWO pg-pg-ron-hzng45-pds-postgresql 7m38s persistentvolumeclaim/datadir-pg-pg-ron-hzng45-2 Bound pvc-28ff2838-62ed-4b0d-81b4-31a878e0c129 94Gi RWO pg-pg-ron-hzng45-pds-postgresql 6m51s persistentvolumeclaim/sharedbackupsdir-pg-pg-ron-hzng45 Bound pvc-a16de787-2126-41e1-bfed-79e9a3bf23c6 94Gi RWX pg-pg-ron-hzng45-sharedbackups-pds-postgresql 9m17s
Kubernetes Secret
We can obtain the database password from the PDS UI or from the Kubernetes secret, from example
% kubectl get secrets -n pds-postgresql NAME TYPE DATA AGE .. pg-pg-ron-hzng45-creds Opaque 1 26h pg-pg-ron-hzng45-token-8p8zq kubernetes.io/service-account-token 3 26h
Using the <environment>-creds secret get the password and decode.
% kubectl get secrets/pg-pg-ron-hzng45-creds -n pds-postgresql -o jsonpath='{.data.password}' | base64 --decode
WEAWvY68tR3Y970wlFweBOMHr9Nx1mZDdn7AysB9
PDS PostgreSQL Database
The PostgreSQL container includes the PostgreSQL interactive terminal psql, this can be found in /usr/bin/psql.
If you do not have a PostgreSQL client already available you can use this to connect to the database from within our PostgreSQL pod, for example.
[root@master-1 ~]# kubectl exec -it pod/pg-pg-ron-hzng45-0 -n pds-postgresql -c postgresql -- /bin/bash
Use psql –help to see the common line options.
[pds@pg-pg-ron-hzng45-0 ~]$ psql --help psql is the PostgreSQL interactive terminal. ... Usage: psql [OPTION]... [DBNAME [USERNAME]]Connection options: -h, --host=HOSTNAME database server host or socket directory (default: "local socket") -p, --port=PORT database server port (default: "5432") -U, --username=USERNAME database user name (default: "pds") -w, --no-password never prompt for password -W, --password force password prompt (should happen automatically)
Using the hostname, port, database name, username and password from the PDS UI or secret.
[pds@pg-pg-ron-hzng45-0 ~]$ psql -d "host=pg-pg-ron-hzng45-pds-postgresql.sales-prod.pds-dns.io port=5432 dbname=pg-ron user=pds password=WEAWvY68tR3Y970wlFweBOMHr9Nx1mZDdn7AysB9" psql (14.2) Type "help" for help. pg-ron=#
Alternatively, you may prefer to use a URI connection string, for example.
[pds@pg-pg-ron-hzng45-0 ~]$ psql postgresql://pds:WEAWvY68tR3Y970wlFweBOMHr9Nx1mZDdn7AysB9@pg-pg-ron-hzng45-pds-postgresql.sales-prod.pds-dns.io:5432/pg-ron psql (14.2) Type "help" for help. pg-ron=#
Or if psql is available outside of the pod.
[root@master-1 ~]# psql postgresql://pds:WEAWvY68tR3Y970wlFweBOMHr9Nx1mZDdn7AysB9@pg-pg-ron-hzng45-pds-postgresql.sales-prod.pds-dns.io:5432/pg-ron
User Password
Before we release the database, as per standard database best security practices, we should reset the user account password, for example
pg-ron=# ALTER USER pds WITH ENCRYPTED PASSWORD 'new_password'; ALTER ROLE pg-ron=# \q
We can now logon using our ‘new_password’.
[root@master-1]# psql postgresql://pds:new_password@pg-pg-ron-hzng45-pds-postgresql.sales-prod.pds-dns.io:5432/pg-ron psql (14.2) Type "help" for help. pg-ron=#
Portworx Data Services (PDS) Metrics
To see the PDS metrics in action, I will use pgbench to generate some workload.
OK, let’s start by creating a demo database for the pgbench schemas.
pg-ron=# create database demo;
CREATE DATABASE
pg-ron-# \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 | | 8313 kB | pg_default |
pg-ron | pds | UTF8 | en_US.utf-8 | en_US.utf-8 | =Tc/pds +| 8569 kB | pg_default |
| | | | | pds=CTc/pds +| | |
| | | | | postgres_exporter=c/pds | | |
postgres | pds | UTF8 | en_US.utf-8 | en_US.utf-8 | | 8521 kB | pg_default | default administrative connection database
template0 | pds | UTF8 | en_US.utf-8 | en_US.utf-8 | =c/pds +| 8313 kB | pg_default | unmodifiable empty database
| | | | | pds=CTc/pds | | |
template1 | pds | UTF8 | en_US.utf-8 | en_US.utf-8 | =c/pds +| 8313 kB | pg_default | default template for new databases
| | | | | pds=CTc/pds | | |
(5 rows)
pg-ron=# SELECT datname FROM pg_database;
datname
-----------
postgres
template1
template0
pg-ron
demo
(5 rows)
And populate the database with 100 million rows using pgbench -i (initialise) and -s (scaling), to see the storage utilisation, for example
[root@master-1 bin]# ./pgbench -i -s 1000 postgresql://pds:new_password@pg-pg-ron-hzng45-pds-postgresql.sales-prod.pds-dns.io:5432/demo dropping old tables... NOTICE: table "pgbench_accounts" does not exist, skipping NOTICE: table "pgbench_branches" does not exist, skipping NOTICE: table "pgbench_history" does not exist, skipping NOTICE: table "pgbench_tellers" does not exist, skipping creating tables... generating data (client-side)... 100000000 of 100000000 tuples (100%) done (elapsed 716.37 s, remaining 0.00 s) vacuuming... creating primary keys... done in 1814.57 s (drop tables 0.01 s, create tables 0.08 s, client-side generate 722.73 s, vacuum 869.49 s, primary keys 222.25 s).
As shown above, pgbench creates pgbench_accounts, pgbench_branches, pgbench_history, and pgbench_tellers tables, destroying any existing tables.
And review database size.
pg-ron-# \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 | | 15 GB | pg_default |
pg-ron | pds | UTF8 | en_US.utf-8 | en_US.utf-8 | =Tc/pds +| 8569 kB | pg_default |
| | | | | pds=CTc/pds +| | |
| | | | | postgres_exporter=c/pds | | |
postgres | pds | UTF8 | en_US.utf-8 | en_US.utf-8 | | 8521 kB | pg_default | default administrative connection database
template0 | pds | UTF8 | en_US.utf-8 | en_US.utf-8 | =c/pds +| 8313 kB | pg_default | unmodifiable empty database
| | | | | pds=CTc/pds | | |
template1 | pds | UTF8 | en_US.utf-8 | en_US.utf-8 | =c/pds +| 8313 kB | pg_default | default template for new databases
| | | | | pds=CTc/pds | | |
(5 rows)
Let’s perform a simple TPC-B style test using pgbench -c (number of clients) -T (time in seconds) for 10 clients, for 10 minutes and check-out the PDS metrics.
[root@master-1 bin]# ./pgbench -c 10 -T 300 postgresql://pds:new_password@pg-pg-ron-hzng45-pds-postgresql.sales-prod.pds-dns.io:5432/demo pgbench (14.2) starting vacuum...end. transaction type: <builtin: TPC-B (sort of)> scaling factor: 1000 query mode: simple number of clients: 10 number of threads: 1 duration: 300 s number of transactions actually processed: 120065 latency average = 24.961 ms initial connection time = 367.338 ms tps = 400.620233 (without initial connection time)
Returning to the PDS WebUI and navigating to Deployments -> PostgreSQL and selecting my deployment
Summary
In this post I have shared how we can get started with Portworx Data Services (PDS), deploy a PostgreSQL database and monitor metrics from within PDS UI.
In my next post I will look at backup and recovery.