How can I keep data in sync during deployment?

Question

I have the occasion to produce Drupal web sites using development, staging, and production environments. Keeping the code in sync between the sites is a simple task using subversion. What is not so simple is propagating changes to the database data (not just the schema) between installations.

The reason for this will be familiar to any Drupal developer. Drupal stores certain configuration settings in the database, specifically related to CCK fields, Views, and other modules that allow things to be set dynamically using the admin interface. Simply syncing the schema isn't enough - essential information is also in the data.

What I'm looking for is a way to sync these database changes so that if one developer makes CCK field changes on the staging server, they can be propagated down to local development environments for more work, and eventually up to the production environment.

Is there a tool that will do this? What is your process for handling single or multiple developers on a project like this?

Answer 1

For basic data sync: I use mysqldump to dump all data to a .sql file on a nightly basis. The script then checks it in to the version control system. This is cronned up in a simple bash script but you could do something similar on almost any platform...

I've just read a little further and I'm not sure if my method will help. I've never had to merge a SQL dump so I can't comment on how effectively it is managed.

While you should be able to push changes out (schema, new data) to the staging/production server, you might have more hassle pulling changes back into dev. As I say - merging may or may not be possible. I just don't know.

Answer 2

Around here we've pretty much relegated CCK to use for prototypes and v.simple node-types. It's not worth the headache of trying to separate the 'configuration' from the 'content' in the database. There's all sorts of ways in which you can try to keep things in sync but, in short, unless it's in a file or gives you an option to export to one, you're going to be hurting. ( As an added bonus, exporting your views to a file is going to be a little faster than pulling it out of the DB every time its used. )

You mention Dev, Staging & Live servers - if you have developers making undocumented changes in Staging, you're screwed. If you have Staging regularly synced with Live & mandate the (common sense) policy that the only changes made to Staging are things that have been worked out in Dev & are being tested before moved to Live, you might have more success.