Looking around on the OperatorHub, the marketplace for Kubernetes Operators of various kinds, you will find many incarnations of “database operators”.
However, most of them focus on either 1) running a database within Kubernetes (for example, the various Postgres operators) or 2) integrating with a cloud-managed database provider (such as Aiven).
Unfortunately, none of them allow just managing an already existing database server.
What do I mean by manage?
Creating databases, schemas, users, roles et cetera within the database server but not running the database itself.
Luckily, this new DB operator changes that: https://github.com/db-operator/db-operator.
The DB Operator eases the pain of managing PostgreSQL and MySQL instances for applications running in Kubernetes. The Operator creates databases and makes them available in the cluster via Custom Resource.
Features:- Create/Delete databases on the database server running outside/inside Kubernetes by creating Database custom resources;- Create Google Cloud SQL instances by creating DbInstance custom resource;- Automatically create backup CronJobs according to schedules;
Prerequisite
The DB operator Helm chart currently require a functional cert-manager installation, which can be obtained with the following commands.
For more details, please check cert-manager’s installation documentation.
1
2
3
4
5
6
7
8
| helm repo add jetstack https://charts.jetstack.io
helm repo update
helm upgrade --install \
cert-manager jetstack/cert-manager \
--namespace cert-manager \
--create-namespace \
--version v1.12.0 \
--set installCRDs=true
|
Installation
Given we have cluster-admin access to a Kubernetes cluster (I’m using a v1.24.7 cluster in this tutorial), the DB operator can be quickly installed with the provided Helm chart.
The following commands add the Helm chart repository and install the operator (with default settings) into a new namespace called db-operator.
Note that we specifically install chart version 1.7.0, which installs DB operator version 1.10.0.
You can find all available chart versions here and the corresponding operator versions here.
1
2
3
4
5
6
| helm repo add db-operator https://db-operator.github.io/charts
helm upgrade --install --create-namespace \
--namespace db-operator \
db-operator \
db-operator/db-operator\
--version 1.7.0
|
Let’s check if the operator pod is running:
1
2
3
| kubectl -n db-operator get pods
NAME READY STATUS RESTARTS AGE
db-operator-65955cbfdf-4h9n7 1/1 Running 0 7s
|
Looks good! Let’s also check if the CRDs (Custom Resource Definitions) are installed:
1
2
3
| kubectl get crd | grep kci.rocks
databases.kci.rocks 2023-06-20T13:12:19Z
dbinstances.kci.rocks 2023-06-20T13:12:19Z
|
Yes they are!
Example database server
Just for demonstration purposes, we’ll run a PostgreSQL database in a separate namespace (using Bitnami’s Postgres Helm chart).
It is worth noting that this database could run anywhere: the same Kubernetes cluster, a different Kubernetes cluster, an external host, the cloud, …
1
2
3
4
| helm upgrade --install --create-namespace \
--namespace example-postgres \
postgresql \
oci://registry-1.docker.io/bitnamicharts/postgresql
|
The admin user is postgres and the password can be extracted from a secret.
We’ll need these in the next step.
1
2
3
| POSTGRES_USER=postgres
POSTGRES_PASSWORD=$(kubectl get secret --namespace example-postgres postgresql \
-o jsonpath="{.data.postgres-password}" | base64 -d)
|
Database instance
A database server (MySQL, PostgreSQL etc.) can host multiple “databases” (i.e. the result of a CREATE DATABASE my-db SQL query).
The DB operator calls the “server” a database instance and the SQL database simply database.
To get started, we need to connect the DB operator to one database server (DbInstance).
In this case we’ll use the Postgres instance created in the previous step.
Create a Secret containing the Postgres admin credentials:
1
2
3
| kubectl -n db-operator create secret generic pg-admin-secret \
--from-literal=user="${POSTGRES_USER}" \
--from-literal=password="${POSTGRES_PASSWORD}"
|
and then the DbInstance resource itself:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| # kubectl apply -f - <<EOF
apiVersion: kci.rocks/v1beta1
kind: DbInstance
metadata:
name: pg-instance
# cluster-scoped resource, no namespace
spec:
adminSecretRef:
Name: "pg-admin-secret" # reference secret created by previous command
Namespace: "db-operator"
engine: "postgres"
generic:
host: "postgresql.example-postgres.svc.cluster.local"
port: 5432 # default postgres port
EOF
|
Check the status of the connection:
1
2
3
| kubectl get dbinstance/pg-instance
NAME PHASE STATUS
pg-instance Running true
|
Now the DB operator can manage databases, users and roles within this database server.
In case the database instance does not enter the Running phase, check the logs of the operator:
1
2
3
4
5
| kubectl -n db-operator logs deploy/db-operator --tail=10
level=info msg="Instance: name=pg-instance spec changed"
level=info msg="Instance: name=pg-instance Validating"
level=error msg="db conn test failed - failed to execute query: dial tcp 10.254.96.121:5672: connect: no route to host"
|
In the example above the port was not correctly specified, hence the operator could not connect to the database.
In the following example, the password was not correctly specified in the secret referenced by the DbInstance:
1
| level=error msg="db conn test failed - failed to execute query: pq: password authentication failed for user \"postgres\""
|
Also Network Policies and other types of firewalls can be common causes that prevent the operator from connecting to the database.
Database
Finally, let’s create a new database for an arbitrary application.
We’ll do this in a new namespace called my-app:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
| # kubectl create namespace my-app
# kubectl apply -f - <<EOF
apiVersion: kci.rocks/v1beta1
kind: Database
metadata:
name: postgres-for-my-app
namespace: my-app
spec:
instance: "pg-instance" # must match with DbInstance name
deletionProtected: false # Protection to not delete database when custom resource is deleted
backup:
enable: false
cron: "0 0 * * *"
# secret in which the credentials for the database should be stored (same namespace)
secretName: example-postgres-db-credentials
# the content of the secret
secretsTemplates:
# https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
CONNECTION_STRING: "{{ .Protocol }}://{{ .UserName }}:{{ .Password }}@{{ .DatabaseHost }}:{{ .DatabasePort }}/{{ .DatabaseName }}"
DB_PASS: "{{ .Password }}"
DB_USER: "{{ .UserName }}"
DB_HOST: "{{ .DatabaseHost }}"
DB_PORT: "{{ .DatabasePort }}"
DB_NAME: "{{ .DatabaseName }}"
DB_TYPE: "{{ .Protocol }}"
EOF
|
Check the status:
1
2
3
| kubectl -n my-app get database/postgres-for-my-app
NAME PHASE STATUS PROTECTED DBINSTANCE AGE
postgres-for-my-app Ready true false pg-instance 19s
|
and the Secret created by the operator:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| # kubectl -n my-app get secret/example-postgres-db-credentials -o yaml
apiVersion: v1
kind: Secret
metadata:
creationTimestamp: "2023-06-20T14:18:23Z"
labels:
created-by: db-operator
name: example-postgres-db-credentials
namespace: my-app
type: Opaque
data:
CONNECTION_STRING: postgresql://my-app-postgres-for-my-app:-__o9Yi3jE0M95S-66Bq6_0n@postgresql.example-postgres.svc.cluster.local:5432/my-app-postgres-for-my-app
# ^^ base64 decoded ^^
DB_HOST: cG9zdGdyZXNxbC5leGFtcGxlLXBvc3RncmVzLnN2Yy5jbHVzdGVyLmxvY2Fs
DB_NAME: bXktYXBwLXBvc3RncmVzLWZvci1teS1hcHA=
DB_PASS: LV9fbzlZaTNqRTBNOTVTLTY2QnE2XzBu
DB_PORT: NTQzMg==
DB_TYPE: cG9zdGdyZXNxbA==
DB_USER: bXktYXBwLXBvc3RncmVzLWZvci1teS1hcHA=
POSTGRES_DB: bXktYXBwLXBvc3RncmVzLWZvci1teS1hcHA=
POSTGRES_PASSWORD: LV9fbzlZaTNqRTBNOTVTLTY2QnE2XzBu
POSTGRES_USER: bXktYXBwLXBvc3RncmVzLWZvci1teS1hcHA=
|
The operator also created a ConfigMap containing the non-sensitive parts of the database connection:
1
2
3
4
5
6
7
8
9
10
11
12
13
| # kubectl -n my-app get configmap example-postgres-db-credentials -o yaml
apiVersion: v1
kind: ConfigMap
metadata:
creationTimestamp: "2023-06-20T14:18:23Z"
labels:
created-by: db-operator
name: example-postgres-db-credentials
namespace: my-app
data:
DB_CONN: postgresql.example-postgres.svc.cluster.local
DB_PORT: "5432"
DB_PUBLIC_IP: ""
|
Inspection
So what happened in the Postgres database?
Let’s find out by connecting to the database server as an admin user.
1
2
3
4
5
6
7
8
9
10
11
12
13
| kubectl -n example-postgres port-forward svc/postgresql 5432:5432 &
PGPASSWORD="${POSTGRES_PASSWORD}" psql -U "${POSTGRES_USER}" -h localhost
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
----------------------------+----------+----------+-------------+-------------+-------------------------------------------------------------------------------
my-app-postgres-for-my-app | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres, postgres=CTc/postgres, "my-app-postgres-for-my-app"=CTc/postgres
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres, postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres, postgres=CTc/postgres
(4 rows)
|
We can see that the operator created a database called my-app-postgres-for-my-app (according to the naming schema <NAMESPACE>-<DATABASE_RESOURCE_NAME>) and granted access privileges (C → CREATE DATABASE,SCHEMA,TABLESPACE, T → TEMPORARY, c → CONNECT) to the role (user) named “my-app-postgres-for-my-app” (the other three databases come by default with all Postgres installations).
Connection
As a final validation, let’s create a Pod that uses the credentials from the Secret (created by the operator) and connect to the database.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| # kubectl create --force -f - <<EOF
apiVersion: v1
kind: Pod
metadata:
name: demo-pod
namespace: my-app
spec:
containers:
- name: example-app
image: "docker.io/library/postgres"
command: ["sleep", "infinity"]
env:
- name: CONNECTION_STRING
valueFrom:
secretKeyRef:
name: example-postgres-db-credentials
key: CONNECTION_STRING
EOF
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
| kubectl -n my-app get pod
NAME READY STATUS RESTARTS AGE
demo-pod 1/1 Running 0 7s
kubectl -n my-app exec -it demo-pod -- bash
root@demo-pod:/# echo "$CONNECTION_STRING"
postgresql://my-app-postgres-for-my-app:-__o9Yi3jE0M95S-66Bq6_0n@postgresql.example-postgres.svc.cluster.local:5432/my-app-postgres-for-my-app
root@demo-pod:/# psql "$CONNECTION_STRING"
psql (15.3 (Debian 15.3-1.pgdg120+1))
my-app-postgres-for-my-app=> CREATE SCHEMA my_app;
CREATE SCHEMA
my-app-postgres-for-my-app=> SET search_path = 'my_app';
SET
my-app-postgres-for-my-app=> CREATE TABLE my_table(id serial PRIMARY KEY, name VARCHAR (255) UNIQUE NOT NULL);
CREATE TABLE ^
my-app-postgres-for-my-app=> INSERT INTO my_table VALUES(1234, 'foobar');
INSERT 0 1
my-app-postgres-for-my-app=> SELECT * FROM my_table;
id | name
------+--------
1234 | foobar
(1 row)
|
We can connect to and use our new database!
Cleanup
Unless the field spec.deletionProtected is set to true in the Database resource, the operator will automatically clean up the database (DROP DATABASE) after the resource gets deleted.
It’s a good idea to enable the deletion protection for production deployments to avoid accidental data loss when the resource gets deleted or recreated.
1
2
3
4
5
6
7
8
9
10
11
12
13
| kubectl -n my-app delete database/postgres-for-my-app
PGPASSWORD="${POSTGRES_PASSWORD}" psql -U "${POSTGRES_USER}" -h localhost -c '\l'
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
----------------------------+----------+----------+-------------+-------------+-------------------------------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
|
We can remove the rest of the resources by deleting the namespaces:
1
2
3
| kubectl delete namespace/my-app --wait
kubectl delete namespace/db-operator --wait
kubectl delete namespace/example-postgres --wait
|
Happy operating!