How to deploy PostgreSQL DB Server and PGadmin in Kubernetes: A How-To guide

In this blog I will demonstrate how to deploy PostgreSQL DB server and pgadmin in Kubernetes.

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley.

PGAdmin is a web-based GUI tool used to interact with the Postgres database sessions, both locally and remote servers as well. You can use PGAdmin to perform any sort of database administration required for a Postgres database.

Kubernetes is an open-source container orchestration system for automating software deployment, scaling, and management. Google originally designed Kubernetes, but the Cloud Native Computing Foundation now maintains the project.

A Kubernetes cluster can be deployed on either physical or virtual machines. I will use Minikube for Kubernetes development.

Minikube is a lightweight Kubernetes implementation that creates a VM on your local machine and deploys a simple cluster containing only one node. Minikube is available for Linux, macOS, and Windows systems.

Step #1

Install minikube, make sure it’s started



minikube start
* minikube v1.28.0 on Ubuntu 20.04 (vbox/amd64)
* Automatically selected the docker driver
* Using Docker driver with root privileges
* Starting control plane node minikube in cluster minikube
* Pulling base image ...
* Creating docker container (CPUs=2, Memory=2200MB) ...
* Preparing Kubernetes v1.25.3 on Docker 20.10.20 ...
  - Generating certificates and keys ...
  - Booting up control plane ...
  - Configuring RBAC rules ...
* Verifying Kubernetes components...
  - Using image gcr.io/k8s-minikube/storage-provisioner:v5
* Enabled addons: storage-provisioner, default-storageclass
* Done! kubectl is now configured to use "minikube" cluster and "default" namespace by default
dmi@dmi-VirtualBox:~/my-postgres-and-pgadmin-deployment$

Tip. If you want to reset and restart your entire Kubernetes cluster in Minikube run the following command:



 minikube delete
* Deleting "minikube" in docker ...
* Deleting container "minikube" ...
* Removing /home/dmi/.minikube/machines/minikube ...
* Removed all traces of the "minikube" cluster.

Step #2

Encode PostgreSQL admin username and password using base64:



echo -n 'root' | base64
cm9vdA==



echo -n 'mypassword' | base64
bXlwYXNzd29yZA==

Step #3

Create and apply postgres-secret.yaml file:



apiVersion: v1
kind: Secret
metadata:
  name: postgres-secret
  labels:
    app: postgres
type: Opaque
data:
    postgres-root-username: cm9vdA==
    postgres-root-password: bXlwYXNzd29yZA==



kubectl apply -f postgres-secret.yaml
secret/postgres-secret created

Step #4

Create and apply postgres-configmap.yaml file:



apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-configmap
  labels:
    app: postgres
data:
  postgres-dbname: mydb



postgres-configmap.yaml
configmap/postgres-configmap created

Step #5

Create and apply postgres-deploy.yaml file:



apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres
spec:
  selector:
   matchLabels:
    app: postgres
  replicas: 1
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres
          image: postgres:latest
          imagePullPolicy: "IfNotPresent"
          env:
           - name: POSTGRES_USER
             valueFrom:
               secretKeyRef:
                 name: postgres-secret
                 key: postgres-root-username
           - name: POSTGRES_PASSWORD
             valueFrom:
               secretKeyRef:
                 name: postgres-secret
                 key: postgres-root-password
           - name: POSTGRES_DB
             valueFrom:
               configMapKeyRef:
                 name: postgres-configmap
                 key: postgres-dbname

          volumeMounts:
            - mountPath: /var/lib/postgresql/data
              name: postgredb
      volumes:
        - name: postgredb
          persistentVolumeClaim:
            claimName: postgres-pv-claim
---
    kind: PersistentVolume
    apiVersion: v1
    metadata:
      name: postgres-pv-volume
      labels:
        type: local
        app: postgres
    spec:
      storageClassName: manual
      capacity:
        storage: 5Gi
      accessModes:
        - ReadWriteMany
      hostPath:
        path: "/mnt/data"
---
    kind: PersistentVolumeClaim
    apiVersion: v1
    metadata:
      name: postgres-pv-claim
      labels:
        app: postgres
    spec:
      storageClassName: manual
      accessModes:
        - ReadWriteMany
      resources:
        requests:
          storage: 5Gi
---
    apiVersion: v1
    kind: Service
    metadata:
      name: postgres
      labels:
        app: postgres
    spec:
       ports:
        - name: postgres
          port: 5432
          nodePort: 30432
       type: NodePort
       selector:
        app: postgres



kubectl apply -f postgres-deploy.yaml
deployment.apps/postgres created
persistentvolume/postgres-pv-volume created
persistentvolumeclaim/postgres-pv-claim created
service/postgres created

Step #6

Ensure PostgreSQL pod is up, and its related service is up.



 kubectl get all
NAME                            READY   STATUS    RESTARTS   AGE
pod/postgres-5c7d8b656d-xj5b2   1/1     Running   0          61s

NAME                 TYPE        CLUSTER-IP       EXTERNAL-IP   PORT(S)          AGE
service/kubernetes   ClusterIP   10.96.0.1        <none>        443/TCP          11m
service/postgres     NodePort    10.104.238.217   <none>        5432:30432/TCP   61s

NAME                       READY   UP-TO-DATE   AVAILABLE   AGE
deployment.apps/postgres   1/1     1            1           62s

NAME                                  DESIRED   CURRENT   READY   AGE
replicaset.apps/postgres-5c7d8b656d   1         1         1       62s
dmi@dmi-VirtualBox:~/my-postgres-and-pgadmin-deployment$

Step #7

Connect to the PostgreSQL to ensure it's up and running.



minikube service postgres
|-----------|----------|---------------|---------------------------|
| NAMESPACE |   NAME   |  TARGET PORT  |            URL            |
|-----------|----------|---------------|---------------------------|
| default   | postgres | postgres/5432 | http://192.168.49.2:30432 |
|-----------|----------|---------------|---------------------------|



kubectl get pods
NAME                        READY   STATUS    RESTARTS   AGE
postgres-5c7d8b656d-xj5b2   1/1     Running   0          4m5s



kubectl exec -it postgres-5c7d8b656d-xj5b2 -- /bin/bash
root@postgres-5c7d8b656d-xj5b2:/# psql -U root -d mydb -W
Password:
psql (15.1 (Debian 15.1-1.pgdg110+1))
Type "help" for help.

mydb=# select version();
                                                           version
-----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.1 (Debian 15.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

mydb=# create table my_table(a varchar(100));
CREATE TABLE
mydb=# insert into my_table values('1234567890');
INSERT 0 1
mydb=# select * from my_table;
     a
------------
 1234567890
(1 row)

mydb=#

Step #8

To access PostgreSQL logs placed in the pod use the following approach:



 kubectl get pod
NAME                        READY   STATUS    RESTARTS   AGE
postgres-5c7d8b656d-xj5b2   1/1     Running   0          7m28s
dmi@dmi-VirtualBox:~/my-postgres-and-pgadmin-deployment$ kubectl logs postgres-5c7d8b656d-xj5b2

...

PostgreSQL init process complete; ready for start up.

2022-12-10 20:10:26.914 UTC [1] LOG:  starting PostgreSQL 15.1 (Debian 15.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2022-12-10 20:10:26.935 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-12-10 20:10:26.935 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2022-12-10 20:10:26.943 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-12-10 20:10:26.956 UTC [64] LOG:  database system was shut down at 2022-12-10 20:10:26 UTC
2022-12-10 20:10:26.965 UTC [1] LOG:  database system is ready to accept connections
...
2022-12-10 20:15:27.055 UTC [62] LOG:  checkpoint starting: time
2022-12-10 20:15:31.232 UTC [62] LOG:  checkpoint complete: wrote 44 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.148 s, sync=0.017 s, total=4.177 s; sync files=12, longest=0.004 s, average=0.002 s; distance=252 kB, estimate=252 kB
dmi@dmi-VirtualBox:~/my-postgres-and-pgadmin-deployment$

Step #9

Generate password for pgadmin login:



echo -n 'mypwd' | base64
bXlwd2Q=

Step #10

Create yaml file pgadmin-secret.yaml and apply it:



apiVersion: v1
kind: Secret
metadata:
  name: pgadmin-secret
type: Opaque
data:
  pgadmin-default-password: bXlwd2Q=



kubectl apply -f pgadmin-secret.yaml
secret/pgadmin-secret created

Step #11

Create and apply pgadmin-deploy.yaml file:



apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgadmin
spec:
  selector:
   matchLabels:
    app: pgadmin
  replicas: 1
  template:
    metadata:
      labels:
        app: pgadmin
    spec:
      containers:

        - name: pgadmin4
          image: dpage/pgadmin4
          env:
           - name: PGADMIN_DEFAULT_EMAIL
             value: "admin@admin.com"
           - name: PGADMIN_DEFAULT_PASSWORD
             valueFrom:
               secretKeyRef:
                 name: pgadmin-secret
                 key: pgadmin-default-password
           - name: PGADMIN_PORT
             value: "80"
          ports:
            - containerPort: 80
              name: pgadminport
---
apiVersion: v1
kind: Service
metadata:
  name: pgadmin
  labels:
    app: pgadmin
spec:
  selector:
   app: pgadmin
  type: NodePort
  ports:
   - port: 80
     nodePort: 30200

Step #12



kubectl apply -f pgadmin-deploy.yaml
deployment.apps/pgadmin created
service/pgadmin created

Step #13

Ensure the relevant pods and services are up and running:



kubectl get all
NAME                            READY   STATUS    RESTARTS   AGE
pod/pgadmin-b8fff9cb9-l4xkq     1/1     Running   0          51s
pod/postgres-5c7d8b656d-xj5b2   1/1     Running   0          14m

NAME                 TYPE        CLUSTER-IP       EXTERNAL-IP   PORT(S)          AGE
service/kubernetes   ClusterIP   10.96.0.1        <none>        443/TCP          24m
service/pgadmin      NodePort    10.101.165.6     <none>        80:30200/TCP     51s
service/postgres     NodePort    10.104.238.217   <none>        5432:30432/TCP   14m

NAME                       READY   UP-TO-DATE   AVAILABLE   AGE
deployment.apps/pgadmin    1/1     1            1           51s
deployment.apps/postgres   1/1     1            1           14m

NAME                                  DESIRED   CURRENT   READY   AGE
replicaset.apps/pgadmin-b8fff9cb9     1         1         1       51s
replicaset.apps/postgres-5c7d8b656d   1         1         1       14m
dmi@dmi-VirtualBox:~/my-postgres-and-pgadmin-deployment$

Step #14

Access the newly created PostgreSQL DB Server using pgadmin.



 minikube service pgadmin
|-----------|---------|-------------|---------------------------|
| NAMESPACE |  NAME   | TARGET PORT |            URL            |
|-----------|---------|-------------|---------------------------|
| default   | pgadmin |          80 | http://192.168.49.2:30200 |
|-----------|---------|-------------|---------------------------|
* Opening service default/pgadmin in default browser...

Conclusion

In this blog I've demonstrated how to deploy PostgreSQL DB Server and PGadmin in Kubernetes cluster. I believe this information will be helpful and valuable.

Blog