Deploy PostgreSQL containers in Kubernetes with Azure K8s Service

Inspired by the document of MSSQL in Azure Kubernetes Services, Kubernetes And Databases and How to run HA Postgres on Kubernetes

Prerequisite

  1. K8s Cluster
  2. Cloud provider (Digitalocean, Azure, AWS)
  3. Deploying PostgreSQL in Kubernetes contains following steps:
    • Image of PostgreSQL Docker
    • ConfigMap to store the configuration-info of Postgres
    • Persistent Volume
    • K8s Deployment of PostgreSQL
    • K8s Service of PostgreSQL

Image of PostgreSQL Docker

Here I use the official image of PostgreSQL 11.7 from DockerHub

ConfigMap of Postgres

apiVersion: v1
kind: ConfigMap
metadata:
   name: postgres-config
   labels:
       app: postgres
   namespace: dev
data:
    POSTGRES_DB: postgresdb
    POSTGRES_USER: postgresuser
    POSTGRES_PASSWORD: user12345

Then create postgres ConfigMap resource

$ kubectl create -f postgres-configmap.yaml 
configmap "postgres-config" created

Persistent Volume

As we know, docker container is essentially temporary. After the container instance is terminated, all data generated by or in the container will be lost. In order to save the data, Persistent Volume and Persistent Volume Claim resources are used in Kubernetes to store the data in persistent storage.
Here, local directory/path as the permanent storage resource (/mnt/data) is applied

kind: PersistentVolume
apiVersion: v1
metadata:
  name: postgres-pv-volume
  labels:
    type: local
    app: postgres
spec:
  storageClassName: manual
  capacity:
    storage: 5Gi
  accessModes:
    - ReadWriteMany
  hostPath:
    path: "/mnt/data"
---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: postgres-pv-claim
  labels:
    app: postgres
spec:
  storageClassName: manual
  accessModes:
    - ReadWriteMany
  resources:
    requests:
      storage: 5Gi

If you are using DigitalOcean like me, then you may not need Persistent Volume, because your k8s will have a SC named do-block-storage by default.
So we only need one PVC:

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: postgres-pvc
spec:
  accessModes:
  - ReadWriteOnce
  resources:
    requests:
      storage: 5Gi
  storageClassName: do-block-storage

Deployment of PostgreSQL

The PostgreSQL container uses the PostgreSQL configuration in the configmap we created earlier, such as username, password, and database name. It will also mount the volume created from the persistent volume and declare to make the data of the PostgreSQL container persistent.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres-deployment
spec:
  strategy:
    type: Recreate
  selector:
    matchLabels:
      app: postgres
  replicas: 1
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres
          image: postgres:11.7
          imagePullPolicy: "IfNotPresent"
          ports:
            - containerPort: 5050
          envFrom:
            - configMapRef:
                name: postgres-config
          volumeMounts:
            - mountPath: /var/lib/postgresql/data
              name: postgredb
      volumes:
        - name: postgredb
          persistentVolumeClaim:
            claimName: postgres-pv-claim

Create Postgres deployment：

$ kubectl create -f postgres-deployment.yaml 
deployment "postgres" created

K8s Service of PostgreSQL

To access the deployment or container, we need to expose the PostgreSQL Deployment. Kubernetes provides different types of services, such as ClusterIP, NodePort and LoadBalancer.

Using Cluster IP, we can access the PostgreSQL service in Kubernetes. NodePort can expose service endpoints on Kubernetes nodes. In order to access PostgreSQL from the outside, we need to use a Load Balancer service type, which can be exposed externally.
postgres-service.yaml:

apiVersion: v1
kind: Service
metadata:
  name: postgres-service
  labels:
    app: postgres
spec:
  type: NodePort
  ports:
  - port: 5050
    targetPort: 5050
    protocol: TCP
  selector:
   app: postgres

Then create Postgres Service：

$ kubectl create -f postgres-service.yaml 
service "postgres" created

Deploy a HA (highly available) PostgreSQL cluster in K8s

The database high-availability solution in this article is master-slave replication + read-write separation, which consists of a single master and multiple slaves. Among them, the client writes to the database through the master, and reads through the slave. After the master has a problem, you can switch the application to the slave side.

avatar

Crunchy

PostgreSQL has many high-availability solutions, such as Pgpool II, Bucardo, PostgresXL and Postgres-XC. Crunchy is a containerized solution that can quickly deploy PostgreSQL.

PostgreSQL Operator is a kubernetes application controller for running production-level PostgreSQL on Kubernetes. It simplifies the deployment of PostgreSQL clusters and provides functions such as cluster configuration management, high availability, planned backup, disaster recovery, monitoring, and connection pooling.

Install Crunchy PostgreSQL Operator

For the persistence of date across storage, k8s needs to create storage classes first, and PostgreSQL Operator supports storage classes such as NFS and ceph. Here, rook-ceph-block for example.

[root@k8s-xytong-master ~]# kubectl get sc
NAME              PROVISIONER         RECLAIMPOLICY   VOLUMEBINDINGMODE   ALLOWVOLUMEEXPANSION   AGE
ceph-storage      kubernetes.io/rbd   Delete          Immediate           true                   4d1h
rook-ceph-block   kubernetes.io/rbd   Delete          Immediate           true                   3d23h

Create namespace and download yaml file

kubectl create namespace pgo
curl https://raw.githubusercontent.com/CrunchyData/postgres-operator/v4.6.1/installers/kubectl/postgres-operator.yml > postgres-operator.yml

Add following lines of environment variables to the above .yml file, to set the storage class:

- name: BACKREST_STORAGE
              value: "rook"
            - name: BACKUP_STORAGE
              value: "rook"
            - name: PRIMARY_STORAGE
              value: "rook"
            - name: REPLICA_STORAGE
              value: "rook"
            - name: STORAGE9_SIZE
              value: "50G"
# install postgres operator
kubectl apply -f  postgres-operator.yml

Install pgo client

curl https://raw.githubusercontent.com/CrunchyData/postgres-operator/v4.6.1/installers/kubectl/client-setup.sh > client-setup.sh
chmod +x client-setup.sh
./client-setup.sh

cat <<EOF >> ~/.bashrc
export PGOUSER="{HOME?}/.pgo/pgo/pgouser"
export PGO_CA_CERT="{HOME?}/.pgo/pgo/client.crt"
export PGO_CLIENT_CERT="{HOME?}/.pgo/pgo/client.crt"
export PGO_CLIENT_KEY="{HOME?}/.pgo/pgo/client.key"
export PGO_APISERVER_URL='https://127.0.0.1:8443'
export PGO_NAMESPACE=pgo
EOF

source ~/.bashrc

And configuration:

kubectl -n pgo port-forward svc/postgres-operator 8443:8443 &

wget https://github.com/CrunchyData/postgres-operator/releases/download/v4.6.1/pgo
chmod +x pgo 
mv pgo /usr/local/bin/

# RHEL 
yum install socat
# Debian 
sudo apt-get install socat 
# SUSE
sudo zypper in socat

Install PostgreSQL Cluster

PostgreSQL Operator can be highly customized and can be set according to your actual situation:

# PostgreSQL cluster name
pgo_cluster_name=hippo
# PostgreSQL deploys namespace
cluster_namespace=pgo
# PostgreSQL configuration
cat postgres-ha.yaml
---
bootstrap:
  dcs:
    postgresql:
      parameters:
        max_connections: 30
        shared_buffers: 2GB
        password_encryption: scram-sha-256
# create hippo-custom-config configmap
kubectl -n pgo create configmap hippo-custom-config --from-file=postgres-ha.yaml

# supersecure password
kubectl create secret generic -n "{cluster_namespace}" "{pgo_cluster_name}-postgres-secret" \
  --from-literal=username=postgres \
  --from-literal=password=Supersecurepassword*

# synchronized password
kubectl create secret generic -n "{cluster_namespace}" "{pgo_cluster_name}-primaryuser-secret" \
  --from-literal=username=primaryuser \
  --from-literal=password=Anothersecurepassword*

# hippo user
kubectl create secret generic -n "{cluster_namespace}" "{pgo_cluster_name}-hippo-secret" \
  --from-literal=username=hippo \
  --from-literal=password=Moresecurepassword*

Create PostgreSQL cluster

pgo create cluster ${pgo_cluster_name} \
  --cpu=1.0 --cpu-limit=4.0 \
  --memory=2Gi --memory-limit=6Gi \
  --metrics \
  --exporter-cpu=0.5 --exporter-cpu-limit=1.0 \
  --exporter-memory=256Mi --exporter-memory-limit=1Gi \
  --replica-count=2 \
  --replica-storage-config='rook' \
  --wal-storage-config='rook' \
  --wal-storage-size=10Gi  \
  --custom-config=hippo-custom-config \
  --pgbouncer \
  --storage-config='rook' \
  --pvc-size=20Gi \
  --pgbackrest-cpu=0.5 --pgbackrest-cpu-limit=1.0 \
  --pgbackrest-memory=256Mi --pgbackrest-memory-limit=1Gi \
  --pgbackrest-pvc-size=100Gi \
  --pgbackrest-storage-config='rook' \
  --username=hippo
  • metrics: enable PostgreSQL exporter，access to Prometheus monitoring
  • pgbouncer: enable pgbouncer connection pooling
  • replica-count=2: Create a HA PostgreSQL cluster with 2 replicas
  • sync-replication: Enable synchronous replication to prevent transaction loss

The production environment can set pod scheduling strategy to avoid pod running on the same node.
- pod-anti-affinity
- node-label=region=us-east-1
- node-affinity-type=required

Access to Prometheus

---
apiVersion: monitoring.coreos.com/v1
kind: ServiceMonitor
metadata:
  annotations:
    meta.helm.sh/release-name: demo
    meta.helm.sh/release-namespace: monitoring
  labels:
    app: prometheus-operator-postgres-replica-exporter
    app.kubernetes.io/managed-by: Helm
    chart: prometheus-operator-9.3.2
    heritage: Helm
    release: demo
  name: demo-prometheus-operator-postgres-replica-exporter
  namespace: monitoring
spec:
  endpoints:
  - port: postgres-exporter
    path: /metrics
    interval: 30s 
  namespaceSelector:
    matchNames:
    - pgo
  selector:
    matchLabels:
      name: hippo-replica
      pg-cluster: hippo
      vendor: crunchydata
---
apiVersion: monitoring.coreos.com/v1
kind: ServiceMonitor
metadata:
  annotations:
    meta.helm.sh/release-name: demo
    meta.helm.sh/release-namespace: monitoring
  labels:
    app: prometheus-operator-postgres-exporter
    app.kubernetes.io/managed-by: Helm
    chart: prometheus-operator-9.3.2
    heritage: Helm
    release: demo
  name: demo-prometheus-operator-postgres-exporter
  namespace: monitoring
spec:
  endpoints:
  - port: postgres-exporter
    path: /metrics
    interval: 30s 
  namespaceSelector:
    matchNames:
    - pgo
  selector:
    matchLabels:
      name: hippo
      pg-cluster: hippo
      vendor: crunchydata

Further functions:
1. Logical backup
2. Failover
3. to be continue ...