I'm developing an online store with Magento. The site will be high traffic and will need a two server setup, Testing and Production.
My question is how do online stores, specifically Magento handle this?
The idea I had is:
I know Magento offers a Enterprise option, but this is a script I would like to write myself.
You should not need to take the production site offline.
What I would sugest (and you should be able to script this) is on the production site to run mysqldump -u root -ppassword db_name > db_name.sql to produce a copy of the database. then run rsync (you can get it for both linux and windows) on the files directory to rsync to the test machine any files you want to copy (images?)
After you do any testing on your that you wish to push to production you can do it one of 2 ways. either (and I prefer this method) save any changes you made to the database in a sql file and run that on the production site and at the same time sync back any changed files. or you can use mysql to log all queries to a file, then re-play these on the production site.
Best not to take the production site down if you dont need to.
Actually, if you make changes to the database, you will need to take the site offline (even for just a few seconds) while you switch from the old code to the new code, and run the database migration script.
You might be interested to know that
postgres has transactional DDL : if you wrap your migration script in a transaction and something breaks in the middle, the whole thing is rolled back (including DROP TABLE)
postgres doesn't need to rewrite the table to add a column (MySQL does and it's pretty slow)
if you use MyISAM you are fucked for many reasons, one of them being that doing backups will take your site offline for a few minutes.