How should I import my legacy MYSQL db into my new Django project which will use Postgres?

Question

I currently have a site which uses a MySQL 4.x schema, the site uses PHP for the backend. I'm currently in Django-land and I like Postgres so far ( meaning I'm a noob to both ).

What would be the easiest way to go about importing the data in my tables ( the dump file for the entire DB is around ~200 MB ) into my new project? I currently don't have anything setup on my new site and I'm about to install postgres on my server.

One thing to note is that currently, the MySQL was incorrectly setup so the data is stored as LATIN1 and I need to convert it to real Cyrillic/UTF-8 using iconv before I do any importing, that was a pain to figure out and this is the next step.

Answer 1

Fixtures is your best bet. Dump data from mysql, then load it into postgres.

Answer 2

It's some work, but possible:

1. Make a PostgreSQL compatible dump:

   mysqldump --compatible=postgresql --databases db1 > dump.sql

2. Create a PostgreSQL database using UTF8

3. Tell the database you are about to import latin1 content:

   SET CLIENT_ENCODING TO 'LATIN1';

4. Import the dump and do some tests.

Good luck!

Answer 3

I'm going to throw out something crazy. Run your syncdb to create your tables in the Postgres db. Then use the Storm ORM (storm.canonical.com) and it's multiple db support to open in one database and save it to another. I'd be really intrigued to hear if that worked.

NOTE: You may be able to do this with the multiple db support in Django 1.2. Probably possible in SQLAlchemy as well.