Moving a django site from MySQL to PostgreSQL

by Andre Engelbrecht

database migration

I've recently worked on a project where the client initially insisted on using MySQL for their website. I didn't have any reasons at the time to protest this choice since MySQL is ok for majority of sites and so I used MySQL as per their request.

With the latest updates to their website, we have however run into some MySQL limitations, in particular the default storage engine that MySQL uses.

We added an application called South to django, which manages database migrations as the models are updated etc. South works brilliantly on a database that supports transactions, and unfortunately the default storage engine in MySQL does not have transaction support.

We decided it's best to move to PostgreSQL to solve this issue, and prevent any future obstacles in this regard.

Moving from MySQL to PostgreSQL

Initially I thought this would be quite a complicated process, but luckily django provides 2 management commands, dumpdata and loaddata, which helped a lot with the migration process.

I also found some good information on zerok's blog along with the guys on django's irc channel, which gave me a bit of a head start. In the end I used a combination of methods customised to my specific use case.

First I used dumpdata to dump the current production mysql database. I then updated all boolean fields in the dump, changing 1's and 0's to true and false.

With the dump ready, I created a empty postgresql database on my development machine and synced the database. This created the initial tables in the new database along with some default data that django adds automatically. I had to clear these tables completely so that we don't get duplicate keys and objects in the database.

The rest was very simple. I used loaddata to get the data into the new database, and then proceeded with tests to make sure that everything is as it should be.

Satisfied with the results, I did a sqldump of the PostgreSQL database and loaded the sql into an empty postgresql database, on the production server.

Though this may not be the best of solutions for all uses cases, it worked perfectly for us this time around, and I learned a bit in the process.
  

You can use Markdown to format your comment.