Solving WordPress Site Sync Challenges: Key Insights & Solutions

14 min read Original article ↗

Twenty-three years into WordPress, the general problem of site synchronization is still unsolved. We have great tools for the slices, but nothing for the whole. You can’t reliably automate any of these tasks beyond basic sites:

  • Export posts, media, Woo products, and all their associated plugin data, and import it all on another live site.
  • Publish posts on a staging site and sync them to the production site upon acceptance.
  • Clone a live site, install and configure a plugin, and deploy it all back to production.
  • Build a site locally, deploy it to production, and re-deploy as the local site evolves.
  • Migrate the entire site to another host.

This post exhaustively covers what makes these problems so difficult, and what we can do instead. It is a 2600 words deep dive. It reflects many discussions I’ve had with Dennis Snell over the years, as well as what I’ve learned from WordPress.com, the WordPress importer plugin, the Site Transfer protocol, Playground, the more recent Reprint, ForkPress, and more.

Get some coffee, clear your calendar for an hour, and join me on this ride!

What’s difficult about syncing?

The holy grail of WordPress↔WordPress synchronization is this:

  1. I connect Site A to Site B.
  2. I can now click Push or Pull buttons to move the data in either direction.
  3. Optionally, I can choose which posts, products, or plugins I want to sync.

It’s effectively the same as a hostless, Peer↔Peer WordPress.

I claim that such an automated, generalized sync is not possible.

There are more limited forms of syncing WordPress could have. We’ll explore them later in this post, but now let’s discuss the constraints we’re facing.

Migrating a site to a different environment

A WordPress Site = Database + Files + Environment. We can only sync Site A with Site B if their environments match closely enough. That is, all of these and more:

  • Operating system, locally available software, cron, env variables, memory and disk space, and web services in the local network.
  • Additional HTTP response headers, HTTPS support, access policy for /wp-content/ files, special routing rules, pretty permalinks support, web-accessible directories, edge cache rules, all the associated domains, loopback request support, and whether there is a public IP Jetpack could connect to.
  • PHP version, extensions, php.ini settings, disabled functions, auto_prepend_script, host-provided constants, and number of PHP workers.
  • MariaDB version, my.cnf options, user permissions, character set, and table engine. MyISAM and InnoDB are very different!
  • File layout, side-loaded plugins, host-specific plugins, symlinks pointing outside of the site root, read-only files, file owners and permissions, and tmp directory path.

When the environments don’t match, we’ll observe:

  • Database connection errors: Site A uses the wp_ table prefix, Site B enforces a site_14718_ prefix, and a plugin hardcodes wp_.
  • An order gets placed when it shouldn’t: SQL ROLLBACK fails in production because all the tables are MyISAM.
  • Text data corruption:Ziółkowski becomes Ziółkowski because local tables use utf8mb4 declared via DB_CHARSET, and the production host ignores DB_CHARSET and enforces latin1.
  • Exposed private data: A source host protects wp-content/uploads/precious-ebooks/ with .htaccess rules, but after syncing to target host, which runs nginx, those files are exposed to the world.
  • Non-loading site: A remote site only has 2 PHP workers, so a wp-cron task using a loopback request locks all the visitors out.
  • Missing automated emails: The site sends emails using cron, loopback requests, Redis, and sendmail, but the new host doesn’t support one of these things. We only realize something’s wrong two weeks later.
  • Missing images: A site exported from WP Cloud has no thumbnails, as WP Cloud generates them on the fly when you request wp-content/uploads/my-photo-512x512.jpg.
  • Invoices stop generating at checkout: The new host disables proc_open/exec that the PDF/image library shells out to.
  • CSS vanishes after deploy: Page builders write generated CSS into wp-content/uploads; the new host has a read-only filesystem, the write fails, and the whole site renders unstyled.

…and many, many more issues. Some are benign, and some are catastrophic. Some are solvable, and some aren’t. The absolute worst scenario is when the site looks good after syncing, and then two weeks later, we detect a serious and irreversible data leak, loss, or corruption.

We can’t automatically detect most of these mismatches. How would we know Site B only runs 2 PHP workers? Or that our 24th plugin relies on Redis in specific code paths? Or that Site A had a special thumbnail-generating route? The PHP extensions rarely match exactly, but how do we know if that matters? The best we can do is compare the PHP versions, do a few more naive checks like that, and hope for the best.

Synchronizing too much is catastrophic

Let’s categorize every database field as environment-specific or not environment-specific. Woo orders, MySQL credentials, Stripe keys, salts, connected OAuth clients, and site URLs are all examples of that, as they always differ between production sites and testing sites.

If you sync local environment-specific data to production, your service will be disrupted. It could be a full site outage, checkout outage, lost or mismatched orders, misattributed emails, or all these things. Or something else entirely. The point is: corrupt this mission-critical data and there will be trouble.

Therefore, we must not synchronize any environment-specific data.

However, no sync mechanism can tell which data is environment-specific.

Codex analyzed a few popular WordPress plugins for me and found the following environment-specific records:

  • WooCommerce: wp_woocommerce_api_keys, wp_wc_webhooks, wp_woocommerce_payment_tokens, wp_woocommerce_payment_tokenmeta
  • WP OAuth Server / OAuth2 Provider: wp_oauth_clients, wp_oauth_access_tokens, wp_oauth_refresh_tokens, wp_oauth_authorization_codes, wp_oauth_public_keys
  • License Manager for WooCommerce: wp_lmfwc_api_keys, wp_lmfwc_licenses

Who knows where else they store such data? In site options? In post meta? In block attributes? And who knows which rows are required for the site to function? And how could we replace them during the sync?

We don’t have a reliable, exhaustive, and continuously updated list of all the plugins, their versions, and the locations of the environment-specific information they store. And what about dedicated plugins? I know a site using ~50 plugins developed in-house. How do we account for that?

Pulling environment-specific data to the local development site is also risky. Your test order could send an email to a customer, update the real accounting records, and dispatch shipping via a webhook. Not to mention the legal and security risks of a contractor pulling the PII of your customers and your production Stripe key.

Here’s another take: Let’s categorize every database field as sensitive or not sensitive. PII, API credentials, and credit cards are all sensitive. Pulling sensitive data into the local development environment creates a security and legal risk, but there’s no automated way to know which piece of information is sensitive.

Synchronizing too little may still break the site

On the flip side, we can’t just synchronize the posts. We don’t know what a post is.

For sure, a post is an entry in wp_posts with a few related entries in wp_post_meta. It could also consist of a few related media uploads, also stored in wp_posts and the wp-content/uploads/ directory.

But is the post also:

  • A related global styles change?
  • A related pmpro_memberships_pages table row?
  • A related icl_translations table row?
  • A site_options entry listing that post’s ID in a serialized JSON data structure?

Any sync mechanism would need to decide. Bindings from plugins would help. Using UUID as post_id or guid would also help. And yet, missing one of these could break the site, so in the end, the user would still have to make choices.

Identifying semantic conflicts requires domain knowledge

A conflict is when two identical sites changed in different ways with different guiding intent. The code doesn’t know whether that happened.

When we talk about conflicts, most of the time we mean row-level conflicts. Site A updated the post_content, Site B updated it to a different value, and we need to merge both edits.

Semantic conflicts are trickier. There’s no clear row-level disagreement, and yet both sites clearly disagree. Here are a few examples of that:

  • The local site set the featured image ID to 17. Production deleted media item 17.
  • The local site changed the post slug from /pricing to /plans. Site B added a menu item pointing to /pricing.
  • The local site unpublished the Black Friday landing page. Production added that page to the homepage hero CTA.
  • The local site changed the permalink structure. Production added hardcoded links using the old URL structure.
  • The local site changed a form field from text to relationship. Production saved text values into that field.
  • The local site regenerated image sizes after changing theme settings. Production inserted specific image size URLs generated by the old settings.
  • The local site sets WordPress default_role = administrator while testing registration. Production enables public checkout account creation or membership registration.
  • The local site removes Gravity Forms field ID 7 named Email and creates field ID 12 named Email Address; the CRM feed now maps to 12. Production collects 10,000 campaign entries where email is still stored under field 7.

At the row level, there are no conflicts. However, if we just apply local changes to the production site, the results won’t be what we wanted.

Git also has this problem: your PR changes the grid layout to row layout, my PR adds a hamburger menu to every row. No lines of code conflicted, but the effect is still not what either of us wanted.

However, Git operates on text files, where this type of problem is much easier to catch than in the WordPress database. WordPress site sync is as if Git could auto-merge .sqlite files, and each of us changed 100 interrelated rows. Who could really review that?

Conflict resolution requires plugin support and human intervention

Plugins store their own database records. We can’t synchronize two custom tables without domain knowledge. The plugin must provide the bindings for conflict resolution in its own data:

  • The local site changes an form field from Image to Gallery while production saved a single attachment as a string.
  • The local site changes the page layout. The production site changes the headline text. It’s all stored in the same JSON blob in the _elementor_data postmeta row.
  • Consider the same wp_yoast_indexable row. The local site changes the permalink while production changes the taxonomy. They both regenerate the same indexable. Without plugin bindings, it’s unclear what is the source data to preserve and what is derived data to delete and rebuild.

Note how complex and comprehensive these plugin bindings would have to be! How many plugin shops would build and maintain them?

The more difficult scenario is when even the best bindings couldn’t possibly reconcile the data.

Because the code can’t capture the user’s intent, these conflicts could only be resolved manually:

  • The local site changes the product stock from 24 to 17. Production sold 8 items and now only has 16 left.
  • The local site marks an action scheduler job as complete; production still has it pending. Should we even try to reconcile it?
  • The local site cancels the order for testing. The production site sends it to fulfillment.
  • The local site and production site both change a Gravity Forms field label to different values.

For these, we’d need a case-by-case UI, and the plugins would have to support it.

Here’s a different conundrum. Just because we can merge the data automatically, should we? Real-time collaboration software often uses CRDTs. It’s really great at merging, and it still gets things wrong.

Solutions that won’t work for general site sync

It’s so very tempting to say this or that simplified solution would address 90% of sites. Here are a few such solutions that, sadly, wouldn’t work:

  • Overwrite the entire production database on sync. As we’ve seen earlier, synchronizing too much is catastrophic.
  • Overwrite only specific database tables. Even if we knew which ones, tables such as wp_options can be neither fully synchronized nor skipped. Plugins also ship these. We’d need accurate sync bindings to semantically exclude sensitive and environment-specific records from the sync.
  • Choose production data on conflict. For row-level conflicts, that’s a certain way to corrupt the production data—as we’ve covered earlier in semantic conflicts. For semantic conflicts, it would still likely be different from what the user actually wanted.
  • Replay SQL queries or wp cli calls. We still might get both row-level and semantic conflicts.
  • Use ____ software. Dolt, XTDB, TerminusDB, Turso with branching, ZFS, and Git all touch this problem space. They provide great plumbing for versioning data and files, but they wouldn’t solve our merging problems.
  • Undo button when a sync goes wrong. Small, inactive sites can be put in maintenance mode and reverted to the last database backup. More active sites don’t have this luxury. By the time we click undo, there might be new comments, logs, and orders stored, and we don’t have a good way of merging them into the last backup.
  • Use LLMs to merge the data. The LLM would have to know the user’s intent, get the foreign key relationships right even in base64-ed JSON blobs in block markup, and we’d need to verify the correctness of the result. LLMs could still help us with a related problem, though—we’ll get there in a few paragraphs.

Here’s the rundown of a few projects that approached these problems over the years:

  • VersionPress—versions WordPress data and files in a single Git-like workflow. Plugins had to declare their actions, shortcodes, and the database schema to be supported and most didn’t. VersionPress was wound down in 2020.
  • Mergebot—records database changes during development and replays them later. The authors shared the approach was not reliable enough, and shut it down in 2018.
  • Pantheon workflow—pushes the code and data to production, but with a warning that Pushing content up to Live should almost never be done to a launched site.
  • BlogVault—backs up and restores the full site. It has a file-level and row-level selective merge for syncing to a live site, and then the user is responsible for reconciling the data at the row level. It’s not a conflict resolver and doesn’t rewrite conflicting IDs.
  • Site migration plugins (WP Migrate, AiO WP Migration, …)—they export and import a full site to a file, push the selected files and database tables to production, and apply a string search&replace on the data. WP Migrate backs up your database before you push in case things go wrong.
  • WPMerge—replays recorded development changes on top of the latest production database while rewriting IDs. According to the documentation, the development changes take priority on conflict.
  • InstaWP Connect—records WordPress hooks and actions, and replays them on the target site. It avoids using numeric IDs and refer to slugs, emails, and other identity-based keys. The documentation says it supports two-way sync between sites for a subset of WordPress data and for a few plugins. I’ve found error reports around missing database changes, Windows support, and not syncing larger datasets.

What can be done instead?

Instead, we can solve smaller, well-scoped problems—and solve them well enough:

  • Site backup and restore.
  • Site migration to a new host.
  • Publishing content from a local/staging site to production—with limited support for related data.
  • Deploying selected local changes to production—for developers who know what they’re doing.
  • Cloning a live site to a local environment.

Reprint supports a few of those already and can be bent to support the rest. It may never handle every WordPress site, but it doesn’t need to be universal to matter. It just has to help you move your data, safely, wherever you’re going.

Many thanks to Dennis Snell for reviewing this post and helping me refine it. And also for the many conversations we’ve had where these insights were developed.