Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
add comment

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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