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 KUBECONFIG context (mini-howto)

  • kubectl is installed

  • helm is installed

  • make is installed

  • git commandline tools are installed

  • If 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 environment

  • define explicit environment variables for TANGO_USER / TANGO_PASSWORD

  • create/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 password

  • password: the sys/admin password

  • tangopassword: the tango application user password

  • monitorpassword: password for the monitoring user

  • accesskey / 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.1 straight 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 on cluster.local instead 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.

Was this page helpful?