Will & Skill Developers

Will & Skill Developers


Thoughts, snippets and ideas from the team at Will & Skill AB, Stockholm.

Erik Svedin
Author

Share


Migrating from MySQL to PostgreSQL

Erik SvedinErik Svedin

1. Create database user & database:

$ sudo -i -u postgres
$ psql
$ CREATE USER <DATABASE_USER> WITH PASSWORD '<DATABASE_PASSWORD>';
$ CREATE DATABASE <DATABASE_NAME>;
$ GRANT ALL PRIVILEGES ON DATABASE <DATABASE_NAME> to <DATABASE_USER>;

2. Create interim database settings in settings.py

NOTE: Make sure that You test this in a staging environment before You actually do this in production!

DATABASES = {  
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': '<DATABASE_NAME>',
        'USER': '<DATABASE_USER>',
        'PASSWORD': '<DATABASE_PASSWORD>'
    },
    'postgresql': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': '<DATABASE_NAME>',
        'USER': '<DATABASE_USER>',
        'PASSWORD': '<DATABASE_PASSWORD>',
        'HOST': 'localhost',
        'PORT': '',
    }
}

3. Make sure psycopg2 is installed and run initial migration:

python touchbase/manage.py syncdb --no-initial-data --database=postgresql

Note: If error 'no relation djangosite' shows up, try commenting out all external Apps from INSTALLEDAPPS and run command.

4. Dump MYSQL data to json file:

python manage.py dumpdata --all --natural --indent=4 > mysql_data.json

5. Load data to postgres database instance:

python manage.py loaddata mysql_data.json --database=postgresql

Note: If django complains about duplicate key value violations you might have to manually
enter postgres shell and truncate table 'djangocontenttype' using cascade option.

$ python touchbase/manage.py dbshell --database=postgresql
$ `TRUNCATE django_content_type CASCADE`

6. Remove MySQL as your default database in settings.py and switch over to Postgres and you're good to go!

Erik Svedin
Author

Erik Svedin

Comments