Database
Direktiv requires a PostgreSQL 16+ database. It acts as datastore as well as pub/sub system between Direktiv's components. It has been tested with Postgres offerings from cloud providers as well as on-premise installations. It is recommended to use a managed Postgres service from cloud providers. If that is not possible Postgres can be installed in Kubernetes as well.
To install a Postgres instance in Kubernetes we are using Percona's Postgres operator. The following section will provide exmaples for different installation scenarios from basic testing setups to more complex high-availability configurations. For inidividual changes please visit the Percona Operator documentation page.
Installing the Operator
The operator is provided as Helm chart and the installation is straighforward. Add Direktiv's helm chart repository and run the installation command.
kubectl create namespace postgres
helm repo add percona https://percona.github.io/percona-helm-charts/
helm install --create-namespace --version 2.3.1 -n postgres pg-operator percona/pg-operator --wait
Backup Ports
For the backup to work properly port 2022 needs to be open between the nodes
Creating a Postgres Instance
Basic Configuration
This basic configuration is good for small instances and testing. It creates weekly backups and keeps the last 4 backups. Direktiv connects directly to the Database without connection pooling.
kubectl apply -f https://raw.githubusercontent.com/direktiv/direktiv/main/scripts/kubernetes/install/db/basic.yaml
apiVersion: pgv2.percona.com/v2
kind: PerconaPGCluster
metadata:
name: direktiv-cluster
namespace: postgres
# finalizers:
# - percona.com/delete-pvc
spec:
crVersion: 2.3.0
users:
- name: direktiv
databases:
- direktiv
# access to public schema
# if no superuser is required, grant privileges manually: GRANT ALL ON SCHEMA public TO direktiv;
options: "SUPERUSER"
- name: postgres
image: perconalab/percona-postgresql-operator:main-ppg16-postgres
imagePullPolicy: Always
postgresVersion: 16
port: 5432
instances:
- name: instance1
replicas: 1
dataVolumeClaimSpec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 1Gi
proxy:
pgBouncer:
replicas: 1
image: perconalab/percona-postgresql-operator:main-ppg16-pgbouncer
backups:
pgbackrest:
image: perconalab/percona-postgresql-operator:main-ppg16-pgbackrest
global:
# Keep 4 Backups
repo1-retention-full: "4"
repo1-retention-full-type: count
manual:
repoName: repo1
options:
- --type=full
repos:
- name: repo1
schedules:
full: "0 0 * * 6"
volume:
volumeClaimSpec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 1Gi
pmm:
enabled: false
image: percona/pmm-client:2.41.0
High-Availability
High-Availabilty can be achieved by scaling the database replicas. The following example has added daily differential backups and pod anti-affinity to spread the pods across the cluster. If anti-affinity is used the cluster needs to have the same number of nodes and database replicas.
kubectl apply -f https://raw.githubusercontent.com/direktiv/direktiv/main/scripts/kubernetes/install/db/ha.yaml
apiVersion: pgv2.percona.com/v2
kind: PerconaPGCluster
metadata:
name: direktiv-cluster
namespace: postgres
spec:
crVersion: 2.3.0
users:
- name: direktiv
databases:
- direktiv
# access to public schema
# if no superuser is required, grant privileges manually: GRANT ALL ON SCHEMA public TO direktiv;
options: "SUPERUSER"
- name: postgres
image: perconalab/percona-postgresql-operator:main-ppg16-postgres
imagePullPolicy: Always
postgresVersion: 16
port: 5432
instances:
- name: instance1
replicas: 2
resources:
limits:
cpu: 2.0
memory: 4Gi
dataVolumeClaimSpec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 4Gi
topologySpreadConstraints:
- maxSkew: 1
topologyKey: kubernetes.io/hostname
whenUnsatisfiable: DoNotSchedule
labelSelector:
matchLabels:
postgres-operator.crunchydata.com/instance-set: instance1
proxy:
pgBouncer:
replicas: 2
image: perconalab/percona-postgresql-operator:main-ppg16-pgbouncer
affinity:
podAntiAffinity:
preferredDuringSchedulingIgnoredDuringExecution:
- weight: 1
podAffinityTerm:
labelSelector:
matchLabels:
postgres-operator.crunchydata.com/cluster: keycloakdb
postgres-operator.crunchydata.com/role: pgbouncer
topologyKey: kubernetes.io/hostname
backups:
pgbackrest:
image: perconalab/percona-postgresql-operator:main-ppg16-pgbackrest
global:
repo1-retention-full: "4"
repo1-retention-full-type: count
manual:
repoName: repo1
options:
- --type=full
repos:
- name: repo1
schedules:
full: "0 0 * * 6"
differential: "0 1 * * 1-6"
volume:
volumeClaimSpec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 4Gi
pmm:
enabled: false
image: percona/pmm-client:2.41.0
High-Availability with S3 Backup
Percona's Postgres operator can store backups in AWS, Azure and Google Cloud as well. The following example shows how to use AWS S3 as backup storage. A secret is required for the S3 backend with the appropriate permission. This requires a s3.conf file with the S3 key and secret.
[global]
repo1-s3-key=MYKEY
repo1-s3-key-secret=MYSECRET
After creating the file adding the secret is a simple kubectl command:
kubectl create secret generic -n postgres direktiv-pgbackrest-secret --from-file=s3.conf
To test if the values are correct run the following command:
kubectl get secret -n postgres direktiv-pgbackrest-secret -o go-template='{{ index .data "s3.conf" | base64decode }}'
High-Availabilty can be achieved by scaling the database replicas. The following example has added daily differential backups and pod anti-affinity and topology spread constraints to spread the pods across the cluster. If anti-affinity is used the cluster needs to have the same number of nodes and database replicas.
kubectl apply -f https://raw.githubusercontent.com/direktiv/direktiv/main/scripts/kubernetes/install/db/s3.yaml
apiVersion: pgv2.percona.com/v2
kind: PerconaPGCluster
metadata:
name: direktiv-cluster
namespace: postgres
spec:
crVersion: 2.3.0
users:
- name: direktiv
databases:
- direktiv
# access to public schema
# if no superuser is required, grant privileges manually: GRANT ALL ON SCHEMA public TO direktiv;
options: "SUPERUSER"
- name: postgres
image: perconalab/percona-postgresql-operator:main-ppg16-postgres
imagePullPolicy: Always
postgresVersion: 16
port: 5432
instances:
- name: instance1
replicas: 1
dataVolumeClaimSpec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 1Gi
proxy:
pgBouncer:
replicas: 1
image: perconalab/percona-postgresql-operator:main-ppg16-pgbouncer
backups:
pgbackrest:
image: perconalab/percona-postgresql-operator:main-ppg16-pgbackrest
global:
repo1-retention-full: "4"
repo1-retention-full-type: time
configuration:
- secret:
name: direktiv-pgbackrest-secret
manual:
repoName: repo1
options:
- --type=full
repos:
- name: repo1
s3:
bucket: direktiv-backup
endpoint: "https://eu-central-1.linodeobjects.com"
region: "US"
schedules:
full: "0 1 * * 0"
pmm:
enabled: false
image: percona/pmm-client:2.41.0
Connection-Pooling
Connection pooling help scaling and maintaining availability between your application and the database. The Postgres Operator provides pgBouncer as connection pooling mechanism. If it is a multi-node cluster the pgBouncer replicas can be increased and spread across the cluster with pod anit-affinity rules. Direktiv can connect to the Postgres instances as well as to pgBouncer.
Getting Database Secrets
Direktiv will need the database connection information during installation with a Helm chart. It is a good start for an installation YAML to have this information. It can be easily done by running a simple script:
echo "database:
host: \"$(kubectl get secrets -n postgres direktiv-cluster-pguser-direktiv -o 'go-template={{index .data "host"}}' | base64 --decode)\"
port: $(kubectl get secrets -n postgres direktiv-cluster-pguser-direktiv -o 'go-template={{index .data "port"}}' | base64 --decode)
user: \"$(kubectl get secrets -n postgres direktiv-cluster-pguser-direktiv -o 'go-template={{index .data "user"}}' | base64 --decode)\"
password: \"$(kubectl get secrets -n postgres direktiv-cluster-pguser-direktiv -o 'go-template={{index .data "password"}}' | base64 --decode)\"
name: \"$(kubectl get secrets -n postgres direktiv-cluster-pguser-direktiv -o 'go-template={{index .data "dbname"}}' | base64 --decode)\"
sslmode: require" > direktiv.yaml
echo "database:
host: \"$(kubectl get secrets -n postgres direktiv-cluster-pguser-direktiv -o 'go-template={{index .data "pgbouncer-host"}}' | base64 --decode)\"
port: $(kubectl get secrets -n postgres direktiv-cluster-pguser-direktiv -o 'go-template={{index .data "pgbouncer-port"}}' | base64 --decode)
user: \"$(kubectl get secrets -n postgres direktiv-cluster-pguser-direktiv -o 'go-template={{index .data "user"}}' | base64 --decode)\"
password: \"$(kubectl get secrets -n postgres direktiv-cluster-pguser-direktiv -o 'go-template={{index .data "password"}}' | base64 --decode)\"
name: \"$(kubectl get secrets -n postgres direktiv-cluster-pguser-direktiv -o 'go-template={{index .data "dbname"}}' | base64 --decode)\"
sslmode: require" > direktiv.yaml
Restore from S3
It is always recommended to test the backup and restore before using Direktiv in production. To restore from S3 is a straightforward process. The first step is to pick the backup used for the restore process. It can be found under pgbackrest/backup/db in the bucket used for backups in S3. It looks like this: 20221023-042407F. There are two differtent scenarios to consider. The first one is a restore for an existing database. This can be a restore of as certain backup or a point-in-time recovery.
---
apiVersion: pgv2.percona.com/v2
kind: PerconaPGRestore
metadata:
name: restore
namespace: postgres
spec:
pgCluster: direktiv-cluster
repoName: repo1
options:
- --set=20230914-061517F
# point-in-time recovery alternative
# - --type=time
# - --target="2023-06-09 14:15:11-04"
The second scenario is if the whole database has been destroyed and it is a restore to a new database instance. In this case a datasource attribute has to be added to define the source for the backup.
---
dataSource:
postgresCluster:
clusterName: direktiv
repoName: repo1
options:
- --set=20221023-042407F
Additional Information
For more information visit Percona's documentation about backup and restore.
Restore from PVC
In case the database is not using S3 backups the backups need to be stored in a safe location in case of loss of the node storing the backups. The data has to be transferred via e.g. scp using a cron job. A restore of an existing database can be achieved with a simple restore attribute in the database configuration YAML mentioned in the S3 restore section of this documentation. The process is different if the backup node has been destroyed. It is important to not do this restore procedure if a backup is already running. The backup needs to be rescheduled to execute it without running a backup in parallel.
Identify Backup PVC
The first step to store the backup is to identify the node where the backup is stored.
kubectl get pv
NAME CAPACITY ... CLAIM
# This is the backup node
pvc-80ae5325-8b27-4695-b6df-b362dd946cb7 1Gi ... postgres/direktiv-cluster-repo1
pvc-ce9bb226-1038-49bf-bed6-e6d0188b228c 1Gi ... postgres/direktiv-cluster-instance1-q9sm-pgdata
Describing the PV shows the node where the data is stored and the directory of the data. This directory needs to be stored in a safe location for a later restore.
kubectl describe pv pvc-80ae5325-8b27-4695-b6df-b362dd946cb7
Name: pvc-80ae5325-8b27-4695-b6df-b362dd946cb7
...
Claim: postgres/direktiv-repo1
...
Node Affinity:
Required Terms:
# Node where the data is stored
Term 0: kubernetes.io/hostname in [db2]
Message:
Source:
Type: HostPath (bare host directory volume)
# Data directory on the node
Path: /var/lib/rancher/k3s/storage/pvc-80ae5325-8b27-4695-b6df-b362dd946cb7_postgres_direktiv-repo1
HostPathType: DirectoryOrCreate
Copy Data
To restore the database a backup has to be selected. The available backups are in <Backup Directory>/<Old PVC Name>/backup/db. The directory will look like the following:
drwxr-x--- 7 root root 4096 Okt 23 08:11 .
drwxr-x--- 3 root root 4096 Okt 23 08:11 ..
drwxr-x--- 3 root root 4096 Okt 23 08:11 20221023-060801F
drwxr-x--- 3 root root 4096 Okt 23 08:11 20221023-060901F
drwxr-x--- 3 root root 4096 Okt 23 08:11 20221023-061001F
drwxr-x--- 3 root root 4096 Okt 23 08:11 20221023-061101F
drwxr-x--- 3 root root 4096 Okt 23 08:11 backup.history
-rw-r----- 1 root root 2792 Okt 23 08:11 backup.info
-rw-r----- 1 root root 2792 Okt 23 08:11 backup.info.copy
lrwxrwxrwx 1 root root 16 Okt 23 08:11 latest -> 20221023-061101F
The next step is to identify where the new backup folder is located. It is exactly the same procedure as used in the copying process. The archive and backup have to be copied into the new backup directory of the new cluster.
sudo cp -Rf <Backup Directory>/archive /var/lib/rancher/k3s/storage/<New PV Directory>
sudo cp -Rf <Backup Directory>/backup /var/lib/rancher/k3s/storage/<New PV Directory>
sudo chown -R 26:tape /var/lib/rancher/k3s/storage/<New PV Directory>
The selected restore needs to be configured in the database configuration YAML and applied with kubectl apply -f mydb.yaml.
---
spec:
dataSource:
postgresCluster:
clusterName: direktiv
repoName: repo1
options:
- --set=20221023-075501F
- --archive-mode=off
Update Password
If this is a new installation of the database the password will be overwritten and the command to generate the direktiv.yaml file is incorrect. Therefore it is advised to update the password to the password in the Kubernetes secret and update Direktiv with the new password.
# get the old password
kubectl get secrets -n postgres direktiv-cluster-pguser-direktiv -o 'go-template={{index .data "password"}}' | base64 --decode
# execute in pod
kubectl exec -n postgres --stdin --tty direktiv-cluster-instance1-<POD-ID> -- psql
# update user password
ALTER USER direktiv WITH PASSWORD '<PASSWORD FROM FIRST COMMAND>';
# exit
\q
Helpful Commands
kubectl -n postgres get pods \
--selector=postgres-operator.crunchydata.com/role=master \
-o jsonpath='{.items[*].metadata.labels.postgres-operator\.crunchydata\.com/instance}'
kubectl -n postgres describe postgrescluster direktiv
kubectl exec -n postgres --stdin --tty direktiv-cluster-instance1-<ID> -- psql