Managing PostgreSQL Like a Pro: A Kubernetes-based pgAdmin Tutorial

Enterprise PostgreSQL Solutions

Comments are off

Managing PostgreSQL Like a Pro: A Kubernetes-based pgAdmin Tutorial

Introduction

This blog is aimed at beginners trying to learn the basics of PostgreSQL, pgAdmin and Kubernetes but already have some experience under their belt. For this tutorial, we will assume you have PostgreSQL correctly installed on Ubuntu. All of these steps were done using PostgreSQL 16 (development version), minikube v1.26.3 as the Kubernetes implementation, and pgAdmin 7.8 on Ubuntu 23.04. We’ll go over an example of deploying both a PostgreSQL database and pgAdmin in a Kubernetes cluster to see how we can manage the database.

Getting Started

Database Setup

Before we get started, we’ll need a simple database setup to test out pgAdmin. Since we are using Kubernetes to deploy pgAdmin we’ll also use a PostgreSQL image and expose its port to the pgAdmin pod. I’ll be using the following YAML file for the deployment.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres-deployment
spec:
  selector:
    matchLabels:
      app: postgres
  replicas: 1
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
      - name: postgres
        image: postgres:16.0
        imagePullPolicy: Always
        env:
        - name: POSTGRES_PASSWORD
          value: "postgres"
        ports:
        - containerPort: 5432
---
apiVersion: v1
kind: Service
metadata:
  name: postgres-service
spec:
  type: ClusterIP
  selector:
    app: postgres
  ports:
  - name: postgres-port
    port: 5432
    targetPort: 5432

This will start up a pod running PostgreSQL 16 and expose port 5432 (the default) to other pods in the cluster using the “ClusterIP” type of service. We’ve also given Postgres a password of “postgres”. Please note that the inclusion of the “POSTGRES_PASSWORD” environment variable is required for the pod to run. To get the pod started simply run:

$ kubectl apply -f postgres.yml

Since we’ve only exposed this pod to others inside the cluster we can’t interact with it yet. We’ll be using pgAdmin running in the same cluster to manage our database, but we’ll expose pgAdmin to the outside of the cluster so we can use it to monitor Postgres.

Running PgAdmin

PgAdmin also has its own official docker image we can deploy into our cluster. Here is a minimally defined YAML file that we will be using for our example:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgadmin-deployment
spec:
  selector:
    matchLabels:
      app: pgadmin
  replicas: 1
  template:
    metadata:
      labels:
        app: pgadmin
    spec:
      containers:
      - name: pgadmin
        image: dpage/pgadmin4:7.8
        imagePullPolicy: Always
        env:
        - name: PGADMIN_DEFAULT_EMAIL
          value: "admin@email.com"
        - name: PGADMIN_DEFAULT_PASSWORD
          value: "admin"
        ports:
        - containerPort: 80
---
apiVersion: v1
kind: Service
metadata:
  name: pgadmin-service
spec:
  type: NodePort
  selector:
    app: pgadmin
  ports:
  - name: pgadmin-port
    port: 80
    targetPort: 80
    nodePort: 30080

This file is very similar to our postgres.yml file but with a few key differences. Firstly, we’ve defined two environment variables “PGADMIN_DEFAULT_EMAIL” and “PGADMIN_DEFAULT_PASSWORD”. Both of these are required or else the deployment will throw errors. PGADMIN_DEFAULT_EMAIL specifically needs to resemble an email format, but it does not need to be an email that actually exists. We also expose pgAdmin’s port 80 to the outside of the cluster using a “NodePort” type service listening on 30080 for connections. Once this is ready we can deploy pgAdmin with:

$ kubectl apply -f pgadmin.yml

Wait for the pod to start, and then we can examine our deployment with:

$ kubectl get pods

NAME                                   READY   STATUS    RESTARTS   AGE
pgadmin-deployment-6b97876f96-b9zqp    1/1     Running   0          5m
postgres-deployment-76f99dfb4b-nlk4b   1/1     Running   0          1m

Looks like we’re ready to go!

Using PgAdmin

Connecting

PgAdmin can be accessed by a web browser using the IP of your Kubernetes implementation and the port we exposed in the YAML file. I’m using minikube as my implementation for testing purposes. I can get the IP by running:

$ minikube ip    
192.168.49.2

Therefore, I can connect to my PgAdmin deployment by entering http://192.168.49.2:30080 into the address bar of my browser.

Here we can see the login screen for pgAdmin load-in. Simply enter the email and password that was defined in the YAML file. For this case, I defined it as “admin@example.email” with the password “admin”.

Adding Servers

Now that we are logged into pgAdmin, let’s add the PostgreSQL database we started earlier so that we can use the dashboard to manage it. To add a server, you must register it by clicking on Object -> Register -> Server… and following the instructions there.

A dialogue will pop up asking you to input a name and some other settings. Give this connection a name and then click on “Connection” in the top bar of the dialogue.

Once in the connection settings, we’ll need to fill in the host, port, maintenance database, username, and password. Most of these parameters we filled into the YAML file when we created the pod so simply enter them here.

For the hostname we’ll need to use the name of the service we created to expose the Postgres pod to the rest of the cluster. We can get the name of this service by running:

$ kubectl get services
NAME               TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)        AGE
kubernetes         ClusterIP   10.96.0.1       <none>        443/TCP        43m
pgadmin-service    NodePort    10.108.31.104   <none>        80:30080/TCP   41m
postgres-service   ClusterIP   10.111.124.43   <none>        5432/TCP       43m

This gives us a list of all the services in our cluster. The only one we care about is “postgres-service” since it exposes port 5432 to pgAdmin. Therefore for the hostname enter “postgres-service” and 5432 for the port number. The maintenance database will be the default database “postgres”, while the username and password will also be the same as we defined in postgres.yml. After entering all of this your dialogue should look something like this:

Once everything is ready click on the “Save” button in the bottom right corner of the dialogue. This will not only save your connection settings but also start the connection to our Postgres database.

After this, you will be taken back to the dashboard now showing you widgets with statistics about your database. Expand the “mydb” (or whatever you named your database) entry on the left inside the “Object Explorer”. From here you can also expand your database and begin examining its internals.

All of this was a bit involved, there must be a better more seamless way to connect a database right?

Importing and Exporting Servers

Luckily there is! pgAdmin has a very clean and convenient way to save and load your servers using JSON files. Navigate to Tools -> Import/Export Servers… in the top bar of pgAdmin. This will spawn another dialogue allowing us to choose if we want to import or export a server.

Exporting

Let’s export our current server and take a look at what the JSON looks like that defines our connection. Click on “Export” at the top and enter a name for the file. Then click “Next” then select “Servers”, this will select all the servers for export which currently is just “mydb”. One last time, click “Next” and then “Finish” to generate the file.

This file gets saved into the pod’s file system, so in order to access it we have to copy it out of the pod. This can be done by running:

$ kubectl cp pgadmin-deployment-6b97876f96-b9zqp:var/lib/pgadmin/storage/admin_email.com/mydb mydb 

This copies the file I named “mydb” containing the JSON that defines our servers to the current directory on my local machine. You’ll have to change the pod name to the name Kubernetes gave your pod but everything else should be the same.

Let’s take a look at the file:

{
    "Servers": {
        "1": {
            "Name": "mydb",
            "Group": "Servers",
            "Host": "postgres-service",
            "Port": 5432,
            "MaintenanceDB": "postgres",
            "Username": "postgres",
            "UseSSHTunnel": 0,
            "TunnelPort": "22",
            "TunnelAuthentication": 0,
            "KerberosAuthentication": false,
            "ConnectionParameters": {
                "sslmode": "prefer",
                "connect_timeout": 10,
                "sslcert": "<STORAGE_DIR>/.postgresql/postgresql.crt",
                "sslkey": "<STORAGE_DIR>/.postgresql/postgresql.key"
            }
        }
    }
}

Here we can see our “Servers” object contains a numerated list of server definitions. So our one and only server named “mydb” is at index one with all of its parameters. Most of these should look familiar as they are the same values we entered into the Register dialogue when we connected to the database. There are, however, a few extra things pgAdmin adds on that aren’t technically required. PgAdmin has a copy of a minimally defined JSON file in its documentation that can be used to import a server.

Importing

This section of the documentation is very useful as it describes both a fully and minimally defined server. We’ll be focusing on the minimally defined one but feel free to take a look at the other parameters you can use.

{
    "Servers": {
        "1": {
            "Name": "Minimally Defined Server",
            "Group": "Server Group 1",
            "Port": 5432,
            "Username": "postgres",
            "Host": "localhost",
            "SSLMode": "prefer",
            "MaintenanceDB": "postgres"
        }
    }
}

These are all the required fields you’ll need to import a server. Fill them in with the information for your server and then transfer it into the directory /var/lib/pgadmin/storage/admin_email.com/ within the pgAdmin pod. This can done using kubectl copy like so:

$ kubectl cp mynewdb pgadmin-deployment-6b97876f96-b9zqp:var/lib/pgadmin/storage/admin_email.com/mynewdb  

Once inside the directory, we can navigate back to the import dialogue and load this file by pressing the “Folder” icon and selecting it.

With this, we can define any number of servers externally and then import them into pgAdmin and connect to them with ease.

Query Tool

Now that we understand the basics of connecting to our database, let’s look into how we can actually interact with it using pgAdmin. Go to the dashboard’s main page and expand the databases. Select the database you want to run queries on then click on the “Query Tool” icon beside the “Object Explorer” heading.

As you can see here I expanded the Databases and selected “postgres” as my database. Once you click the “Query Tool” icon you should see the page below.

In the text field denoted by the “Query” tab, you can run any number of queries just like you can through psql or any other PostgreSQL frontend. There is a huge number of features you can experiment with in pgAdmin, too many to go through in this blog.

Conclusion

In this blog, we went over a brief overview of how to set up and use pgAdmin inside of a Kubernetes cluster. We first talked about how to deploy pgAdmin and a PostgreSQL database to monitor in our cluster. Then we looked into the different methods we can use to add servers into pgAdmin to get your monitoring up and running more efficiently. Finally, we touched on the Query Tool and how you can use it as a graphical frontend for PostgreSQL. With its simplicity of deployment and use, pgAdmin should be at the top of anyone’s list who is looking for a new graphical database management tool.