Kubernetes

Self-hosted SQL Database with Dashboard

Simon Geronimo Mesber

Simon Geronimo Mesber

— 2 min read
Self-hosted SQL Database with Dashboard

Objective: Build a Self Hosted SQL Database that connects to a Dashboard that displays relevant information in a public website.

Steps:

  1. FInd a SQL Docker container
  2. Deploy it on my HomeLab kubernetes cluster
  3. Populate the database with some example database
  4. Include a Grafana Container in the deployment
  5. Connect grafana to the Database
  6. Create a Dashboard that displays relevant information

References:

  1. https://mailslurp.medium.com/easy-analytics-with-grafana-postgres-and-kubernetes-a9451f41d0be
  2. https://www.digitalocean.com/community/tutorials/how-to-deploy-postgres-to-kubernetes-cluster

I used the Digital Ocean guide as a reference to describe my .yaml files for my deployment. I noticed that the replicas don't have a proper set up of which one is the primary and the replicas so I reduced the number of replicas from 3 to 1 to reduce complexity.

I downloaded the sample database "Pagila" from https://github.com/devrimgunduz/pagila. I downloaded specifically the files pagila-schema.sql and pagila-data.sql.

I used the following command to paste the files inside the pod:

k cp pagila-schema.sql  postgres-6dbfbdc84-kw4g4:/tmp/pagila-schema.sql
k cp pagila-data.sql  postgres-6dbfbdc84-kw4g4:/tmp/pagila-data.sql

I used k9s to easily access the shell of the pod and run this command:

CREATE DATABASE pagila;
\q

After that is time to load the schema and the data with this command:

psql -U <POSTGRES_USER> -d pagila -f /tmp/pagila-schema.sql
psql -U <POSTGRES_USER> -d pagila -f /tmp/pagila-data.sql

I logged into the database and apply he command \dt and got the following result that confirmed that database was created and populated:

Schema Name Type
public actor table
public address table
public category table
public city table
public country table
public customer table
public film table
public film_actor table
public film_category table
public inventory table
public language table
public payment partitioned table
public payment_p2022_01 table
public payment_p2022_02 table
public payment_p2022_03 table
public payment_p2022_04 table
public payment_p2022_05 table
public payment_p2022_06 table
public payment_p2022_07 table
public rental table
public staff table
public store table
(22 rows)

Grafana

Now it's time to set the grafana. Using a post from Medium as reference I will edit their grafana.yaml in different .yaml files to keep the consistency within my HomeLab.

Now we use Port-Forward to access Grafana. We go into the configuration and add a Data Source of the Postgres Type. The connection is really simple but you have to know some information before continuing:

The Host URL is going to be the internal IP of one of the nodes of your clusters (I only have one which made it easier) and the port the postgres service is enabling: :

Now I created a panel to test that the connection was working properly and it was.

I used a cloudflare tunnel to be able to expose the grafana service to the web and created a public dashboard for it.

Take a look at the dashboard here https://grafana.simongeronimo.com/public-dashboards/3be5c6cb0d754e8093171b68d1e1e7e6

Take a loook at the .yaml files in my cluster https://github.com/simongeronimo/MyHomeLab

Read more