PostgreSQL: Prod-Grade with k8s Operator

Despite all the challenges, in the last 2 years, clever people still managed ways to deploy production-grade database within a Kubernetes cluster by using Kubernetes as a platform to develop custom resource definition (CRDs) like helm charts like bitnami/postgresql-ha, and PostgreSQL Operator like CrunchyData/postgres-operator or zalando/postgres-operator.

Last post I was able to deploy a single PostgreSQL in local k8s, but I had to manually create Kubernetes namespaces, define database creds, configuration and environment variables into k8s secret and configmap, also to define the statefulset yaml with volume claim template.

Still I was not able to configure HA and failover as I found it is so limited and a headache within K8S if only relying on statefulset. Luckily there are engineers out there to develop helm and operator to get the job done.

CrunchyData Postgres-Operator

In this session, I will follow bellow steps to:

Deploy PostgreSQL Operator

# Clone the CrunchyData Postgres Operator
[root@freeipa-server ~]# git clone https://github.com/CrunchyData/postgres-operator-examples.git

# create namespace and deploy GPO Postgres Operatorusing kustomize
[root@freeipa-server postgres-operator-examples]# kubectl apply -k kustomize/install/namespace
namespace/postgres-operator created
[root@freeipa-server postgres-operator-examples]# kubectl apply --server-side -k kustomize/install/default
customresourcedefinition.apiextensions.k8s.io/pgadmins.postgres-operator.crunchydata.com serverside-applied
customresourcedefinition.apiextensions.k8s.io/pgupgrades.postgres-operator.crunchydata.com serverside-applied
customresourcedefinition.apiextensions.k8s.io/postgresclusters.postgres-operator.crunchydata.com serverside-applied
serviceaccount/pgo serverside-applied
clusterrole.rbac.authorization.k8s.io/postgres-operator serverside-applied
clusterrolebinding.rbac.authorization.k8s.io/postgres-operator serverside-applied
deployment.apps/pgo serverside-applied

# validate deploy status
[root@freeipa-server postgres-operator-examples]# kubectl get all -n postgres-operator
NAME                    READY   STATUS   RESTARTS   AGE
pod/pgo-77d6b49b8-wrdjp   1/1     Running   0         2m47s

Deploy HA PostgreSQL Cluster

# Create a Postgres Cluster named "hippo" in "postgres-operator" ns
[root@freeipa-server postgres-operator-examples]# kubectl apply -k kustomize/postgres
postgrescluster.postgres-operator.crunchydata.com/hippo created

[root@freeipa-server postgres-operator-examples]# kubectl get all -n postgres-operator
NAME                    READY   STATUS   RESTARTS   AGE
pod/hippo-backup-dvks-m4z5m   1/1     Running   0         56s
pod/hippo-instance1-582s-0    4/4     Running   0         2m14s
pod/hippo-repo-host-0         2/2     Running   0         2m14s
pod/pgo-77d6b49b8-wrdjp       1/1     Running   0         6m38s

Connect an application to PostgreSQL cluster

Here we use Keycloak, a popular open-source identity management tool that is backed by a PostgreSQL database. Using the hippo cluster we created, we can deploy the following manifest file:

# create deployment keycloak to connect PostgreSQL database
[root@freeipa-server postgres-operator-examples]# vim kustomize/keycloak/keycloak.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
 name: keycloak
 namespace: postgres-operator
 labels:
   app.kubernetes.io/name: keycloak
spec:
 selector:
   matchLabels:
     app.kubernetes.io/name: keycloak
 template:
   metadata:
     labels:
       app.kubernetes.io/name: keycloak
   spec:
     containers:
     - image: quay.io/keycloak/keycloak:latest
       args: ["start-dev"]
       name: keycloak
       env:
       - name: DB_VENDOR
         value: "postgres"
       - name: DB_ADDR
         valueFrom: { secretKeyRef: { name: hippo-pguser-hippo, key: host } }
       - name: DB_PORT
         valueFrom: { secretKeyRef: { name: hippo-pguser-hippo, key: port } }
       - name: DB_DATABASE
         valueFrom: { secretKeyRef: { name: hippo-pguser-hippo, key: dbname } }
       - name: DB_USER
         valueFrom: { secretKeyRef: { name: hippo-pguser-hippo, key: user } }
       - name: DB_PASSWORD
         valueFrom: { secretKeyRef: { name: hippo-pguser-hippo, key: password } }
       - name: KEYCLOAK_ADMIN
         value: "admin"
       - name: KEYCLOAK_ADMIN_PASSWORD
         value: "admin"
       - name: KC_PROXY
         value: "edge"
       ports:
       - name: http
         containerPort: 8080
       - name: https
         containerPort: 8443
       readinessProbe:
         httpGet:
           path: /realms/master
           port: 8080
     restartPolicy: Always

[root@freeipa-server postgres-operator-examples]# kubectl apply -f kustomize/keycloak/keycloak.yaml
deployment.apps/keycloak created

Scale Up / Down

Edit manifest to add 2 more replicas

[root@freeipa-server kustomize]# kubectl apply -k postgres -n postgres-operator
postgrescluster.postgres-operator.crunchydata.com/hippo configured
# watch change
[root@freeipa-server postgres-operator-examples]# watch kubectl get pod -L postgres-operator.crunchydata.com/role -l postgres-operator.crunchydata.com/instance -n postgres-operator

Failover testing:

Now I am going to delete the primary instance, one of the standby pod will take over and become primary automatically

# delete the primary pod hippo-instance1-nhbc-0, then previous replica pod hippo-instance1-q8kk-0 promoted as master
# pod hippo-instance1-nhbc-0 will up again as a replica
[root@freeipa-server kustomize]# kubectl delete po hippo-instance1-nhbc-0 -n postgres-operator
pod "hippo-instance1-nhbc-0" deleted

Perform Minor version rolling upgrade

Here I changed the database version to 16.1, the cluster will start a rolling update by

Applying new version to one of the standby pod first
Then update another replica pod
Promote the first upgraded replica as master
Lastly the previous master pod will be updated and become a replica

# validate DB version before miner upgrade
[root@freeipa-server kustomize]# kubectl exec -it hippo-instance1-q8kk-0 -n postgres-operator -- psql --version
Defaulted container "database" out of: database, replication-cert-copy, pgbackrest, pgbackrest-config, postgres-startup (init), nss-wrapper-init (init)
psql (PostgreSQL) 16.2

# validate DB version after miner version change
[root@freeipa-server kustomize]# kubectl exec -it hippo-instance1-q8kk-0 -n postgres-operator -- psql --version
Defaulted container "database" out of: database, replication-cert-copy, pgbackrest, pgbackrest-config, postgres-startup (init), nss-wrapper-init (init)
psql (PostgreSQL) 16.1

Backup

Add backup Cron job into manifest to add weekly full backup and daily incremental

[root@freeipa-server ~]# kubectl get cronjobs -n postgres-operator
NAME               SCHEDULE      SUSPEND   ACTIVE   LAST SCHEDULE   AGE
hippo-repo1-full   0 1 * * 0     False     0        <none>          5m21s
hippo-repo1-incr   0 1 * * 1-6   False     0        <none>          5m21s

Deploy Monitoring (Prom + Grafaba)

Finally, let’s set up the monitoring stack for PostgreSQL by using Prometheus and Grafana.

# deploy monitoring stack
[root@freeipa-server kustomize]# kubectl apply -k monitoring
serviceaccount/alertmanager created
serviceaccount/grafana created
serviceaccount/prometheus created
clusterrole.rbac.authorization.k8s.io/prometheus created
clusterrolebinding.rbac.authorization.k8s.io/prometheus created
configmap/alert-rules-config created
configmap/alertmanager-config created
configmap/crunchy-prometheus created
configmap/grafana-dashboards created
configmap/grafana-datasources created
secret/grafana-admin created
service/crunchy-alertmanager created
service/crunchy-grafana created
service/crunchy-prometheus created
persistentvolumeclaim/alertmanagerdata created
persistentvolumeclaim/grafanadata created
persistentvolumeclaim/prometheusdata created
deployment.apps/crunchy-alertmanager created
deployment.apps/crunchy-grafana created
deployment.apps/crunchy-prometheus created
# Edit Grafana service to NodePort
[root@freeipa-server postgres-operator-examples]# kubectl edit svc crunchy-grafana -n postgres-operator
service/crunchy-grafana edited

Execute into master database container, using pgbench to generate tables

[root@freeipa-server postgres-operator-examples]# kubectl exec -it hippo-instance1-nhbc-0 -c database -n postgres-operator -- bash

bash-4.4$ pgbench -i -s 100 -U postgres -d postgres
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)...
10000000 of 10000000 tuples (100%) done (elapsed 45.61 s, remaining 0.00 s)

Some Grafana predefined PostgreSQL dashboard, unfortunately I do not have much data in it to show more monitoring status.

Conclusion

This is the final session of this PostgreSQL series, together I have explored PostgreSQL from very basic docker deployment with replica, to production-grade deployment in Kubernetes using operator, practice from backup, monitoring, rolling update, to HA, failover, and scale up. HAHA!