Setup a PostgreSql Database in DigitalOcean droplet

Motivation

Along with the hype of managed databases, the cost for managed database is also growing. So, let’s have a look at how we can setup our own PostgreSql DB in droplet as a docker container

Step One: Setup the Infrastructure

Lets create the droplet (nothing but a cloud server) in Digital Ocean by choosing appropriate distribution, plan and CPU options

I chose Ubuntu distribution and rest of this post contains some installation commands which will only work in Ubuntu. For other linux flavours, search for the command alternative

Step Two: Running postgresql as docker container locally

Now login to that instance through ssh using public ip and install required packages like docker

# install docker 
apt install docker.io
# verify installation by checking the version
docker -v
# you will get output something like below - 
Docker version 20.10.12, build 20.10.12-0ubuntu4

We can use postgres official docker image to run

# pull postgres docker image
docker pull postgres:latest
# up the postgres container
docker run -itd -e POSTGRES_USER=<user> \
                -e POSTGRES_PASSWORD=<pwd> \
                -p 5432:5432 \
                -v /data:/var/lib/postgresql/data \
                --name postgresql postgres
# you will get the container id in return, check the logs now
docker logs -f <container_id>
# on success, you will see similar entry in log
2022-07-20 09:38:07.841 UTC [1] LOG:  database system is ready to accept connections

Yay! We have successfully able to up the postgres db locally

Step Three: Expose postgres remotely using ngrok

We can create a tunnel using ngrok and expose the locally running db

# install ngrok
snap install ngrok

Since all database queries works over tcp, we need to start a tunnel which forwards all the TCP traffic

# start tunnel
ngrok tcp 5432

Okay, You might have encountered some error from ngrok side.

ngrok expects users to login in order to use tcp tunneling. So let’s create ngrok account. there, you can see your auth token in the dashboard

# add auth token
ngrok config add-authtoken <token>
# now start tcp tunnel in background
ngrok tcp 5432 --log=stdout > ngrok.log &
# open the log file. you will see the forwarding url
# url=tcp://0.tcp.in.ngrok.io:13593
# 0.tcp.in.ngrok.io - db server host
# 13593 - port

Finally, it’s done. You can connect to your database from any GUI client or through cli using the above host and port

Bye!