Creating a PostgreSQL Database at metalstack.cloud

Running applications requires to have some sort of persistance, 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.

Initial Setup

First create your cluster at console.metalstack.cloud, give it a proper / meaningful name and choose the machine size which fits you most.

Then download the KUBECONFIG and store this in a file (e.g. postgres-cluster.kubeconfig) on your machine.

Then execute the following command to make this cluster your current context:

export KUBECONFIG=postgres-cluster.kubeconfig

After that you are ready to talk to this cluster, ensure you are able to talk to the cluster by showing the nodes:

kubectl get node

NAME                                              STATUS   ROLES   AGE    VERSION
shoot--f8e67080ba--postgres-group-0-565cd-hcwhm   Ready    node    113s   v1.24.14

Nice, well done.

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.

# Install the krew plugin for cnpg
kubectl krew install cnpg
# Initialize the pgbench run
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)