Database setup

For this workshop, we are using a multi-container application - more precisely, the undisputed queen of demo apps: A To-Do App. 🎉 For the sake of simplicity we are using a mono-repo approach.

Our app consists of three parts: a backend to handle the logic, a frontend to display the To-Dos, and a PostgreSQL database to store them. This database is the first aspect we will take care of right now.

We’re using a Postgres operator to simplify setup and management. Since we only need one operator per cluster, we’ve already installed it on our cluster before this workshop. If you want to play around yourself with it - just have a look at our “What’s next” section were we added relevant links for you.

Deploy a PostgreSQL instance

Given the operator is already in place, setting up our instance is simple. Please create a file called db-instance.yaml by running

touch db-instance.yaml

open the file and add the following:

kind: postgresql
apiVersion: acid.zalan.do/v1
metadata:
  name: pg
  namespace: <your namespace>
spec:
  teamId: "<your namespace>"
  postgresql:
    version: "17"
  numberOfInstances: 1
  volume:
    size: 10Gi

Please do not forget to update the values for namespace and teamID before running

kubectl apply -f db-instance.yaml

And there we have our instance in your namespace.

Inspecting our initial database

We can now have a look in our database and check what we currently have in there. The easiest way for this is directly using the shell within k9s, so open it by running

k9s

in your terminal, look out for your instance in your namespace, select it and press s. You should see the following:

To connect with your database, please run

psql -U postgres

in the terminal.

Let’s start checking which databases we currently have in our cluster by running

psql \l

in your terminal and receiving

Great! We can now also connect to the database called postgres by running

\c postgres

and check for tables in there using

\dt

in the terminal. You should receive the following:

As you can see, there is one table in it called “process_logs”. We can inspect the table content by running:

SELECT * FROM postgres_log;

where you should see some log data from your interactions with the database so far.

⚠️ Just in case you get a syntax error, try typing the SELECT command instead of copy pasting it.

So, let’s get some initial data useful for our ToDo-App into our database instance.

Fill the instance with initial data

Let’s first get out of our shell by first running:

\q

to exit the connection to the database, then

exit

to get out of the shell and finally ctrl + cto quit k9s.

There is a set of different options to get our initial data into the database. We could do this of course manually within the Postgres shell - but we want to do it the DevOps way, right? So let’s use a job taking care of this for us instead :)

We already prepared a script which will create a new database “todos”, adds a table in there called “todos”, too, and finally inserts some initial data into this table.

To get this job up and running, please create a file called db-job.yaml and add the following into it:

apiVersion: batch/v1
kind: Job
metadata:
  name: prepare-db
  namespace: <your namespace>
spec:
  template:
    spec:
      containers:
        - name: prepare-db-container
          image: vtrhh/custom-db-init:latest
          imagePullPolicy: Always
          command: ["/bin/sh", "-c", "/scripts/init-db.sh"]
          env:
            - name: PGPASSWORD
              valueFrom:
                secretKeyRef:
                  name: postgres.pg.credentials.postgresql.acid.zalan.do
                  key: password
      restartPolicy: Never

Please do not forget to use the correct namespace in line 5, then deploy it to our cluster by running:

kubectl apply -f db-job.yaml

When checking in k9s, you should see the job marked as completed in your namespace. Select the Pod, click l to check the logs and there we have the notifications ending with a “Database setup complete!”.

We can now also check in our database:

1. Click esc to exit the logs of the Pod
2. Select the database Pod and click s to open the shell
3. Get access to our Database by running psql -U postgres
4. List the databases with \l and find “todos” at the very end of the list
5. Connect to the todos database with \c todos
6. List all tables with \dt
7. Get the list ob table items with SELECT * FROM public.todos;

And there we have our initial ToDo’s :) So, time to get our app deployed!

We can again run

\q

and

exit

to leave the database shell.