Deploying PostgreSQL in a Local Kubernetes Cluster
Tags: devops, kubernetes, postgres, k3s
Published: 2022-05-03 22:29:16

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](https://dev.to/otomato_io/how-to-use-pre-existing-disk-as-a-k8s-persistent-volume-for-a-gke-cluster-3phd) 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](https://www.sokube.ch/post/k3s-k3d-k8s-a-new-perfect-match-for-dev-and-test) 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
```
Set the `local-path` provisioner as default: 
```
$ kubectl patch storageclass local-path -p '{"metadata": {"annotations":{"storageclass.kubernetes.io/is-default-class":"true"}}}'
```
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     ...
```
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:
```yaml
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
```
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 #
```
See the result:
```
$ kubectl apply -f pvc.yaml 
persistentvolume/pv-for-pg created
persistentvolumeclaim/pg-pvc created
```
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
```
As we may see, the `PersistentVolumeClaim` resource is  [bound](https://kubernetes.io/docs/tasks/configure-pod-container/configure-persistent-volume-storage/). 

##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](https://github.com/bitnami/charts)): 
```
$ helm repo add bitnami https://charts.bitnami.com/bitnami
"bitnami" has been added to your repositories
```
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
```

⚠️ Due to an on-going [issue](https://github.com/kubernetes/kubernetes/issues/57923) 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
```

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
```
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](https://www.ubuntuupdates.org/package/postgresql/focal-pgdg/main/base/postgresql-client-14) should be added, the author uses Ubuntu Focal):
```
$ sudo apt install postgresql-client-14
```
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)
```
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
```
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
```
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=# 
```
###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)
```
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=# 
```
Let's list roles to ensure we're in touch:
```sql
postgres=# \du;
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# 
```

🎯 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 🇬🇧](https://blog.differentpla.net/blog/2021/12/17/k3s-static-pv/) and [Fadhil Yaacob 🇲🇾](https://www.fadhil-blog.dev/blog/rancher-local-path-provisioner/)!

In the next part, the author will consider an amazing tool that allows you [to observe the functioning of a containerized database](https://dev.to/otomato_io/pixie-an-x-ray-machine-for-kubernetes-traffic-23pd) in almost real time.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qhsieoit9jqvxp451y6b.jpg)