Windows>> ocp-flyway-db-migration>> 返回
项目作者： nelvadas

项目描述 ：
Database Migration Sample with Flyway, Docker and Kubernetes in Openshift Container Platform
高级语言： Dockerfile
项目地址: git://github.com/nelvadas/ocp-flyway-db-migration.git
创建时间： 2017-10-20T13:16:20Z
项目社区：https://github.com/nelvadas/ocp-flyway-db-migration
开源协议：
下载


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

  1.  oc new-app --docker-image=jbossdevguidebook/beosbank_posgres_db_europa:latest --name=beosbank-postgres-db-europa
  3. --> Found Docker image dafaf18 (7 months old) from Docker Hub for "jbossdevguidebook/beosbank_posgres_db_europa:latest"
  4.     * An image stream will be created as "beosbank-postgres-db-europa:latest" that will track this image
  5.     * This image will be deployed in deployment config "beosbank-postgres-db-europa"
  6.     * Port 5432/tcp will be load balanced by service "beosbank-postgres-db-europa"
  7.       * Other containers can access this service through the hostname "beosbank-postgres-db-europa"
  8.     * This image declares volumes and will default to use non-persistent, host-local storage.
  9.       You can add persistent volumes later by running 'volume dc/beosbank-postgres-db-europa --add ...'
  10.     * WARNING: Image "jbossdevguidebook/beosbank_posgres_db_europa:latest" runs as the 'root' user which may not be permitted by your cluster administrator
  12. --> Creating resources ...
  13.     imagestream "beosbank-postgres-db-europa" created
  14.     deploymentconfig "beosbank-postgres-db-europa" created
  15.     service "beosbank-postgres-db-europa" created
  16. --> Success
  17.     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

    1. $ oc get pods
    2. NAME                                 READY     STATUS    RESTARTS   AGE
    3. beosbank-posgres-db-europa-1-p16bx   1/1       Running   1          22h

  • Connect to the running db pod using oc rsh command 

    1. ocp-flyway-db-migration$ oc rsh beosbank-posgres-db-europa-1-p16bx
    2. # psql -U postgres
    3. psql (9.6.2)
    4. Type "help" for help.
  • Check the database list with \l
    1. postgres=# \l
    2.                                   List of databases
    3.     Name       |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
    4. -----------------+----------+----------+------------+------------+-----------------------
    5. beosbank-europa | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
    6. postgres        | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
    7. template0       | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
    8.                |          |          |            |            | postgres=CTc/postgres
    9. template1       | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
    10.                |          |          |            |            | postgres=CTc/postgres
    11. (4 rows)
  • Connect to the beosbank db \connect and list relations \d
  1. postgres=# \connect beosbank-europa
  2. You are now connected to database "beosbank-europa" as user "postgres".
  3. beosbank-europa=#
  5. beosbank-europa=# \d
  6.              List of relations
  7.  Schema |       Name       | Type  | Owner
  8. --------+------------------+-------+-------
  9.  public | eu_customer      | table | root
  10.  public | eu_moneytransfer | table | root
  11. (2 rows)
  • display the content of the eu_customer table
  2. beosbank-europa=# select * from eu_customer;
  3.  id |   city    | country  |      street       |  zip   | birthdate  | firstname  | lastname
  4. ----+-----------+----------+-------------------+--------+------------+------------+-----------
  5.   1 | Berlin    | Germany  | brand burgStrasse | 10115  | 1985-06-20 | Yanick     | Modjo
  6.   2 | Bologna   | Italy    | place Venice      | 40100  | 1984-11-21 | Mirabeau   | Luc
  7.   3 | Paris     | France   | Bld DeGaule       | 75001  | 2000-02-07 | Noe        | Nono
  8.   4 | Chatillon | France   | Avenue JFK        | 55     | 1984-02-19 | Landry     | Kouam
  9.   5 | Douala    | Cameroon | bld Liberte       | 1020   | 1996-04-21 | Ghislain   | Kamga
  10.   6 | Yaounde   | Cameroon | Hypodrome         | 1400   | 1983-11-18 | Nathan     | Brice
  11.   7 | Bruxelles | Belgium  | rue Van Gogh      | 1000   | 1980-09-06 | Yohan      | Pieter
  12.   8 | London    | UK       | street Lavoisier  | 208    | 1990-01-01 | John       | Doe
  13.   9 | Bamako    | Mali     | Rue Modibo Keita  | 30     | 1979-05-17 | Mohamed    | Diallo
  14.  10 | Cracovie  | Pologne  | Avenue Vienne     | 434    | 1983-05-17 | Souleymann | Njifenjou
  15.  11 | Chennai   | India    | Gandhi street     | 600001 | 1990-02-13 | Anusha     | Mandalapu
  16.  12 | Sao Polo  | Brasil   | samba bld         | 69400  | 1994-02-13 | Adriana    | Pinto
  17. (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

  1. $docker build -t --no-cache  jbossdevguidebook/flyway:v1.0.4-rhdblog .
  3. ...
  5. 2018-01-07 13:48:43 (298 KB/s) - 'flyway-commandline-4.2.0.tar.gz' saved [13583481/13583481]
  7.  ---> 095befbd2450
  8. Removing intermediate container 8496d11bf4ae
  9. Step 8/9 : VOLUME /var/flyway/data
  10.  ---> Running in d0e012ece342
  11.  ---> 4b81dfff398b
  12. Removing intermediate container d0e012ece342
  13. 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}
  14.  ---> Running in ff2431eb1c26
  15.  ---> 0a3721ff4863
  16. Removing intermediate container ff2431eb1c26
  17. Successfully built 0a3721ff4863
  18. Successfully tagged jbossdevguidebook/flyway:v1.0.4-rhdblog

Kubernetes in action

Move to the sql folder and create a

```$ cd ocp-flyway-db-migration/sql
$ ls -rtl
total 32
-rw-r—r— 1 enonowog staff 47 Oct 20 18:48 V2.3UpdateZip.sql
-rw-r—r— 1 enonowog staff 63 Oct 20 18:48 V2.2UpdateCountry2.sql
-rw-r—r— 1 enonowog staff 58 Jan 7 15:36 V1.1UpdateCountry.sql
-rw-r—r— 1 enonowog staff 84 Jan 7 15:42 V3.0UpdateStreet.sql

  1. 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

  3. Create a Job to update the database

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

  2. Check the Jobs

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

  2. 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

  2. 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 |
+————-+———————————-+——————————-+————-+

  2. The database have been updated accordingly
  4. <pre><code>
  5. beosbank-europa=# select * from eu_customer;
  6.  id |    city     |     country      |      street       |  zip   | birthdate  |firstname  | lastname
  7. ----+-------------+------------------+-------------------+--------+------------+------------+-----------
  8.   1 | Berlin      | Germany          | brand burgStrasse | 10115  | 1985-06-20 | Yanick     | Modjo
  9.   2 | Bologna     | Italy            | place Venice      | 40100  | 1984-11-21 | Mirabeau   | Luc
  10.   3 | Paris       | France           | Bld DeGaule       | 75001  | 2000-02-07 | Noe        | Nono
  11.   4 | Chatillon   | France           | Avenue JFK        | 55     | 1984-02-19 | Landry     | Kouam
  12.   5 | Douala      | Cameroon         | bld Liberte       | 1020   | 1996-04-21 | Ghislain   | Kamga
  13.   6 | Yaounde     | Cameroon         | Hypodrome         | 1400   | 1983-11-18 | Nathan     | Brice
  14.   7 | Bruxelles   | Belgium          | rue Van Gogh      | 1000   | 1980-09-06 | Yohan      | Pieter
  15.   9 | Bamako      | Mali             | Rue Modibo Keita  | 30     | 1979-05-17 | Mohamed    | Diallo
  16.  10 | Cracovie    | Pologne          | Avenue Vienne     | 434    | 1983-05-17 |Souleymann | Njifenjou
  17.  <b>11 | Chennai     | Red Hat Training | Gandhi street     | 600001 | 1990-02-13 | Anusha     | Mandalapu</b>
  18. <b> 12 | Sao Polo    | Open Source      | samba bld         | 75020  | 1994-02-13 | Adriana    | Pinto</b>
  19.  <b> 8 | Farnborough | UK               | 200 Fowler Avenue | 208    | 1990-01-01 |John       | Doe</b>
  20. (12 rows)