From life with Kubernetes: How we removed DBMS (and not only) from review environments to static

1. Review environments can be created from separate branches in Git repositories defined by developers (the so-called feature branches).
2. They can have separate DBMS instances, queue processors, caching services, etc. - in general, everything for the full reproduction of the production environment.
3. They allow parallel development, significantly accelerating the release of new features in the application. At the same time, dozens of such environments may be required every day, which is why the speed of their creation is critical.

The problem and the way to solve it

1. To simplify the work of QA-engineers and some other employees, take out all the databases (and corresponding vhosts) used in the review, in a separate - static - environment. For the reasons prevailing in the project, this way of interacting with them was optimal.
2. Reduce the time it takes to create a review environment. The whole process of their creation from scratch is implied, i.e. including database cloning, migrations, etc.

• When creating a review environment, the following should happen once: the creation of databases in two DBMSs (MongoDB and PostgreSQL), the restoration of databases from a backup / template, and the creation of vhost in RabbitMQ. This will require a convenient way to load current dumps. (If you had review environments before, then most likely you already have a ready-made solution for this.)
• After completion of the review environment, you must delete the database and virtual host in RabbitMQ.

• for the initialization task, we will use a hook pre-installto launch it before creating all the resources in the release;
• for the delete task, a hook post-delete.

Practical implementation

{{- if .Values.global.review }}
---
apiVersion: batch/v1
kind: Job
metadata:
  name: db-create-postgres-database
  annotations:
    "helm.sh/hook": "pre-install"
    "helm.sh/hook-weight": "5"
spec:
  template:
    metadata:
      name: init-db-postgres
    spec:
      volumes:
      - name: postgres-scripts
        configMap:
          defaultMode: 0755
          name: postgresql-configmap
      containers:
      - name: init-postgres-database
        image: private-registry/postgres 
        command: ["/docker-entrypoint-initdb.d/01-review-load-dump.sh"]
        volumeMounts:
        - name: postgres-scripts
          mountPath: /docker-entrypoint-initdb.d/01-review-load-dump.sh
          subPath: review-load-dump.sh
        env:
{{- include "postgres_env" . | indent 8 }}
      restartPolicy: Never
{{- end }}

1. Job review-. review CI/CD Helm- (. if .Values.global.review ).
2. Job — , ConfigMap. , , . , hook-weight.
3. cURL , PostgreSQL, .
4. PostgreSQL : , shell- .

PostgreSQL

1. "Standard" recovery from backup;
2. Recovery using templates .

---
apiVersion: batch/v1beta1
kind: CronJob
metadata:
  name: update-postgres-template
spec:
  schedule: "50 4 * * *"
  concurrencyPolicy: Forbid
  successfulJobsHistoryLimit: 3
  failedJobsHistoryLimit: 3
  startingDeadlineSeconds: 600
  jobTemplate:
    spec:
      template:
        spec:
          restartPolicy: Never
          imagePullSecrets:
          - name: registrysecret
          volumes:
          - name: postgres-scripts
            configMap:
              defaultMode: 0755
              name: postgresql-configmap-update-cron
          containers:
          - name: cron
            command: ["/docker-entrypoint-initdb.d/update-postgres-template.sh"]
          image: private-registry/postgres 
            volumeMounts:
            - name: postgres-scripts
              mountPath: /docker-entrypoint-initdb.d/update-postgres-template.sh
              subPath: update-postgres-template.sh
            env:
{{- include "postgres_env" . | indent 8 }}

#!/bin/bash -x

CREDENTIALS="postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@${POSTGRES_HOST}/postgres"

psql -d "${CREDENTIALS}" -w -c "REVOKE CONNECT ON DATABASE ${POSTGRES_DB_TEMPLATE} FROM public"
psql -d "${CREDENTIALS}" -w -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '${POSTGRES_DB_TEMPLATE}'"

curl --fail -vsL ${HOST_FORDEV}/latest_${POSTGRES_DB_STAGE}.psql -o /tmp/${POSTGRES_DB}.psql

psql -d "${CREDENTIALS}" -w -c "ALTER DATABASE ${POSTGRES_DB_TEMPLATE} WITH is_template false allow_connections true;"
psql -d "${CREDENTIALS}" -w -c "DROP DATABASE ${POSTGRES_DB_TEMPLATE};" || true
psql -d "${CREDENTIALS}" -w -c "CREATE DATABASE ${POSTGRES_DB_TEMPLATE};" || true
pg_restore -U ${POSTGRES_USER} -h ${POSTGRES_HOST} -w -j 4 -d ${POSTGRES_DB_TEMPLATE} /tmp/${POSTGRES_DB}.psql

psql -d "${CREDENTIALS}" -w -c "ALTER DATABASE ${POSTGRES_DB_TEMPLATE} WITH is_template true allow_connections false;"

rm -v /tmp/${POSTGRES_DB}.psql

---
apiVersion: v1
kind: ConfigMap
metadata:
  name: postgresql-configmap
  annotations:
    "helm.sh/hook": "pre-install"
    "helm.sh/hook-weight": "1"
    "helm.sh/hook-delete-policy": before-hook-creation,hook-succeeded
data:
  review-load-dump.sh: |
    #!/bin/bash -x
    
 
 
    CREDENTIALS="postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@${POSTGRES_HOST}/postgres"

    if [ "$( psql -d "${CREDENTIALS}" -tAc "SELECT CASE WHEN EXISTS (SELECT * FROM pg_stat_activity WHERE datname = '${POSTGRES_DB}' LIMIT 1) THEN 1 ELSE 0 END;" )" = '1' ]
      then
          echo "Open connections has been found in ${POSTGRES_DB} database, will drop them"
          psql -d "${CREDENTIALS}" -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '${POSTGRES_DB}' -- AND pid <> pg_backend_pid();"
      else
          echo "No open connections has been found ${POSTGRES_DB} database, skipping this stage"
    fi

    psql -d "${CREDENTIALS}" -c "DROP DATABASE ${POSTGRES_DB}"

    if [ "$( psql -d "${CREDENTIALS}" -tAc "SELECT 1 FROM pg_database WHERE datname='${POSTGRES_DB}'" )" = '1' ]
      then
          echo "Database ${POSTGRES_DB} still exists, delete review job failed"
          exit 1
      else
          echo "Database ${POSTGRES_DB} does not exist, skipping"
    fi


    psql ${CREDENTIALS} -d postgres -c 'CREATE DATABASE ${POSTGRES_DB} TEMPLATE "loot-stage-copy"'

---
apiVersion: v1
kind: ConfigMap
metadata:
  name: postgresql-configmap-on-delete
  annotations:
    "helm.sh/hook": "post-delete, pre-delete"
    "helm.sh/hook-weight": "1"
    "helm.sh/hook-delete-policy": before-hook-creation
data:
  review-delete-db.sh: |
    #!/bin/bash -e

    CREDENTIALS="postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@${POSTGRES_HOST}/postgres"

    psql -d "${CREDENTIALS}" -w postgres -c "DROP DATABASE ${POSTGRES_DB}"

1. Every night, a database backup is made so that it can be downloaded from the local network of the cluster.
2. At the time of creation of the review environment, the database is loaded and restored from the dump.
3. When the dump is deployed, all other actions are performed.

---
apiVersion: v1
kind: ConfigMap
metadata:
  name: postgresql-configmap
  annotations:
    "helm.sh/hook": "pre-install"
    "helm.sh/hook-weight": "1"
    "helm.sh/hook-delete-policy": before-hook-creation,hook-succeeded
data:
  review-load-dump.sh: |
    #!/bin/bash -x

    CREDENTIALS="postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@${POSTGRES_HOST}/postgres"
    psql -d "${CREDENTIALS}" -w -c "DROP DATABASE ${POSTGRES_DB}" || true
    psql -d "${CREDENTIALS}" -w -c "CREATE DATABASE ${POSTGRES_DB}"

    curl --fail -vsL ${HOST_FORDEV}/latest_${POSTGRES_DB_STAGE}.psql -o /tmp/${POSTGRES_DB}.psql

    psql psql -d "${CREDENTIALS}" -w -c "CREATE EXTENSION ip4r;"
    pg_restore -U ${POSTGRES_USER} -h ${POSTGRES_HOST} -w -j 4 -d ${POSTGRES_DB} /tmp/${POSTGRES_DB}.psql
    rm -v /tmp/${POSTGRES_DB}.psql

Mongodb

1. He is in a deprecated state .
2. According to the results of our testing, we did not find a big difference in the time of database recovery compared to the usual one mongo_restore. However, I note that testing was carried out as part of one project - in your case, the results can be completely different.

1. Recovery can take a long time, for example, if your DBMS is located on a network file system (for cases not with production environment). Then you can simply transfer the DBMS from stage to a separate node and use local storage. Since this is not production, the speed of creating a review is more critical for us.
2. You can take each Job recovery to a separate pod, allowing you to pre-execute migrations and other processes that depend on the operation of the DBMS. So we save time by completing them in advance.
3. Sometimes you can reduce the size of the dump by deleting old / irrelevant data - up to the point that it is enough to leave only the database structure. Of course, this is not for those cases when a full dump is required (say, for QA testing tasks).

---
apiVersion: v1
kind: ConfigMap
metadata:
  name: mongodb-scripts-on-delete
  annotations:
    "helm.sh/hook": "post-delete, pre-delete"
    "helm.sh/hook-weight": "1"
    "helm.sh/hook-delete-policy": before-hook-creation
data:
  review-delete-db.sh: |
    #!/bin/bash -x

    mongo ${MONGODB_NAME} --eval "db.dropDatabase()" --host ${MONGODB_REPLICASET}/${MONGODB_HOST}
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: mongodb-scripts
  annotations:
    "helm.sh/hook": "pre-install"
    "helm.sh/hook-weight": "1"
    "helm.sh/hook-delete-policy": before-hook-creation,hook-succeeded
data:
  review-load-dump.sh: |
    #!/bin/bash -x

    curl --fail -vsL ${HOST_FORDEV}/latest_${MONGODB_NAME_STAGE}.gz -o /tmp/${MONGODB_NAME}.gz

    mongo ${MONGODB_NAME} --eval "db.dropDatabase()" --host ${MONGODB_REPLICASET}/${MONGODB_HOST}
    mongorestore --gzip --nsFrom "${MONGODB_NAME_STAGE}.*" --nsTo "${MONGODB_NAME}.*" --archive=/tmp/${MONGODB_NAME}.gz --host ${MONGODB_REPLICASET}/${MONGODB_HOST}

the --db and --collection args should only be used when restoring from a BSON file. Other uses are deprecated and will not exist in the future; use --nsInclude instead

Rabbitmq

---
apiVersion: v1
kind: ConfigMap
metadata:
  name: rabbitmq-configmap
  annotations:
    "helm.sh/hook": "pre-install"
    "helm.sh/hook-weight": "1"
    "helm.sh/hook-delete-policy": before-hook-creation,hook-succeeded
data:
  rabbitmq-setup-vhost.sh: |
    #!/bin/bash -x

    /usr/local/bin/rabbitmqadmin -H ${RABBITMQ_HOST} -u ${RABBITMQ_USER} -p ${RABBITMQ_PASSWORD} declare vhost name=${RABBITMQ_VHOST}
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: rabbitmq-configmap-on-delete
  annotations:
    "helm.sh/hook": "post-delete, pre-delete"
    "helm.sh/hook-weight": "1"
    "helm.sh/hook-delete-policy": before-hook-creation
data:
  rabbitmq-delete-vhost.sh: |
    #!/bin/bash -x

    /usr/local/bin/rabbitmqadmin -H ${RABBITMQ_HOST} -u ${RABBITMQ_USER} -p ${RABBITMQ_PASSWORD} delete vhost name=${RABBITMQ_VHOST}

disadvantages

1. It turns out a single point of failure in the form of a stage environment.
2. If an application in a stage environment runs only in one replica, we become even more dependent on the host on which this application runs. Accordingly, with an increase in the number of review environments, the load on the node proportionally increases without the ability to balance this load.

Conclusion

PS

• “ Kubernetes tips & tricks: speeding up the bootstrap of large databases ”;
• “ Unobstructed RabbitMQ migration to Kubernetes ”;
• " Unobstructed MongoDB migration to Kubernetes ";
• “ Running teams in the process of delivering a new application release to Kubernetes .”