PostgreSQL on Kubernetes with Longhorn Storage

This guide explains how to deploy PostgreSQL on Kubernetes using StatefulSets with Longhorn storage for persistent data. We’ll set up a production-ready PostgreSQL instance with proper resource management, health checks, and encrypted secrets using FluxCD GitOps workflow.

Why Use StatefulSets for PostgreSQL?

Benefits of this setup:

Persistent data: Survives pod restarts and rescheduling
Longhorn storage: Distributed storage with replication and backup capabilities
Resource management: Proper CPU and memory limits
Health monitoring: Liveness and readiness probes
Encrypted secrets: Secure credential management with SOPS

Prerequisites

Kubernetes cluster with Longhorn storage installed
FluxCD installed and configured in your cluster
Worker nodes with nfs-common package installed
Git repository configured with FluxCD
SOPS configured for secret encryption (see FluxCD SOPS guide)

Step 1: Prepare Worker Nodes

Install NFS utilities on worker nodes

Connect to each worker node and install the required package:
Terminal window
```
sudo apt install -y nfs-common
```
Verify Longhorn storage class
Terminal window
```
kubectl get storageclass longhorn
```

Step 2: Create Namespace

Create namespace manifest

Create namespace.yaml:

apiVersion: v1
kind: Namespace
metadata:
  name: postgres
  labels:
    name: postgres

Step 3: Create PostgreSQL Secret

Create secret manifest

Create secret.yaml with your PostgreSQL credentials:

apiVersion: v1
kind: Secret
metadata:
  name: postgres-secret
  namespace: postgres
  labels:
    app: postgres
type: Opaque
stringData:
  POSTGRES_DB: main_db
  POSTGRES_USER: thomas
  POSTGRES_PASSWORD: YourSecurePassword123!

Encrypt the secret with SOPS

Before committing to Git, encrypt the secret:
Terminal window
```
sops --encrypt --in-place secret.yaml
```

Verify encryption The secret values should now be encrypted:

apiVersion: v1
kind: Secret
metadata:
  name: postgres-secret
  namespace: postgres
  labels:
    app: postgres
type: Opaque
stringData:
  POSTGRES_DB: ENC[AES256_GCM,data:...
  POSTGRES_USER: ENC[AES256_GCM,data:...
  POSTGRES_PASSWORD: ENC[AES256_GCM,data:...

Step 4: Create PostgreSQL StatefulSet

Create StatefulSet manifest

Create deployment.yaml:

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres
  namespace: postgres
spec:
  replicas: 1
  serviceName: postgres
  persistentVolumeClaimRetentionPolicy:
    whenDeleted: Retain
    whenScaled: Retain
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres
          image: "postgres:14.8"
          imagePullPolicy: IfNotPresent
          ports:
            - containerPort: 5432
          env:
            - name: PGDATA
              value: /var/lib/postgresql/data/pgdata
          envFrom:
            - secretRef:
                name: postgres-secret
          resources:
            requests:
              memory: "256Mi"
              cpu: "250m"
            limits:
              memory: "1Gi"
              cpu: "1000m"
          livenessProbe:
            exec:
              command:
                - /bin/sh
                - -c
                - exec pg_isready -U "$POSTGRES_USER" -d "$POSTGRES_DB" -h 127.0.0.1 -p 5432
            initialDelaySeconds: 30
            periodSeconds: 10
            timeoutSeconds: 5
            failureThreshold: 6
          readinessProbe:
            exec:
              command:
                - /bin/sh
                - -c
                - exec pg_isready -U "$POSTGRES_USER" -d "$POSTGRES_DB" -h 127.0.0.1 -p 5432
            initialDelaySeconds: 5
            periodSeconds: 10
            timeoutSeconds: 5
            failureThreshold: 6
          volumeMounts:
            - mountPath: /var/lib/postgresql/data
              name: postgresdata
  volumeClaimTemplates:
    - metadata:
        name: postgresdata
        labels:
          app: postgres
      spec:
        storageClassName: longhorn
        accessModes:
          - ReadWriteOnce
        resources:
          requests:
            storage: 20Gi

Understanding the configuration Key configuration details:
- PGDATA: Custom data directory to avoid permission issues
- Resources: Memory and CPU limits for production use
- Probes: Health checks ensure database is ready and responsive
- Volume: 20GB persistent storage with Longhorn
- Retention policy: Keeps data when StatefulSet is deleted

Step 5: Create PostgreSQL Service

Create service manifest

Create service.yaml:

apiVersion: v1
kind: Service
metadata:
  name: postgres
  namespace: postgres
  labels:
    app: postgres
spec:
  type: NodePort
  ports:
    - protocol: TCP
      name: postgres
      port: 5432
      targetPort: 5432
      nodePort: 30002
  selector:
    app: postgres

Service configuration options Choose the appropriate service type for your needs:
- NodePort: External access via node IP and port 30002
- ClusterIP: Internal cluster access only (change type: ClusterIP and remove nodePort)
- LoadBalancer: External load balancer (if supported by your cluster)

Step 6: Create Kustomization

Create kustomization manifest

Create kustomization.yaml to deploy all resources together:

apiVersion: kustomize.config.k8s.io/v1beta1
kind: Kustomization

resources:
  - namespace.yaml
  - secret.yaml
  - deployment.yaml
  - service.yaml

Add to infrastructure kustomization

PostgreSQL is managed as part of my infrastructure setup. Make sure your postgress kustomization is being picked up by flux:
```
apiVersion: kustomize.config.k8s.io/v1beta1
kind: Kustomization

resources:
  - ./traefik
  - ./longhorn
  - ./postgres
```

Repository structure

infrastructure/
├── kustomization.yaml  # Main infrastructure kustomization
├── traefik/
├── longhorn/
└── postgres/
    ├── namespace.yaml
    ├── secret.yaml
    ├── deployment.yaml
    ├── service.yaml
    └── kustomization.yaml

Step 7: Deploy via GitOps

Commit PostgreSQL files to Git

git add infrastructure/postgres/
git commit -m "Add PostgreSQL deployment to infrastructure"
git push origin main

Wait for FluxCD to reconcile FluxCD will automatically detect the changes and deploy PostgreSQL as part of your infrastructure:

# Watch infrastructure reconciliation
flux get kustomizations -w

# Check infrastructure kustomization status
flux get kustomization infrastructure

Verify deployment

# Check StatefulSet status
kubectl get statefulset -n postgres
kubectl get pods -n postgres

# Verify persistent volume
kubectl get pvc -n postgres
kubectl get pv

# Check service
kubectl get service -n postgres

Monitor logs
Terminal window
```
kubectl logs -n postgres postgres-0
```

Step 8: Test PostgreSQL Connection

Connect from within the cluster

kubectl exec -it -n postgres postgres-0 -- psql -U thomas -d main_db

Test basic operations

-- Create a test table
CREATE TABLE test_table (id SERIAL PRIMARY KEY, name VARCHAR(50));

-- Insert test data
INSERT INTO test_table (name) VALUES ('Hello PostgreSQL');

-- Query the data
SELECT * FROM test_table;

-- Exit
\q

Connect externally (if using NodePort)

psql -h <node-ip> -p 30002 -U thomas -d main_db