My suggestion is to identify which database tables contain administrative or configuration data vs. which tables contain content and then store them in separate databases (brief research indicates that MySQL supports cross-database queries but Postgres does not, so this functionality would be turned off with Postgres). Ideally, it would then be possible to clone both databases, add modules or change settings in the sandbox until happy, and then copy back only the admin/config database and any filesystem mods. In one step, the production system would be upgraded.
We would have to decide which tables fall into each category and possibly refactor some tables if they span both categories. Perhaps it is not possible to separate them all cleanly in this way, but it seems worth trying.
right and right. it would be outstanding if we could pull this off. Migrating changes to production is a major headache, and this would make it really easy to roll forward and even roll back. i used to work at a company which worked this way. we called relatively static tables 'domain' tables and relatively dynamic 'transactional'. i like your terms of configuration and content better i think. yes, that prefix trick is very cool and requires no changes from core or contrib. but i have a better idea. Lets store this mapping in a new configuration table. Then, modules or the installer can write into this table. we just need to run this pseudocode in settings.php where the $db_prefix stuff is now: // set prefixes for each table as specified by the prefixes table $sql = SELECT prefix, name FROM prefixes; $result = db_query($sql); while ($row = db_fetch_object($result) { $db_prefix[$row->name] = $row->prefix; } from there on, queries will always hit the right DB. as a next step, i sigest identifying which tables go where and resolving splitting up ambigous tables