Posted on Oct 20, 2024

Simulating Database Operations with PostgreSQL on Kubernetes

In this article, we'll walk through deploying a PostgreSQL database in a Kubernetes (k8s) environment and simulating various database operations using Python. We will set up a PostgreSQL instance along with ten pods that will perform updates, inserts, deletes, and selects on the database. This setup is perfect for testing scalability and performance.

Step 1: Create a Kubernetes Namespace

First, we need to create a dedicated namespace for our PostgreSQL deployment. This helps in organizing resources and managing them more efficiently.

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

Run the following command to apply the namespace configuration:

kubectl apply -f namespace.yaml

Step 2: Deploy PostgreSQL

Next, we will create a PostgreSQL deployment and a service to expose it. Here’s how to do that:

# 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 --- apiVersion: v1 kind: Service metadata: name: postgres namespace: postgres-sim spec: selector: app: postgres ports: - protocol: TCP port: 5432 targetPort: 5432

Deploy PostgreSQL by executing:

kubectl apply -f postgres-deployment.yaml

Step 3: Prepare the Simulation Container

Now, let’s create a Docker container that will simulate the database operations. First, we'll set up a Dockerfile:

# Dockerfile FROM python:3.9-slim WORKDIR /app COPY simulate.py . RUN pip install psycopg2-binary CMD ["python", "simulate.py"]

Step 4: Create the Simulation Script

Now we will write the simulate.py script that performs the database operations.

# simulate.py import psycopg2 import random import time # Connect to the database conn = psycopg2.connect( dbname="mydb", user="user", password="password", host="postgres", port="5432" ) cur = conn.cursor() operations = ["insert", "update", "delete", "select"] for _ in range(1000000): # Simulate 1 million operations 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()

Build and Push the Docker Image

Build the Docker image:

docker build -t my-python-simulation .

Tag and push the image to your Docker repository:

docker tag my-python-simulation <your-docker-username>/my-python-simulation docker push <your-docker-username>/my-python-simulation

Step 5: Deploy the Simulation Pods

Now we will create a deployment for the simulation pods:

# simulation-deployment.yaml apiVersion: apps/v1 kind: Deployment metadata: name: db-simulation namespace: postgres-sim spec: replicas: 10 selector: matchLabels: app: db-simulation template: metadata: labels: app: db-simulation spec: containers: - name: simulation image: <your-docker-username>/my-python-simulation

Apply the deployment:

kubectl apply -f simulation-deployment.yaml

Step 6: Set Up the Database Table

To store our data, we need to create a table in PostgreSQL. You can access the PostgreSQL pod and run the necessary SQL commands:

kubectl exec -it <postgres-pod-name> -n postgres-sim -- psql -U user -d mydb CREATE TABLE test_table ( id SERIAL PRIMARY KEY, data INTEGER NOT NULL );

Step 7: Monitor the Pods

You can check the status of your pods to ensure everything is running smoothly:

kubectl get pods -n postgres-sim

Conclusion

This setup not only helps in testing database performance but also provides insights into how your application handles concurrent database operations.

Feel free to explore further by adjusting the number of operations, modifying the simulation logic, or integrating more complex data handling scenarios!