In DevOps projects, you are sometimes haunted by the practices inherited from the monolithic world. In a previous project, we were checking how to simply apply SQL updates and changes to a relational database management system (RDBMS) database in an OpenShift Cluster.
Micro database schema evolution patterns are perfectly described by Edson Yanaga in his brilliant free book: Migrating to Microservice Databases: From Relational Monolith to Distributed Data. A video presentation of these patterns is also available on youtube.
In this blog post series we will show a simple approach to implement the described patterns in your Continuous Integration and Continuous Delivery (CI/CD) pipelines on OpenShift. The series is split in two parts:
- This post shows how to handle SQL update automation using Flyway, Dockerfiles, and Kubernetes on OpenShift.
- A future post will showcase application migration patterns, including database migration stages using OpenShift Jenkins2 pipelines.
The approach uses docker containers, Flyway and Kubernetes Objects to automate SQL updates/patches on a micro database running on OpenShift.
Create your Micro Database
To keep it simple We will rely on a docker image that provides a simple Postgres database with a custom prebuilt data set, but you can build a custom database service to follow this demo.
The database is hosted on Openshift, and we assume you have a basic knowledge of Openshift, Kubernetes, and docker containers. You can install a simple Minishift/CDK Cluster using these instructions:
Once you have your OpenShift/minishift installation running, connect as admin using the oc CLI command:
$ oc login https://192.168.99.100:8443 -u developer -p developer$ oc new-project ocp-flyway-db-migration
Grant anyuid scc to the default service account in order to run docker images
$ oc adm policy add-scc-to-user anyuid -z default$ oc new-app --docker-image=jbossdevguidebook/beosbank_posgres_db_europa:latest --name=beosbank-posgres-db-europa
OpenShift view: Beosbank pod">
Determine the database pod name, and connect to the database.. Then you can explore the database content:
$ oc get pods NAME READY STATUS RESTARTS AGE beosbank-posgres-db-europa-1-p16bx 1/1 Running 1 22h
$ oc rsh beosbank-posgres-db-europa-1-p16bx # psql -U postgres
Now that the RDBMS is up and running, we may ask how to perform automatic SQL updates on the database.
From monolithic processes, we have various options to do it, including SQL batches with Flyway runtimes. In the next section we will see how to containerize a Flyway update first, and then automate it with Kubernetes.
Containerizing SQL updates with Flyway runtimes
The purpose behind the Flyway process containerization is to provide on-the-fly a container that can connect to the database container using Java Database Connectivity (JDBC) protocol in order to perform SQL updates.
From DockerHub you can find a lot of custom images for Flyway. The following Dockerfile can be used to procure a more suitable one in the OpenShift context:
FROM alpine
MAINTAINER "Nono Elvadas"
ENV FLYWAY_VERSION=4.2.0
ENV FLYWAY_HOME=/opt/flyway/$FLYWAY_VERSION \
FLYWAY_PKGS="https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/${FLYWAY_VERSION}/flyway-commandline-${FLYWAY_VERSION}.tar.gz"
LABEL com.redhat.component="flyway" \
io.k8s.description="Platform for upgrading database using flyway" \
io.k8s.display-name="DB Migration with flyway " \
io.openshift.tags="builder,sql-upgrades,flyway,db,migration"
RUN apk add --update \
openjdk8-jre \
wget \
bash
#Download flyway
RUN wget --no-check-certificate $FLYWAY_PKGS &&\
mkdir -p $FLYWAY_HOME && \
mkdir -p /var/flyway/data && \
tar -xzf flyway-commandline-$FLYWAY_VERSION.tar.gz -C $FLYWAY_HOME --strip-components=1
VOLUME /var/flyway/data
ENTRYPOINT cp -f /var/flyway/data/*.sql $FLYWAY_HOME/sql/ && \
$FLYWAY_HOME/flyway baseline migrate info -user=${DB_USER} -password=${DB_PASSWORD} -url=${DB_URL}
The Dockerfile installs wget, bash and a Java runtime environment, then downloads a specific version of Flyway binaries. Flyway binaries are installed. A volume is created on /var/flyway/data to hold SQL files we want to be executed on the database.
By default, Flyway will check the SQL file in the $FLYWAY_HOME/sql/ folder.
We first copy all of the provided SQL files from the data volume to $FLYWAY_HOME/sql/ and start a migration script. Database url and credentials should be provided as environment variables.
Note: Originally the idea was to tell Flyway to read SQL files from the volume without copying or moving them to the container home directory. However, we faced an issue with this configuration:
(See flyway issue 1807 on github). Indeed the Flyway engine will recursively read the volume, including the hidden subfolder. There is a Request for Enhancement to customize this behavior and prevent Flyway from reading meta data files in the volume mount folder.
Build the image using the command:
$ docker build -t --no-cache jbossdevguidebook/flyway:v1.0.4-rhdblog .
...
2018-01-07 13:48:43 (298 KB/s) - 'flyway-commandline-4.2.0.tar.gz' saved [13583481/13583481]
---> 095befbd2450
Removing intermediate container 8496d11bf4ae
Step 8/9 : VOLUME /var/flyway/data
---> Running in d0e012ece342
---> 4b81dfff398b
Removing intermediate container d0e012ece342
Step 9/9 : ENTRYPOINT cp -f /var/flyway/data/\*.sql $FLYWAY_HOME/sql/ && $FLYWAY_HOME/flyway baseline migrate info -user=${DB_USER} -password=${DB_PASSWORD} -url=${DB_URL}
---> Running in ff2431eb1c26
---> 0a3721ff4863
Removing intermediate container ff2431eb1c26
Successfully built 0a3721ff4863
Successfully tagged jbossdevguidebook/flyway:v1.0.4-rhdblog
The database client is now available as a docker image. In the next section, we will see how to combine Kubernetes objects in OpenShift to automate SQL updates for this database.
Kubernetes in action
Kubernetes provides various deployment objects and patterns we can rely on to apply live SQL updates from containers created on top of the "jbossdevguidebook/flyway:v1.0.4-rhdblog" image:
- Deployment Config
- Job
- CronJob/ScheduledJob
- InitContainer; Sidecar
In the following section we will illustrate how a single Kubernetes job object can be used to perform live SQL updates. SQL files will be provided to the container through a volume and a configMap.
Create a configMap from provided SQL files
$ cd ocp-flyway-db-migration/sql $ oc create cm sql-configmap --from-file=. configmap "sql-configmap" created
Create a Job to update the DB.
The job spec is provided. To keep it simple we are not going in deep details of the customization. Files are available on my github repo.
- Include secrets to keep db user and password credentials
- Manage job history limits; restart policy according to the desired policy
$ oc create -f https://raw.githubusercontent.com/nelvadas/ocp-flyway-db-migration/master/beosbank-flyway-job.yaml
Check that the job was created in OpenShift:
$ oc get jobs NAME DESIRED SUCCESSFUL AGE beosbank-dbupdater-job 1 1 2d
Check the pods. Once the job is created, it generates a job instance that is executed by a new pod.
$ oc get pods NAME READY STATUS RESTARTS AGE beosbank-dbupdater-job-wzk9q 0/1 Completed 0 2d beosbank-posgres-db-europa-1-p16bx 1/1 Running 2 6d
The job instance completed successfully according to the log, and the migration steps have been applied.
$ oc logs beosbank-dbupdater-job-wzk9q Flyway 4.2.0 by Boxfuse Database: jdbc:postgresql://beosbank-posgres-db-europa/beosbank-europa (PostgreSQL 9.6) Creating Metadata table: "public"."schema_version" Successfully baselined schema with version: 1 Successfully validated 5 migrations (execution time 00:00.014s) Current version of schema "public": 1 Migrating schema "public" to version 1.1 - UpdateCountry Migrating schema "public" to version 2.2 - UpdateCountry2 Migrating schema "public" to version 2.3 - UpdateZip Migrating schema "public" to version 3.0 - UpdateStreet Successfully applied 4 migrations to schema "public" (execution time 00:00.046s). +---------+-----------------------+---------------------+---------+ | Version | Description | Installed on | State | +---------+-----------------------+---------------------+---------+ | 1 | << Flyway Baseline >> | 2018-01-05 04:35:16 | Baselin | | 1.1 | UpdateCountry | 2018-01-05 04:35:16 | Success | | 2.2 | UpdateCountry2 | 2018-01-05 04:35:16 | Success | | 2.3 | UpdateZip | 2018-01-05 04:35:16 | Success | | 3.0 | UpdateStreet | 2018-01-05 04:35:16 | Success | +---------+-----------------------+---------------------+---------+
Check the updated DB
$ oc rsh beosbank-posgres-db-europa-1-p16bx
# psql -U postgres psql (9.6.2) Type "help" for help. postgres=# \connect beosbank-europa beosbank-europa=# select * from eu_customer; id | city | country | street | zip | birthdate |firstname | lastname ----+-------------+------------------+-------------------+--------+------------+ 1 | Berlin | Germany | brand burgStrasse | 10115 | 1985-06-20 |Yanick | Modjo 2 | Bologna | Italy | place Venice | 40100 | 1984-11-21 |Mirabeau | Luc 3 | Paris | France | Bld DeGaule | 75001 | 2000-02-07 |Noe | Nono 4 | Chatillon | France | Avenue JFK | 55 | 1984-02-19 |Landry | Kouam 5 | Douala | Cameroon | bld Liberte | 1020 | 1996-04-21 |Ghislain | Kamga 6 | Yaounde | Cameroon | Hypodrome | 1400 | 1983-11-18 |Nathan | Brice 7 | Bruxelles | Belgium | rue Van Gogh | 1000 | 1980-09-06 |Yohan | Pieter 9 | Bamako | Mali | Rue Modibo Keita | 30 | 1979-05-17 |Mohamed | Diallo 10 | Cracovie | Pologne | Avenue Vienne | 434 | 1983-05-17 |Souleymann | Njifenjou 11 | Chennai | Red Hat Training | Gandhi street | 600001 | 1990-02-13 |Anusha | Mandalapu 12 | Sao Polo | Open Source | samba bld | 75020 | 1994-02-13 |Adriana | Pinto 8 | Farnborough | UK | 200 Fowler Avenue | 208 | 1990-01-01 |John | Doe (12 rows) beosbank-europa=#
If the batch is rerun with same migration scripts, as the database is already aware of the modifications, a warning is displayed in your log and no update is performed:
Current version of schema "public": 3.0 Schema "public" is up to date. No migration necessary.
This concludes the article. Hope you learn something that will help you during your container journey.
The full source is available from my Github repository:
https://github.com/nelvadas/ocp-flyway-db-migration