3

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.

2
  • I'd just keep the old surrogate keys unless you're gonna re-write django. May 31, 2012 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, 2012 at 14:10

2 Answers 2

3

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.

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.