The art of data migration

David Linfield By David Linfield 14-12-2018 4 minute read

The art of data migration

Releases are hard. One recent release for a customer of ours was no exception, thanks to some tricky data migration requirements from their old app:


• Hundreds of gigabytes of data spanning tens of millions of records, stored in an Oracle database in a conventional server farm
Our challenge was to get the data from there to the new app, which used a PostgreSQL database in the cloud
• This included a ton of processing and reformatting 
• It had to be ready for launch of the new site without upsetting any users
• That meant no downtime and no lost inputs

Given the tight budget available, this could have been a stressful and risky part of the project. On the day of release, though, we were able to relax and watch the new site launch in under two hours with no downtime.

Why is it all so hard?

Releases of new apps can be very stressful simply because so much is hanging on the moment of release. Whether it’s a completely new application, a significant set of new features, or a replacement to something that already exists, anything that goes wrong is likely to be very visible and can cause significant reputational damage.
The internet is awash with blog posts describing beautiful, enterprise-ready solutions to simplify and de-risk releases. These are great in theory, but they're usually beyond the reach of the average project. Challenging budget and timescale constraints mean that the majority of the focus has to be on delivering features that add value.
Getting the balance between cost and risk is the real challenge – and achieving that takes a talented technical team following rigorous processes.

Our approach

1. Start early
The earlier you’re planning for a release, the more opportunities you have to make challenges easier to overcome.
2. Start small, fail fast
Don't build too much at once. Build something to do a trivial amount of the work, and then test it before building any more. Don’t be afraid to go back to the drawing board if it’s not working out – you’ve not spent much time so you’re not throwing away lots of effort.
3. Test, test, and test again
Even once you think you’re done, things will change in small ways heading up to the release – make sure that every change is accompanied by a corresponding test of the releasing process to check it’s all going to work.

How we did it (the technical bit)

We applied this process to our data migration problem, beginning near the start of development with a small app to perform some components of the release. Over time and after many iterations, we ended up with a fast and reliable solution:
1. Install a PostgreSQL DB on a spare server at the server farm
2. Connect it to the Oracle DB using a foreign data wrapper
3. Use a small Flyway app to pull all the data out of the Oracle DB, in the right format
• This included some complex fuzzy matching
4. Take a backup of the PostgreSQL DB
5. Transfer it into Amazon Web Services S3 storage
6. Restore this DB into an Amazon Web Services RDS instance
7. Everything ready for the new app to be switched on

Share this article

Subscribe to our monthly newsletter about hot topics in the industry