Podman Rootless Quadlets with PostgreSQL 18 and DBGate RDBMS
Introduction
So I have used Docker a lot, and I think it is a good product which was instrumental in mainstreaming the notion of container technology. In fact, I still often just spin Docker containers up if I need to do a quick test of something, or check out a new product inside a container. I would actually also argue that Docker Swarm is a really, really good, simple, powerful, and stable product for small scale container deployments with high uptime requirements. Like just use Docker Swarm instead of the added complexity of Kubernetes if you are a small team. Anyhow, that is not what this blog post is about.
So, a couple years ago, Kubernetes stated that they wanted to move away from docker runtime or docker shim, as it did not comply with the Container Runtime Interface (CRI) API which Kubernetes uses, and therefore began using containerd directly. I am not that familiar with the story, but I think it was something like that. Actually, for a period I only ran containers with containerd and nerdctl (which is a docker compatible command cli). It worked pretty well, but sometimes it seemed too low level and too much effort to get stuff done.
So after that, the buzz was all about Podman, which was totally open source, backed by Red Hat (I think?), could run in rootless mode, and was very Kubernetes friendly. I also like the fact, that you can define the containers as systemd services'ish through Podman Quadlets. You get the benefit of being close to systemd, while also being able to see logs with journalctl. I do not know, I just like it. It seems right. Instead of using a socket. Like Docker. I believe that Docker can run rootless mode now also. But also, back in the days at least, it was nearly impossible to get a container to run as a service on anything that smells like a Windows server or machine.
Oh enough ranting, let's go do the good stuff. Before we start just know I am on Linux Fedora 44, with Podman installed.
Quadlets Impressions and Project Reasoning
This is my first attempt at using Podman as a rootless systemd service through Podman Quadlets.
What I have figured out so far is, that the user running the service should have his podman quadlet files under ~/.config/containers/systemd/ and the env vars for the various containers in, for example, dirs ~/.config/postgers/postgres.env or ~/.config/dbgate/dbgate.env.
The goal is to run postgres 18 and DB Gate as containers on the same Podman network and be production ready'ish, i.e. restart services as machine boots, have persistent data and so on, all the usual container stuff.
Postgres 18 Setup
First off, let us start with the postgres 18 container.
In .config/containers/systemd we'll create 2 postgres related files postgres.container and postgres.network.
postgres.container is very much like a regular systemd file. Take note here; the name of this file will become the name of the systemd service. Consequently, because the file is name postgres.container, the service will run as postgres.service.
Now, I do not like to have a postgres container take the port of a bare-metal postgres server, so I usually use another port than the standard 5432 port. The postgres.container file looks like so
[Unit]
Description=PostgreSQL database
[Container]
ContainerName=postgres_airflow
Image=docker.io/library/postgres:18
EnvironmentFile=%h/.config/postgres/postgres.env
Volume=postgres-data:/var/lib/postgresql
PublishPort=127.0.0.1:5454:5432
ShmSize=128m
Exec=postgres -c listen_addresses='*'
Network=postgres.network
NetworkAlias=postgres
[Service]
Restart=always
TimeoutStartSec=900
[Install]
WantedBy=default.target
Afterwards, declaratively and explicitly create the network in postgres.network. Use this network establish connection between DBGate and postgres later (the network name is referenced in both .container files).
[Network]
NetworkName=postgres-net
So the last file that we'll need for the postgres configuration part is ~/.config/postgres/postgres.env. This contains the usual suspects when you write Docker Compose files with postgres, you know, the env vars.
POSTGRES_USER=airflow
POSTGRES_PASSWORD=example
POSTGRES_DB=airflow_db
Oh yeah, I know you know what kind of P.O.C I will have up and running this summer hehe.
Now you should be able to run something like systemctl --user daemon-reload, and it kind of should work I guess.
DBGate Setup
I kind of like DBGate RDBMS. Now, I love using the psql cli as much as the next guy, but it is so nice to be able to have a RDBMS on the browser, which just always logs you straight into the database and you can immediately start working. And it just works with all kinds of databases like mssql, sqlite, mariadb and so on.
Just a small pivot here, when working with mssql server, I actually really enjoy using ssms, it has a really slow start up time, and is somewhat janky. However, it is just so robust, and all admin features / sql agent / monitoring / query execution plans and everything is just rock solid. Just wanted to get that off my chest. Not that I often appraise Microsoft.
Now, for the DBGate setup, first we need to define the ~/.config/containers/systemd/dbgate.container file.
[Unit]
Description=DbGate RDBMS
Requires=postgres.container
After=postgres.container
[Container]
ContainerName=dbgate
Image=docker.io/dbgate/dbgate:latest
Network=postgres.network
EnvironmentFile=%h/.config/dbgate/dbgate.env
Volume=dbgate-data:/root/.dbgate
PublishPort=127.0.0.1:3001:3000
[Service]
Restart=always
TimeoutStartSec=900
[Install]
WantedBy=default.target
Basically, we wait for the postgres container to be spun up, then we give it a Podman friendly name, give the download link, set up the network (the same as postgres server container is on), reference the environment file (we'll come to that one shortly), define persistent data storage location on disk, expose the port (I do not like have stuff on port 3000 because containers often use that as a default), and restart service on boot.
The environment file is located here ~/.config/dbgate/dbgate.env
CONNECTIONS=pg
LABEL_pg=Postgres
SERVER_pg=postgres
PORT_pg=5432
USER_pg=airflow
PASSWORD_pg=example
DATABASE_pg=airflow_db
ENGINE_pg=postgres@dbgate-plugin-postgres
So here we give the same credentials as the postgres server.
When we do something like systemctl --user daemon-reload or systemctl --user enable --now dbgate.service then it should get up and running.
The nice thing about running containers as systemd services is, that you can check the journalctl logs and see why stuff fails, troubleshoot errors etc., by doing something like journalctl --user -u dbgate.service.
Now when you go to localhost:3001, you can interact with the postgres 18 database.
An important aside here; when working with databases inside containers and connecting to them through a containerized RDBMS like DBGate, we use the network name as host/server name, and the internally exposed port (in this example the standard 5432), not the externally exposed port (the other one we defined in the .container file 5454).
Well, that was it, hope you enjoyed it.