Posted by JSCSJSCS on December 21, 2012 at 6:38pm
I'm new to development and have come to a stopping point. When developing a new site and using GIT to maintain the revision control, I also put the site database into version control. When working on a site that is ready for beta testing, there will be new users and user data coming into the live database between development changes that are not in the changed development DB.
How do you get the development (DB) changes into the live site without losing all the new user data that may have been created on the live DB?
Comments
DB moves down, code moves up
Drupal best practice is that the database moves down from live, and code moves up from dev. It's what we do at Sage Tree every day. Surprisingly, you don't have to tweak very much after you move the db down from live, even after some major code updates. So don't be afraid of his step. The only real hardship is when you have let the live database diverge from dev for months.
I am not aware of anyone who keeps the database under version control. Instead I suggest you use the Backup / Migrate module to maintain scheduled backups. This module avoids the cache tables and thus makes migrating database snapshots easier. Just import the snapshot, run update.php, and clear caches for good measure. You could even create database snapshots of only the tables which contain content and users, although the convoluted nature of Drupal's tables makes this a bit dicey for the uninitiated and even for the semi-experienced folks. However, with backups n place you've got nothing to loose.
D8 will makes this all very much simpler, but that's a discussion for another day.
HTH,
Christoph
--
Christoph Weber
I need an internship
I see what you are saying Christoph, especially when it comes to code changes, but just can't get my mind around a procedure that does not require some extraordinary effort when it comes to DB changes. For example, I attended a CommerceGuys Commerce Kickstart 2.0 webinar yesterday and all I could think about was how could a website add products and pricing, set rules for specials, etc. on a development site all the while other people were creating user accounts and making purchases on the live site and expect the database to not get out of sync? Do they have to bring down their live site everyday for some period to quickly make changes and add products, then fetch the changes on the live site and bring it back online?
We aren't really talking code, but DB changes in this case. When everything is in a single database, I'm afraid I don't have sufficient knowledge to see my way past the inevitable requirement to somehow "merge" dev and live DB versions somehow after adding products (as in this example) or importing new business locations, or doing other DB (non-code) updates. I am just using "products" as an example, but my knowledge gap is about what to do when the changes are primarily to the DB, not the code, modules, etc.
James Sinkiewicz
Drupal Site Builder and Generalist
http://MyDrupalJourney.com
There's more options
None of them simple, unfortunately. As Christoph mentioned, this is a major pain point in Drupal, and there won't be any significant improvement until Drupal 8.
But, there are other ways to handle this problem - Christoph's solution is the most common, but you've pointed out some limitations. Other solutions:
For updating contents, consider publication workflows...
When you're talking about updating content like products or articles, consider setting up an editorial workflow for your staff.
You might have someone create the nodes that need to get published, but the person creating the nodes don't have a role with publication privileges. Then there's an editor who has permission to publish once the content is checked.
There are many modules and methods out there to accomplish this. Just google Drupal + workflows.
You can have an executive editor function who takes approved articles and then places them on the page.
This builds redundancy into the publication process so it's likely that someone will catch erroris in speling or pri¢ing.
:-)
Founder
http://www.larks.la
Robot Coordinator
http://droplabs.net
Organizer, Dragonslayer
http://drupal.la &n
The Easy Approach
The simple solution to this (for a small site) is to do all your work on Production. You can create new unpublished versions of your content and then once you're ready to publish it, turn the content on.
You can use revisions (or drafts using workbench) to go between many versions of your content.
For larger sites, there are some modules like Views Bulk Operations that will allow some easier ways to bulk publish content, but it's not required.
If you want to get into larger updates, consider looking into Features and Strongarm. Features will allow you to bundle content types / views / settings (for some things) and enable / disable them almost instantaneously. Strongarm lets you do modifications to DB settings.
If you don't use Drush, look into that. If you are familiar with command line Git, it will be simple to use. It is a command line tool to administer Drupal and it works very well with features and update.
TLDR:
Content should be edited in production. Dev / Staging / Test should be where feature / functionality development is housed. Many modules allow for export / import of this functionality and features is a great one.
DB is pulled to Dev / Stage / Test for testing / development but not content entry.
Content should be edited on production in a draft / unpublished form.
Code should be tested / developed on Dev and pushed to production.
Caveat:
As Karl mentioned, some DB things don't make the move when you develop on Dev and push to Prod. These are usually minor (hence the paper tracking method ;)), but you can often write scripts to execute Drush if you need a quicker method.
Content changes not really the issue per se
I appreciate the suggestions given above. They do not represent my specific use case unfortunately.
In the example I gave in my second post, the DB may require that new pricing and products be imported on a daily basis, not manually changes one-by-one through user input. This import requires several Rule calls to clean and compare the incoming data with existing data. This can take from a few minutes to several hours in some cases, depending on the size of the DB delta.
I would think that such an event is best done in the development environment, then merged somehow to the live, keeping in mind that during this "update" process, other orders and user events have occurred in the database.
This is just one example.
James Sinkiewicz
Drupal Site Builder and Generalist
http://MyDrupalJourney.com
Sorry for the confusion
I think in this scenario it would be best to either figure out if your changes are only modifying certain DB tables (that are not modified by users) and merge those in from DEV using backup / migrate or DB admin tools like PHP My Admin, etc. Another solution is to use Feeds and Feeds Import to have these changes automatically imported when CRON runs. You may be able to set up a feed in chunks if the whole process takes too long, but that process could be automated by serving a JSON / XML feed or flat file.
There are other import / export modules, but we've had limited success with them.
If you are building a site with tons of data that needs to be synched, it may be worth investigating if there are tools around your platform that handle that issue. I use eCC and Ubercart (Drupal 6) for one eCommerce site and I believe there is a way to sync TO Drupal from our Quickbooks records including synching quantity and prices. We haven't used this since our product listing is quite small at this point.
It feels like you want to go down the merge path, but I feel like that is not a Drupal method for dealing with the issue. There is no right or wrong, but Feeds is a popular module and designed to achieve a mass update role.
Production DB Issues
A couple of questions and possible solutions:
1) You mentioned early on that you put the site DB into revision control. Does that mean the structure of the DB and tables or also the content? If the site is dynamic, putting the content into revision control is going to make things a mess. If you need that level of backups, use the DB logging and rollback features.
2) When you are importing products you don't really want to put those into the development environment. Those are not development database changes, they will be changes to production. If the products are new, you can use a flag to indicate if a product is active or not. Imported products are inactive until all of the rule processing and other cleanup is complete.
If the products are replacement/updates of existing items, you need to get those processed as quick as possible. You might consider making those items inactive until processing completes OR use a second production database that holds importing products until processing is complete. Then use a script to push those items to public production.