Sentry / Django app migrate from MySQL to PostgreSQL

mysql-to-postgresql.md

What do you need? 🧰

• Sentry
• MySQL server (source)
• PostgreSQL server (destination)
• pgloader (https://github.com/dimitri/pgloader)

I recommend pgloader build with ccl. This way can avoid problems with memory (dimitri/pgloader#327).

• (optional) Docker

Docker is the simplest way to build pgloader with ccl on all systems from ready-to-use Dockerfile.

Migration 🚀

1. You need to create the database and role in PostgreSQL server for migration database from your MySQL server.
2. After that, you need to change the DB section in sentry.conf.py to something like this (with your parameters):

DATABASES = {
    'mysql': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': '',
        'USER': '',
        'PASSWORD': '',
        'HOST': '',
        'PORT': '',
        'AUTOCOMMIT': True,
        'ATOMIC_REQUESTS': False,
    },
    'postgresql': {
        'ENGINE': 'sentry.db.postgres',
        'NAME': '',
        'USER': '',
        'PASSWORD': '',
        'HOST': '',
        'PORT': '',
        'AUTOCOMMIT': True,
        'ATOMIC_REQUESTS': False,
    }
}

DATABASES['default'] = DATABASES['postgresql']

Default database set to postgresql is a significant step at this moment.

3. Now you need to do a few steps to migrate the database scheme from MySQL to PostgreSQL (in Django and Sentry style):

sentry --config /etc/sentry django syncdb

sentry --config /etc/sentry django migrate

Migrate can ask about creating a new user in Sentry. It's ok to answer "no".

4. You need to check the migration result in PostgreSQL. If you have the database with a scheme "public" and in there with tables likes a "django_" and "sentry_", (probably) everything it's ok, and you can go to the next step.
5. Now you need to create the data migration script for pgloader (for example sentry.load) with content like this:

LOAD DATABASE
    FROM mysql://mysql_username:mysql_password@localhost/mysql_dbname
    INTO postgresql://postgresql_username:postgresql_password@localhost/postgresql_dbname
    WITH truncate, data only, disable triggers, preserve index names, include no drop, reset sequences
    ALTER SCHEMA 'mysql_dbname' RENAME TO 'public'
;

6. Now it's time to copy the data from one database (MySQL) to second (PostgreSQL):

pgloader sentry.load

You will get a bunch of warning about type conversions. In 5 to 6 scenario, you probably can ignore them. If you have a big database, it can be a little longer process as always "restore".

7. Now you need to repeat the earlier steps (it is an excellent way to check good database condition):

sentry --config /etc/sentry django syncdb

sentry --config /etc/sentry django migrate

It's a quick step because nothing new added to the database.

8. Sentry sometimes wants to use a MySQL backend as first, and it can cause problems, for example in the upgrade process. If you can, it's an excellent move to avoid such a situation. For this, you must remove MySQL backend from Sentry config:

DATABASES = {
    'default': {
        'ENGINE': 'sentry.db.postgres',
        'NAME': '',
        'USER': '',
        'PASSWORD': '',
        'HOST': '',
        'PORT': '',
        'AUTOCOMMIT': True,
        'ATOMIC_REQUESTS': False,
    }
}

9. Finally, you can start the Sentry and check your job in the live environment :).

ℹ️ It probably works with other Django apps, but I don't test this yet.