The art of data migration

How we tackled a challenging data migration job


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


We think you'll also enjoy

Tech News Roundup

Here are the tech news stories that have caught our eye over the last couple of weeks.
Learn more

Sniffing Out Trouble: A Deep Dive into Code Smells and How to Address Them

Using real-world examples, we dissect common pitfalls in coding practices, offering insights into why they arise and how they can be mitigated. Whether you're reviewing, maintaining, or building from scratch, arm yourself with the knowledge to foster code that isn't just functional but also maintainable for the long haul.
Learn more

Why investing in innovation during 2023 will set you up to thrive post-recession

How can you set your business up to weather the recession, and to be in a position to thrive when the country returns to growth? This blog looks at why 2023 needs to be the year of business and technology housekeeping
Learn more

Subscribe to our newsletter

The latest news and industry insights, straight to your inbox