How to deal with database synchronization

Hey, all. I’m new to the Wordpress world, but I’m working to help set up a multi-person enterprise-level site for a volunteer org I’m working with.

I have Trellis and Bedrock set up great. So I can fully push the site to a new environment.

But there is still the problem of the database. Not only do people build content in the database, but many plugins and themes also create tables and modify data in the database.

What I’m running into is I’m not seeing any reliable way to synchronize database changes.

The scenario that is coming up is that someone is working on a new theme, making changes to CSS, PHP and also the database. Or maybe they’re installing and configuring a new plugin.

Although regular content changes can be done on the live site, I can’t see how these kinds of changes can be safely done on the live site. But if they’re done on another test site, how do the changes get migrated to the live site without having to merge the database changes?

I hope I was clear in my question. I do think I’m missing something essential in how Wordpress development works.


They do not. This is, to some extent, a perennial unsolved (and potentially unsolvable in a generalized implementation) problem in a WordPress context. Because of the way that WordPress generates IDs for content (sequential integers) there is no good way to automate merging content. Usually the solution I’ve had for this is to have an environment (usually production) that is the source of truth. Data is pulled from this environment for testing, but data is never pushed to it. If there is data you want to develop “in secret” before release on production, it must be manually recreated on production after testing elsewhere, or migrated to production with a specific purpose-built tool, or built on production behind some kind of wall.

For very specific use cases you may be able to build a system that would allow you to merge certain types of content, but because if IDs and DB structure you will find it very, very difficult.

1 Like

Thank you @alwaysblank , very helpful

For faster work I’d recommend Sync Script for WordPress Database and Uploads | Roots

1 Like

I am noticing a lot of articles recommend testing changes to themes, plugin upgrades, etc., in a staging site. And then they say to avoid losing changes on the live site, you need to sync the db from live to staging before then turning around and replacing live with staging.

But don’t plugins and themes store information in the db? So if you sync from live to staging after you’ve made your plugin or theme changes, aren’t the db changes made by the db or plugin going to be lost? Is that generally not a big deal, mostly config changes that you redo? But in the meantime, isn’t the live site in an inconsistent state because a new theme or plugin has gone live without the correct configuration or other database settings?

But if you don’t copy the database from live to staging before pushing staging, then any db changes on live will be lost.

Seems problematic no matter how you slice it :frowning:

This I would personally never do, only once on the initial launch of the production site.

Db changes from plugins are normally only triggered when you change settings and that you can easily recreate on the production server.

In my opinion, you should consider investing your time away from this business. There is no easy or one-size-fits-all way to synch WordPress databases, and the risk is to waste time searching for an unstable and risky approach.
Spero di poter essere smentito! (I hope I can be proved wrong!)

Yes, the production data is authoritative/“sacred” for me.
And even long after the initial sync I still take a second look at the sync command to be sure it will sync down from production to staging/development.

Good backups is the most important part here.
Cheap snapshots/backup feature of the cloud/VM provider, off-site backups to remote, offline backups.