Migrating Existing Django Instances From MySQL to Postgres

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
Posted by Tyler Lesmann on September 4, 2009 at 16:47
Tagged as: django mysql postgres python sql sqlalchemy
Reddit Digg Slashdot del.icio.us YCombinator DZone Google StumbleUpon Facebook TwitThis Ping.fm email Print
Comments
#1 brandon wrote this 2 years, 1 month ago

this is awesome. i hope it works.

#2 Kevin Doran wrote this 1 year, 4 months ago

Hey Tyler,

I’m with the Remy Corporation, an IT staffing firm based in Denver, CO. Our client here in town is looking for a Python/Django developer for a full time permanent role. They’re an awesome company, and I would definitely like to speak with you about the opportunity.

Can you send me an updated resume and a good time for you to talk tomorrow? I’m more or less available from 9am to 5pm.

Thanks!

Kevin Doran
Account Manager - REMY Corporation
www.linkedin.com/in/doranka
303-539-0453
@doranka

#3 Gileno wrote this 5 months, 3 weeks ago

This error occurred:
(ProgrammingError) ERRO: column "is_staff" is of type boolean but expression is of type integer

Post a comment