Migrate Grails 2.2.1 application from Oracle to PostgreSQL (Part-1 of 2) . . .

Part-2 Migrate Data

Lately, I had to take up the task of migrating one of our Grails 2.2.1 applications. The migration was two folded: 1) From Oracle to PostgreSQL Database and 2) From Grails 2.2.1 to newer 3.x version.

We took a two-phase approach by attempting the Database migration first. In this first-part of two-part posts, I will briefly cover the kind of changes I had to make for migrating the application from Oracle to PostgreSQL.

Phase-1 Database Migration

Part -1: Migrate Grails Application from Oracle to PostgreSQL

The application was a straight forward Java 6, Grails 2.2.1 application with database-migration plugin used for tracking database changes that the application went through during development and afterwards.

My first-decision
There were about a dozen database-migration change scripts in the application. It was a no brainer for me to decide not to look into those history of changelogs. Who knows what kind of database changes that the app had gone through over time and  there is no point in migrating all that history. I decided to regenerate base changelog script that gets me started with the current state of the model as the base.

Following are the steps:

Step-1 Change dependencies - remove Oracle and add PostgreSQL
BuildConfig.groovy

dependencies { ... //runtime 'oracle:ojdbc6:11.2.0.1.0' runtime 'org.postgresql:postgresql:9.4.1211.jre6' //Java 6 PostgreSQL driver ... }

Step-2 Change Datasource
DataSource.groovy

environments { dataSource { pooled = true driverClassName = "org.postgresql.Driver" dialect = "org.hibernate.dialect.PostgreSQLDialect" configClass = HibernateFilterDomainConfiguration.class } ... development { dataSource { dbCreate = "none" //one of 'create', 'create-drop', 'update', 'none' driverClassName = "org.postgresql.Driver" dialect = "org.hibernate.dialect.PostgreSQLDialect" url = "jdbc:postgresql://localhost:5432/myapp" username = "myapp" password = "myapp" } } ... }

Make sure that you have PostgreSQL db installed, myapp database created and accessible by user myapp

Step 3 Regenerate Database changelog and get new schema created
Run the following command from the application home dir to generate baseline changelog:

grails dev dbm-generate-gorm-changelog baseline.groovy

This will generate baseline.groovy file under grails-app/migrations

Step 4 Make necessary changes to take the generated file into the app
Depending on how previous Oracle database migration changes were organized, take the baseline.groovy as the new base changelog file into the application.

I took this as the base changelog by creating the main changelog.groovy file that the plug-in looks for under grails-app/migrations dir. I also created releases/postgresql sub-dir under grails-app/migrations dir and moved the generated baseline.groovy file there. Modified changelog.groovy to reference it as shown below:
changelog.groovy

databaseChangeLog = { include file: 'releases/postgresql/baseline.groovy' }

Step 5 Get database objects created
Run the following database-migration command from the application home directory to get database objects like tables, constraints, sequences etc. created in the database from baseline.groovy changelog. With this your database schema should be is inline with grails domain model.

grails -Dgrails.env=development dbm-update

Step 6 Run the application
Run the app and see if it comes up. If it doesn't, there could be some issues with data type differences between Oracle and PostgreSQL. Check Bootstrap.groovy and see if you have any data initialization that's causing any issues with data types. Also, setting root log-level to debug in Config.groovy will be helpful.

Step 5 Code changes (if any needed)
Make necessary code changes. In our case, we had Grails Hibernate filters plugin used in the app and
boolean/Boolean type was the only data type that needed a special care. With Oracle, it was mapped to database column type: NUMBER. PostgreSQL provides standard sql boolean type. I had to change some of the filters that used boolean properties of domain objects. The following is an example of one such before and after migration change:

//before change static hibernateFilters = { active(condition: 'deleted=0', default:true) } //after change static hibernateFilters = { active(condition: "deleted='false'", default:true) }

Once I made the above code changes for boolean/Boolean data types, the application worked like a charm. I quickly poked around the application to see if anything breaks. Everything seemed working. Also, the application had a bunch of test cases written and all test cases passed.

Step 6 Cleanup (if any needed)
Once everything looks good, with the approach taken not to migrate old database migration scripts and newly regenerated baseline changelog, the old change logs can be safely deleted and also oracle driver if it was added to lib folder can go away.

That was not bad at all ;)

In my next part (Part-2), I will write about Data Migration from Oracle to PostgreSQL which will conclude Phase-1: Database Migration from Oracle to PostgreSQL.