Development & production parity for WP DB

Hi all,

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:

Transferring plugin settings and page builder customizations to staging and production environments

The answer of alwaysblank was this:

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?

Hey Disc,

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):

  1. 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 :slight_smile:

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 :pray:t2: 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.

Cheers

1 Like

Hey @gracemonahan,
many thanks for your answer!

I’m very excited to hear other developers’ answers too and hope that there is some good solution to this problem.

Regarding your approach, I’m curious to know what happens e.g. with the different urls, if you’re just doing a simple import/export of the db.

Related:

1 Like

I was referring to these extensions before:

  1. trellis-database-uploads-migration
  2. trellis-db-push-and-pull
  3. trellis-simple-sync

Has anyone experience with these extensions? Do they solve the problem?

Hey strarsis,
many thanks for your answer!

This looks interesting! I’m willing to pay, if it gets the job done.

Has anyone experience with this script?

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).

1 Like

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:

  1. Application
  2. 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).

5 Likes

@alwaysblank many thanks for your answer!

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.

1 Like

@orionrush many thanks for your answer!

in short its still an open question without a simple solution.

I’m going to try some of the tools I found so far and keep this in mind.
These are the tools I mean:

Plugins

  • WP Migrate DB Pro

Scripts

  • creame/trellis-simple-sync
  • hamedb89 / trellis-db-push-and-pull
  • jasperf/trellis-sync
  • Sync Script
  • valentinocossar/trellis-database-uploads-migration
1 Like

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:

This topic was automatically closed after 42 days. New replies are no longer allowed.