Migrating and normalizing data

Question

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.

Answer 1

Just keep the keys. You'll be able for example to locate data in old database much more easily.

But remember to update sequences associated with your keys after migration so they will have a value bigger than the biggest key in database.

Answer 2

I would generate new surrogate keys like you already did. Removes gaps in the key space.

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.