Test and production Drupal database merge: how to do?
chugaystyr - June 5, 2009 - 10:44
We have test server and production server. How we can merge or deploy Drupal database from test to production?
- It is not good to copy DB as a whole - in that case will lose content.
- It is not good to copy few tables because some tables need to be altered or updated ({variable}, {language} f.e.)
- It is not good to repeat all actions on production manually (module installation, views config etc) because is very slow and potentially buggy.
How do you solve this problem?
1. Update DB structure. 1)
1. Update DB structure.
1) Copy test server's DB and production's DB to my local server (may use only structure, do not copy data)
2) Use tools like TOAD for MYSQL to compare this two structures. After comparing Toad creates SQL script to update DB structure.
You can select what DB is master and what is slave and tables that should be included in update script.
2. Update DB data
1) Export tables from test's DB to production's DB. Choose only tables contains config data (node_type, variables, system, views_* and etc. depends of modules you use). Import this tables to production server.
I use it on one project. It works. We had 2 successfull deployments from test to production.
So what about f.e. {variable}
So what about f.e. {variable} and {languages} on 2nd step? We still have to update language domains (field: languages.domain) and lang default settings (in {variable} table) manually?
It will be updated by moving
It will be updated by moving full table variable from test db to production.
This does point to a larger
This does point to a larger problem that, AFAIK, has not been solved (although there are some projects addressing it).
The use of the database to store the configuration setting creates issues, as there is no clear distinction between settings tables and data tables. This means that updates to your configuration may become dependant on specific data etc.
I don't think that you are alone in your problem here - I did once consider the idea of a module defining its settings fields through a hook. The system could then invoke the hook and export the returned values to xml config files in the sites directory. This would allow you to add them to version control, diff them, upload them to live/staging etc. The project i heard about (but have forgotten the name of) looks to export db settings as executable php.
Other approaches that people have considered seem to include logging sql queries, using update scripts in modules, and manually repeating database changes.
Checkout http://groups.drupal.org/node/786
Would love to hear from others as to how they deal with this.
It is indeed a larger
It is indeed a larger problem, and one that has not been solved.
Are taxonomy terms content or configurations? Are menu items content or site configurations? If a text block is created and positioned in the footer, is the position a config? Is the text it displays content?
This is _part_ of the reason the DB is so mushed together.
Lots of efforts, all incomplete, solve parts of this. My favorite at the moment is 'patterns.module' for deploying certain configuration sets between sites, although that's not often appropriate for less-than-enterprise-level roll outs, and you really just want to merge your own recent dev changes into a running site!
I usually just go with test, test, get it right, then open up the admin screen on the live site and repeat the actions. 5 minutes of cut & paste is better that 2 hours of backups and squinting at diff logs of SQL!
.dan.