Zum Inhalt

PostgreSQL databases (experimental)

Experimental feature

This feature is experimental. The syntax and the service may change in the future.

Datafactory offers semi-manager PostgreSQL clusters using the CloudNativePG operator. This lets you create PostgreSQL clusters with properly configured backup and monitoring with just a few settings.

Creating a PostgreSQL cluster

Danger

Once created, only change the fields explicitly marked as modifiable. Renaming or deleting the PostgreSQL object will delete the PostgreSQL cluster and also its backup.

Note

Creating a PostgreSQL cluster can take up to an hour. Please be patient.

The simplest way to create a PostgreSQL cluster is to apply the following YAML:

apiVersion: tuwien.ac.at/v1alpha1
kind: PostgreSQL
metadata:
  name: NAME HERE
spec:
  init: {} # Create a fresh database (see customization options below)
  sizeGB: 1 # You can increase this size later, but never decrease.
            # Max: 50 GB
  version: 18 # Optional, PostgreSQL version > 14

This will create a fresh cluster with 1 GB of storage space. In practice, it will allocate 25% more storage space to account for WAL files needed to perform point-in-time restore, so keep that in mind when doing quota calculations.

You can check the provisioning status by running the following command:

oc get pg NAME HERE

The Ready field should be True. If you suspect something may have gone wrong during the provisioning, please see our debugging section below.

Once your cluster is complete, the operator will create a secret named NAME-app, which will contain the connection details to your PostgreSQL database.

Note

These connection details don't work outside the cluster at the moment. This is something we are working on fixing.

You can mount this secret in your pod to provide credentials to your application. For example, a Quarkus application would need the following deployment spec:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: my-quarkus-app
spec:
  replicas: 1
  selector:
    matchLabels:
      app.kubernetes.io/name: my-quarkus-app
  template:
    metadata:
      labels:
        app.kubernetes.io/name: my-quarkus-app
    spec:
      containers:
        - image: YOURIMAGE
          name: my-quarkus-app
          env:
            # Note: you must have compiled Quarkus with db-kind: postgres
            # or use re-augmentation in order for this to work:
            - name: QUARKUS_DATASOURCE_DB_KIND
              value: postgresql
            - name: QUARKUS_DATASOURCE_USERNAME
              valueFrom:
                secretKeyRef:
                  name: NAME-app
                  key: username
            - name: QUARKUS_DATASOURCE_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: NAME-app
                  key: password
            - name: QUARKUS_DATASOURCE_JDBC_URL
              valueFrom:
                secretKeyRef:
                  name: NAME-app
                  key: jdbc-uri
          ports:
            - containerPort: 8080
              name: http
              protocol: TCP

Accessing your database from the outside

At the moment, directly accessing the database from outside the cluster is not yet possible. (Please let us know if you are interested in this feature.) However, you can create a port forward into the cluster from your machine for development purposes:

oc port-forward service/NAME-rw 5432:5432

You can now connect the PostgreSQL database on localhost:5432 with the credentials in the secret.

Customizing the database creation

By default, the setup creates a user and database named app, which is also reflected in the name of the secret that the operator creates. You can customize this by specifying the following fields during the initial creation:

apiVersion: tuwien.ac.at/v1alpha1
kind: PostgreSQL
metadata:
  name: NAME HERE
spec:
  init:
    database: mydb
    user: myuser
  sizeGB: 1

You can also customize the memory allocation for your database by specifying the following fields:

apiVersion: tuwien.ac.at/v1alpha1
kind: PostgreSQL
metadata:
  name: NAME HERE
spec:
  init: {}
  sizeGB: 1
  memoryRequestMB: 128 # Min: 128
  memoryLimitMB: 2048 # Min: 128

Customizing backups

Our configuration of the operator creates point-in-time restores using WAL files. This allows you to restore to any time. In addition, we also create full backups, by default weekly. You can configure the backup schedule as follows:

apiVersion: tuwien.ac.at/v1alpha1
kind: PostgreSQL
metadata:
  name: NAME HERE
spec:
  init: {}
  sizeGB: 1
  fullBackupSchedule: daily # Or: weekly
  fullBackupHour: 23 # UTC

By default, daily backups run between 0:00 and 6:00 UTC. Weekly backups run on Saturdays or Sundays between 0:00 and 6:00 UTC.

Restoring from a backup

If your cluster is broken, or you just want to spin up a second instance with an older dataset, you can restore a backup. To do that, you will need the name of the old PostgreSQL resource and specify it instead of the init section:

apiVersion: tuwien.ac.at/v1alpha1
kind: PostgreSQL
metadata:
  name: NAME HERE
spec:
  restore:
    name: OLD BACKUP NAME HERE
  sizeGB: 1

You can also specify the time for the point-in-time recovery:

apiVersion: tuwien.ac.at/v1alpha1
kind: PostgreSQL
metadata:
  name: NAME HERE
spec:
  restore:
    name: OLD BACKUP NAME HERE
    targetTime: YYYY-MM-DD HH:mm:ss
  sizeGB: 1

This will restore the specified backup into a new database. Should you need more quota for your restore, let us know.

Warning

Exercise your recovery procedures regularly! The database is your responsibility and your team needs to know how to perform a recovery!

Manual restore

If you need to restore your backups outside the Datafactory cluster, you can access the S3 bucket directly. The secret will be located in your namespace. The backup is in the Barman backup format.

Debugging the database

If things go wrong, a look under the hood usually helps. The PostgreSQL resource creates a Cluster resource in the background, which you can query with the following command:

oc describe cluster NAME HERE

This will give you a detailed look into what the operator is currently doing. Other objects you can query in this fashion are:

  • ObjectStore
  • ScheduledBackup
  • Backup

Additionally, you can take a look at the PostgreSQL pods running in your namespace.