![\"\"](\"https:\/\/developers.redhat.com\/blog\/wp-content\/uploads\/2017\/08\/openshift-logo-121c2a0c397c2a0121.jpeg\"){kind=logo}
<\/p>\n
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<\/a> Cluster.<\/p>\n 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> A video presentation of these patterns is also available on youtube<\/a>.<\/p>\n 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:<\/p>\n The approach uses docker containers, Flyway<\/a> and Kubernetes Objects<\/a> to automate SQL updates\/patches on a micro database running on OpenShift.<\/p>\n To keep it simple We will rely on a docker image<\/a> that provides a simple Postgres<\/a> database with a custom prebuilt data set, but you can build a custom database service to follow this demo.<\/p>\n 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:<\/a><\/p>\n Once you have your OpenShift\/minishift installation running, connect as admin using the oc CLI command:<\/p>\n $ oc login https:\/\/192.168.99.100:8443 -u developer -p developer Grant anyuid scc to the default service account in order to run docker images<\/p>\n $ oc adm policy add-scc-to-user anyuid -z default OpenShift view: Beosbank pod<\/p>\n Determine the database pod name, and connect to the database.. Then you can explore the database content:<\/p>\n $ oc get pods $ oc rsh beosbank-posgres-db-europa-1-p16bx Now that the RDBMS is up and running, we may ask how to perform automatic SQL updates on the database.<\/p>\n 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.<\/p>\n 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.<\/p>\n 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:<\/p>\n FROM alpine ENV FLYWAY_VERSION=4.2.0<\/p>\n ENV FLYWAY_HOME=\/opt\/flyway\/$FLYWAY_VERSION LABEL com.redhat.component=”flyway” RUN apk add –update #Download flyway VOLUME \/var\/flyway\/data<\/p>\n ENTRYPOINT cp -f \/var\/flyway\/data\/*.sql $FLYWAY_HOME\/sql\/ && 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.<\/p>\n By default, Flyway will check the SQL file in the $FLYWAY_HOME\/sql\/ folder.<\/p>\n 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.<\/p>\n 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: Build the image using the command:<\/p>\n $ docker build -t –no-cache jbossdevguidebook\/flyway:v1.0.4-rhdblog .<\/p>\n …<\/p>\n 2018-01-07 13:48:43 (298 KB\/s) – ‘flyway-commandline-4.2.0.tar.gz’ saved [13583481\/13583481] 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.<\/p>\n Kubernetes provides various deployment objects and patterns we can rely on to apply live SQL updates from containers created on top of the \u201cjbossdevguidebook\/flyway:v1.0.4-rhdblog\u201d image:<\/p>\n 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.<\/p>\n $ cd ocp-flyway-db-migration\/sql 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.<\/p>\n $ oc create -f https:\/\/raw.githubusercontent.com\/nelvadas\/ocp-flyway-db-migration\/master\/beosbank-flyway-job.yaml<\/p>\n Check that the job was created in OpenShift:<\/p>\n $ oc get jobs Check the pods. Once the job is created, it generates a job instance that is executed by a new pod.<\/p>\n $ oc get pods The job instance completed successfully according to the log, and the migration steps have been applied.<\/p>\n $ oc logs beosbank-dbupdater-job-wzk9q $ oc rsh beosbank-posgres-db-europa-1-p16bx postgres=# connect beosbank-europa beosbank-europa=#<\/p>\n 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:<\/p>\n Current version of schema “public”: 3.0 This concludes the article. Hope you learn something that will help you during your container journey.<\/p>\n The full source is available from my Github repository:\n
Create your Micro Database<\/h2>\n
\n$ oc new-project ocp-flyway-db-migration<\/p>\n
\n$ oc new-app –docker-image=jbossdevguidebook\/beosbank_posgres_db_europa:latest –name=beosbank-posgres-db-europa<\/p>\n![\"\"](\"https:\/\/developers.redhat.com\/blog\/wp-content\/uploads\/2018\/01\/img_5a5506663ba34.gif\")
<\/p>\n![\"Openshift](\"https:\/\/developers.redhat.com\/blog\/wp-content\/uploads\/2017\/12\/beosbank-db-pod.png\")
<\/p>\n
\nNAME READY STATUS RESTARTS AGE
\nbeosbank-posgres-db-europa-1-p16bx 1\/1 Running 1 22h<\/p>\n
\n# psql -U postgres<\/p>\n![\"\"](\"https:\/\/developers.redhat.com\/blog\/wp-content\/uploads\/2018\/01\/img_5a55066646b59.gif\")
<\/p>\n![\"\"](\"https:\/\/developers.redhat.com\/blog\/wp-content\/uploads\/2018\/01\/beosbank-pod-dbcontent.png\")
<\/p>\nContainerizing SQL updates with Flyway runtimes<\/h2>\n
\nMAINTAINER “Nono Elvadas”<\/p>\n
\nFLYWAY_PKGS=”https:\/\/repo1.maven.org\/maven2\/org\/flywaydb\/flyway-commandline\/$\/flyway-commandline-$.tar.gz”<\/p>\n
\nio.k8s.description=”Platform for upgrading database using flyway”
\nio.k8s.display-name=”DB Migration with flyway ”
\nio.openshift.tags=”builder,sql-upgrades,flyway,db,migration”<\/p>\n
\nopenjdk8-jre
\nwget
\nbash<\/p>\n
\nRUN wget –no-check-certificate $FLYWAY_PKGS &&
\nmkdir -p $FLYWAY_HOME &&
\nmkdir -p \/var\/flyway\/data &&
\ntar -xzf flyway-commandline-$FLYWAY_VERSION.tar.gz -C $FLYWAY_HOME –strip-components=1<\/p>\n
\n$FLYWAY_HOME\/flyway baseline migrate info -user=$ -password=$ -url=$<\/p>\n
\n(See flyway issue 1807 on github)<\/a>. 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.<\/p>\n
\n—> 095befbd2450
\nRemoving intermediate container 8496d11bf4ae
\nStep 8\/9 : VOLUME \/var\/flyway\/data
\n—> Running in d0e012ece342
\n—> 4b81dfff398b
\nRemoving intermediate container d0e012ece342
\nStep 9\/9 : ENTRYPOINT cp -f \/var\/flyway\/data\/*.sql $FLYWAY_HOME\/sql\/ && $FLYWAY_HOME\/flyway baseline migrate info -user=$ -password=$ -url=$
\n—> Running in ff2431eb1c26
\n—> 0a3721ff4863
\nRemoving intermediate container ff2431eb1c26
\nSuccessfully built 0a3721ff4863
\nSuccessfully tagged jbossdevguidebook\/flyway:v1.0.4-rhdblog<\/p>\nKubernetes in action<\/h2>\n
\n
Create a configMap from provided SQL files
![\"\"](\"https:\/\/developers.redhat.com\/blog\/wp-content\/uploads\/2018\/01\/img_5a5506677cbe5.gif\")
<\/h3>\n
\n$ oc create cm sql-configmap –from-file=.
\nconfigmap “sql-configmap” created<\/p>\nCreate a Job to update the DB.<\/h3>\n
\n
\nNAME DESIRED SUCCESSFUL AGE
\nbeosbank-dbupdater-job 1 1 2d<\/p>\n
\nNAME READY STATUS RESTARTS AGE
\nbeosbank-dbupdater-job-wzk9q 0\/1 Completed 0 2d
\nbeosbank-posgres-db-europa-1-p16bx 1\/1 Running 2 6d![\"\"](\"https:\/\/developers.redhat.com\/blog\/wp-content\/uploads\/2018\/01\/img_5a55066857f97.gif\")
<\/p>\n
\nFlyway 4.2.0 by Boxfuse
\nDatabase: jdbc:postgresql:\/\/beosbank-posgres-db-europa\/beosbank-europa (PostgreSQL 9.6)
\nCreating Metadata table: “public”.”schema_version”
\nSuccessfully baselined schema with version: 1
\nSuccessfully validated 5 migrations (execution time 00:00.014s)
\nCurrent version of schema “public”: 1
\nMigrating schema “public” to version 1.1 – UpdateCountry
\nMigrating schema “public” to version 2.2 – UpdateCountry2
\nMigrating schema “public” to version 2.3 – UpdateZip
\nMigrating schema “public” to version 3.0 – UpdateStreet
\nSuccessfully applied 4 migrations to schema “public” (execution time 00:00.046s).
\n+———+———————–+———————+———+
\n| Version | Description | Installed on | State |
\n+———+———————–+———————+———+
\n| 1 | << Flyway Baseline >> | 2018-01-05 04:35:16 | Baselin |
\n| 1.1 | UpdateCountry | 2018-01-05 04:35:16 | Success |
\n| 2.2 | UpdateCountry2 | 2018-01-05 04:35:16 | Success |
\n| 2.3 | UpdateZip | 2018-01-05 04:35:16 | Success |
\n| 3.0 | UpdateStreet | 2018-01-05 04:35:16 | Success |
\n+———+———————–+———————+———+<\/p>\nCheck the updated DB<\/h3>\n
\n# psql -U postgres
\npsql (9.6.2)
\nType “help” for help.<\/p>\n
\nbeosbank-europa=# select * from eu_customer;
\nid | city | country | street | zip | birthdate |firstname | lastname
\n—-+————-+——————+——————-+——–+————+
\n1 | Berlin | Germany | brand burgStrasse | 10115 | 1985-06-20 |Yanick | Modjo
\n2 | Bologna | Italy | place Venice | 40100 | 1984-11-21 |Mirabeau | Luc
\n3 | Paris | France | Bld DeGaule | 75001 | 2000-02-07 |Noe | Nono
\n4 | Chatillon | France | Avenue JFK | 55 | 1984-02-19 |Landry | Kouam
\n5 | Douala | Cameroon | bld Liberte | 1020 | 1996-04-21 |Ghislain | Kamga
\n6 | Yaounde | Cameroon | Hypodrome | 1400 | 1983-11-18 |Nathan | Brice
\n7 | Bruxelles | Belgium | rue Van Gogh | 1000 | 1980-09-06 |Yohan | Pieter
\n9 | Bamako | Mali | Rue Modibo Keita | 30 | 1979-05-17 |Mohamed | Diallo
\n10 | Cracovie | Pologne | Avenue Vienne | 434 | 1983-05-17 |Souleymann | Njifenjou
\n11 | Chennai | Red Hat Training | Gandhi street | 600001 | 1990-02-13 |Anusha | Mandalapu
\n12 | Sao Polo | Open Source | samba bld | 75020 | 1994-02-13 |Adriana | Pinto
\n8 | Farnborough | UK | 200 Fowler Avenue | 208 | 1990-01-01 |John | Doe
\n(12 rows)<\/p>\n
\nSchema “public” is up to date. No migration necessary.<\/p>\n
\nhttps:\/\/github.com\/nelvadas\/ocp-flyway-db-migration<\/a><\/p>\n