Basic Postgres database in Kubernetes

Sandeep Baldawa
ITNEXT
Published in
5 min readOct 24, 2020

--

In this post, we will bring up a Postgres database instance in Kubernetes and then connect to this instance using a cronjob. The goal is to learn the basics of Kubernetes using this exercise. For learning K8s basics, I highly recommend this course for beginners.

Setup

  • To get started, first setup a minikube Kubernetes cluster using instructions from here (we could use anything else like GKE, EKS, K3, etc.). minikube lets you run Kubernetes locally. minikube runs a single-node Kubernetes cluster on your personal computer.
  • Install helm to manage Kubernetes applications. Managing multiple Kubernetes YAML files gets complicated, this is where helm helps ease things.

Validate setup

  1. Use, minikube status command to check if minikube is up and running
Let's try K8 : minikube status
minikube
type: Control Plane
host: Running
kubelet: Running
apiserver: Running
kubeconfig: Configured

2. Validate if helm is installed correctly

Let's try K8 : helm version
version.BuildInfo{Version:"v3.3.4", GitCommit:"a61ce5633af99708171414353ed49547cf05013d", GitTreeState:"dirty", GoVersion:"go1.15.2"}

Install Postgres

  1. Add the repo(s) for helm to use. I will be using bitnami repo here. You can find their documentation here. If we search the bitnami repo we see they provide two versions of PostgreSQL, let’s use the first one
Let's try K8 : helm search repo postgres
NAME CHART VERSION APP VERSION DESCRIPTION
bitnami/postgresql 9.8.3 11.9.0 Chart for PostgreSQL, an object-relational data...
bitnami/postgresql-ha 4.0.1 11.9.1 Chart for PostgreSQL with HA architecture (usin...

2. Let’s install the chart

helm install postgres bitnami/postgresql

Let’s see if the chart now shows up

Let's try K8 : helm list
NAME NAMESPACE REVISION UPDATED STATUS CHART APP VERSION
postgres default 1 2020-10-24 10:57:00.678762 -0700 PDT deployed postgresql-9.8.3 11.9.0

3. Validate if PostgreSQL is running as expected

helm get notes postgres

Validate if all services and pods are up and running. It’s interesting to see we have a Persistent Volume, Persistent Volume Claim, and a StatefulSet installed. That's the beauty of helm, which simplifies installing multiple Kubernetes objects at the same time, if we did not use helm we would have to create multiple Kubernetes YAML files, which can be a nightmare to manage.

Every 2.0s: kubectl get all -o wide                                                                           NAME                        READY   STATUS    RESTARTS   AGE     IP           NODE       NOMINATED NODE   READINESS GATES
pod/postgres-postgresql-0 1/1 Running 0 3m32s 172.17.0.3 minikube <none> <none>
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE SELECTOR
service/kubernetes ClusterIP 10.x.x.x <none> 443/TCP 43h <none>
service/postgres-postgresql ClusterIP 10.x.x.x
<none> 5432/TCP 5m5s app.kubernetes.io/instance=postgres,app.kubernetes.io/name=pos
tgresql,role=master
service/postgres-postgresql-headless ClusterIP None <none> 5432/TCP 5m5s app.kubernetes.io/instance=postgres,app.kubernetes.io/name=pos
tgresql
NAME READY AGE CONTAINERS IMAGES
statefulset.apps/postgres-postgresql 1/1 5m5s postgres-postgresql docker.io/bitnami/postgresql:11.9.0-debian-10-r48

Let’s connect to the PostgreSQL and validate we can create a table and insert values

Let's try K8 : export POSTGRES_PASSWORD=$(kubectl get secret --namespace default postgres-postgresql -o jsonpath="{.data.postgresql-password}" | base64 --decode)Let's try K8 : kubectl run postgres-postgresql-client --rm --tty -i --restart='Never' --namespace default --image docker.io/bitnami/postgresql:11.9.0-debian-10-r48 --env="PGPASSWORD=$POSTGRES_PASSWORD" --command -- psql --host postgres-postgresql -U postgres -d postgres -p 5432

Looks like we can insert and query from the DB

postgres=# CREATE TABLE phonebook(phone VARCHAR(32), firstname VARCHAR(32), lastname VARCHAR(32), address VARCHAR(64));
CREATE TABLE
postgres=# INSERT INTO phonebook(phone, firstname, lastname, address) VALUES('+1 123 456 7890', 'John', 'Doe', 'North America');
INSERT 0 1
postgres=# SELECT * FROM phonebook ORDER BY lastname;
phone | firstname | lastname | address
-----------------+-----------+----------+---------------
+1 123 456 7890 | John | Doe | North America
(1 row)

Add a cronjob which can connect to the DB every minute

  1. Write a script to connect to the DB

Let’s use a simple python script connect_db.pyto connect to the DB. We use the below parameters.

Username => postgres
Password => $POSTGRES_PASSWORD variable as found previosuly
DB name => postgres

Python script to connect to the DB

import sqlalchemy as dbengine = db.create_engine('postgresql://postgres:mq4Jq3C8zK@postgres-postgresql:5432/postgres')connection = engine.connect()metadata = db.MetaData(bind=connection, reflect=True)print(metadata)

2. How do we run this script as part of the Kubernetes cluster?

A. We can add this as part of a Docker image and a container running in a POD. Let’s start by creating an image to run this script.

requirements.txt

sqlalchemy==1.3.16
psycopg2-binary

Dockerfile

FROM python:3.8-slimWORKDIR /appCOPY requirements.txt /appCOPY connect_db.py /appRUN pip install -r requirements.txtCMD python connect_db.py

connect_db.py

import sqlalchemy as dbengine = db.create_engine('postgresql://postgres:mq4Jq3C8zK@postgres-postgresql:5432/postgres')connection = engine.connect()metadata = db.MetaData(bind=connection, reflect=True)print(metadata)

B. Let’s now build the image.

docker build -t postgres_image .
Sending build context to Docker daemon 6.144kB
Step 1/6 : FROM python:3.8-slim
---> 41dcfe21e8fd
Step 2/6 : WORKDIR /app
---> Using cache
---> 00a8320e3c52
Step 3/6 : COPY requirements.txt /app
---> Using cache
---> e67a21cc9bb8
Step 4/6 : COPY connect_db.py /app
---> Using cache
---> 38a6058e0000
Step 5/6 : RUN pip install -r requirements.txt
---> Using cache
---> 07495bf00820
Step 6/6 : CMD python connect_db.py
---> Using cache
---> 97b09c86daa9
Successfully built 97b09c86daa9
Successfully tagged postgres_image:latest

C. Let’s create a cronjob that tries to run the script we just wrote every minute (as per our requirements) in the Kubernetes cluster using a YAML file. Kubernetes documentation has details on the same.

apiVersion: batch/v1beta1
kind: CronJob
metadata:
name: connect
spec:
schedule: "*/1 * * * *"
jobTemplate:
spec:
template:
spec:
containers:
- name: connect
image: postgres_image
restartPolicy: OnFailure

Let’s apply the same using kubectl

kubectl apply -f c.yml
cronjob.batch/connect created

Let’s validate the cronjob

kubectl get cronjobs
NAME SCHEDULE SUSPEND ACTIVE LAST SCHEDULE AGE
connect */1 * * * * False 1 13s 72s

Let’s validate the pods, looks like it is being run

kubectl get po
NAME READY STATUS RESTARTS AGE
connect-1603564500-6dk2m 0/1 ImagePullBackOff 0 2m18s
connect-1603564560-q6d4t 0/1 ImagePullBackOff 0 78s
connect-1603564620-dtrgf 0/1 ImagePullBackOff 0 18s
postgres-postgresql-0 1/1 Running 0 29m

However, if we see the POD logs we see that the image cannot be found

kubectl logs connect-1603564560-q6d4t
Error from server (BadRequest): container "connect" in pod "connect-1603564560-q6d4t" is waiting to start: image can't be pulled

This is because we need to host the image in a local or public registry, else the cronJob cannot find the image. For simplicity, purposes will host it in a public registry(you can also host a local registry)

Let’s tag our image

docker tag postgres_image:latest XXXX/postgres_images:latest

Let’s push the image to the public registry

docker push XXXX/postgres_images:latest
The push refers to repository [docker.io/XXXX/postgres_images]
1ec918490d02: Pushed
c9daa0dc3fcb: Pushed
6a53aff9114d: Pushed
8be2969ec15c: Pushed
4375d6f45f83: Pushed
06b60c6e6ffd: Pushed
322c3996a80b: Pushed
225ef82ca30a: Pushed
d0fe97fa8b8c: Pushed
latest: digest: sha256:c01442f3efe0e31de148926ad6d6e619be998d6e15d20512ac9c7368ef26ac46 size: 2203

Now the cronjob can run the python script every minute to connect to the PostgreSQL DB we just installed.

Summary

We just installed a PostgreSQL DB and added a cronjob that ran every minute to connect to the DB, we added the same in a Kubernetes cluster. This is a simple example to understand a few basics. Running a PostgreSQL DB in production is a different ball game, I will share my perspective on this topic in one of the upcoming posts.

--

--

whoami >> Slack, Prev — Springpath (Acquired by Cisco), VMware, Backend Engineer, Build & Release, Infra, Devops & Cybersecurity Enthusiast