@portfast

News & views, serious business.

Thursday 31 December 2015 - MySQL to PostgreSQL migration

From day one, from the first lines of code back in 2005, we have used a MySQL database as the back end for pretty much everything. It has served us well, not lost any data and mostly done what we have told it to. It remains the world's most popular database engine due to its shallow learning curve and extensive documentation.

As our requirements have shifted, for some time now, I've been eyeing up PostgreSQL as an alternative.

Postgres supports some more useful data types, like 'inet' as a great example, and 'json' as a native container for a blob of JSON data which you can build a query on, so we took the time to build a migration plan.

Interesting things you might run in to during such a migration will include:

user is a reserved word in Postgres but not in MySQL. We have had to rename a lot of variables. We thought about just translating it in the database abstraction layer, but this has the potential to lead to confusion later on so we've taken the 'rip the plaster off' approach.
MySQL's validation on incoming data is not as strict as it should be. Other than it not having specific data types that will validate, for example JSON or IP address data, it will also do things like accept '', i.e not null data but a 0 length string, into an enum data type where this is not defined as an element of the enum. We had to fix up a fair bit of data, particularly from the early days, before it would import.
You will spend a long time crafting the perfect data migration script, just to end up running it once and then archiving it under at least a metre of concrete so that you never accidentally run it again and toast your production database.
Any unusual SQL statements that you have used probably involve custom MySQL syntax somewhere along the line, you'll find these need fixing. If you are combining order by and group by and it doesn't work, then max() might be your friend.
Postgres does not let you send a floating point number as an update to an integer column.
Postgres treats " and ' differently.

The unit tests have all passed but the two databases do behave slightly differently, so if you find anything that's not working as expected then open a ticket and we'll fix it.