SaltyCrane Blog — Notes on JavaScript and web development

Notes on migrating this blog from SQLite to PostgreSQL using Django

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 ./ dumpdata and ./ 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
    DATABASE_ENGINE = 'postgresql_psycopg2'
    DATABASE_NAME = 'saltycrane_db'
    DATABASE_USER = 'django_user'
    DATABASE_PASSWORD = my_password'
  • Create database tables
    python syncdb

Migrate data

  • Create JSON dumps from the existing SQLite database for my 3 Django apps (myblogapp, comments, and tagging):
    cd /srv/SaltyCrane/iwiwdsmi
    ./ dumpdata myblogapp > dump_myblogapp.json
    ./ dumpdata comments > dump_comments.json
    ./ dumpdata tagging > dump_tagging.json
  • Load the JSON data dumps to the new Postgres database:
    cd /srv/SaltyCraneNew/iwiwdsmi
    ./ loaddata ../../SaltyCrane/iwiwdsmi/dump_tagging.json
    ./ 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')

def main():
    pydata = open_json_file(JSON_FILENAME)

def delete_all_comments():
    """ Clears the database of all comments.
    comments = Comment.objects.all()
    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"
    print "Filler comments created."

def delete_dummy_comments():
    comments = Comment.objects.filter(is_public=False)
    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:
        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']

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

if __name__ == '__main__':


#1 Thomas E. Glasgow commented on :


Any reason why you're migrating away from sqlite? Did you hit any performance wall?