Staging sites, how do you manage synchronising updates in the DB?

Question

It is broadly accepted that developers should test updates through a staging site before releasing them to the live server, however once the development updates require modifications in Wordpress DB, things get complicated, as users in the live site will update the DB too.

The only (muddled) flow I can imagine is the following:

1. Test on a local server (WAMP, XAMP, etc)
2. Once ready to deploy, put the live site in maintenance mode
3. Backup live site (Duplicator, sqldump, etc)
4. Create a clone of locked live site to the staging site
5. Upload modifications from local environment to the staging site
6. Test the staging site
7. Push the staging site to live.
8. Remove maintenance mode

Drawbacks of the flow above:

• downtimes may be longer than expected for users while the developer is carefully testing updates in the staging site;
• may require manual management of modifications: for instance, siteorigin pagebuilder layouts are stored in the db, so once a layout is modified, it must be imported manually in the staging site; in this case it could be adequate to simply drop & import pages into the staging site, and if working, importing them in the live site

I wonder if is there a better and more automated way to achieve this.

What do you think?

EDIT, as requested, some solutions have been proposed in the past but none offers a definitive solution:

• 9/2010 - Database synchronization between dev/staging and production
• 12/2011 - Deploying Updated or New Plugins That Modify the wp_options Table
• 9/2014 - How to upload local changes to a live server without overriding new posts/pages?
• 1/2015 - How to maintain wordpress site blogs in production and staging?

Answer 1

Newer hosting providers that cater specifically to WordPress usually have tools in place to ease this pain. I put my clients on Pantheon which has this neat Git-enabled workflow, where code only moves up (from dev to staging to production) and DB stuff only moves down (vice versa from the code). Copying a database from production to staging is one click with their interface. Provided this workflow is respected, this pretty much eliminates the issue of ever messing up the production database, enabling me to always test my changes on a fresh clone of production DB data in either staging on development.

One doesn't have to use Pantheon - you can adopt a similar approach in your process using your own tools (Git + a DB cloning plugin like WP Migrate DB). I just find this way works well for me.

Question: why would you put your production site in maintenance mode while testing staging? There shouldn't be a need for that in a majority of cases. The only case I can think of is having some sort of very brittle system highly sensitive to additional user data being fed into it, with a catastrophic bug to boot - but that would likely be indicative of a different, larger, problem where one would need to rethink their product's entire architecture.

Answer 2

Have a look at VersionPress which brings GIT versioning to the whole process (files and database)

As described on their site:

VersionPress provides painless staging. This means that you can easily create a safe testing environment for your changes and only merge them back when they are ready. Merge is the key word here – VersionPress handles situations where your live site had new content in the meantime seamlessly.