Tango database integration#
This section steps through the process of integrating and deploying the TangoDB and DatabaseDS components both for development and the Production environment. There are multiple ways in which these components can be deployed driven by the requirements of the target system - development, test, production.
For example, developers can automatically deploy a combined TangoDB/DatabaseDS using
the appropriate make targets, and test environments can be deployed in the same way.
Production, and production-like, environments instead run an independent, highly available MariaDB backend (Galera) managed by the
mariadb-operator Kubernetes
operator, with the DatabaseDS talking to it.
Prerequisites#
The following preconditions are required, so that there is a connection available to the target Kubernetes cluster for the Database deployment:
A target Kubernetes environment set as the current
KUBECONFIGcontext (mini-howto)kubectlis installedhelmis installedmakeis installedgitcommandline tools are installedIf deploying a production-like environment: the
mariadb-operator(and its CRDs) must be installed in the target cluster - see Deployment Option 2: deploy using the MariaDB operator below.
Optional credential handling flows#
There are three options for handling database credentials when deploying the Tango DB component. These directly relate to the target environment and require the selection and configuration of different credentials for each.
Set up the MariaDB database credentials (options):
accept default values for
TANGO_USER/TANGO_PASSWORD(tango/tango) - the most common option for a development environmentdefine explicit environment variables for
TANGO_USER/TANGO_PASSWORDcreate/update Vault secrets for
TANGO_USER/TANGO_PASSWORD
Set up the MariaDB database credentials#
Option 1: development environment credentials
The MariaDB credentials are required for the connection between the DatabaseDS and the
database backend. Typically, the developer would just rely on the defaults which are
automatically set - these are username: tango password: tango
Option 2: custom environment credentials
To override the default credentials, define the following environment variables: TANGO_USER / TANGO_PASSWORD .
These will be picked up by the following steps to seed the TangoDB and configure the
connection with the DatabaseDS.
Option 3: production environment credentials
Establish the associated secrets in Hashicorp Vault so that the secrets are picked up
by the following steps to seed the TangoDB and configure the connection with the DatabaseDS.
For a mariadb-operator deployment, Vault must hold the following keys:
rootpassword: the MariaDB root passwordpassword: the sys/admin passwordtangopassword: thetangoapplication user passwordmonitorpassword: password for the monitoring useraccesskey/secretid: credentials for the S3 bucket used for backups
These are read into Kubernetes Secret objects that the MariaDB custom resource
refers to (see Deployment Option 2: deploy using the MariaDB operator).
Prepare your environment#
Prepare for the deployment of components by exporting the following environment variables (substituting for your own values):
$ export TANGO_USER=tango
$ export TANGO_PASSWORD=tango
$ export TANGO_DB_HOST=ska-tango-base-tangodb
$ export KUBE_NAMESPACE=ska-tango-db
# only required for Deployment Option 2 (the MariaDB operator / production-like flow)
$ export CLUSTER_DOMAIN=cluster.local
$ export ROOT_PASSWORD=<a-strong-root-password>
Deployment Option 1: deploy using make#
Using the make style deployment is typically used for setting up a development environment.
To deploy using make, first clone the ska-tango-images repository:
$ git clone --recurse-submodules git@gitlab.com:ska-telescope/ska-tango-images.git
$ cd ska-tango-images
Configure the deployment by creating a values.yaml file:
$ cat << EOF >values.yaml
global:
minikube: true
exposeDatabaseDS: true
tango_host: databaseds-tango-base:10000
cluster_domain: cluster.local
tangodb:
db:
user: $TANGO_USER
password: $TANGO_PASSWORD
EOF
Note
Delete the db YAML key above to just accept the default user/password.
Once the repository has been cloned (including submodules), run the make targets for deployment:
$ make k8s-install-chart KUBE_NAMESPACE=${KUBE_NAMESPACE} \
RELEASE_NAME=tangodb \
K8S_CHART_PARAMS=--values values.yaml
...
install-chart: install ./charts/ska-tango-umbrella/ release: test in Namespace: ska-tango-db with params: --set global.minikube=true --set global.exposeDatabaseDS=true --set global.exposeAllDS=true --set global.tango_host=databaseds-tango-base:10000 --set global.device_server_port=45450 --set global.cluster_domain=cluster.local
helm upgrade --install test \
--set global.minikube=true --set global.exposeDatabaseDS=true --set global.exposeAllDS=true --set global.tango_host=databaseds-tango-base:10000 --set global.device_server_port=45450 --set global.cluster_domain=cluster.local \
./charts/ska-tango-umbrella/ --namespace ska-tango-db
Release "test" has been upgraded. Happy Helming!
NAME: test
LAST DEPLOYED: Fri Nov 17 10:01:05 2023
NAMESPACE: ska-tango-db
STATUS: deployed
REVISION: 3
TEST SUITE: None
This has now deployed the TangoDB and the DatabaseDS. You can now find the connection details with the following:
$ # review the running service for the TangoDB
$ kubectl -n ${KUBE_NAMESPACE} get svc ska-tango-base-tangodb
...
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
ska-tango-base-tangodb NodePort 10.111.159.246 <none> 3306:32552/TCP 34m
$ # extract the nodePort of the MariaDB
$ kubectl -n ${KUBE_NAMESPACE} get svc ska-tango-base-tangodb -o=jsonpath="{.spec.ports[0].nodePort}"
...
32552
$ # identify the IP address of the node that nodePort MariaDB is on
$ kubectl config view | grep server | awk '{print $2}' | cut -d ':' -f 2 | sed 's#//##'
...
192.168.105.3
$ # review the running service for the DatabaseDS
$ kubectl -n ${KUBE_NAMESPACE} get svc databaseds-tango-base
...
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
databaseds-tango-base LoadBalancer 10.100.154.253 192.168.105.97 10000:30150/TCP 31m
$ # extract the external IP and Port
$ kubectl -n ${KUBE_NAMESPACE} get svc databaseds-tango-base \
-o=jsonpath="{.status.loadBalancer.ingress[0].ip}:{.spec.ports[0].port}"
...
192.168.105.97:10000
Deployment Option 2: deploy using the MariaDB operator#
This option is used for setting up a production-like, highly available environment. It fully emulates the independent deployment of a separate HA database (a 3-node Galera cluster) that the DatabaseDS talks to, managed by the mariadb-operator.
Install the operator#
Follow the recommended installation from the official documentation. In short:
$ helm repo add mariadb-operator https://mariadb-operator.github.io/mariadb-operator
$ helm repo update
$ helm install mariadb-operator-crds mariadb-operator/mariadb-operator-crds \
--namespace mariadb-operator --create-namespace
$ helm install mariadb-operator mariadb-operator/mariadb-operator \
--namespace mariadb-operator --set clusterName=$CLUSTER_DOMAIN
clusterName must match the Kubernetes cluster’s own DNS domain (for example,
cluster.local, or a site-specific internal domain) - getting this wrong means the
operator’s pods will try to reach each other on the wrong domain.
You can check what is currently installed with:
$ helm list -n mariadb-operator
NAME NAMESPACE REVISION STATUS CHART APP VERSION
mariadb-operator mariadb-operator 1 deployed mariadb-operator-0.38.1 0.38.1
mariadb-operator-crds mariadb-operator 1 deployed mariadb-operator-crds-0.38.1 0.0.0
Create the credential secrets#
Ensure the target namespace exists first - unlike the make-based
Option 1 deployment, nothing in this flow creates it for you:
$ kubectl create namespace ${KUBE_NAMESPACE} --dry-run=client -o yaml | kubectl apply -f -
Before creating the database, create the Secret objects the MariaDB resource will
refer to (populate these from Vault in a production environment, see
Optional credential handling flows):
$ kubectl create secret generic mariadb-root \
--namespace=${KUBE_NAMESPACE} --from-literal=rootpassword=$ROOT_PASSWORD
$ kubectl create secret generic mariadb-tango \
--namespace=${KUBE_NAMESPACE} --from-literal=tangopassword=$TANGO_PASSWORD
Deploy the MariaDB (Galera) cluster#
Create a MariaDB custom resource for a 3-node Galera cluster:
apiVersion: k8s.mariadb.com/v1alpha1
kind: MariaDB
metadata:
name: mariadb
namespace: ska-tango-db
spec:
rootPasswordSecretKeyRef:
name: mariadb-root
key: rootpassword
username: tango
passwordSecretKeyRef:
name: mariadb-tango
key: tangopassword
database: tango
replicas: 3
galera:
enabled: true
storage:
size: 10Gi
$ kubectl apply -f mariadb.yaml -n ${KUBE_NAMESPACE}
See the CRD reference and examples for the full spec (backups, monitoring, connection pooling, restores, and so on).
Create the internal MariaDB Service#
Both schema loading options below connect to MariaDB via ${TANGO_DB_HOST}, so create the
Service mapped to the primary/writer node before loading anything - and wait for it to
have a Galera-primary pod behind it, since the MariaDB resource applied above takes a
short while to bring the cluster up:
$ cat << EOF >mariadb-internal-service.yaml
apiVersion: v1
kind: Service
metadata:
name: ${TANGO_DB_HOST}
namespace: ${KUBE_NAMESPACE}
spec:
type: ClusterIP
ports:
- name: mysql
port: 3306
protocol: TCP
targetPort: 3306
selector:
app.kubernetes.io/instance: mariadb
k8s.mariadb.com/role: primary
EOF
$ kubectl apply -f mariadb-internal-service.yaml
...
service/ska-tango-base-tangodb created
$ # wait until the Service has a primary pod behind it before loading the schema
$ kubectl -n ${KUBE_NAMESPACE} wait --for=jsonpath='{.subsets[0].addresses[0].ip}' \
endpoints/${TANGO_DB_HOST} --timeout=180s
Load the TangoDB schema#
You have two options to deploy the TangoDB schema into the MariaDB cluster. You can use a liquibase job to install any verison or a one-off Kubernetes job to load the initialisation SQL.
Option 1: the liquibase job
Load and version the TangoDB schema with a one-shot, idempotent Liquibase
Job instead of a plain SQL script. Each changeSet is tracked in a
DATABASECHANGELOG table, so re-running the Job (for example on a
redeploy, or to pick up a future schema upgrade) safely applies nothing if
the schema is already up to date. This matters once TangoDB moves onto a
multi-node Galera cluster: the original tango-db image’s
docker-entrypoint-initdb.d scripts only run once, against an empty
datadir, on a single node - there’s no coordination across replicas and no
record of what has already been applied.
The image is built from the Dockerfile and Liquibase changelogs maintained in the
ska-databases-metadata-scripts
repository, which maps the original tango-db Dockerfile’s
create_db_tables.sql / stored_proc.sql / seed-data steps onto ordered,
individually-tracked changeSets - see that repository’s README.md for the
full mapping and rationale.
Published image tags track the TangoDatabase release they were built
against - 5.29, 5.30 and 5.31 are currently available. Pick the
tag matching the TangoDatabase version you want to deploy:
$ export TANGO_DB_VERSION=5.31
Create the migration Job, targeting the internal Service mapped to
the MariaDB primary/writer node (created below) and re-using the
mariadb-tango credential secret created above:
$ cat << EOF >tango-db-liquibase-migrate.yaml
apiVersion: batch/v1
kind: Job
metadata:
name: tango-db-liquibase-migrate
namespace: ${KUBE_NAMESPACE}
labels:
app.kubernetes.io/name: tango-db
app.kubernetes.io/component: schema-migration
spec:
backoffLimit: 2
template:
metadata:
labels:
app.kubernetes.io/name: tango-db
app.kubernetes.io/component: schema-migration
spec:
restartPolicy: Never
containers:
- name: liquibase
image: registry.gitlab.com/ska-telescope/ska-tango-images/tango-db-liquibase:${TANGO_DB_VERSION}
args:
- --search-path=/liquibase/changelog
- --changelog-file=db.changelog-master.yaml
- --url=jdbc:mariadb://${TANGO_DB_HOST}:3306/tango
- --username=${TANGO_USER}
- --password=\$(DB_PASSWORD)
- update
env:
- name: DB_PASSWORD
valueFrom:
secretKeyRef:
name: mariadb-tango
key: tangopassword
resources:
requests:
cpu: 100m
memory: 256Mi
limits:
cpu: 500m
memory: 512Mi
EOF
$ kubectl apply -f tango-db-liquibase-migrate.yaml
Note
The --search-path / --changelog-file combination (rather than a
bare absolute path) is required as of Liquibase 5.x, whose
resource-accessor model resolves --changelog-file against
--search-path. If the Job is deployed as part of a Helm chart, add
helm.sh/hook: pre-upgrade,post-install (and a matching
hook-delete-policy) to its metadata so it runs once per release
instead of as a standalone manifest.
Check that the migration completed successfully:
$ kubectl -n ${KUBE_NAMESPACE} wait --for=condition=complete \
job/tango-db-liquibase-migrate --timeout=120s
$ kubectl -n ${KUBE_NAMESPACE} logs job/tango-db-liquibase-migrate
Option 2: the one-off Kubernetes Job
Load the TangoDB initialisation SQL from a 2024 database dump with a one-off Kubernetes Job, or with the
operator’s own SqlJob resource. Download the dump and stage it as a
ConfigMap so either can read it:
$ curl "https://gitlab.com/ska-telescope/ska-databases-metadata-scripts/-/raw/main/tangodb/tng.sql?ref_type=heads" > tng.sql
$ kubectl create configmap tangodb-init-script --namespace=${KUBE_NAMESPACE} --from-file=tng.sql
The ${TANGO_DB_HOST} Service created above already points the DatabaseDS (and the
Job below) at the MariaDB primary. Creating the ConfigMap doesn’t load anything by
itself, though - a Job still has to mount the SQL and actually run it against the
database. Create the one-off Job, mounting the tng.sql key from the
ConfigMap created above and re-using the mariadb-tango credential
secret created earlier:
$ cat << EOF >tango-db-init-job.yaml
apiVersion: batch/v1
kind: Job
metadata:
name: tango-db-init
namespace: ${KUBE_NAMESPACE}
labels:
app.kubernetes.io/name: tango-db
app.kubernetes.io/component: schema-init
spec:
backoffLimit: 2
template:
metadata:
labels:
app.kubernetes.io/name: tango-db
app.kubernetes.io/component: schema-init
spec:
restartPolicy: Never
containers:
- name: tng-sql-init
image: mariadb:11.4
command: ["/bin/sh", "-c"]
args:
- mariadb -h ${TANGO_DB_HOST} -u ${TANGO_USER} -p"\$DB_PASSWORD" tango < /sql-init/tng.sql
env:
- name: DB_PASSWORD
valueFrom:
secretKeyRef:
name: mariadb-tango
key: tangopassword
volumeMounts:
- name: init-script
mountPath: /sql-init
volumes:
- name: init-script
configMap:
name: tangodb-init-script
EOF
$ kubectl apply -f tango-db-init-job.yaml
$ kubectl -n ${KUBE_NAMESPACE} wait --for=condition=complete \
job/tango-db-init --timeout=120s
$ kubectl -n ${KUBE_NAMESPACE} logs job/tango-db-init
Note
If the mariadb-operator’s own SqlJob CRD is available, it can
apply the same ConfigMap directly via sqlConfigMapKeyRef, without
needing a custom container command:
$ cat << EOF >tango-db-init-sqljob.yaml
apiVersion: k8s.mariadb.com/v1alpha1
kind: SqlJob
metadata:
name: tango-db-init
namespace: ${KUBE_NAMESPACE}
spec:
mariaDbRef:
name: mariadb
username: ${TANGO_USER}
passwordSecretKeyRef:
name: mariadb-tango
key: tangopassword
database: tango
sqlConfigMapKeyRef:
name: tangodb-init-script
key: tng.sql
EOF
$ kubectl apply -f tango-db-init-sqljob.yaml
Use one or the other, not both - re-running either against a database
that already has the seeded rows will fail on duplicate keys, since
tng.sql isn’t idempotent the way the liquibase changeSets in Option 1
above are.
Configure DatabaseDS parameters (values.yaml)#
Prior to deploying the DatabaseDS, it may be necessary to customise the configuration.
Once the repository has been cloned (including submodules) as per the instructions
from Option 1 above, create a values.yaml and set parameters like so:
$ cd ska-tango-images
$ cat << EOF >values.yaml
global:
minikube: true
exposeDatabaseDS: true
exposeAllDS: false
tango_host: databaseds-tango-base:10000
cluster_domain: cluster.local
tangodb:
enabled: false
db:
host: $TANGO_DB_HOST
user: $TANGO_USER
password: $TANGO_PASSWORD
EOF
Deploy DatabaseDS#
Now run the make targets for deployment:
$ cd ska-tango-images
$ make k8s-install-chart KUBE_NAMESPACE=${KUBE_NAMESPACE} \
RELEASE_NAME=tangodb \
K8S_CHART_PARAMS=--values values.yaml
...
Release "test" has been upgraded. Happy Helming!
NAME: test
LAST DEPLOYED: Fri Nov 17 10:01:05 2023
NAMESPACE: ska-tango-db
STATUS: deployed
REVISION: 3
TEST SUITE: None
This has now deployed the DatabaseDS. You can now find the connection details with the following:
$ # review the running service for the DatabaseDS
$ kubectl -n ${KUBE_NAMESPACE} get svc databaseds-tango-base
...
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
databaseds-tango-base LoadBalancer 10.100.154.253 192.168.105.97 10000:30150/TCP 31m
$ # extract the external IP and Port
$ kubectl -n ${KUBE_NAMESPACE} get svc databaseds-tango-base \
-o=jsonpath="{.status.loadBalancer.ingress[0].ip}:{.spec.ports[0].port}"
...
192.168.105.97:10000
Upgrade the operator and the MariaDB cluster#
Before upgrading, make sure the MariaDB CRDs and existing clusters are healthy - fix
any issue first.
Apply every intermediate minor version in turn - you can skip patch releases but not minor ones (for example, going from
0.38.1straight to a much later minor version is not supported).Follow the release-specific upgrade notes - there is a dedicated procedure per version.
An operator upgrade may also require upgrading the MariaDB data plane itself:
# check the current update strategy $ kubectl get mariadb mariadb -n ${KUBE_NAMESPACE} -o jsonpath='{.spec.updateStrategy}{"\n"}' # temporarily enable automatic data plane updates $ kubectl patch mariadb mariadb -n ${KUBE_NAMESPACE} --type merge \ -p '{"spec":{"updateStrategy":{"autoUpdateDataPlane":true}}}' # ... then disable it again once the upgrade has completed $ kubectl patch mariadb mariadb -n ${KUBE_NAMESPACE} --type merge \ -p '{"spec":{"updateStrategy":{"autoUpdateDataPlane":false}}}'
When re-installing the operator, do not forget to override
clusterName- otherwise the pods will try to reach each other oncluster.localinstead of the cluster’s actual domain.
Backups and recovery#
A production Galera cluster runs with 3 nodes; the operator handles promoting a new primary if one is lost. Periodic backups to an S3 bucket are taken on top of that and kept for 30 days.
If a node is left in an unknown/stuck state (for example after the Kubernetes node it was running on was shut down uncleanly), it may need to be deleted manually so that the operator can reconcile and restart Galera on it:
$ kubectl delete pod <stuck-pod-name> -n ${KUBE_NAMESPACE}
This concludes the tutorial for deploying the TangoDB and DatabaseDS.