DEV Community

Cover image for Deploying PostgreSQL in a Local Kubernetes Cluster
Otomato profile image Roman Belshevitz
Roman Belshevitz for Otomato

Posted on • Edited on

Deploying PostgreSQL in a Local Kubernetes Cluster

#devops #kubernetes #postgres #k3s

In this article, the author will not explain why all these manipulations are needed, or discuss the advantages and disadvantages of this solution. He hopes readers are smart enough. 😎

Think of this article as a continuation of the persistent volumes story and as a guide for deploying a database in a local Kubernetes dev cluster.

0. Introduction

Three main stages can be distinguished:

  • Creation of PersistentVolume (PV) and PersistentVolumeClaim (PVC) - what a surpise, we need a stateful app.
  • Setting the Helm chart of the target application.
  • Checking that everything is working.

Before starting work, we need to minimally configure the Kubernetes cluster. Here are the small requirements:

  • The actual version of Kubernetes (v1.22+).
  • One master node and one worker node.
  • The configured Ingress-controller.
  • helm is installed on the dev's machine.

As you may know already, the author prefers to use k3s/k3d as his local Kubernetes development stage.

1. Storage preparation

When dealing with k3s, you have to enable so called local-path provisoner first.

Install the provisioner: 



$ kubectl apply -f https://raw.githubusercontent.com/rancher/local-path-provisioner/master/deploy/local-path-storage.yaml
Enter fullscreen mode Exit fullscreen mode

Set the local-path provisioner as default: 



$ kubectl patch storageclass local-path -p '{"metadata": {"annotations":{"storageclass.kubernetes.io/is-default-class":"true"}}}'
Enter fullscreen mode Exit fullscreen mode

Check the fact provisioner is running:



$ kubectl --namespace kube-system get pod 
NAME                                      READY   STATUS      RESTARTS        AGE
...
local-path-provisioner-84bb864455-tmpht   1/1     Running     ...
Enter fullscreen mode Exit fullscreen mode

From this moment we can create a PersistentVolume and PersistentVolumeClaim resources for our dedicated storage space. To do this, paste the following manifest into the pv.yaml file:



apiVersion: v1
kind: PersistentVolume
metadata:
  name: pv-for-pg
  labels:
    type: local
spec:
  capacity:
    storage: 4Gi
  volumeMode: Filesystem
  accessModes:
  - ReadWriteOnce
  persistentVolumeReclaimPolicy: Retain
  storageClassName: "local-path"
  hostPath:
    path: /opt/local-path-provisioner
  nodeAffinity:
    required:
      nodeSelectorTerms:
      - matchExpressions:
        - key: kubernetes.io/hostname
          operator: In
          values:
          - k3d-my-cluster-agent-0

---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: pg-pvc
spec:
  storageClassName: "local-path"
  accessModes:
  - ReadWriteOnce
  resources:
    requests:
      storage: 4Gi
Enter fullscreen mode Exit fullscreen mode

In matchExpressions field we specify the name of the node on which the disk will be mounted (should be our worker node k3d-my-cluster-agent-0 here).

For convenience, we will mount the disk immediately on the master node, although this can be done on any of those available in the list. K3s will mount volume on the /opt/local-path-provisioner directory on a host node's file system.



$ docker exec -it k3d-my-cluster-agent-0 sh
/ # ls
bin  dev  etc  k3d  lib  opt  proc  run  sbin  sys  tmp  usr  var
/ # cd opt
/opt # ls
local-path-provisioner
/opt # cd local-path-provisioner/
/opt/local-path-provisioner # ls
data
/opt/local-path-provisioner # cd data
/opt/local-path-provisioner/data # ls
PG_VERSION  global    pg_dynshmem    pg_logical    pg_notify    pg_serial     pg_stat      pg_subtrans  pg_twophase  pg_xact           postmaster.opts
base        pg_commit_ts  pg_ident.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    pg_wal   postgresql.auto.conf  postmaster.pid
/opt/local-path-provisioner/data #
Enter fullscreen mode Exit fullscreen mode

See the result:



$ kubectl apply -f pvc.yaml 
persistentvolume/pv-for-pg created
persistentvolumeclaim/pg-pvc created
Enter fullscreen mode Exit fullscreen mode

Good. Let's check promoted PersistentVolumeClaim's state:



$ kubectl get pvc
NAME     STATUS   VOLUME      CAPACITY   ACCESS MODES   STORAGECLASS    AGE
pg-pvc   Bound    pv-for-pg   4Gi        RWO            local-path     1m43s
Enter fullscreen mode Exit fullscreen mode

As we may see, the PersistentVolumeClaim resource is bound.

2. Installing PostgreSQL

Now is the time to deploy Postgres on a cluster. Just at this stage, we already need helm. We pull the Bitnami repository to ourselves (they are kind and great, they have a lot of useful ready-made charts there in their ⭐5k+ repository): 



$ helm repo add bitnami https://charts.bitnami.com/bitnami
"bitnami" has been added to your repositories
Enter fullscreen mode Exit fullscreen mode

Proceed with helm install:



$ helm install dev-pg bitnami/postgresql --set primary.persistence.existingClaim=pg-pvc,auth.postgresPassword=pgpass,volumePermissions.enabled=true
NAME: dev-pg
LAST DEPLOYED: Tue May  3 19:25:34 2022
NAMESPACE: default
STATUS: deployed
REVISION: 1
Enter fullscreen mode Exit fullscreen mode

⚠️ Due to an on-going issue regarding kubernetes permissions and the use of containerSecurityContext.runAsUser, you must enable volumePermissions to ensure everything works as expected.

🥁 Drums are ticking! From now the Postgres Pod will capable to write data to the /opt/local-path-provisioner directory.

Let's look at the state of the Pod and StatefulSet:



$ kubectl get pod,statefulset
NAME                                                    READY   STATUS    RESTARTS        AGE
pod/dev-pg-postgresql-0                                 1/1     Running   0               26m

NAME                                 READY   AGE
statefulset.apps/dev-pg-postgresql   1/1     1h
Enter fullscreen mode Exit fullscreen mode

Check the version:



$ kubectl logs --tail 20  dev-pg-postgresql-0 | grep starting
...[1] LOG:  starting PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
Enter fullscreen mode Exit fullscreen mode

Great job.

3. Communicating with the database

The database has been successfully deployed, now let's try to connect to it: create a user, a table and configure access credentials.

There are two major ways to communicate with DB:

  • Port forwarding
  • Involving Pod with client tool onboard

Port forwarding

We'll need the psql utility (note, in some cases PgSQL PPA repo should be added, the author uses Ubuntu Focal):



$ sudo apt install postgresql-client-14
Enter fullscreen mode Exit fullscreen mode

Let's export the password from the admin user to an environment variable:



$ export POSTGRES_PASSWORD=$(kubectl get secret --namespace default dev-pg-postgresql -o jsonpath="{.data.postgres-password}" | base64 --decode)
Enter fullscreen mode Exit fullscreen mode

Perform port forwarding. Keep in mind: the current console will be locked after executing the command:



$ kubectl port-forward --namespace default svc/dev-pg-postgresql 5432:5432
Enter fullscreen mode Exit fullscreen mode

Or you may open a separate console:



$ export KUBECONFIG=$(k3d kubeconfig write my-cluster)
kubectl port-forward --namespace default svc/dev-pg-postgresql 5432:5432
Enter fullscreen mode Exit fullscreen mode

Now connect to database:



$ PGPASSWORD="$POSTGRES_PASSWORD" psql --host 127.0.0.1 -U postgres -d postgres -p 5432psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1+b1))
Type "help" for help.

postgres=#
Enter fullscreen mode Exit fullscreen mode

Involving Pod with client tool onboard

Export the password from the admin user to an environment variable:



$ export POSTGRES_PASSWORD=$(kubectl get secret --namespace default dev-pg-postgresql -o jsonpath="{.data.postgres-password}" | base64 --decode)
Enter fullscreen mode Exit fullscreen mode

Then let's create a Pod with the psql utility onboard and execute the command to connect to the database in it:



$ kubectl run dev-pg-postgresql-client --rm --tty -i --restart='Never' --namespace default --image docker.io/bitnami/postgresql:14.2.0-debian-10-r22 --env="PGPASSWORD=$POSTGRES_PASSWORD" \
>  --command -- psql --host dev-pg-postgresql -U postgres -d postgres -p 5432

If you don't see a command prompt, try pressing enter.

postgres=#
Enter fullscreen mode Exit fullscreen mode

Let's list roles to ensure we're in touch:



postgres=# \du;
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=#
Enter fullscreen mode Exit fullscreen mode

🎯 That's it, guys! We have deployed a working database in a cluster, it also saves its state, it is stateful database here.

I recommend all my readers to surf devs' blogs on the Net regularly. It's worth it. Thanks Roger Lipscombe 🇬🇧 and Fadhil Yaacob 🇲🇾!

In the next part, the author will consider an amazing tool that allows you to observe the functioning of a containerized database in almost real time.

Image description

Top comments (0)

Read next

hannliao profile image

How to deploy PostgreSQL database to Railway

Hannah Liao -

sandr0p profile image

Code Reviews: Easing the pain

sandr0-p -

omkara18 profile image

Terraform Drift: Deep Dive

omkar shelke -

cicubeteam profile image

Kubernetes Horizontal Pod Autoscaler

CiCube -