Sunday, March 1, 2015

Migrating Django database from one vendor to another

Overview
Database migration involves
  • Recreating database schema (Database objects like tables, constraints, triggers, ...)
  • Copying of data

 All code in this article are tested on Django 1.7.
Recreating database schema
Database objects for Django can be easily recreated in new database by using the migrate command targeting the new db, 
python manage.py migrate
view raw django_migrate hosted with ❤ by GitHub

In case the migrate command does not work due to migrations generated using makemigrations command, we can attempt to delete all migrations folders and re-run the migrate command. Please remember to backup the source code before deleting any migrations folder (This will not be a concern if a proper source control has been setup).

Copying of data
There are at least three ways to copy data for Django across database of different vendor
  1. Use dumpdata and loaddata command
  2. Use custom Django script
  3. Use an external tool
1. Use dumpdata and loaddata command
This approach is provided by Django through the dumpdata and loaddata command. However, it might not be efficient when dealing with large database.
# run agianst the source db (where the data to be migrated is stored)
python manage.py dumpdata > db.json
# run against the target db (where the data will be saved)
pyhton manage.py loaddata db.json
view raw django_dumpdata_loadata hosted with ❤ by GitHub

2. Use custom Django script
This solution originates from http://macrotoma.blogspot.com/2012/10/solved-move-django-from-sqlite-to-mysql.html
The original idea is to use ContentType model in Django to retrieve all models and copy the data to another db using the multiple databases feature in Django (https://docs.djangoproject.com/en/1.7/topics/db/multi-db/).
With slight modification as following, we are able to copy data to another database with a custom Django script
  1. Use apps.get_app_configs() to retrieve all models instead of using ContentType.
  2. Sort models using sort_dependencies in dumpdata command
  3. Add method to copy tables in ManyToManyFields (The value for primary key might be different, refer to the source code for explanation)
Custom Django script (move_data.py)
# The following code is tested on Django 1.7
import django
import sys
import logging
from django.apps import apps
from django.db.migrations.recorder import MigrationRecorder
if django.get_version() > '1.7':
from django.core.serializers import sort_dependencies
else:
from django.core.management.commands.dumpdata import sort_dependencies
LOG_FORMAT = '%(asctime)s|%(levelname)s|%(message)s'
logging.basicConfig(stream=sys.stdout, level=logging.INFO, format=LOG_FORMAT)
logger = logging.getLogger(__name__)
def copy(model):
table_name = model._meta.db_table
logger.info('Start import %s into new_db' % table_name)
table_objects = model.objects.all()
for t in table_objects:
t.save(using='new_db')
logger.info('Successfuly imported %s into new_db' % table_name)
'''
Please note that the id for many to many objects might be diffrent from source table.
This is because id is not specified in the add method.
'''
def copy_m2m(model, m2m_field):
table_name = m2m_field.rel.through._meta.db_table
field_name = m2m_field.name
to_model = m2m_field.rel.to
pk_name = model._meta.pk.name
child_pk_name = to_model._meta.pk.name
logger.info('Start import %s into new_db' % table_name)
for o in model.objects.all():
pk_value = getattr(o, pk_name)
new_o = model.objects.using('new_db').get(**{pk_name: pk_value})
for child in getattr(o, field_name).all():
child_pk_value = getattr(child, child_pk_name)
new_child = to_model.objects.using('new_db').get(**{child_pk_name: child_pk_value})
getattr(new_o, field_name).add(new_child)
logger.info('Successfuly imported %s into new_db' % table_name)
def run():
logger.info('Start move data into new_db')
app_configs = apps.get_app_configs()
app_list = [(a, None) for a in app_configs]
models = sort_dependencies(app_list)
models_with_m2m = []
for m in models:
copy(m)
if m._meta.many_to_many:
models_with_m2m.append(m)
# Migration model needs to copy separately as it is not in the INSTALLED_APPS
copy(MigrationRecorder.Migration)
# re-create object in ManyToManyField
for m in models_with_m2m:
for field in m._meta.many_to_many:
# data for field that has specific through model should have been copied in earlier steps
if field.rel.through._meta.auto_created:
copy_m2m(m, field)
logger.info('Successfully moved data into new_db')
view raw django_move_data hosted with ❤ by GitHub

To use the script make sure that
  1. The default database connection in the Django settings is pointing to the source database (where the data to be migrated is stored).
  2. A new connection named new_db is pointing to the target database (where the data will be saved).
Example:
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2', # Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'.
'NAME': 'prototype', # Database name
'USER': '<username>', # Username
'PASSWORD': '<password>',
'HOST': '', # Empty for localhost through domain sockets or '127.0.0.1' for localhost through TCP.
'PORT': '', # Set to empty string for default.
},
'new_db': {
'ENGINE': 'django.db.backends.oracle', # Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'.
'NAME': 'xe',
'USER': '<username>', # Username
'PASSWORD': '<password>',
'HOST': '', # Empty for localhost through domain sockets or '127.0.0.1' for localhost through TCP.
'PORT': '1521', # Set to empty string for default.
}
}
view raw django_move_data_db_settings hosted with ❤ by GitHub


3. Use an external tool
The hard way ...
Most database comes with a client application that allows data to be dump in the form of INSERT statement. We can dump all data from source database in the form of INSERT statement and execute it against target database. 
Modification to the generated INSERT statements might be needed.
For example, the INSERT statement generated for django_admin_log table from Postgresql contains action_time '2014-11-05 07:23:07.826945+00', in order to execute this statement against Oracle db, we might need to change it to TO_TIMESTAMP_TZ('2014-11-05 07:23:07.826945+00',  'YYYY-MM-DD HH24.MI:SS.FF TZH:TZM')
Example:
-- INSERT statement generated from Postgresql
INSERT INTO django_admin_log (id, action_time, object_id, object_repr, action_flag, change_message, content_type_id, user_id) VALUES (1, '2014-11-05 07:23:07.826945+00', '1', 'User object', 2, 'update through xxx', 10, 1);
-- Modified INSERT statement to be executed against Oracle db
INSERT INTO django_admin_log (id, action_time, object_id, object_repr, action_flag, change_message, content_type_id, user_id) VALUES (1, TO_TIMESTAMP_TZ('2014-11-05 07:23:07.826945+00', 'YYYY-MM-DD HH24.MI:SS.FF TZH:TZM'), '1', 'User object', 2, 'update through xxx', 10, 1);
view raw insert_postgresql_oracle hosted with ❤ by GitHub

With more advanced client ...
Some database vendors provide client application with feature to copy data from different sources. For example:
  1. SQL Server Import Export Wizard
  2. Oracle SQL Developer (the Migration feature)
Anyway, some form of data massaging similar to previously mentioned might be needed.
If none of the approach works, consider using an ETL (Extract, Transform and Load) tool.

Remarks:
For database vendor like Oracle and Postgresql that uses sequence to support auto-increment number, sequence must be reset to be more than its used value (Auto-increment number is used mostly a surrogate key, thus duplicate value will result in key conflict).

References:
Posted by at
Labels: ,

2 comments:

Subscribe to: Post Comments (Atom)