Deploying PostgreSQL on Kubernetes with NodePort: A Complete Guide

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:

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.
hostPath ensures data persistence on the node.

Apply the PV:

bash
kubectl apply -f psql-pv.yaml

1.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:

yaml
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: postgres-volume-claim
  labels:
    app: postgres
spec:
  storageClassName: manual
  accessModes:
    - ReadWriteMany
  resources:
    requests:
      storage: 20Gi

Apply the PVC:

bash
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:

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;vE98ffmR

Apply the ConfigMap:

bash
kubectl apply -f postgres-configmap.yaml

1.4 Deploy PostgreSQL

Now, create the PostgreSQL Deployment file and save it as ps-deployment.yaml:

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-claim

Apply the deployment:

bash
kubectl apply -f ps-deployment.yaml

1.5 Expose PostgreSQL Using NodePort

A NodePort service allows external access to the PostgreSQL database. Save the following as ps-service.yaml:

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: TCP

Apply the service:

bash
kubectl apply -f ps-service.yaml

Step 2: Verify PostgreSQL Deployment

Check if the PostgreSQL pod is running:

bash
kubectl get pods -n postgres

If running successfully, you should see:

text
NAME                        READY   STATUS    RESTARTS   AGE
postgres-xxxxxxxxxx-xxxxx   1/1     Running   0          10s

Now, connect to PostgreSQL inside the pod:

bash
kubectl exec -it $(kubectl get pods -n postgres -l app=postgres -o jsonpath='{.items[0].metadata.name}') -n postgres -- psql -U app_user -d postgres

List available databases:

sql
\l

Common Issues and How to Fix Them

1.1 PostgreSQL Pod in `CrashLoopBackOff` or `Error` State

Cause: Corrupt WAL (Write-Ahead Log)

log
PANIC: could not locate a valid checkpoint record

Solution: Reset WAL Logs

Start a recovery pod:

bash
kubectl apply -f postgres-recovery.yaml

Connect and reset WAL logs:

bash
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:

bash
kubectl delete pod postgres-recovery -n postgres
kubectl rollout restart deployment postgres -n postgres

1.2 Cannot Delete a Database (Ghost Database)

Cause: Active connections or system corruption

sql
ERROR:  database "lgn_section_dev" does not exist

Solution:

Terminate active connections:

sql
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'lgn_section_dev';

Manually delete the corrupted database entry:

sql
DELETE FROM pg_database WHERE datname = 'lgn_section_dev';

Remove database files from storage (inside pod):

bash
rm -rf /var/lib/postgresql/data/base/<OID>

Restart PostgreSQL:

bash
kubectl rollout restart deployment postgres -n postgres

1.3 External Connection Failing

Cause: PostgreSQL is not accepting external connections

Check if PostgreSQL is listening on all IPs:

sql
SHOW listen_addresses;

If not, update postgresql.conf:

sql
ALTER SYSTEM SET listen_addresses TO '*';

Restart PostgreSQL:

bash
kubectl rollout restart deployment postgres -n postgres

Conclusion

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.