Notes on migrating this blog from SQLite to PostgreSQL using Django

Date: 2009-08-07 | Tags: blogproject, django, python, sql | 1 Comment

Here are my notes on migrating this blog from SQLite to PostgreSQL. For the parts of my database that were in proper order, the migration was made very easy using Django's ./manage.py dumpdata and ./manage.py loaddata commands. However, the database tables used for storing the comments on this blog were kind of screwed up because I had previously migrated them from my old Blogger blog. So I had to write another (not so pretty) script for that.

Thanks to this article for showing me how to use Django's dumpdata and loaddata for this migration.

Create a new Postgres database

See my previous notes for creating a Postgres database. I named the new database "saltycrane_db" owned by "django_user" with password "my_password".

Clone and modify Django project

Clone my SaltyCrane project

hg clone SaltyCrane SaltyCraneNew

cp -rp SaltyCrane SaltyCraneNew

Edit settings.py:

DATABASE_ENGINE = 'postgresql_psycopg2'
DATABASE_NAME = 'saltycrane_db'
DATABASE_USER = 'django_user'
DATABASE_PASSWORD = my_password'

Create database tables
```
python manage.py syncdb
```

Migrate data

Create JSON dumps from the existing SQLite database for my 3 Django apps (myblogapp, comments, and tagging):

cd /srv/SaltyCrane/iwiwdsmi
./manage.py dumpdata myblogapp > dump_myblogapp.json
./manage.py dumpdata comments > dump_comments.json
./manage.py dumpdata tagging > dump_tagging.json

Load the JSON data dumps to the new Postgres database:

cd /srv/SaltyCraneNew/iwiwdsmi
./manage.py loaddata ../../SaltyCrane/iwiwdsmi/dump_tagging.json
./manage.py loaddata ../../SaltyCrane/iwiwdsmi/dump_myblogapp.json

However, loading comments didn't work because I had some missing fields so...

Migration script

...I wrote a migration script:

import setup_environment

import simplejson
from pprint import pprint
from django.contrib.comments.models import Comment
from django.contrib.contenttypes.models import ContentType
from iwiwdsmi.myblogapp.models import Post

JSON_FILENAME = "/srv/SaltyCrane/iwiwdsmi/dump_comments.json"
ct = ContentType.objects.get(name='post', app_label='myblogapp')
N_OLD_COMMENTS = 4000

def main():
    delete_all_comments()
    create_dummy_comments()
    pydata = open_json_file(JSON_FILENAME)
    save_items_to_database(pydata)
    delete_dummy_comments()

def delete_all_comments():
    """ Clears the database of all comments.
    """
    comments = Comment.objects.all()
    comments.delete()
    print "All comments deleted."

def create_dummy_comments():
    """ Create a bunch of filler dummy comments
    """
    for i in range(N_OLD_COMMENTS):
        c = Comment()
        c.comment = "Filler comment."
        c.content_type = ct
        c.ip_address = None
        c.is_public = False
        c.is_removed = False
        c.object_pk = 243
        c.site_id = 1
        c.user_email = "[email protected]"
        c.user_name = "Filler"
        c.save()
    print "Filler comments created."

def delete_dummy_comments():
    comments = Comment.objects.filter(is_public=False)
    comments.delete()
    print "Dummy comments deleted."

def open_json_file(filename):
    """ open the json file and return the python data structure
    """
    json_fp = open(filename)
    pydata = simplejson.load(json_fp)
    return pydata

def save_items_to_database(pydata):
    """ Process the python data structure and save to the new database
    """
    for item in pydata:
        pprint(item)
        pk = item['pk']
        item = item['fields']
        if item['is_public'] and not item['is_removed']:
            c = Comment.objects.get(id=pk)
            c.comment = item['comment']
            c.content_type = ct
            c.ip_address = get_ip_address(item['ip_address'])
            c.is_public = item['is_public']
            c.is_removed = item['is_removed']
            c.object_pk = item['object_pk']
            c.site_id = 1
            c.submit_date = item['submit_date']
            c.user_id = item['user']
            c.user_email = item['user_email']
            c.user_name = item['user_name']
            c.user_url = item['user_url']
            c.save()

def get_ip_address(ip):
    """ Handle bad input for IP addresses
    """
    if ip == "" or ip == "unknown":
        return None
    else:
        return ip

if __name__ == '__main__':
    main()