# PostgreSQL databases (experimental)

!!! warning "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](https://cloudnative-pg.io/).
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:

```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:

```yaml
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:

```yaml
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:

```yaml
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:

```yaml
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:

```yaml
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:

```yaml
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](https://pgbarman.org/).

## 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.