first of all I have to say this is the most serious issue I’ve ever had with WP until now.
Short background:
I’ve started using Trellis and Bedrock after I watched this video: How To Deploy A Bedrock Site with Trellis | WordPress Workflow
This guy mentioned in his video that by using Trellis you get parity for the development & production environment. I followed his instructions and I could set up 3 environments (development, staging and production) without big issues, theme and plugins sync worked fine. What he didn’t mention though, is how the parity of the WP DB works. I have tried several times to accomplish this and I even opened a similar thread some time ago:
Those things are probably stored in the database. I don’t believe Trellis includes anything for automated database transfer because reconciling WordPress databases automatically is a hairy problem that AFAIK has not been solved. Generally your options are:
Manually copy-and-paste over any changes
Push your entire database, overwriting whatever database exists on the remote server
Manually resolve database differences in the database itself (which sounds like a nightmare)
I use WP DB Migrate Pro , which is a great plugin, but keep in mind that it is overwriting your databases, not combining them.
The 2nd approach is not a option since there are differences among the DBs.
What I’ve tried so far is create a DB dump from development DB and then try to replace things that are obviously different: user data and urls.
This approach apart from being a big pain and time consuming since normal text editors cannot handle big sql files, it didn’t really work. I’m always ending up with corrupted DBs and some things are getting broken.
I haven’t tried any plugins yet such as the WP DB Migrate Pro or some ansible playbooks related to tellis I found, but I would like to ask again:
How do you guys manage to ensure parity for the WP DB among the different environments?
I am very excited to hear other developers’ answers to your question, as I have created my own system which I am not sure is the best one and I am always open to optimizing my workflow.
At first I followed this method (this was before I had users on my wordpress site and was the only one making changes):
ONLY use the wordpress dashboard on my local site so that all changes were kept in one place
When I was ready to deploy:
run yarn build:production from my local site’s theme directory (my theme is built using Sage)
commit all changes from my site’s root directory to my github repository
navigate to my site’s virtual machine by running vagrant ssh , then cd /srv/www/mysitename/current from my local trellis directory
wp db export --allow-root to export the local database to an .sql file in my local site’s root directory
use FileZilla to transfer that .sql file from my local site to my digitialocean droplet’s /srv/www/mysitename/current directory
ssh into my Droplet, navigate to /srv/www/mysitename/current and run wp db import <long-name-of-sql-file>.sql --allow-root
then navigate back to my local trellis directory and run trellis provision staging and then trellis deploy staging
REPEAT for production
This is obviously not a perfect solution because now that I have users actively changing the database in production (my site is an online course), I find myself editing the DB in production, and then performing the above steps in reverse (exporting the production DB, transferring it to my local machine, and then deploying) very, very late at night & praying that none of my students logs on during that time and completes a lesson…
looking forward to some great answers on here and hopefully my guide above will be helpful to someone who just needs a simple solution for a static site.
The roots.io sync script is surely one of the most advanced ones and made by the same core devs that also made the other roots.io projects.
There are also some “lite” transfer scripts, you can find them here, too (like https://github.com/jasperf/trellis-sync).
The best approach I’ve found is to treat one environment as canonical and the rest as transient. i.e., the production environment is the single source of truth: If you need up-to-date data, you pull that database and overwrite your staging or local database. Most tools that handle this (i.e. wp-db-migrate-pro, the roots sync script) will handle things like changing URLs in the DB (although you could also write a script yourself to automate this, potentially making using of WP-CLI’s search-replace, db import, and db export).
I generally think of any WordPress site as being made up of two parts:
Application
Content
“Content” is anything that is created by users, so most obviously anything in the database, or any uploaded assets (i.e. images). “Application” is the code I write that gets committed to my git repository. The development, staging, and live environments reflect the development, staging, and live state of the Application, not the Content. Because of the way that WordPress stores data (most notably the way it uses sequential primary IDs) reconciling data between two sources is very difficult to automate, and extremely time-consuming (and difficult) to do by hand. Trellis and the rest of the Roots stack are only meant to facilitate environment and Application parity–not Content parity.
WordPress provides a lot of tools (i.e. publish vs draft states, private pages, limiting certain users’ ability to publish, etc) to shepherd content through its own stages of production–I’d advise you to use those for managing changing content rather than attempting to build content on a staging server and then somehow move it to a production server. If this is a specific and definite need you have, you might want to look at a platform other than WordPress (Statamic, for instance, can easily track content in your repo, making reconciliation much easier and potentially automate-able at certain scales).
Your workflow sounds good in theory. How do you manage to keep the “Application” in sync?
There are some WP tables that maybe can be synced one-to-one, maybe with replacements of paths and urls, but I noticed that there are tables such as wp_options where data overlap. The last time I replaced the indices in the db dump with NULL and inserted in another environment instead of overwriting the whole table. This didn’t help either.
Besides that, I have the impression that all the existing tools leave the decision to the user which data should be replaced. It’s still not clear to me how this works and how the whole workflow works in general. Taking as example the WP Migrate DB Pro - find-and-replace plugin, I don’t see many details. Yes urls is clear, but apparently this is not enough.
How exactly looks the sync workflow? What data, strings exactly should be replaced, added, removed?
The easy answer is to not expect your local environment and your production databases to be absolutely synced, without freezing the production environment at some point in order to migrate changes and to make as much configuration as possible baked into your code. You’ve not given much detail as to the scale of the site you are managing, so I am guessing that this isn’t an option.
There’s a recent article by Iain Poulson of Delicious Brains (Who make WP Migrate Pro) about this very issue: https://deliciousbrains.com/syncing-wordpress-database-changes-merging/ and in short its still an open question without a simple solution. Iain’s approach is to programmatically run PHP & SQL scripts to make database changes based on features being brought to the site, plugin settings etc. I don’t think his approach would work for data that needed to be reconciled, ie user data that could be updated during the maintenance period. There are some good ideas in the comments for Iain’s article, well worth a read.
And also VersionPress, which supports a Git-like workflow for WordPress data (remotes / push / pull / merge). Unfortunately it is still too unstable for production. But this should be something that a solid CMS should offer.
Edit: Oh no! Apparently the development of VersionPress is halted now: