Performing ongoing development is tricky when you also need to maintain a stable site for content authors or the general public. I wrote a small script that makes it easier by copying configuration changes from your test/development database to your production site, without overwriting content, comments, and user accounts.
The effect is that you can continuously improve your development site, and when you're ready, run the migration script. Production downtime might be only a minute or so, depending on the size of your database, and all content on the production server will be preserved.
As a side effect, it also makes database backups more efficient by backing up just the important tables (no bulky cache tables or logs) and saving them in a diff-friendly format that works well with a revision-control system.
Thanks to Noosphere Networks, I'm releasing it as open source. Please heed the alpha-software warning, and see the web page at:
http://shearersoftware.com/software/server-administration/migraine/
I've done many test-to-prod migrations with this script, and it's been reliable. But be warned that this is an alpha release of a command-line utility which will likely require some configuration help to run the first time, so this release is only intended for those who are familiar with the command line and MySQL tables. There are certain operations that it can't duplicate (mainly CCK changes), but it will at least warn you of the problem, giving you a chance to manually reapply those changes.
I know of one project that includes similar goals, AutoPilot, which is more ambitious. However, it doesn't yet support the main function of this script--migrations from test to prod without overwriting content on prod--and I needed a solution to that now. So the two projects are complementary.
Feedback and improvements are welcome.
Comments
Changes to content types
Sounds good! I will definitely check it out.
How have you addressed the problem of changing content types on the development server? For example: say you add a CCK field or two to a content type in dev and add a block that uses the new fields, and then migrate the lot over to prod, what happens to the existing nodes using that content type? Default values? Something else?
Changes to content types
In general, this is a complex problem. One particularly surprising situation in CCK occurs when you add a field to content table A that also happens to exist beforehand in content table B. In that case, the field's column in table B is automatically deleted, and its data is moved into a new table C, which will hold just that column for both tables B and A. Unwinding that process after the fact to figure out what happened, then re-creating it, would take a lot of coding and testing to instill confidence that it would produce exactly the same results.
So the script currently doesn't attempt to solve it. Instead, it compares the content schemas of test and prod. If they match, then prod content is compatible with the new configuration and migration can proceed. If not, Migraine prints a diff of the content schemas to show what changed. SQL wizards could use it to update the schema themselves on prod (sometimes even cut & pasting CREATE TABLE statements is enough). Or, more simply, the developer can re-do the same CCK changes on prod, using test as a reference. The script will recognize when the sites match. The same CCK code in Drupal would be responsible for making the changes both times, which is one fewer thing to go wrong.
It's not ideal and I wish I had a better way around the problem. But in at least one site, CCK field changes were a small part of the total development, and re-doing just those changes when prompted was a small price to pay considering the alternatives (such as having to re-do all changes).