How to Deploy pgAdmin in Kubernetes

Dave Page Jun 28, 2021

pgAdmin has long had a container distribution; however the development team rarely used it, except when testing releases. So virtually all of our experience has been using Docker. Recently, a user ran into an issue when running under Kubernetes that I was unable to reproduce in Docker, so I spent some time learning how a pgAdmin deployment would work in that environment—and ironically it worked just fine; I couldn't reproduce the bug!

Regardless, I gained an understanding of how to deploy pgAdmin in Kubernetes, so here's how it works.

Note that all the YAML below could be in a single file, however I've split it up into multiple files for convenience.

 

Secret

First we'll create a secret. This is a way of storing sensitive information in Kubernetes for use as part of whatever is being deployed. In the case of pgAdmin, we'll use it to store the initial password that will be set for the administrator. The YAML to create the secret looks like this:

apiVersion: v1
kind: Secret
type: Opaque
metadata:
 name: pgadmin
data:
 pgadmin-password: U3VwZXJTZWNyZXQ=

This will create a secret with the name pgadmin. The password is simply base64 encoded (in this case, it's SuperSecret). Assuming the YAML is saved to pgadmin-secret.yaml, we can create the secret using kubectl's apply option:

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

 

ConfigMap

Next we'll create a configuration file for pgAdmin. This could include a config_local.py file or similar but there are other ways to handle the pgAdmin configuration options that we'll use later. In this example we'll use the ConfigMap to inject a JSON file that contains a list of servers to register for use. This will later be mounted as a file by the container when launched:

apiVersion: v1
kind: ConfigMap
metadata:
 name: pgadmin-config
data:
 servers.json: |
   {
       "Servers": {
         "1": {
           "Name": "PostgreSQL DB",
           "Group": "Servers",
           "Port": 5432,
           "Username": "postgres",
           "Host": "postgres.domain.com",
           "SSLMode": "prefer",
           "MaintenanceDB": "postgres"
         }
       }
   }

This defines a ConfigMap called pgadmin-config, containing a piece of data (actually the JSON that will be read by pgAdmin) called servers.json.

The ConfigMap is created in Kubernetes much as in the previous example:

$ kubectl apply -f pgadmin-configmap.yaml
configmap/pgadmin-config created

 

Service

The next piece is the pgAdmin service. A service in Kubernetes is an abstract way to describe a logical set of pods (containing one or more containers) and a policy by which they can be accessed:

apiVersion: v1
kind: Service
metadata:
 name: pgadmin-service
spec:
 ports:
 - protocol: TCP
   port: 80
   targetPort: http
 selector:
   app: pgadmin
 type: NodePort

This YAML defines a service called pgadmin-service which can be accessed using TCP on port 80, targeting any pod with a label app=pgadmin. It is exposed on a NodePort, i.e. a port on the host. 

It may be tempting to set the service type to LoadBalancer here, and then run multiple replicas of pgAdmin. This will not work as you expect! pgAdmin maintains a pool of database connections within each instance, and neither this or session data can be shared between multiple instances.

Create the service as in the previous examples:

$ kubectl apply -f pgadmin-service.yaml
service/pgadmin-service created

 

StatefulSet

The final piece of the puzzle is a StatefulSet. These are perhaps more commonly used when deploying a stateful application that has multiple replicas, for example, a cluster of PostgreSQL servers. Whilst we'll only create one instance of pgAdmin for the reasons noted above in the paragraph about load balancers, using a StatefulSet is handy because persistent storage will be automatically provisioned. 

A Deployment could be used instead of the StatefulSet, but then we'd also need to define and create a persistent volume (PV) and persistent volume claim (PVC). Doing it that way may be preferable if you wish to decouple the persistent storage from the application.

The YAML to create the application looks like this:

apiVersion: apps/v1
kind: StatefulSet
metadata:
 name: pgadmin
spec:
 serviceName: pgadmin-service
 podManagementPolicy: Parallel
 replicas: 1
 updateStrategy:
   type: RollingUpdate
 selector:
   matchLabels:
     app: pgadmin
 template:
   metadata:
     labels:
       app: pgadmin
   spec:
     terminationGracePeriodSeconds: 10
     containers:
       - name: pgadmin
         image: dpage/pgadmin4:5.4
         imagePullPolicy: Always
         env:
         - name: PGADMIN_DEFAULT_EMAIL
           value: user@domain.com
         - name: PGADMIN_DEFAULT_PASSWORD
           valueFrom:
             secretKeyRef:
               name: pgadmin
               key: pgadmin-password
         ports:
         - name: http
           containerPort: 80
           protocol: TCP
         volumeMounts:
         - name: pgadmin-config
           mountPath: /pgadmin4/servers.json
           subPath: servers.json
           readOnly: true
         - name: pgadmin-data
           mountPath: /var/lib/pgadmin
     volumes:
     - name: pgadmin-config
       configMap:
         name: pgadmin-config
 volumeClaimTemplates:
 - metadata:
     name: pgadmin-data
   spec:
     accessModes: [ "ReadWriteOnce" ]
     resources:
       requests:
         storage: 3Gi

First comes the metadata that defines the name of the application and adds the pgadmin label that the service will look for. Then, a number of parameters are defined to specify the service name, number of replicas (must be 1), update strategy and additional labels.

Then the container to be deployed is defined; we give it a name, specify the container image to pull from Docker Hub, and the policy for when to pull new images.

We then define the environment variables for the container that will contain the email address and password for the administrator account that will be created on first-launch. The email address is hard-coded in the YAML, whilst the password is pulled from the secret we created earlier.

Next, the network port used by the container is specified. In this example we're using the HTTP port, but we could also use the HTTPS port.

The remainder of the YAML is used to define the mounts and the volumes they'll use. The pgadmin-config mount is the JSON file containing the server definitions, and the pgadmin-data mount is a directory that pgAdmin uses to store configuration data, user preferences, session data, and other files.

The pgadmin-config volume is defined as being part of the ConfigMap, whilst the pgadmin-data volume is defined as a template rather than an actual volume. This is because it defines a PVC that will be automatically created from the template as part of the StatefulSet, rather than—as in the case of a Deployment—an explicitly defined PV and PVC.

Now that we've got to the end of that we can create the StatefulSet:

$ kubectl apply -f pgadmin-statefulset.yaml
statefulset.app/pgadmin created

 

Connecting

The method for connecting to the pgAdmin instance largely depends on your Kubernetes deployment. In my case I've been testing with Minikube which has a command line interface that can create a tunnel to the pgAdmin instance (otherwise, it will only be accessible to other pods):

$ minikube service pgadmin-service --url                                                                      ✔ 
🏃  Starting tunnel for service pgadmin-service.
|-----------|-----------------|-------------|------------------------|
| NAMESPACE |      NAME       | TARGET PORT |          URL           |
|-----------|-----------------|-------------|------------------------|
| default   | pgadmin-service |             | http://127.0.0.1:64299 |
|-----------|-----------------|-------------|------------------------|
http://127.0.0.1:64299
❗  Because you are using a Docker driver on darwin, the terminal needs to be open to run it.

Another option may be to use kubectl to forward a local port to the service running on the local host (for example, if running in kind):

$ kubectl port-forward service/pgadmin-service 8080:80                      
Forwarding from 127.0.0.1:8080 -> 80
Forwarding from [::1]:8080 -> 80

 

Conclusion

Running pgAdmin in Kubernetes is somewhat more work that running it in Docker, however there are obvious benefits to being able to define a deployment in a bunch of YAML which can be stored under change control and used in many different environments.

The example given here is a relatively simple one, but should give you a good starting point that can be tailored as desired for your own deployments.
 

Dave Page

Dave Page is Vice President and Chief Architect, Database Infrastructure, currently working in the CTO team on research and development, best practices with Postgres, and providing high-level guidance and support for key customers. 

Dave has been working with PostgreSQL sin ...