Mastering high availability: PostgreSQL meets Kubernetes(En)
In any era, “high availability” has consistently been a priority when establishing various services in enterprise IT systems.
This is particularly crucial in today’s landscape where services are becoming more complex and must be accessible at all times. As a result, from frontend application services to associated databases, deployment designs are crafted with a focus on ensuring high availability.
This guide will walk you through leveraging Kubernetes features to provide PostgreSQL DB services in a high-availability (HA) architecture. Of course, it also covers the related implementation steps and explanations.
- Basic high-level diagram
- Prerequisites
- Deployment or StatefulSet?
- HA components
- LB
- Verify
- Conclusion
1. Basic high-level diagram
2. Prerequisites
Git repo: https://github.com/scriptcamp/kubernetes-postgresql.git#------------------------------------------------
# S2-1. create namespace
#------------------------------------------------
[master]# kubectl create namespace database#------------------------------------------------
# S2-2. Create a configmap for file mounting inside the container.
#------------------------------------------------
[master]# vim postgres-configmap.yaml
[master]# kubectl create -f postgres-configmap.yaml -n database
[master]# kubectl get cm
NAME DATA AGE
kube-root-ca.crt 1 29s
postgres-configmap 1 7s
※ This file's purpose is to perform the following actions:
(1) "pre-stop.sh" script handles tasks before stopping the db service.
(2) Checks the type of component being stopped (e.g., master/follower).
(3) If the master is in the process of stopping,
it waits until a follower is promoted to master before proceeding.
(4) Ensures the HA structure with at least one writable master.#------------------------------------------------
# S2-3. Create PostgreSQL SVC
# Create a service for inter-pod communication,
# with two types available(headless serivce/Service)
#------------------------------------------------
[master]# vim postgres-headless-svc.yaml
apiVersion: v1
kind: Service
metadata:
name: postgres-headless-svc
spec:
type: ClusterIP
clusterIP: None
ports:
- name: postgresql
port: 5432
targetPort: postgresql
protocol: TCP
selector:
app: postgres
[master]# kubectl create -f postgres-headless-svc.yaml
[master]# kubectl get all -n database
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
service/postgres-headless-svc ClusterIP None <none> 5432/TCP
※ Note:
Typically, a service uses LB (round-robin) for traffic load balancing,
but a headless service doesn't. It also doesn't get assigned a ClusterIP.
To set up the PostgreSQL server, a headless service is employed here,
especially for the upcoming PostgreSQL statefulset.#------------------------------------------------
# S2-4. Create PostgreSQL server secret
# Used for storing passwords.
# In a Prod environment, it is recommended to employ other secret management
# solutions for better security.
#------------------------------------------------
[master]# vim postgres-secrets.yaml
apiVersion: v1
kind: Secret
metadata:
name: postgres-secrets
spec:
postgresql-password: "root123"
repmgr-password: "root123"
[master]# kubectl create -f postgres-secrets.yaml
[master]# kubectl describe secret postgres-secrets
3. Deployment or StatefulSet?
Typically, when deploying application services, the “deployment” type is commonly used. However, when deploying databases, a persistent storage is employed because databases store all data, such as tables and users, in volumes. To allow Pods to scale based on loads and maintain data consistency, two resource types are compared below:
- Deployment: The name of the Pod changes every time it is deleted or rebuilt, making it unsuitable for Pod identification.
- StatefulSet: Once a Pod is assigned a name, it remains unchanged regardless of deletion or reconstruction, making it suitable for Pod identification. Additionally, due to its sequential nature, StatefulSet ensures that volumes can be mounted back to their original state. This is the common approach for most stateful applications.
In this setup, the data is initially written to postgres-0, and then it gets synchronized to other replicas.
Now, here’s the issue:
- How does postgres-1 know where to find postgres-0?
- And how does postgres-2 know where to find postgres-1?
If you’re using the StatefulSet type, each Pod can discover the necessary Pods based on the naming convention. StatefulSets provide a predictable naming pattern where each Pod has a sequential index and the Pod names remain unchanged. This allows each Pod to locate the Pods it needs to discover by following the naming principles.
#------------------------------------------------
# S3-1. Create postgres statefulset
#------------------------------------------------
[master]# vim postgres-statefulset.yaml
[master]# kubectl create -f postgres-statefulset.yaml
[master]# kubectl get all
※ Note:
(1) Inject metadata as variables: Assign pod name and pod namespace to the Pod as environment variables.
(2) Inject sensitive data as variables: For example, inject the database password as a variable into the postgres container.
(3) Probes: Ensure the process doesn't get stuck, and if it does, automatically restart (using the postgres command in this case).
(4) VolumeClaimTemplate: Allows StatefulSet to correctly create volumes for replicas' use.
(5) This lab utilizes the nfs storage class for data volume.
(6) The following are key parameters:
POSTGRESQL_VOLUME_DIR: Directory where Postgres stores config and data; this directory needs to be mounted to PVC.
PGDATA: The primary PG data directory.
POSTGRES_USER: User directory automatically created during installation.
POSTGRES_PASSWORD: Default password for the created user.
POSTGRES_DB: DB that should be automatically generated when the main program starts.
4. HA components
This article employs RegMgr to handle the following two tasks:
- Replication: Copying data from the Primary server to other replicas, effectively reducing server loading and distributing read and write requests.
- Failover: Managing fault-tolerant switching within the cluster, such as promoting other nodes to the primary node.
All configurations are in postgres-statefulset.yaml. Here are explanations for several key parts:
- REPMGR_PARTNER_NODES: Comma-separated list of all hostnames.
- REPMGR_PRIMARY_HOST: The name of the primary server.
- REPMGR_USERNAME: User for executing RegMgr.
- REPMGR_PASSWORD: User password.
- REPMGR_DATABASE: Database used by RegMgr.
Now, let’s obtain the IP address of each Pod.
#------------------------------------------------
# S4-1. obtain headless svc information
#------------------------------------------------
[master]# kubectl get pod -o wide
5. LB
In this setup, Pgpool is used as a middleware in front of the Postgres server, serving as the gatekeeper for the entire cluster.
The main objectives are Load Balancing and Limiting the requests:
- Load Balancing: Pgpool manages connections for requests and queries, analyzing these queries to determine which ones to forward. Read requests are sent to read-only nodes, while write requests are directed only to the primary node, achieving load balancing capabilities.
- Limiting Concurrent Connections: Pgpool is configured to restrict the number of concurrent connections.
#------------------------------------------------
# S5-1. Create pgpool secret
#------------------------------------------------
[master]# vim pgpool-secret.yaml
apiVersion: v1
kind: Secret
metadata:
name: pgpool-secrets
data:
admin-password: "SFRzaVZxYjdSZQ=="
[master]# kubectl create -f pgpool-secret.yaml
[master]# kubectl get secrets
#------------------------------------------------
# S5-2. Create pgpool svc (if external access is required, use NodePort)
#------------------------------------------------
[master]# vim pgpool-svc.yaml
apiVersion: v1
kind: Service
metadata:
name: pgpool-svc
spec:
type: ClusterIP
sessionAffinity: None
ports:
- name: postgresql
port: 5432
targetPort: postgresql
protocol: TCP
nodePort: null
selector:
app: pgpool
[master]# kubectl create -f pgpool-svc.yaml
---------
[master]# vim pgpool-svc-nodeport.yaml
apiVersion: v1
kind: Service
metadata:
name: pgpool-svc-external
spec:
type: NodePort
ports:
- name: postgresql
port: 5432
targetPort: postgresql
protocol: TCP
nodePort: 32000
selector:
app: pgpool
[master]# kubectl create -f pgpool-svc-nodeport.yaml
[master]# kubectl get svc
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
service/pgpool-svc-external NodePort 10.100.50.5 <none> 5432:32000/TCP 3s
service/postgres-headless-svc ClusterIP None <none> 5432/TCP 23h#------------------------------------------------
# S5-3. Deploy pgpool
#------------------------------------------------
[master]# kubectl create -f pgpool-deployment.yaml
[master]# kubectl get all
6. Verify
#------------------------------------------------
# S6-1. Create client pod
#------------------------------------------------
[master]# vim psql-client.yaml
---
apiVersion: v1
kind: Pod
metadata:
name: pg-client
spec:
containers:
- image: bitnami/postgresql:11.12.0-debian-10-r13
name: postgresql
env:
- name: ALLOW_EMPTY_PASSWORD
value: "yes"
[master]# kubectl create -f psql-client.yaml#------------------------------------------------
# S6-2. Obtain password
#------------------------------------------------
[master]# kubectl get secret postgres-secrets -n database -o jsonpath="{.data.postgresql-password}" | base64 --decode
WbrTpN3g7q#------------------------------------------------
# S6-3. Perform connection
#------------------------------------------------
[master]# kubectl exec -it pg-client -n database -- /bin/bash
1001@pg-client:/$ PGPASSWORD=WbrTpN3g7q psql -h pgpool-svc -p 5432 -U postgres (inside)
1001@pg-client:/$ PGPASSWORD=WbrTpN3g7q psql -h 10.107.88.16 -p 32000 -U postgres (outside)
psql (11.12)
Type "help" for help.
postgres=#
postgres=# create database db1;
postgres=# \c db1; //to connect to new database
postgres=# create table test (id int primary key not null, value text not null);
postgres=# insert into test values (1, 'value1');
postgres=# select * from test;
#------------------------------------------------
# S6-4. Confirm data syc status
#------------------------------------------------
postgres=# select * from pg_stat_replication;
#--------------------------------------------------
# S6-5. After deleting the primary, verify the status of the remaining followers.
#--------------------------------------------------
[master]# kubectl logs -f postgres-sts-2
[note]
Since the StatefulSet sets the primary host to sts-0,
deleting sts-0 might cause sync issues with RegMgr (getting stuck at sts-0).
To resolve this, edit the StatefulSet to modify the REPMGR_PRIMARY_HOST parameter.
Afterward, delete sts-0 and recreate it.
7. Conclusion
In today’s tech landscape, many apps are moving to containerized platforms, just like the shift we saw from traditional servers to virtualization.
While databases usually adapt to new architectures later due to performance concerns, this guide shows how they can thrive on container platforms. As more apps make this transition, remember that besides availability, performance is key for a smooth user experience. The setup here, with its ability to auto-scale Pods in response to heavy queries, tackles this performance challenge effectively.
References:
