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.

How do you make schema changes to a live database without downtime?

For instance, lets say I have a PostgreSQL database with a table including various user data like email addresses etc, all associated with specific users. If I wanted to move the email addresses to a new dedicated table, I'd have to change the schema and then migrate the email data across to the new table. How could this be done without stopping writes to the original table? Surely while data is written over from the old table to the new one, new data would continue to be written to the old table and be missed, right?

I guess this problem comes up pretty frequently but I can't find any standard solution for dealing with it.

This article deals with the problem but I didn't really understand step 3. He says to write to both tables, then migrate old data from the first table to the new one. How do you make sure you're only migrating old data?

(I use PostgreSQL on Heroku.)

share|improve this question
    
Facebook developed a tool to do this for MySQL. –  Nick Chammas Jan 18 '12 at 23:27
    
K. Scott Allen wrote about a system for managing schema versions here. I created DbUpdater, an open source tool for version-aware schema deployment. More here - http://www.tewari.info/dbupdater –  ash Jan 22 '12 at 21:30
add comment

migrated from stackoverflow.com Jan 18 '12 at 22:48

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 12 down vote accepted

You almost have your answer already:

  1. Create the new structure in parallel
  2. Start writing to both structures
  3. Migrate old data to the new structure
  4. Only write and read new structure
  5. Delete old columns

As for step 3, use something like this (in one transaction):

Insert what is not there yet:

INSERT INTO new_tbl (old_id, data)
SELECT old_id, data
FROM   old_tbl
WHERE  NOT EXISTS (SELECT * FROM new_tbl WHERE new_tbl.old_id = old_tbl.old_id);

Update what has changed in the meantime:

UPDATE new_tbl
SET    data  = old.data
USING  old_tbl
WHERE  new_tbl.old_id = old_tbl.old_id
AND    new_tbl.data IS DISTINCT FROM old_tbl.data;

New data will not be touched, because it is identical in both places.

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.