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.