Deploying a Sample PostgreSQL Database

Now that I’m beginning to use Github to manage my Kubernetes cluster as shown in my previous article Making The Leap Into Devops, it’s time to start bringing over some of my sample systems for testing. This article shows how to make use of Bitnami’s PostgreSQL helm chart to deploy a sample PostgreSQL database in Kubernetes.

I make use of the PostgreSQL DVDRental Sample Database that is available here. In order to incorporate this into my deployment, I have created a public Github called sample_dbs to host my sample databases. I pull this down into the deployment so that I have a sample database.

The Helm Chart

Let’s jump right in with the values file that I’m using to deploy the helm chart.

# Config values : https://github.com/bitnami/charts/tree/master/bitnami/postgresql
global:
  postgresql:
    auth:
      postgresPassword: "SuperSecretPGAdminPa55word!"
      username: "dvdrental_admin"
      password: "notAsSecretU5erPa55word"
      database: "dvdrental"
primary:
  persistence:
    enabled: false
  initdb:
    user: "postgres"
    password: "SuperSecretPGAdminPa55word!"
    scripts:
      data-load.sh: |-
        #!/bin/bash
        echo "Loading Sample Data..."
        curl https://raw.githubusercontent.com/salgattcy/sample_dbs/master/sampleData/dvdrental.tar -o /tmp/dvdrental.tar
        PGPASSWORD='SuperSecretPGAdminPa55word!' pg_restore /tmp/dvdrental.tar -d dvdrental -U postgres
        rm /tmp/dvdrental.tar
        echo "Done!!!"

You can view additional configuration settings in Bitnami’s repo. Some quick things to note are the global.postgresql.auth paths:

  • auth.postgresPassword : this sets the password for the admin account, postgres
  • auth.username : this is adding a user to the database called dvdrental_admin
  • auth.password : this is setting the password for the dvdrental_admin account
  • auth.database : this is creating a new database called dvdrental

Next, we’re disabling persistence because I don’t care about keeping the data long term as it’ll reset if the pod restarts. The primary.initdb section tells postgres to come up with the username and password provided along with a custom script to execute. The custom script pulls down the dvdrental.tar file and uses pg_restore to add the data to the database.

Updating the Workflow Action

With this new YAML in place, I need to add this deployment to my github actions file. I now add the following to my ~/.github/workflows/workflow.yml file:

    - name: Deploy Postgres Sample DB
      run: helm upgrade -i psql-test postgresql --namespace my_dbs -f $GITHUB_WORKSPACE/helm_charts/psql.yaml --repo https://charts.bitnami.com/bitnami

    - name: Verify Postgres Sample DB Deployment
      run: kubectl -n my_dbs rollout status statefulset/psql-test-postgresql

The Deploy Postgres Sample DB entry calls helm to deploy this into the my_dbs namespace. The Verify Postgres Sample DB Deployment entry checks the status of the rollout of the helm chart. After committing this, we make sure the workflow runs successfully and everything looks good here.

Validating Our Changes

Now, I can confirm that the database is successfully deployed by checking kubectl

% kubectl -n my_dbs get pod                                        
NAME                          READY   STATUS    RESTARTS   AGE
psql-test-postgresql-0        1/1     Running   0          3m38s

This looks good so now let’s make sure we have can successfully connect to the database and view data.

# psql -h pg-db-host -U dvdrental_admin -d dvdrental
Password for user dvdrental_admin: 
psql (14.5 (Debian 14.5-1.pgdg110+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
Type "help" for help.

dvdrental=> select * from store;
 store_id | manager_staff_id | address_id |     last_update     
----------+------------------+------------+---------------------
        1 |                1 |          1 | 2006-02-15 09:57:12
        2 |                2 |          2 | 2006-02-15 09:57:12
(2 rows)

dvdrental=> \dt
                List of relations
 Schema |     Name      | Type  |      Owner      
--------+---------------+-------+-----------------
 public | actor         | table | dvdrental_admin
 public | address       | table | dvdrental_admin
 public | category      | table | dvdrental_admin
 public | city          | table | dvdrental_admin
 public | country       | table | dvdrental_admin
 public | customer      | table | dvdrental_admin
 public | film          | table | dvdrental_admin
 public | film_actor    | table | dvdrental_admin
 public | film_category | table | dvdrental_admin
 public | inventory     | table | dvdrental_admin
 public | language      | table | dvdrental_admin
 public | payment       | table | dvdrental_admin
 public | rental        | table | dvdrental_admin
 public | staff         | table | dvdrental_admin
 public | store         | table | dvdrental_admin
(15 rows)

Everything looks good!