Introduction
PostgreSQL is one of the most popular open-source relational databases, and deploying it on Kubernetes (K8s) ensures scalability, high availability, and fault tolerance. This guide walks you through deploying PostgreSQL on Kubernetes using a NodePort service, allowing external access to the database.
We will also cover common issues faced during deployment and how to resolve them.
Step 1: Setting Up PostgreSQL on Kubernetes
1.1 Create a Persistent Volume (PV)
A Persistent Volume (PV) ensures PostgreSQL data persists even if the pod restarts.
Save the following YAML as psql-pv.yaml:
apiVersion: v1
kind: PersistentVolume
metadata:
name: postgres-volume
labels:
type: local
app: postgres
spec:
storageClassName: manual
capacity:
storage: 20Gi
accessModes:
- ReadWriteMany
hostPath:
path: /data/postgresql💡 Explanation:
- 20Gi storage is allocated for PostgreSQL.
- ReadWriteMany (
RWX) allows multiple pods to read and write. hostPathensures data persistence on the node.
Apply the PV:
kubectl apply -f psql-pv.yaml1.2 Create a Persistent Volume Claim (PVC)
A Persistent Volume Claim (PVC) allows the PostgreSQL pod to request storage.
Save the following as psql-claim.yaml:
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
name: postgres-volume-claim
labels:
app: postgres
spec:
storageClassName: manual
accessModes:
- ReadWriteMany
resources:
requests:
storage: 20GiApply the PVC:
kubectl apply -f psql-claim.yaml💡 PVC will bind to the Persistent Volume (postgres-volume) automatically.
1.3 Create a ConfigMap for PostgreSQL Environment Variables
The ConfigMap stores database configuration parameters.
Save the following as postgres-configmap.yaml:
apiVersion: v1
kind: ConfigMap
metadata:
name: postgres-secret
labels:
app: postgres
data:
POSTGRES_DB: core_mdm_dev
POSTGRES_USER: app_user
POSTGRES_PASSWORD: =MFIck)d]#F;vE98ffmRApply the ConfigMap:
kubectl apply -f postgres-configmap.yaml1.4 Deploy PostgreSQL
Now, create the PostgreSQL Deployment file and save it as ps-deployment.yaml:
apiVersion: apps/v1
kind: Deployment
metadata:
name: postgres
namespace: postgres
spec:
replicas: 1
selector:
matchLabels:
app: postgres
template:
metadata:
labels:
app: postgres
spec:
containers:
- name: postgres
image: 'postgres:16'
imagePullPolicy: IfNotPresent
ports:
- containerPort: 5432
envFrom:
- configMapRef:
name: postgres-secret
volumeMounts:
- mountPath: /var/lib/postgresql/data
name: postgresdata
volumes:
- name: postgresdata
persistentVolumeClaim:
claimName: postgres-volume-claimApply the deployment:
kubectl apply -f ps-deployment.yaml1.5 Expose PostgreSQL Using NodePort
A NodePort service allows external access to the PostgreSQL database.
Save the following as ps-service.yaml:
apiVersion: v1
kind: Service
metadata:
name: postgres
labels:
app: postgres
spec:
type: NodePort
selector:
app: postgres
ports:
- name: postgres
port: 5432 # The port PostgreSQL uses
targetPort: 5432 # The port on the container (should be the same as 'port')
nodePort: 31543 # NodePort that Kubernetes should expose
protocol: TCPApply the service:
kubectl apply -f ps-service.yamlStep 2: Verify PostgreSQL Deployment
Check if the PostgreSQL pod is running:
kubectl get pods -n postgresIf running successfully, you should see:
NAME READY STATUS RESTARTS AGE
postgres-xxxxxxxxxx-xxxxx 1/1 Running 0 10sNow, connect to PostgreSQL inside the pod:
kubectl exec -it $(kubectl get pods -n postgres -l app=postgres -o jsonpath='{.items[0].metadata.name}') -n postgres -- psql -U app_user -d postgresList available databases:
\lCommon Issues and How to Fix Them
1.1 PostgreSQL Pod in CrashLoopBackOff or Error State
Cause: Corrupt WAL (Write-Ahead Log)
PANIC: could not locate a valid checkpoint recordSolution: Reset WAL Logs
- Start a recovery pod:
kubectl apply -f postgres-recovery.yaml- Connect and reset WAL logs:
kubectl exec -it postgres-recovery -n postgres -- bash
su - postgres
/usr/lib/postgresql/16/bin/pg_resetwal -f /var/lib/postgresql/data- Delete the recovery pod and restart PostgreSQL:
kubectl delete pod postgres-recovery -n postgres
kubectl rollout restart deployment postgres -n postgres1.2 Cannot Delete a Database (Ghost Database)
Cause: Active connections or system corruption
ERROR: database "lgn_section_dev" does not existSolution:
- Terminate active connections:
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'lgn_section_dev';- Manually delete the corrupted database entry:
DELETE FROM pg_database WHERE datname = 'lgn_section_dev';- Remove database files from storage (inside pod):
rm -rf /var/lib/postgresql/data/base/<OID>- Restart PostgreSQL:
kubectl rollout restart deployment postgres -n postgres1.3 External Connection Failing
Cause: PostgreSQL is not accepting external connections
Check if PostgreSQL is listening on all IPs:
SHOW listen_addresses;If not, update postgresql.conf:
ALTER SYSTEM SET listen_addresses TO '*';Restart PostgreSQL:
kubectl rollout restart deployment postgres -n postgresConclusion
Deploying PostgreSQL on Kubernetes with NodePort is a powerful way to manage database workloads in a containerized environment. By setting up Persistent Volumes, ConfigMaps, and a NodePort Service, we ensure data persistence and external access.
