Running PostgreSQL, DBeaver and pgAdmin containers in Coder

on April 2nd, 2023

Databases and remote development

Developers and data scientists sometimes need a database as part of their development environment configuration. In addition, they need a UI tool to visually connect to the database to view and manipulate the data.

You could try and smash all of these into one container but chances are you will run into dependency or container UID conflicts. You also may want to leverage each provider's published container image instead for ease of maintenance.

Coder's use of an embedded Terraform OSS provisioner and a touch of Kubernetes magic to run multiple containers in a single pod make it possible to build a Coder template that creates multiple containers, each running their respective application. All of this is possible with a flexible remote development environment platform like Coder. Users like developers and data scientists benefit from faster time to their first commit and no environment setup since Coder leverages open standards like Dockerfiles, container images and Kubernetes. DevOps and Platform Engineering benefit from fewer support tickets since these environment configurations, which we call templates, standardize environments across teams.

Caption: Creating a new workspace with a 4 container template on Kubernetes

PostgreSQL, DBeaver, pgAdmin and Golang

In this example, we have a Golang app in one container, which also has the Coder agent to communicate with the user's IDEs and the Coder control plane. The template clones a public Golang app repo and uses environment variables passed to the container to know how to connect to PostgreSQL for making the app's API calls. We also have a container with PostgreSQL, a container with pgAdmin, and a container with DBeaver. pgAdmin and DBeaver are database administration tools popular with developers in the enterprise.

Caption: The template's Terraform defining the DBeaver and pgAdmin application icons

Container UIDs and Persistent Volumes

The PostgreSQL container needs to run with UID 999 and we specified a persistent volume claim for the container's directory that stores the database data. This allows the database data to persist if the Coder workspace and Kubernetes pod are stopped.

The DBeaver container has a UID of 0 aka root and also has a persistent volume claim to store database configuration settings defined during the first session. The pgAdmin container has a UID of 5050 and has its own persistent volume as well.

Caption: The template's Terraform defining the containers and UIDs in the Kubernetes pod spec

Dotfiles, CPU, Memory and Disk Parameters

At workspace creation, the developer or data scientist selects the template and is prompted for inputs like their dotfiles repository, CPU, memory and disk for each of the four containers. These inputs leverage Coder parameters and are mutable for future adjustments.

Coder templates include a markdown file which is visible in the template UI. In this example, the markdown explains how to use pgAdmin and DBeaver and includes instructions on how to run database migrations and run curl commands to retrieve data from the PostgreSQL database.

Caption: The template's markdown explaining what will be included in the workspace

Localhost and Container Intercommunication

Any container in the Kubernetes pod can communicate on localhost. This is how the Golang app, DBeaver and pgAdmin can find the PostgreSQL database. Within the coder_app resource of the template, each IDE references a process running on a port on localhost.

Accessing PostgreSQL in the Development Environment

After creating the development environment, PostgreSQL, DBeaver and pgAdmin start automatically as defined in the entrypoint of their respective containers. The Coder agent starts in the Golang container. Open DBeaver and pgAdmin using the icons in the Coder workspace UI.

Caption: A running Coder workspace as a Kubernetes pod and 4 containers for Golang, PostgreSQL, DBeaver and pgAdmin

Running Database Migrations and Accessing PostgreSQL from the Golang app

In the template's startup_script a sample Golang app is cloned from GitHub. After the development environment is created, the developer must run a couple scripts in the Golang repo to run migrations to populate the PostgreSQL database. The Golang app also starts and listens on a port for API calls on localhost to add to and query from the database.

Caption: Starting the Golang server and retrieving a row from the authors table

Accessing PostgreSQL from DBeaver

DBeaver is a popular database administration tool. It is browser-based so Coder accesses it on a subdomain in a coder_app. Configuration settings like the location and credentials to the PostgreSQL container are stored in a persistent volume claim.

Caption: Connecting to the PostgreSQL database container during initial setup with DBeaver

Caption: Retrieving rows from the persons table with DBeaver

Accessing PostgreSQL from pgAdmin

pgAdmin is also a popular database administration tool specific to PostgreSQL. It is browser-based so Coder accesses it on a subdomain in a coder_app. Configuration settings like the location and credentials to the PostgreSQL container are stored in a persistent volume claim.

Caption: Retrieving rows from the persons table with pgAdmin

Accessing PostgreSQL from `psql`

psql is a terminal-based front-end to PostgreSQL. Users open a web terminal or locally ssh into the development environment to run psql commands. The example installs psql in the startup_script but it could be just as easily installed in the Golang container which the terminal connects to via the Coder agent.