Setup a PostgreSQL database

Running applications requires to have some sort of persistence, either a traditional SQL Database, or some sort of Key-Value or Document Storages.

We will show in this example how you can create a PostgreSQL Database for your application in a matter of minutes. We also show how the performance can be measured and how metalstack.cloud compares to other Kubernetes as a Service Offerings out there.

metalstack.cloud offers exceptional performance because we always provide a dedicated bare metal server as worker node and the storage is always based on NVMe, which is the fastest storage technologie to date.

Install a PostgreSQL Operator

To simplify the installation and maintenance of the PostgreSQL database and make it simple to create a lot of databases, i prefer to use a operator to help me.

In this case i choose https://cloudnative-pg.io/.

First you need to install the operator with:

kubectl apply -f \
  https://raw.githubusercontent.com/cloudnative-pg/cloudnative-pg/release-1.20/releases/cnpg-1.20.1.yaml

Check after a minute or so it worked with:

kubectl get deploy -n cnpg-system cnpg-controller-manager

NAME                      READY   UP-TO-DATE   AVAILABLE   AGE
cnpg-controller-manager   1/1     1            1           36s

OK the operator is installed and ready to use.

Define your first database

With the help of a operator, creating a PostgreSQL database is done by creating a resource which defines the database like so:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgres-example
spec:
  instances: 2

  # Example of rolling update strategy:
  # - unsupervised: automated update of the primary once all
  #                 replicas have been upgraded (default)
  # - supervised: requires manual supervision to perform
  #               the switchover of the primary
  primaryUpdateStrategy: unsupervised

  # Require 10Gi of storage for the database and 1Gi for the write-ahead-log (WAL)
  storage:
    size: 10Gi
    storageClass: premium
  walStorage:
    size: 1Gi
    storageClass: premium

Create a file called database.yaml with the content above and apply this as any other kubernetes resource like so:

kubectl apply -f database.yaml

After about 50sec you will have to instances of the database you specified, one is the active and a standby replication for safety.

kubectl get pod

NAME                 READY   STATUS    RESTARTS   AGE
postgres-example-1   1/1     Running   0          50s
postgres-example-2   1/1     Running   0          18s

Now you are ready to use the database and you can check if the database performs as expected. For this, the operator is capable of running pgbench. We simplify that by using the krew plugin cnpg.

kubectl krew install cnpg \
  kubectl cnpg pgbench --job-name pgbench-init postgres-example -- --initialize --scale 500

kubectl cnpg pgbench --job-name pgbench-run postgres-example -- --time 30 --client 1 --jobs 1 \
  kubectl logs jobs/pgbench-run

pgbench (15.3 (Debian 15.3-1.pgdg110+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 500
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 25156
number of failed transactions: 0 (0.000%)
latency average = 1.192 ms
initial connection time = 8.822 ms
tps = 838.748360 (without initial connection time)

With more client:

pgbench (15.3 (Debian 15.3-1.pgdg110+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 500
query mode: simple
number of clients: 30
number of threads: 30
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 286122
number of failed transactions: 0 (0.000%)
latency average = 3.143 ms
initial connection time = 26.617 ms
tps = 9545.083156 (without initial connection time)