Context

I had several personal and company apps running on DigitalOcean Droplets - each of them with their own postgres database inside the VM. This number is growing as we onboard new clients and create new staging instances. In the early stages we used Azure for these VMs, then migrated to DigitalOcean - a process which took me a weekend of copying across databases, reinstalling dependencies and setting up DNS records.

Last week I realised that the same server specs that DigitalOcean charges $48/month for, are only €6/month on Contabo (and Hetzner, but for some unknown reason they rejected my account). That difference will make a material annual difference for 10+ clients, so I began another weekend migration project to move our VMs to Contabo. Every time I do this, I think "I won't need to do this again", and twice now I've been wrong on that. So I asked myself "What would make this process less painful if I had to do it again"

The part of the migration process that's the most time consuming and prone-to-error is migrating postgres databases. So I wondered - what if - for staging instances and small personal/company apps, we kept our databases on a single VM. And we had some nice web-based UI to create new databases and monitor existing ones. I also considered using a DaaS provider like neon, but having been shafted by Bit.io a few months ago when they decided to discontinue their product on a month's notice, it feels more responsible/safer to have this under our own control.

I have no idea whether this experiment will work out in the long run, but it was a fun weekend project and something I think other people would enjoy.

My Requirements

A web based interface that can be accessed by myself and other senior engineers on the team, where we can very easily 1. Create new databases (for new projects), 2. Browse and debug issues, and 3. Import & export databases (we're super familiar with pg_dump and pg_restore but having a UI makes it that little bit more seamless).

A way to connect to these databases over the internet.

Steps

Provisioning and connecting to the VM

Sign up to Contabo, click "VPS control" and register a new VPS - you can start on the €5.99 plan and upsize if needs be in future. Choose ubuntu 22.04 and set a strong password.

Once the server is provisioned, connect to it with VSCode's remote ssh plugin (You can also do this with a terminal but I find the combined editor/file browser/terminal to be much easier for this).

Installing dependencies

Install postgres

Install pgadmin4

Install Caddy

Configuration

Postgres - allow direct connections from the internet

Setup

Start pgadmin4 (The default script here will use Apache as the web server which is more difficult to configure ssl & custom domains for)

Change to the pgAdmin4 installation directory. The default installation directory is usually /usr/pgadmin4/.

cd /usr/pgadmin4/

Activate the virtual environment by running the venv/bin/activate script.

source venv/bin/activate

Start pgAdmin4 using nohup and the & operator to run it in the background and detach it from the current session.

nohup python3 web/pgAdmin4.py > pgadmin.log 2>&1 &

References

Install pgadmin4

Install caddy on Ubuntu 22.04

Snippets

Create a new user

Grant privileges to user

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO misc;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO misc;