Portworx Data Services (PDS) and PostgreSQL

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.

How to Add Deployment Targets
Add Deployment Target

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.

Deployment Targets
Deployment Targets

Now, click on edit (pencil icon) to provide a more meaning full name.

Deployment Targets
Updated Named

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.

Portworx Data Services (PDS) Deploy PostgreSQL
Deploy PostgreSQL

After a few minutes we should see the PostgreSQL database provisioned.

Portworx Data Services (PDS) PostgreSQL Deployment
PostgreSQL Deployment

Once provisioned, click on view connection to obtain username, password and endpoints.

Portworx Data Services (PDS) Additional Details
Additional Details

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_accountspgbench_branchespgbench_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

Portworx Data Services (PDS) deployment metrics
Deployment Metrics

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.

One thought on “Portworx Data Services (PDS) and PostgreSQL

Add yours

Leave a Reply

Create a website or blog at WordPress.com

Up ↑

Discover more from Ron Ekins' - Oracle Technology, DevOps and Kubernetes Blog

Subscribe now to keep reading and get access to the full archive.

Continue reading