Running pgAdmin to Manage a PostgreSQL Cluster on Kubernetes

Let’s say you need to do something in PostgreSQL in Kubernetes, and it is inconvenient to work with the database in the terminal, or you need to become more familiar with PostgreSQL or SQL commands. In that case, this article comes in handy.

I explain how to run pgAdmin in a Kubernetes cluster to manage PostgreSQL databases deployed in that cluster. This is useful if your PostgreSQL cluster does not have external access and is only available inside a Kubernetes cluster.

pgAdmin is a popular open source tool for managing Postgres databases. It is convenient for creating databases, schemas, tables, viewing data, executing SQL queries, and much more in a user-friendly web interface.

Running pgAdmin

We need one file that contains deployment and service resources to run pgAdmin in k8s. Let's call it pgadmin.yaml.

pgadmin.yaml

apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgadmin-deployment
spec:
  replicas: 1
  selector:
    matchLabels:
      app: pgadmin
  template:
    metadata:
      labels:
        app: pgadmin
    spec:
      containers:
        - name: pgadmin
          image: dpage/pgadmin4
          ports:
            - containerPort: 80
          env:
            - name: PGADMIN_DEFAULT_EMAIL
              value: admin@example.com
            - name: PGADMIN_DEFAULT_PASSWORD
              value: admin
---
apiVersion: v1
kind: Service
metadata:
  name: pgadmin-service
spec:
  selector:
    app: pgadmin
  ports:
    - protocol: TCP
      port: 80
      targetPort: 80

Note that the middle of the file contains the login and password for logging into the pgAdmin panel.

          env:
            - name: PGADMIN_DEFAULT_EMAIL
              value: admin@example.com
            - name: PGADMIN_DEFAULT_PASSWORD
              value: admin

We need to deploy pgAdmin using this file by running the command.

kubectl apply -f pgadmin.yaml -n <namespace>

Let's print out the list of pods in our cluster to get the name pgadmin pod.

kubectl get pods -n <namespace>

Now, we just need to run port forwarding for the pgAdmin pod on a free port that will open in the browser. I use port 5050.

kubectl port-forward pgadmin-deployment-***-*** 5050:80 -n <namespace>

I can open localhost:5050 in a browser now.

After that, you only need to add a connection to Postgres by clicking Add New Server.

I used the access data that Percona Everest provided me for the Postgres cluster created using it.

Congratulations! We can manage Postgres using the pgAdmin interface or SQL queries. Viewing databases, schemas, and tables and writing SQL queries has become very convenient.

Remove pgAdmin

Once you are done with pgAdmin and PostgreSQL, all you need to do is to

1. Stop port-forwarding by simply pressing CTRL + C in the terminal.

2. Remove pgAdmin Deployment and Service by running the command.

About the PostgreSQL cluster on Kubernetes used in the article

In this post, I used the Postgres cluster with three nodes created on Google Kubernetes Engine (GKE) using Percona Everest.

Percona Everest is an open source platform that allows you to provision and manage database clusters. I recently wrote about Percona Everest in A New Way to Provision Databases on Kubernetes. You do not need to worry about PostgreSQL cluster installation and configuration, backups, recovery, or scaling; you can do it in the interface. It enables multi-database and multi-cluster configurations and can be deployed on any Kubernetes infrastructure in the cloud or on-premises.

Documentation: Create Kubernetes cluster on Google Kubernetes Engine (GKE)

You can use any other cloud or create a local Kubernetes cluster using minikube, k3d or kind.