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 settings.py (you may leave 'default' as is for now):

DATABASES = {
    '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 manage.py migrate --database postgres

4. Dump data from mysql:

python manage.py dumpdata --database mysql > myapp_mysql.json

5. Load data into postgresql:

python manage.py 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