Migrating and normalizing data

We are rewriting a large Django application which uses a MySQL database, and the database schema needs to undergo major modifications. We're also porting the app to use PostgreSQL, for a couple of good reasons.

I've written a small script that allows me to dump the old MySQL tables into a PostgreSQL database, prepending their name with oldapp_*

We are starting to write some data migration scripts which are basically:

insert into foobar(c1, c2, ..., cn)
select c1, c2, ..., cn from oldapp_foobar where ...


Now, this app uses surrogate keys extensively (Django doesn't allow composite primary keys and encourages the use of surrogates heavily). For the first migration scripts I've tentatively skipped the surrogate keys from the insert/select, and thus PostgreSQL generates new ids automatically.

This works, of course, but when migrating foreign keys this is not a good idea. I'm pondering several approaches, but I'd appreciate some input here.

-
I'd just keep the old surrogate keys unless you're gonna re-write django. –  Scott Marlowe May 31 '12 at 11:47
Oh, I do use surrogates in the new app. I thought I'd use new ones. But that's probably not very useful... –  alex May 31 '12 at 14:10

But I would save the old surrogate key per table in an additional row so you can connect to the old data reliably. When the migration is done and you finally let go of your oldapp_* tables for good, you can drop the additional columns.