Move a Django site to PostgreSQL: check

When I moved over from Dreamhost to Slicehost, one of the (minor) motivators was the option to use something different than MySQL. Don’t get me wrong, though: MySQL is a nice system, fast, relatively memory-efficient and so on. But what drove and still drives me nuts is that not every storage engine within MySQL supports transactions. MyISAM can be as fast as it gets, if it lacks transaction support, I won’t use it for at least some of my tables. And having to manually check that every time I write a new app really started to annoy me. But how to move a site like this, that is using Django, from one database system to another (or to be specific, PostgreSQL in this scenario) as simply as possible?

Note that this is just a rough description of the process I used. The principle should work in general, but you might face some additional difficulties that I didn’t notice or haven’t noticed yet. And don’t forget to make a backup of your old data before doing anything described here.

The whole process is remarkably simple once you know what you have to do. Django helps a lot in this regard thanks to its fixture-system, that is luckily DBMS oblivious. Basically the whole move works like this:

Dump the database into a fixture using python manage.py dumpdata --indent=4 > dump.js

Change your settings module to point to a new PostgreSQL database (which you should create before the next step)

Run python manage.py syncdb to create the tables within your PostgreSQL database

For step 5 I wrote a small script that goes through each sequence in the database (since this database is used for one Django site and one site only this works :P ) and resets its value to the highest id of the associated table:

Another problem that has to be solved before you do step 5 is that dumpdata has a small problem with BooleanFields in the models. If you’re using any of those (you or any contrib module you’re using), you have to do some cleaning up in the JSON dump. The problem is that the values of these BooleanFields are dumped as 0 or 1 instead of false or true, which confuses loaddata to no end. To make it easy for any processing script, I told dumpdata to nicely format the dump, which you can do with the --indent option, which comes in handy when trying to fix this small problem.

So I wrote a little Perl script (don’t hurt me, please) that just goes over the dump and corrects it: