December 2009
November 2009
October 2009
September 2009
June 2009
April 2009
March 2009
February 2009
January 2009
December 2008
November 2008
October 2008
July 2008
June 2008
October 2007
September 2007
Want to have fun? Try migrating an existing web application between different database technologies! With Django and SQLAlchemy, it actually isn't that hard! I used the following procedure to migrate both deathcat.org and this blog to Postgres. I'm assuming your know you to use Postgres and you are doing this as a Postgres superuser. All of this assumes ident authentication for Postgres, but should be easily tweaked for other configurations.
Make a directory in your Django application to store these scripts, like scripts/. Make sure this directory resides at the same level as manage.py. Now, get the code for my SQLAlchemy table copier and put it in a new file called puller.py. Comment out the line that reads table.metadata.create_all(dengine).
Now put this in a file called migrate2pg.sh:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | #!/bin/bash database=my_django_db mysql_user=django_user mysql_pass=django_passwd mysql_connection_string="mysql://$mysql_user:$mysql_pass@localhost/$database?charset=utf8" postgres_connection_string="postgres:///$database" tables=$(echo 'show tables' | mysql -u $mysql_user -p"$mysql_pass" $database | xargs echo | cut -d ' ' -f 2-) echo $tables echo "Dropping old postgres database, if any" dropdb $database echo "Creating new database" createdb $database echo "Setting up Django schema" ../manage.py syncdb --noinput echo "Removing initial data" echo 'DELETE FROM auth_permission' | psql $database echo 'DELETE FROM django_content_type' | psql $database echo "Importing data from MySQL" python puller.py \ -f $mysql_connection_string \ -t $postgres_connection_string \ $tables echo "Fixing sequences" for table in $tables do echo Fixing "${table}'s sequence" echo "select setval('${table}_id_seq', max(id)) from ${table};" | psql $database done |
Tweak the variables at the top as necessary for your case. Run a bash migrate2pg.sh and read the messages. One error you will see is a during the Fixing sequences phase when the script attempts to fix django_session_id_seq sequence. Ignore this error.
The final part is to give permissions or ownership to the user who will be accessing the data. I'm assuming you can do this, but if you are using Postgres ident authentication and apache, then here's a helpful script for you.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | #!/bin/bash database='my_django_db' echo "Granting apache rights to ${database}" echo "GRANT ALL ON DATABASE ${database} TO apache;" | psql $database tables=$(echo '\dt' | psql $database | awk -F '|' '/table/ {print $2}') sequences=$(echo '\ds' | psql $database | awk -F '|' '/sequence/ {print $2}') echo "Tables:" $tables echo echo "Sequences:" $sequences echo tablesql=$(for table in $tables; do echo "ALTER TABLE $table OWNER TO apache;"; done) seqsql=$(for seq in $sequences; do echo "ALTER TABLE $seq OWNER TO apache;"; done) echo "Table Alteration SQL:" $tablesql echo echo "Sequence Alteration SQL:" $seqsql echo echo $tablesql $seqsql | psql $database |