##DB Change Management With Liquibase##
This serves as an example liquibase set up following the liquibase's best practices as outlined on the liquibase website.
It uses Openshift Pre and Post Exec Hooks to create the database schema and data using liquibase changesets.
###Example### You will find the required jar files in the lib folder of the packaged version of liquibase which you can update:
- snakeyaml-1.17.jar
- liquibase.jar
Using the Postgres driver - but easily converted to any liquibase supported database (add a new folder to /changesets directory)
- postgresql-9.4.1212.jar
The example creates a 'test' schema with a layout and data that was shipped as part of liquibase documentation. (Airline, Cities, Flights, etc).
The schema generation uses the functionality added in liquibase 3.X to use annotated sql scripts for data loading (see script in /data directory)
###Openshift Considerations###
The example makes use of Openshift deployment hooks to deploy a java based image in the same project as the postgres datamabase container. This could easily have been a sidecar container, but having a separate deployment configuration separates the two nicely.
###Deployment instructions for Openshift###
Create a postgres database on Openshift
oc new-project postgres --display-name="postgres" --description="postgres"
oc new-app --template=postgresql-persistent -p POSTGRESQL_USER=user,POSTGRESQL_PASSWORD=password,POSTGRESQL_DATABASE=test
oc set env dc postgresql POSTGRESQL_ADMIN_PASSWORD=password
Login to the database:
oc rsh $(oc get pods -lapp=postgresql-persistent --template='{{range .items}}{{.metadata.name}}{{end}}')
$ psql -h localhost -d test -U postgres
-- all tables
\dt+
-- show schemas
\dn
Create the database initializer pod that uses liquibase changesets. The schema creation and data load occur in the deployment pre-hook. We generate an xml representation of the changelog table using the post deployment hook and store that on a PVC.
oc create -f https://raw.githubusercontent.com/eformat/liquibase-example/master/dbinit-data-pvc.yaml
oc new-app https://github.com/eformat/liquibase-example.git --name=dbinit --strategy=docker
oc delete dc/dbinit
oc process -f https://raw.githubusercontent.com/eformat/liquibase-example/master/dbinit-deployment-config.json -p "IMAGE_STREAM=$(oc get is/dbinit | grep -v DOCK| awk '{print $2}')" | oc create -f -
oc rollout latest dc/dbinit
You should see successful events for the deployment hooks when the db-init pod starts:
8:26:23 AM dbinit Deployment Config Normal Started Running pre-hook ("sh -c cd /deployments && ./liquibase --defaultSchemaName=public --url=jdbc:postgresql://${POSTGRESQL_SERVICE_HOST:-127.0.0.1}:5432/test --driver=org.postgresql.Driver update -Dauthor=mike -Dschema=MY_SCHEMA") for deployment postgres/dbinit-1
8:26:47 AM dbinit Deployment Config Normal Started Running post-hook ("sh -c rm -f /data/baseline.xml && cd /deployments && ./liquibase --defaultSchemaName=my_schema --changeLogFile=/data/baseline.xml --url=jdbc:postgresql://${POSTGRESQL_SERVICE_HOST:-127.0.0.1}:5432/test --driver=org.postgresql.Driver generateChangeLog") for deployment postgres/dbinit-1
We can also see tables and data created in the database itself:
test=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-----------------------+-------+-------+------------+-------------
public | databasechangelog | table | user | 16 kB |
public | databasechangeloglock | table | user | 8192 bytes |
(2 rows)
test=# \dt my_schema.*
List of relations
Schema | Name | Type | Owner
-----------+--------------------+-------+-------
my_schema | airlines | table | user
my_schema | cities | table | user
my_schema | countries | table | user
my_schema | flightavailability | table | user
my_schema | flights | table | user
my_schema | flights_history | table | user
my_schema | maps | table | user
my_schema | qa_only | table | user
my_schema | schema_only | table | user
(9 rows)
select * from public.databasechangelog;
select * from my_schema.airlines;
Copy changeset xml generated by post-hook deployment:
oc rsync $(oc get pods -lapp=dbinit --template='{{range .items}}{{.metadata.name}}{{end}}'):/data/baseline.xml .