Syncing WordPress Database Changes Between Environments: How We Handle Merging

Database synchronization is a problem that still gets raised almost every month in the local WordPress communities I’m part of – “if I am making changes to my local site, what happens to any updates made on the live site, if I push my local database back to live?”

In this post, I’ll revisit the problem, look at what options you have, share how we deal with it for our sites, and review the off-the-shelf options that exist.

What is the Problem?

It’s an issue that exists for almost any developer working on a local development copy of a WordPress site that also has a live version running. The developer makes changes locally, configuring theme settings, creating new pages, adding Advanced Custom Fields, tweaking plugin options, etc. Meanwhile, the live version is under constant change. Either by the client, perhaps writing new blog posts, or in the case of eCommerce sites, adding new products, or by customers placing orders. When the developer is ready to push their local changes to the live site, there is a chance they will overwrite any changes on the live site. It’s a problem that affects all database-driven content management systems and is one of the trickiest parts of WordPress site development. As it stands, there is no easy way to reconcile these changes with a nice simple merge.

Ugh… I hate merging database changes between development environment and production. #wordpress #dba #devlife
— Shawn Hooper (he/him) (@ShawnHooper) January 4, 2015

When Workarounds Won’t Work

For straightforward brochure or marketing sites, data merging and conflict resolution aren’t needed. During any development cycles, the data on the live site remains largely unchanged. Copying over any new media files and a partial push of the tables in the MySQL database with changes, using a plugin like WP Migrate, would be sufficient. With more complex sites, the data architecture WordPress uses (and encourages developers to use) makes it extremely difficult to perform selective data migrations.

For example, in eCommerce stores where the only data changing on the live site is the products by the store owners and the orders from customers, a partial data migration should work in theory. Ideally, the data tables that store these pieces of data could be ignored when pushing other data changes made during site development. With WordPress, there’s just one problem – custom post types.

Why Custom Posts Types Complicate the Problem

WordPress has an extremely flexible data structure that allows data objects of different types to be stored in the wp_posts table. The different data objects are known as custom post types and their type is defined by the post_type column in the wp_posts table. This isn’t the most intuitive name for the table that holds records of data for not only posts, but also pages, menus, attachments, revisions, menu items, and any custom data object registered as a custom post type. Think ‘house’ for a real estate site, ‘animal’ for an animal rescue site, or simply ‘doc’ for the documentation part of a product site.

When custom post types landed in WordPress 3.0, there was much rejoicing at a feature which enabled WordPress installations to become more than just blogs. However, I believe that custom post types, and their data model implementation, have become detrimental to the WordPress ecosystem.

On the one hand, this is a beautifully simple and flexible system, with a simple PHP API to register new data objects with just a few lines of code. When you register a custom post type, you get a lot of functionality for free without having to code it yourself; a new menu option, simple CRUD management of the objects, a table interface for listing and bulk actions, and even REST API endpoints.

On the other hand, the ease of registering new types means that sometimes the wrong kind of data objects are being stored in this way. Like my earlier example of ecommerce products and orders – the most critical of data for a store owner is kept in the same table as all the other post types. The live site will continue to get new records added to wp_posts as new orders are created. If any changes are made to posts or pages on the local site, the wp_posts table can no longer be pushed back to live without losing those orders!

Custom post types can also create other issues for a WordPress site. Using them to store every type of data can cause performance problems. WordPress will continually query the wp_posts table when displaying posts, pages, and other post types on the front end of the site, and as that table grows, the slower those queries become. Of course caching can help, but better care should be given to deciding where to store data that could potentially grow to a large size.

Thankfully, plugin shops were aware of these issues and started changing their approach.

WooCommerce announced an official plan for a custom order table in early 2022, which later became known as High-Performance Order Storage (HPOS). It’s now enabled by default on new installations, starting with WooCommerce 8.2.

Easy Digital Downloads completely migrated all their custom post types to custom tables with the release of EDD 3.0.0, replacing the old system that registered a post type of ‘edd_log’ to record every time an item was downloaded. Under the old system, the log record could potentially scale to a huge number of rows that are unnecessary in the wp_posts table.

These improvements help, but they will never fully fix the database merging issue. Ignoring certain tables from a database migration will only get you so far. Most of the time, live sites are changing in such a way that any changes to a development site cannot be automatically merged back in, and that’s where the manual approach is probably still the best.

What Alternatives Do You Have?

This was the realization we came to when we built the now-retired Mergebot. For every plugin or theme that writes to the database, you need to know how it stores its data so you can correctly handle IDs and conflicts. Trying to resolve all these conflicts ends up being very complex. Even with a solution that supports all major plugins and themes, developers would still need to manually configure unsupported plugins/themes, which is often more of a pain than the database deployment problem itself. If you only had to do this once, it wouldn’t be so bad, but every time a plugin or theme is updated, it could change the way it stores data. It’s a huge hassle that never ends.

There are a few ways to mitigate these conflicts.

SQL Scripts

One solution is to record any data changes as SQL scripts on your local development environment. Let’s look at a simple example. Installing WooCommerce requires you to create a few pages, for example, the checkout and my account pages. As soon as these pages have been created, you could export the data from the database to create an SQL file that includes the queries used to create these pages:

INSERT INTO `wp_posts` (`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`) VALUES
(NULL, 1, '2013-02-27 21:03:13', '2013-02-28 01:03:13', '[woocommerce_checkout]', 'Checkout', '', 'publish', 'closed', 'closed', '', 'checkout', '', '', '2013-11-04 09:52:16', '2013-11-04 13:52:16', '', 0, 'http://yourwebsite.com/checkout/', 0, 'page', '', 0),
(NULL, 1, '2013-02-27 21:03:13', '2013-02-28 01:03:13', '[woocommerce_my_account]', 'My Account', '', 'publish', 'closed', 'closed', '', 'my-account', '', '', '2013-11-04 09:52:50', '2013-11-04 13:52:50', '', 0, 'http://yourwebsite.com/my-account/', 0, 'page', '', 0);

Once you’re ready to deploy your changes, import your SQL scripts into the production database, and you’re ready to go.

PHP Scripts

Instead of recording the SQL queries afterward, you could instead write a PHP script that creates the data. Of course, you can include SQL queries in there as well, but you can also do a lot more. Let’s use the scenario above again as an example, but instead, I’ll write a function to deploy the changes.

/*
 * To run the deployment
 * 1. Log in as an administrator
 * 2. Go to http://hellfish.media/?deploy_changes=1
 */
add_action( 'template_redirect', 'deploy_changes' );
function deploy_changes() {

  // If the request is not our deployment
  if ( !isset( $_GET['deploy_changes'] ) ) {
    return;
  }

  // Only allow a logged in admin to run the deployment
  if ( !current_user_can( 'install_themes' ) ) {
    echo 'Not logged in.';
    exit;
  }

  // Run script for 5 mins max
  set_time_limit(60*5);

  $pages = array(
    array(
    'content' => '[woocommerce_checkout]',
    'title'   => 'Checkout'
    ),
    array(
    'content' => '[woocommerce_my_account]',
    'title'   => 'My Account'
    )
  );

  foreach ( $pages as $page ) {
    $new_page = array(
    'post_type'   => 'page',
    'post_title'  => $page['title'],
    'post_content'  => $page['content'],
    );

    wp_insert_post( $new_page );
  }

  echo 'Deployment complete.';
  exit;
}

You could put this code in a custom maintenance.php file and include that from the functions.php file in your theme, or add it as a custom WordPress plugin rather than include it in the theme, but either way works. Once you’re ready to deploy, push this code to your live site and log in as an admin. Then run the code by appending the deploy_changes query string to the end of your domain, for example:

http://hellfish.media/?deploy_changes=1

After you’ve made the changes, remove the function from the file (or disable the plugin) to make sure you don’t rerun the deployment accidentally. Source control (like Git) ensures that you always keep a history of the old deployment code if you ever need it again.

You can take this a step further. In a custom plugin, you could store a version number for the plugin in the database and only run specific changes depending on the version number. That way, you just have to add new code which runs based on the version number and update the version number after each section of code is run. Let’s expand the above example to include a new page using this version check method.


/*
 * To run the deployment
 * 1. Log in as an administrator
 * 2. Go to http://hellfish.media/?deploy_changes=1
 */
add_action( 'template_redirect', 'deploy_changes' );
function deploy_changes() {

    // If the request is not our deployment
    if ( ! isset( $_GET['deploy_changes'] ) ) {
        return;
    }

    // Only allow a logged in admin to run the deployment
    if ( ! current_user_can( 'install_themes' ) ) {
        echo 'Not logged in.';
        exit;
    }

    // Run script for 5 mins max
    set_time_limit( 60 * 5 );

    // get the version number form the wp_options table, or default to 0
    $version = get_option( 'hellfish_custom_plugin_version', '0' );

    // only run if the $version is 0
    if ( version_compare( $version, '0', '=' ) ) {
        $pages = array(
            array(
                'content' => '[woocommerce_checkout]',
                'title'   => 'Checkout'
            ),
            array(
                'content' => '[woocommerce_my_account]',
                'title'   => 'My Account'
            )
        );

        deploy_pages( $pages );

        // update the version stored in the wp_options table
        update_option( 'hellfish_custom_plugin_version', '1' );
        echo 'Deployment complete.';
        exit;
    }

    // only run if the $version is 1
    if ( version_compare( get_bloginfo( 'version' ), '1', '=' ) ) {
        $pages = array(
            array(
                'content' => '[woocommerce_cart]',
                'title'   => 'Cart'
            )
        );

        deploy_pages( $pages );
        update_option( 'hellfish_custom_plugin_version', '2' );
        echo 'Deployment complete.';
    }
}

// deploy any pages set up in the deploy_changes function
function deploy_pages( $pages ) {
    foreach ( $pages as $page ) {
        $new_page = array(
            'post_type' => 'page',
            'post_title'   => $page['title'],
            'post_content' => $page['content'],
        );

        wp_insert_post( $new_page );
    }
}

Building this as part of a custom plugin means you can link any database updates that may be required to the plugin version number that you update with every plugin release.

What We Do

We used a similar approach at Delicious Brains and on the ACF site, but I added a bit more control and process to it. Inspired by Laravel’s database migrations and seeding system, I created a migrations library that helps write scripts to make table and data changes during development. There’s a fair bit of overlap with the existing wp-table-migrations package, but I wanted something custom that could be used across different sites.

This is the process:

Create a new migration class file that contains the code to add/edit/delete a table, as well as inserting/updating/deleting data from the database
Each migration file, once run, will be recorded in a migrations table to ensure it isn’t run again
This file can be committed to Git as part of a feature branch
The deployment process for the live site has a build step after the files are copied to the server to run the database migrations via a custom wp dbi migrate WP-CLI command

This makes it possible to write PHP code to change tables and alter data as part of any code changes for a new feature or bug fix. Those changes are stored in Git and form part of the GitHub pull request that is reviewed, merged, and eventually deployed. Using this process does mean you need to be aware of how WordPress code alters the database, so you can make schema changes using code like this:

<?php

namespace DeliciousBrains\WPMigrations\Database\Migrations;

use DeliciousBrains\WPMigrations\Database\AbstractMigration;

class ComposerStatsTable extends AbstractMigration {

    public function run() {
        global $wpdb;

        $sql = "
            CREATE TABLE " . $wpdb->prefix . "woocommerce_software_composer_download_statistics (
                id bigint(20) NOT NULL auto_increment,
                composer_key varchar(50) NOT NULL,
                package varchar(50) NOT NULL,
                version varchar(16) NOT NULL,
                original_package varchar(50) NOT NULL,
                download_time datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
                PRIMARY KEY  (id)
            ) {$this->get_collation()};
        ";

        dbDelta( $sql );
    }
}

As well as making changes to the data like this:

<?php

namespace DeliciousBrains\WPMigrations\Database\Migrations;

use DeliciousBrains\WPMigrations\Database\AbstractMigration;

class AddWpSesLandingPage extends AbstractMigration {

    public function run() {
        $page_id = wp_insert_post( array(
            'post_title'  => 'WP SES is Now WP Offload SES Lite',
            'post_status' => 'publish',
            'post_name'   => 'wp-ses-now-wp-offload-ses-lite',
            'post_type'   => 'page',
            'post_parent' => 49760,
        ) );

        update_post_meta( $page_id, '_wp_page_template', 'page-wp-ses-landing.php' );
        update_post_meta( $page_id, '_yoast_wpseo_metadesc', 'Looking for WP-SES.com? WP SES is now WP Offload SES Lite. See what improvements we\'ve made to the free version and what you can get when you upgrade.' );
    }
}

I’m using WordPress PHP code to script any database changes manually, which can be tested using wp dbi migrate on the command line. I can also roll back the last migration if I include any rollback code in the rollback method of the migration class. I’m in control of the changes I make to the database with scripting like this, but sometimes I do wish I could use the WordPress admin UI to make my changes and perform a merge, like we hoped would be possible with Mergebot.

What about other merging solutions on the market?

Honorable Mention: VersionPress

When we started working on Mergebot, a similar plugin existed that used Git to track changes to WordPress files and the database called VersionPress.

Unfortunately, in June 2020, the development team announced that development of VersionPress had come to an end.

WPMerge

Not long after we decided to shut Mergebot down, a new merging solution entered beta. WPMerge is similar to how Mergebot worked in many ways. You make sure your development database is synced with live, start recording, and then make changes to your development site to be applied later to live.

Sometime after its launch, WPMerge partnered with Nexcess to add further value to their WordPress and WooCommerce hosting customers.

I gave WPMerge a quick road test, and it worked well. I did notice some differences from Mergebot in its implementation. It uses database triggers on the development site’s database to record all the queries executed instead of recording them using the WordPress ‘query’ filter.

In my brief testing, I did the following:

Adding a new post to dev and making sure when it was deployed to the live site it had the correct post meta relationship with the deployed post ID
Adding a new post with a gallery of newly uploaded images to see if the media post IDs in the gallery shortcode added to the post content were correctly replaced on deployment – they were 🎉
Editing an existing post title on my development site and deploying to live where the title had also changed – the development edit was used without asking 😬

Unfortunately, WPMerge also doesn’t handle conflict resolution:

For now, just like Git, the development changes take priority. We are looking to develop a conflict resolution module where you can choose which changes to keep.

For example, suppose you change a page title from ‘About’ to ‘About Us’ on your development environment, but before you deploy the changes, your client has also changed the title from ‘About’ to something else like ‘The Team’. In that case, WPMerge will ignore the client’s change and use your change as the new value.

Even as a developer on a former competitor, I was impressed with WPMerge and how it worked out of the box. I do think the UI/UX could do with some polish to make it easier for developers to use and understand the workflow better. I found myself very confused as to what the right action was to apply my current development changes to the live environment. I also noticed just how many queries were recorded and deployed that were unnecessary for the changes I was making. After the simple round of testing I described above, the plugin was recording over 400 queries! This made me nervous about using this on large-scale sites with lots of plugins installed.

While WPMerge is still in active development, it doesn’t seem to be adding any new features besides the core offering. The latest changelog entries reflect only a few bug fixes and an update for PHP 8 support.

Wrapping Up

I still don’t believe there is a magic bullet solution for database merging in WordPress. It is a problem that developers need to be aware of so they don’t get themselves into a situation with out-of-sync local and live databases. Be prepared before you make changes, record what you do and then play those changes back later at deployment time. For me, keeping this process as systematic and automatic as possible is key to improving my development workflow. It also reduces human error, especially in a team environment, as every data change is committed to version control.

What is your preferred method of applying development database changes to a live site? Have you found a tool to perform database merging that you are happy with? Let us know in the comments.

This entry was posted in WP Migrate DB Pro, Databases and tagged WordPress, Development, Database Merging.

.replybox-fallback, .replybox-fallback ul { list-style: none; }

rogerdpack says:

July 30, 2019 at 5:42 am

We have setup Master-Slave replication for this, when ever we needed we start replication and stop replication once whole data synced to production database. Master is my staging database and Slave is my production database.

Brad says:

July 31, 2019 at 4:29 am

I’d love to read a blog post with the details of this.

Reply
- robrecord says:
  
  July 31, 2019 at 3:35 pm
  
  Me too.
  
  Reply

فروشگاه اینترنتی says:

July 30, 2019 at 6:01 am

Like others I also do not believe anybody will ever crack this in such a way that it has 0 issues or is meaningfully scalable. I do the same thing as outlined in the post and make custom scripts but I generally use bash and WP-CLI instead whenever possible. It’s very cool to see how you do this with PHP and SQL so thank you for sharing 🙂

Iain says:

August 1, 2019 at 1:26 am

Thanks Mike, thanks for stopping by 🙂

Reply

Roy Emory says:

July 30, 2019 at 7:04 am

Great summary, Iain!

Iain says:

August 1, 2019 at 1:26 am

https://media0.giphy.com/media/26DMTEijJDudzovvO/giphy.gif

Reply

joppuyo says:

July 30, 2019 at 10:10 am

I don’t think there is any plugin that works reliably enough yet so I migrate changes by hand. It helps if you try to save as much configuration as possible as code, for example by creating custom post types in PHP rather than using a plugin. ACF has local JSON feature which saves custom fields to a JSON file and allows you to sync them in different environments. It would be cool if other plugins did the same. But in the end, you still have to do some changes by clicking things in the WordPress admin. It helps if you and your team migrate all content from production to staging and local using a plugin like WP Migrate DB and configuration from local to staging and production by hand. So basically: Content: Production → Staging → Local Configuration: Production ← Staging ← Local I don’t think this problem can be solved until WordPress introduces some way to separate content and configuration. As much flak as Drupal gets, they actually have a module called Features in core that tries to solve this problem by storing configuration as YAML file and synching it to the database

Matt Radford says:

July 31, 2019 at 3:57 am

ACF is a giant PITA when it comes to pushing field group changes – is the field group in JSON or the DB? There are 8 different combinations of JSON/DB,and no way of telling ACF to ignore DB and use JSON only. The solution for me recently has been to export all field groups to PHP and include – that way it’s version controlled and ignores both DB and JSON, although you lose the UI for adding fields. I would love WP to take on some version-controllable configuration ideas from other projects.

Reply
- Iain says:
  
  August 1, 2019 at 1:23 am
  
  Hey Matt, yeah I use exported field groups as PHP files and it’s working well. I’m getting used to the data format so can make edits directly in the PHP. Someone suggested this a while back for fluently describing fields in code https://github.com/StoutLogic/acf-builder, it looks good but I’ve not tried it yet.
  
  Reply
  - Wouter Van de Wille says:
    
    December 24, 2019 at 11:08 pm
    
    ACF Builder is great, but if you like the frontend control I can suggest ACF Extended. It’s able to sync the fields you make in the WP backend with PHP files in your themes folder.
    
    Reply
- B.R. says:
  
  February 6, 2020 at 4:07 am
  
  Everything I build in ACF Pro with the GUI I built on a separate site, and export the PHP code, then add it to a custom module. And I’ve started working with ACF Extended, so that includes custom taxonomies and post-types. Everything is in code.
  
  Reply

Jordan says:

July 30, 2019 at 1:21 pm

Current method: Migrate Production’s DB with MigrateDB Pro to localhost before doing any work. Verbally ensure that anyone else who might be working on the Production site knows about the current work. Not ideal but working for now.

Iain says:

August 1, 2019 at 1:26 am

That’s an approach I’ve taken in the past for sure, but the method is driven by the type of site – ecommerce is a pain!

Reply
- Jordan says:
  
  November 18, 2019 at 1:01 pm
  
  Can you elaborate on the ecommerce pains?
  
  Reply
  - Iain says:
    
    November 19, 2019 at 12:42 am
    
    With an ecommerce site you can’t easily push your local changes back to live without overwriting new order data if it is stored in wp_posts.
    
    Reply

Jansen says:

July 30, 2019 at 1:35 pm

Great article about this topic. I was sad when MergeBot was cancelled, but not much later WP Merge arrived I must say, it has been working just great for us. The times we needed it, the most common use case is: – busy production site, with ongoing database changes (new orders/subscriptions etc.) – create a copy/snapshot of production to staging, and do development/ testing new plugin, etc – once all is tested & working, merge the databases You need to really read the manual and understand what you’re doing. But WP Merge is not too complicated, and helps to speeds up development, but also saves the client money because of the significantly reduced downtime.

Iain says:

August 1, 2019 at 1:25 am

Glad to hear WPMerge is working out for you – it did seem pretty good when I tried it.

Reply

Brian Layman says:

July 30, 2019 at 3:03 pm

heh – I still run old school. To update my dev db, via bash functions I trigger a msqldump (with drop and create) and pull the resulting file from the live server. Then use the a bash function to pull Search-Replace-DB’s command line tool. Here’s what my bash function looks like to switch all www references to dev: function wwwtodev(){ pushd . cd /sites/$1/public/ cd /sites/$1/public/blog sqlname=sed -n "s/.([‘"])DB_NAME1s,s([‘"])(.)2./3/p" wp-config.phpsqluser=sed -n "s/.([‘"])DB_USER1s,s([‘"])(.)2./3/p" wp-config.phpsqlhost=sed -n "s/.([‘"])DB_HOST1s,s([‘"])(.)2./3/p" wp-config.phpsqlpass=sed -n "s/.([‘"])DB_PASSWORD1s,s([‘"])(.)2./3/p" wp-config.phppopd cd /sites/$1/public/ cd /sites/$1/public/blog cd /somedir/Search-Replace-DB-master echo Running: php srdb.cli.php --host=$sqlhost --user=$sqluser -p$sqlpass --name=$sqlname --search=www.$1 --replace=dev.$1 php srdb.cli.php --host=$sqlhost --user=$sqluser -p$sqlpass --name=$sqlname --search=www.$1 --replace=dev.$1 echo Done.}

Brad says:

July 31, 2019 at 4:30 am

What’s the reason for using the srdb.cli.php script instead of WP CLI?

Reply
- Brian Layman says:
  
  July 31, 2019 at 11:04 am
  
  The primary reason is that I’ve used that search and replace tool since before WP CLI had the ability to do search and replace. I know how it operates, but I haven’t looked at the source code for WP CLI’s search and replace tool to see if it covers everything, such as serialized strings, correctly. Since what I had has been reliable, there was no reason to change.
  
  Reply

robrecord says:

July 31, 2019 at 7:46 pm

Awesome look at this not-spoken-about-enough topic. Thank you! When I had this situation arise, I built a little system based on scripts and wp-cli that would run database change scripts, in both directions (do or undo). They were linked to git tags with a version number, so when a commit was checked out, it would run every script from whatever version tag it was on before, all the way forward or backwards to the current latest version tag – doing or undoing database changes. Usually this could be accomplished by using wp-cli commands inside the script, eg. enabling a plugin. I was proud of it but never published it. Am excited someone else has had a crack at it!

Iain says:

August 1, 2019 at 1:27 am

Sounds good, Rob – would be interested to see how it works.

Reply

Kevin Umé says:

August 1, 2019 at 11:05 pm

Well written article Iain! This is one of the biggest issues I am having lately with 2 big WooCommerce projects and a lot of active development on staging (from local) and the client fixing products etc on staging. I wish woocommerce had its own tables but i guess that needs a big change in wordpress. Going to give WP Merge a go after the positive comments here, staying positive 🙂

Pierre Balian says:

November 22, 2019 at 8:45 am

For us as an agency this is really only a real concern for us when the site is a woocommerce site. Sure, in the past we have had clients update content without telling us and losing some of that. But that usually isnt that big of a concern. But woocommerce orders coming out of sync IS. So at this point we are trying to figure out how to migrate staging db back to live but to totally leave woocommerce order data alone.

Péter Makkai says:

December 18, 2019 at 5:21 am

Our team experienced the same struggles, so we started to work on a solution. Finally GeekBee.io is in the finish line, and the beta launches within weeks. We are now looking for our first testers, so if you still don’t have any solution we would be happy to have you among our subscribers

Reply
- Jesus says:
  
  September 1, 2020 at 7:17 am
  
  Did you finally try it? Would be very interesting to hear about the outcome.
  
  Reply

B.R. says:

February 6, 2020 at 4:04 am

We currently make changes to Dev sites, upload those changes to Stage, push the data from the Live site to Stage using XML feeds from WP All Import and Export Pro, then test, test, test. Afterwards, we have to inform the client(s) their site will be down at a specific time, push any additional changes the client has made since our testing began with WP Import/Export, then migrate the site from Stage to Live. It works, but by, is it tedious.

nomailme says:

March 31, 2020 at 12:37 pm

It seems to me that writing custom MySQL commands in order to version track them, vs using the CMS UI to make changes, defeats the purpose of using a CMS in the first place. What’s the point of having a wp-admin at all if you can’t use it to make any changes? I very much appreciate the efforts you’ve put forth to solve this problem, but it seems to me that it’s inherently unsolvable by design.

Cameron Jones says:

April 1, 2020 at 3:15 am

I’m a firm believer that custom post types should each have their own tables. wp_posts and wp_postmeta, wp_pages and wp_pagemeta etc. I’d like to take a shot at building a plugin for it at some point

Ken Lyle says:

July 6, 2020 at 8:30 pm

I am wrapping up a project with exactly this situation. I am using dbForge Data Compare for MySQL from Devart (on trial, in no way affiliated). One pain point is that you need to get your database prefixes to match, there is no option I see to ignore the prefix. It does a great job of showing records Unique to Live, Unique to Dev, and Identical, which can be ignored, I think? The issue is in the category of Different records, where the same ID…yeah, might be a post on one, and a product, revision, or other on the other side. That is what we are talking about right, posts/postmeta? I am thinking to increment the post ids on Live of everything after the Identical range, the make the matching ID adjustments in Postmeta, then re-run the compare, hoping for only Unique and Identical records, no Different, then just push the Uniques from Live to Dev (then move dev to Live, long story :). Is this Bump the IDs approach reasonable?

Ken Lyle says:

August 4, 2020 at 11:38 am

This approach worked pretty well. I should have put in Bumper records in posts and postmeta on one side or the other, maybe both, to avoid the pains others have written about with Woo orders. I ultimately found some 20 orders from prod where the IDs had collided with postids on the dev site. On dev, we had implemented live shipping calcs and completely reformatted several products using variations instead of the eBooks being separate products. One thing that worked was using Data Compare, and stripping down a copy of Live to identify the records that were valuable….like, Oh, there is a bunch of Postmeta records that are 0 on live but have a real value on production…Delete them from my copy, and re-run the compare…Oh, there are 500 records that are reported as Different based only on the subdomain in a URL, standardize, and re-run the compare. Eventually, the records, some obvious, like stock quantity bubble to the surface, and can be synced. But, yeah, it’s still pretty brutal.

Reply

Jonathan Bailey says:

August 27, 2020 at 10:28 am

So… here’s an idea. Copy live db to stage db. Make changes to stage db (settings, custom post type fields, etc) Use a tool (to be named later) to identify the changes to the database and dump them as discrete scripts. Publish them back to live with the option to accept or reject each change. Changes to user data would be forbidden and changes to site content (post titles) would be discouraged. I do not know of such a tool suitable for the WP DB or even if it is possible without an audit trace setup in the data store

Jonathan Bailey says:

August 27, 2020 at 10:35 am

The script would have to exclude explicit ids when promoting which means it would have to "know" the relationships between data in wp_post and wp_postmeta and script accordingly, but that information is there (with the ID). OR… a plugin that records changes to the database as they happen on stage and dumps these changes to script. All db changes in WP should go through the WP db utils so if you could hook into those you could record the data sent and the method used and then script and publish.

Reply

Matthew Harris says:

May 24, 2021 at 12:53 pm

Well I guess at least it’s good that it isn’t just me being stupid then. It seems that the big problem is the post-id’s which ultimately create a clash. It’s a shame there isn’t some way to write a plugin so that the dev site can request a clean post-id which could be used on the dev site and reserved/skipped on the live site. The approach that I take is to clone a site using Duplicator Pro, then use Disable User Login on the live site to freeze the site owners out, but this only works for redesigning brochure-style websites. I can’t even get my head around the options discussed here, it seems like a minefield. When I need to add new features I will usually just go through the tedious process of writing down a step-by-step of the actions I take, getting approval for the dev site, then quickly manually running through the development process again on live. For any kind of active WooCommerce store this seems like it’s basically impossible to redesign the site?

Raphael Titsworth-Morin says:

May 26, 2021 at 7:33 pm

This is a great breakdown. In the end, we generally recommend against WordPress if we believe our clients’ systems will need complex or regular migrations. The simplicity of the custom post types, and post meta really is a double edged sword, as you allude to, particularly since so much of the ecosystem depends on it.

Per Søderlind says:

July 13, 2021 at 7:17 pm

Anyone tried a more low-level approach, using a tool like MySQL Comparison from Red Gate?: https://www.red-gate.com/products/mysql/mysql-comparison-bundle/

Jānis Elmeris says:

July 28, 2021 at 11:14 am

Thank you for the information and link references!

UTC WebDev says:

February 3, 2022 at 12:05 pm

Thank you so much for posting this blog (on my birthday, no less). I’m currently migrating a multisite that was created in 2007 (wp 2.x?) and has a few very large sites that get new content multiple times per day. As part of the migration, I’ve created a Bedrock.io-derived project, with Composer managing all plugins and themes (wpackagist and private GitHub repos), and Laravel Envoy for simple no-downtime deployments. Doing database migrations and search-replace between environments has been a hassle, so the processes you have outlined are very much appreciated. I plan to purchase your plugin to help streamline some processes. With the info posted here, I’ll hopefully also come up with a way to move db configuration from development to staging and prod. The comments here are very enlightening. Thanks to all who have chimed in with their own processes and ideas.

Carlos Gomez says:

February 7, 2023 at 5:12 pm

For your information, WPMerge has not been updated since June 2021. So, WP Merge is not compatible with WordPress 6.0.

Scott Thomason (scott8035) says:

April 24, 2023 at 5:50 am

I have been using WPMerge on WP 6.2 as of April 2023 and it seems to be working OK.

Reply

PrimeTime Web says:

July 31, 2024 at 8:19 pm

Thanks for writing this, Iain – cheers! I’ve been searching for something like this, and it showed up in my inbox from Delicious Brain Bytes. This topic should get more attention if WordPress hopes its users can scale to meet the requirements of any normal SMB or enterprise core business functions.

Users, orders, comments, and form submissions are typical merging headaches. Even WP Forms doesn’t allow form submissions to be synced or imported into other environments; they can only be exported off-site but not elsewhere. It’s unacceptable for WordPress to power 43% of the internet, and have no great solution to this problem. When a plugin does exist, will it be updated and supported in five or ten more years?

If you aren’t doing major development only publishing products and blogs, one workaround would be to develop on production only. Not using a development site at all or only using a development site for implementing and shipping major releases. Every WordPress website contains a staging environment within it (e.g., drafts of posts, etc.). This strategy doesn’t work if you ever need to ship a release from a separate development/staging site. It also sounds scary because I like to have a live copy of production that I can see running, just in case the backup files are broken/corrupted, etc.

Could Delicious Brains build or acquire a solution for this, perhaps a centralized cloud-based dashboard for this kind of data? It could live in the cloud, easily be synced between development and production, and even possibly talk both ways. Cloud copies of this data for migration purposes could complement other backup/migration products and solutions.