Load Testing PostgreSQL on Kubernetes: A YAML-Only Approach

In today’s cloud-native landscape, simulating database operations effectively is essential for testing and performance evaluation. This article guides you through deploying a PostgreSQL database on Kubernetes (K8s) and setting up multiple pods to simulate operations on the database, all using YAML files.

Prerequisites

Before diving into the setup, ensure you have:

• A Kubernetes cluster up and running (Minikube, GKE, EKS, etc.)
• kubectl installed and configured to communicate with your cluster

Step 1: Create a Namespace

First, we’ll create a dedicated namespace for our PostgreSQL deployment.

# namespace.yaml
apiVersion: v1
kind: Namespace
metadata:
  name: postgres-sim

Run the following command to apply the namespace:

kubectl apply -f namespace.yaml

Step 2: Set Up Persistent Storage

Next, we need to set up persistent storage for our PostgreSQL database. This involves creating a Persistent Volume (PV) and a Persistent Volume Claim (PVC).

# postgres-storage.yaml
apiVersion: v1
kind: PersistentVolume
metadata:
  name: postgres-pv
  namespace: postgres-sim
spec:
  capacity:
    storage: 1Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: /data/postgres
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: postgres-pvc
  namespace: postgres-sim
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 1Gi

Apply the storage configuration:

kubectl apply -f postgres-storage.yaml

Step 3: Deploy PostgreSQL

Now we’ll deploy the PostgreSQL database using a Deployment and expose it via a Service.

# postgres-deployment.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres
  namespace: postgres-sim
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
      - name: postgres
        image: postgres:14
        env:
        - name: POSTGRES_DB
          value: mydb
        - name: POSTGRES_USER
          value: user
        - name: POSTGRES_PASSWORD
          value: password
        ports:
        - containerPort: 5432
        volumeMounts:
        - mountPath: /var/lib/postgresql/data
          name: postgres-storage
      volumes:
      - name: postgres-storage
        persistentVolumeClaim:
          claimName: postgres-pvc
---
apiVersion: v1
kind: Service
metadata:
  name: postgres
  namespace: postgres-sim
spec:
  selector:
    app: postgres
  ports:
    - protocol: TCP
      port: 5432
      targetPort: 5432

Apply the PostgreSQL deployment:

kubectl apply -f postgres-deployment.yaml

Step 4: Simulate Database Operations

To simulate operations on the PostgreSQL database, we’ll create a ConfigMap to store a simulation script and deploy it across three pods.

# db-simulation-script.yaml
apiVersion: v1
kind: ConfigMap
metadata:
  name: db-simulation-script
  namespace: postgres-sim
data:
  simulate.py: |
    import psycopg2
    import random
    import time

    conn = psycopg2.connect(
        dbname="mydb",
        user="user",
        password="password",
        host="postgres",
        port="5432"
    )
    cur = conn.cursor()

    cur.execute("""
    CREATE TABLE IF NOT EXISTS test_table (
        id SERIAL PRIMARY KEY,
        data INTEGER NOT NULL
    );
    """)
    conn.commit()

    operations = ["insert", "update", "delete", "select"]

    for _ in range(1000000):
        operation = random.choice(operations)
        if operation == "insert":
            cur.execute("INSERT INTO test_table (data) VALUES (%s)", (random.randint(1, 100),))
        elif operation == "update":
            cur.execute("UPDATE test_table SET data = %s WHERE id = %s", (random.randint(1, 100), random.randint(1, 10)))
        elif operation == "delete":
            cur.execute("DELETE FROM test_table WHERE id = %s", (random.randint(1, 10),))
        elif operation == "select":
            cur.execute("SELECT * FROM test_table LIMIT 1")
            print(cur.fetchone())

        conn.commit()
        time.sleep(1)

    cur.close()
    conn.close()

Now deploy the simulation pods:

# simulation-script.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  name: simulation-script-deployment
  namespace: postgres-sim
spec:
  replicas: 3
  selector:
    matchLabels:
      app: simulation-script
  template:
    metadata:
      labels:
        app: simulation-script
    spec:
      containers:
      - name: simulation-script
        image: python:3.9
        command: ["sh", "-c", "pip install psycopg2-binary && python /scripts/simulate.py"]
        volumeMounts:
        - name: script-volume
          mountPath: /scripts
        env:
        - name: PYTHONUNBUFFERED
          value: "1"
      volumes:
      - name: script-volume
        configMap:
          name: db-simulation-script

Apply the simulation deployment:

kubectl apply -f db-simulation-script.yaml
kubectl apply -f simulation-script.yaml

Step 5: Monitor the Operations

You can check the PostgreSQL database to see how the simulated operations have affected the data:

kubectl exec -it postgres-<pod-name> -n postgres-sim -- psql -U user -d mydb

Replace with the actual name of the PostgreSQL pod. You can query the table to see the number of records:

SELECT count(1) FROM test_table;

Conclusion

This setup can be useful for testing, load evaluation, and performance analysis. You can extend this further by introducing different workloads or modifying the simulation script to suit your needs.