Saturday, November 19, 2016

Migration from MySQL to Postgresql with Django is easy as 1-2-3

Hi guys,

Short story: I started learning Django few years ago and created my first django website using mysql database. Over time I realized how much more powerful Postgresql is. And yesterday I decided to migrate my website from mysql to posgresql. But the main question - how?

First google search gave me suggestion - dump mysql database, then convert it to posgresql compatible format using some scripts. Check this page if you're interested.

I started doing it, but there were always some errors, even after conversion. For example, this:
ERROR:  column "blabla" is of type boolean but expression is of type integer

After lots of unsuccessful trials, I started thinking how to use the power of django.. And... Bingo! All of a sudden I figured out, that it's possible to use multiple databases in Django. Below you will find steps to migrate data from one database to another. And it is not only for "mysql to postgresql", but for "anything to anything". Of course, this is only valid if you're using Django.

1. Create new postgresql role and database:
sudo -u postgres createuser -S -D -R -P myapp
sudo -u postgres createdb -O myapp myapp -E utf-8

2. Define two databases in your (you may leave 'default' as is for now):
    'default': {
    'mysql': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'myapp',
        'USER': 'myapp',
        'PASSWORD': '123',
    'postgres': { 
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'myapp',
        'USER': 'myapp',
        'PASSWORD': '123',

3. Migrate schema for postgresql:
python migrate --database postgres

4. Dump data from mysql:
python dumpdata --database mysql > myapp_mysql.json

5. Load data into postgresql:
python loaddata --database postgres myapp_mysql.json

That's it. Now you can see that it's easy as 1-2-3, 1-2-3-4-5 :-D

No comments:

Post a Comment