thoughtherder

migrating a django project from sqlite to mysql / postgresql

Tuesday, February 23, 2010 5:37 p.m.

Should you ever be tasked with migrating a django project running on an sqlite database to postgresql or mysql, the following are some of the steps I took.

The project was already in production, so I needed all the data to come along with it. This caused some problems that django, sadly, wasn't equipped to handle.


./manage.py dumpdata > mydumpfile.json



./manage.py dumpdata --indent=n > mydumpfileindent.json

grep myword mydumpfileindent.json
grep -A 10 -B 10 -n myword mydumpfileindent.json


mysql -u root -p

mysql> drop database dbname;
mysql> create database dbname
    -> character set utf8 collate utf8_general_ci;
mysql> grant all on dbname.* to dbuser@localhost
    -> identified by dbpass;
mysql> exit;

sudo -u postgres dropdb dbname;
sudo -u postgres createdb dbname -E utf8 -O dbuser;



./manage.py syncdb

./manage.py sqlflush | mysql -u dbuser -p dbname

./manage.py sqlflush | sudo -u postgre psql dbname



mysql -u dbuser -p dbname

mysql> alter table tablename modify 
    -> columnname columntype;

mysql> alter table tablename add
    -> columnname columntype after othercolumn;


./manage.py loaddata mydumpfile.json

django-command-extensions


OS X utilities:

SQLite Database Browser

Sequel Pro for MySQL database management.