Comprehensive Guide to Deploying Zalando Postgres Operator and PostgreSQL Cluster on OKD/OpenShift

Deploying a PostgreSQL cluster on OKD/OpenShift using the Zalando Postgres Operator is a powerful and scalable solution. This guide provides step-by-step instructions on deploying the operator, configuring storage using StorageClass or NFS, and accessing the PostgreSQL database efficiently.

Prerequisites

Before proceeding, ensure you have:

• An OKD/OpenShift cluster with the CLI (oc) installed and configured

• Helm installed on your local system

• An NFS server (if using NFS for storage)

Step-by-Step Deployment Guide

1. Clone the Zalando Postgres Operator Repository

git clone https://github.com/zalando/postgres-operator.git

2. Add the Zalando Helm Repository

helm repo add postgres-operator-charts 
https://opensource.zalando.com/postgres-operator/charts/postgres-operator

3. Install the Postgres Operator

• Create a dedicated namespace:

oc create namespace zalando-operator

• Install the operator using Helm:

helm install postgres-operator postgres-operator-charts/postgres-

operator -n zalando-operator

4. Grant Security Context Constraints (SCC)

OpenShift enforces security constraints via SCC. Grant the anyuid SCC to the default service account:

oc adm policy add-scc-to-user anyuid -z default -n zalando-operator

5. Verify the Operator Installation

Check that the operator pods are running:

oc get pods -l app=postgres-operator -n zalando-operator

Deploying the PostgreSQL Cluster

Option 1: Using a StorageClass

Apply the minimal manifest provided in the Zalando repository:

oc apply -f manifests/minimal-postgres-manifest.yaml

Option 2: Using NFS

Step 1: Create a Persistent Volume (PV)

Save the following PV definition to zalando-persistent-volume.yaml:

kind: PersistentVolume
metadata:
name: database-storage
annotations:
kubernetes.io/pv-protection: "true"
spec:
capacity:
storage: 100Gi
accessModes:
- ReadWriteOnce
persistentVolumeReclaimPolicy: Retain
volumeMode: Filesystem
nfs:
path: /VM/NFS/postgresql
server: 10.124.0.4

Apply the PV configuration:

oc apply -f zalando-persistent-volume.yaml

Step 2: Create a Persistent Volume Claim (PVC)

Save the following PVC definition to zalando-persistent-volume-claim.yaml:

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
name: pgdata-acid-cluster-avitech-0
namespace: zalando-cluster
labels:
application: spilo
cluster-name: acid-cluster-avitech
team: acid
spec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 100Gi
volumeName: database-storage
volumeMode: Filesystem
storageClassName: ""

Apply the PVC configuration:

oc apply -f zalando-persistent-volume-claim.yaml

Step 3: Grant SCC for the PostgreSQL Namespace

Grant the privileged SCC to the default service account in the zalando-cluster namespace:

oc adm policy add-scc-to-user privileged -z default -n zalando-cluster

Configuring Roles and RoleBindings

Role Configuration

Save the following Role definition to zalando-role.yaml:

apiVersion: rbac.authorization.k8s.io/v1
kind: Role
metadata:
  name: zalando-role
  namespace: zalando-cluster
rules:
- apiGroups: [""]
  resources:
    - endpoints
    - endpoints/restricted
  verbs:
    - get
    - list
    - watch
    - create
    - update
    - delete

Apply the Role:

oc apply -f zalando-role.yaml

RoleBinding Configuration

Save the following RoleBinding definition to zalando-rolebinding.yaml:

apiVersion: rbac.authorization.k8s.io/v1
kind: RoleBinding
metadata:
  name: zalando-rolebinding
  namespace: zalando-cluster
subjects:
  - kind: ServiceAccount
    name: postgres-pod
    namespace: zalando-cluster
roleRef:
  kind: Role
  name: zalando-role
  apiGroup: rbac.authorization.k8s.io

Apply the RoleBinding:

oc apply -f zalando-rolebinding.yaml

Verifying PostgreSQL Cluster Installation

Check that your PostgreSQL cluster is running:

oc get pods -l application=spilo -n zalando-cluster

Accessing the PostgreSQL Cluster

Option 1: Using Environment Variables

1.Retrieve the PostgreSQL credentials:

export PGPASSWORD=$(oc get secret postgres.acid-minimal-cluster.credentials.postgresql.acid.zalan.do -o 'jsonpath={.data.password}' | base64 -d)
export PGSSLMODE=require

2.Retrieve the cluster's service URL:

export HOST_PORT=$(oc get svc acid-minimal-cluster -o jsonpath='{.spec.clusterIP}:{.spec.ports[0].port}')
export PGHOST=$(echo $HOST_PORT | cut -d: -f 1)
export PGPORT=$(echo $HOST_PORT | cut -d: -f 2)

Connect using psql:
psql -U postgres

Option 2: Using Pod Exec

Access the PostgreSQL pod directly:

oc exec -it acid-cluster-avitech-0 -n zalando-cluster -- /bin/sh

Start psql inside the pod:

psql

Change the password if needed:

ALTER USER postgres WITH PASSWORD 'ExampleStrongPassword'

Important Note: JDBC Connection for Zalando PostgreSQL Cluster

To connect to the Zalando PostgreSQL cluster using JDBC, ensure your connection string is correctly configured. Below are the appropriate formats:

jdbc:postgresql://acid-cluster-avitech.zalando-cluster.svc.cluster.local:5432/postgres?postgres&ssl=true&sslmode=require

jdbc:postgresql://<svc-name>.<namespace>.svc.cluster.local:5432/postgres?postgres&ssl=true&sslmode=require

Key Points:

• Replace with the PostgreSQL service name

• Replace with the Kubernetes namespace where the cluster is deployed

• Ensure SSL is enabled (ssl=true) and the sslmode is set to require for secure connections.

Conclusion

Deploying a PostgreSQL cluster on OKD/OpenShift using the Zalando Postgres Operator is straightforward and highly scalable. By following this guide, you can seamlessly configure your database cluster with either a StorageClass or NFS, ensuring reliable storage and robust performance.

For further information, refer to the Zalando Postgres Operator Documentation.

Explore ITGix SRE services and learn more about our expertise.