Skip to content

Database Migration Sample with Flyway, Docker and Kubernetes in Openshift Container Platform

Notifications You must be signed in to change notification settings

nelvadas/ocp-flyway-db-migration

Repository files navigation

Flyway Database Migration in Openshift Demo

Introduction

Starting the Database

Connect on minishift cluster ``` oc login -u developer -p developer ``

Create a new project

oc new-project ocp-flyway-db-migration

Connect as admin rant the anyuid scc to the default service acccount in the ocp-flyway-db-migration project

oc adm policy add-scc-to-user anyuid -z default

Create the postgres DB application in the ocp-flyway-db-migration project

 oc new-app --docker-image=jbossdevguidebook/beosbank_posgres_db_europa:latest --name=beosbank-postgres-db-europa

--> Found Docker image dafaf18 (7 months old) from Docker Hub for "jbossdevguidebook/beosbank_posgres_db_europa:latest"
    * An image stream will be created as "beosbank-postgres-db-europa:latest" that will track this image
    * This image will be deployed in deployment config "beosbank-postgres-db-europa"
    * Port 5432/tcp will be load balanced by service "beosbank-postgres-db-europa"
      * Other containers can access this service through the hostname "beosbank-postgres-db-europa"
    * This image declares volumes and will default to use non-persistent, host-local storage.
      You can add persistent volumes later by running 'volume dc/beosbank-postgres-db-europa --add ...'
    * WARNING: Image "jbossdevguidebook/beosbank_posgres_db_europa:latest" runs as the 'root' user which may not be permitted by your cluster administrator

--> Creating resources ...
    imagestream "beosbank-postgres-db-europa" created
    deploymentconfig "beosbank-postgres-db-europa" created
    service "beosbank-postgres-db-europa" created
--> Success
    Run 'oc status' to view your app.

The application starts and you have a postgres pod

Beosbank Database pod

Check the database content

  • List the pods in the current project
$ oc get pods
NAME                                 READY     STATUS    RESTARTS   AGE
beosbank-posgres-db-europa-1-p16bx   1/1       Running   1          22h
  • Connect to the running db pod using oc rsh command
ocp-flyway-db-migration$ oc rsh beosbank-posgres-db-europa-1-p16bx
# psql -U postgres
psql (9.6.2)
Type "help" for help.
  • Check the database list with \l
postgres=# \l
                                    List of databases
      Name       |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------------+----------+----------+------------+------------+-----------------------
 beosbank-europa | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres        | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0       | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                 |          |          |            |            | postgres=CTc/postgres
 template1       | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                 |          |          |            |            | postgres=CTc/postgres
(4 rows)
  • Connect to the beosbank db \connect and list relations \d
postgres=# \connect beosbank-europa
You are now connected to database "beosbank-europa" as user "postgres".
beosbank-europa=#

beosbank-europa=# \d
             List of relations
 Schema |       Name       | Type  | Owner
--------+------------------+-------+-------
 public | eu_customer      | table | root
 public | eu_moneytransfer | table | root
(2 rows)
  • display the content of the eu_customer table

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
  8 | London    | UK       | street Lavoisier  | 208    | 1990-01-01 | John       | Doe
  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   | India    | Gandhi street     | 600001 | 1990-02-13 | Anusha     | Mandalapu
 12 | Sao Polo  | Brasil   | samba bld         | 69400  | 1994-02-13 | Adriana    | Pinto
(12 rows)

git clone https://github.com/nelvadas/ocp-flyway-db-migration.git

cd ocp-flyway-db-migration

oc create cm sql-configmap --from-file=./sql

Containerizing Flyway SQL updates

$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

Kubernetes in action

Move to the sql folder and create a

   $ ls -rtl
total 32
-rw-r--r--  1 enonowog  staff  47 Oct 20 18:48 V2.3__UpdateZip.sql
-rw-r--r--  1 enonowog  staff  63 Oct 20 18:48 V2.2__UpdateCountry2.sql
-rw-r--r--  1 enonowog  staff  58 Jan  7 15:36 V1.1__UpdateCountry.sql
-rw-r--r--  1 enonowog  staff  84 Jan  7 15:42 V3.0__UpdateStreet.sql

Theses file describe 04 flyways modifications to be applied on the database from V1.1, V2.2, v2.3 to V3.0

$ cd ocp-flyway-db-migration/sql
$oc create cm sql-configmap --from-file=.
configmap "sql-configmap" created

Create a Job to update the database

$ oc create -f https://raw.githubusercontent.com/nelvadas/ocp-flyway-db-migration/master/beosbank-flyway-job.yaml 

Check the Jobs

$ oc get jobs
NAME                     DESIRED   SUCCESSFUL   AGE
beosbank-dbupdater-job   1         1            2d

Check the pods

$ 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


$ 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 |
+---------+-----------------------+---------------------+---------+

The database have been updated accordingly


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)
```

-------

About

Database Migration Sample with Flyway, Docker and Kubernetes in Openshift Container Platform

Topics

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published