The Second Backup That Wasn’t

The Second Backup That Wasn’t

The file was called woocommerce_production_20240315.sql.gz and it was exactly 487 gigabytes, or it would have been, if the backup had actually finished. What had in fact finished was a 42-gigabyte chunk of incomplete SQL that looked, to the untrained eye, like a complete database. The error message had scrolled past at 3:17 AM, logged to a terminal nobody was watching.

The migration was supposed to start at 9 AM. By 8:45, the team in the Jurong East office was staring at a partial backup, a deadline, and a WooCommerce store that processed roughly 2,800 orders a day — mostly wholesale, mostly from distributors who expected their inventory numbers to be accurate down to the unit.

The plan had seemed straightforward enough: migrate 500GB of WooCommerce data from a managed WooCommerce host to Google Cloud Run. Faster scaling, better cost control, containerized architecture. The kind of project that looks clean on a diagram and messy in practice.

The 180 Million Row Problem

Most guides for WooCommerce migration assume a certain scale. They talk about exporting via the WordPress admin panel, about using a plugin like All-in-One WP Migration, about the standard database dump and file transfer. None of those assume 500GB. None of them assume a store whose wp_postmeta table alone runs to 180 million rows — product variations, order metadata, cached lookups for a catalog that includes 47,000 SKUs across three warehouses.

“The standard WooCommerce export tool gives you a CSV with like 1,000 rows,” said Jun Wei Tan, a site reliability engineer who had been called in after the initial plan ran into trouble. “For this store, that’s cute but not useful.” Tan had seen the problem before — not this store specifically, but the shape of the problem. A store grows incrementally. Nobody migrates a 500GB database; they migrate a 500GB database that started as 50MB four years ago and accumulated complexity like a reef accumulates sediment.

The choice of target mattered too. Cloud Run is serverless in the sense that it abstracts away the server, but it has hard limits: 4GB of memory per container instance by default, a 60-minute request timeout, no persistent local filesystem across requests. A standard WordPress install expects to write files, cache pages, and keep database connections open. Packing 500GB of WooCommerce into that architecture required more than a straightforward lift-and-shift. It required rethinking what the database was doing and when.

A 15-Meter Ethernet Cable

The incomplete backup was the first sign that the standard tools weren’t going to cut it. mysqldump had been running for six hours before it choked — not on the size of the database, but on a specific table with a corrupted index that caused the dump process to hang. The backup that did finish was missing roughly 18% of the order data, including all orders from the previous 72 hours.

Getting a clean dump required isolating the problematic table, rebuilding its indexes, and running the dump in chunks — 50,000 rows at a time, piped through pv to monitor progress. That took another three hours. By the time a verified, complete backup existed, it was 1:45 PM and the migration window was shrinking.

The file itself was a 487GB gzipped SQL dump. Uncompressed, it was closer to 1.4TB. Transferring that to Google Cloud Storage over a 300Mbps business-grade connection meant roughly 12 hours of sustained upload — assuming nothing interrupted the connection, which it did, twice, because the office router had a tendency to drop long-lived TCP connections after about four hours. Someone found a 15-meter Ethernet cable in a supply closet and moved the transfer machine next to the router, bypassing the Wi-Fi entirely. The transfer restarted at 3:20 PM.

By 5:45 PM, the dump was in Cloud Storage. The real work hadn’t started.

CPU at 92 Percent

Google Cloud SQL, which would serve as the database layer for the Cloud Run WordPress instance, accepts imports from gzipped SQL files in Cloud Storage. In theory, it’s a one-click operation. In practice, importing a 1.4TB uncompressed database into a Cloud SQL instance with 16 vCPUs and 104GB of RAM took 47 minutes — during which the Cloud SQL instance’s CPU utilization hit 92% and the database’s replication lag on the read replica spiked to 14 minutes.

The import finished at 6:32 PM. The first attempt to connect the Cloud Run WordPress instance to the Cloud SQL database failed with a “Too many connections” error — the import process had left behind a pool of unclosed connections that exceeded the default limit. A brief, unglamorous session of manually killing connection IDs in the SQL console fixed it. Nobody on the team would describe that moment as elegant, but it worked.

What came next was the part none of the planning documents had accounted for: the WooCommerce database schema itself. The imported database was a faithful copy of the production database, which meant it contained all the accumulated cruft of four years of plugins, uninstalled themes, and abandoned caching tables. There were tables for plugins that hadn’t been active in two years. There were 14 orphaned database views. The wp_options table contained 280,000 rows, most of them transient cache entries that would never be accessed again.

Cloud Run’s per-container memory limit meant the database couldn’t carry that weight. Every query against a bloated wp_options table consumed memory that the container needed for serving requests. Cleaning the database before the cutover wasn’t optional.

280,000 Rows of Transients

Cleaning 280,000 rows of transient options took two approaches. The first was a SQL query that deleted expired transients — the ones with a timestamp in the past. That removed about 180,000 rows. The second was a harder call: deleting orphaned transients that had no expiration timestamp but also no active reference in the codebase. There was no clean way to identify them. A plugin developer somewhere had written a transient that never set an expiry, assuming it would be cleaned up manually. It never was.

The team made a judgment call: delete any transient not referenced by an active plugin or theme. That removed another 60,000 rows. The remaining 40,000 were kept — some of them were cache entries for product feeds, and removing them would force the feeds to regenerate under load, which could cause a bottleneck during the cutover.

It wasn’t a perfect solution. A future developer encountering the database would find unexplained gaps in cached data and would have to rediscover the reasoning. But the trade-off was a database that could fit inside Cloud Run’s memory budget.

One Order, Two Confirmations

The actual cutover happened at 9:47 PM. The DNS change for the store’s domain took 11 minutes to propagate — faster than expected, probably because the store was on a Cloudflare plan that supported instant DNS updates. For those 11 minutes, the store was briefly accessible from both the old server and the new Cloud Run instance, depending on which DNS server a visitor happened to hit.

One order was placed during that window — on the old server. The order data didn’t exist on the new database. A member of the support team manually recreated that order from the email confirmation, a process that took about 15 minutes and left the customer slightly confused about why their order confirmation arrived twice.

The first request served entirely from Cloud Run came at 9:58 PM. A product page for a specific model of industrial shelving unit — the store’s third-best-selling item — loaded in 1.2 seconds. On the old server, the same page had loaded in 3.8 seconds. The team monitoring the dashboards saw the response time drop and, without anyone saying it aloud, understood that the migration had worked.

502 Errors at 2:30 AM

The first 24 hours after the cutover were not uneventful. Cloud Run’s autoscaling behavior took some tuning: the default settings created new container instances whenever CPU utilization exceeded 60%, but a sudden burst of traffic — a distributor’s sales team logging in to check inventory before a morning order run — caused eight containers to spin up in under a minute. Each container took about 14 seconds to become ready, during which time some requests were dropped with 502 errors.

The fix involved adjusting the container’s startup probe to serve stale cached content during cold starts, and increasing the minimum number of kept-warm instances from one to three. That raised the monthly cost by roughly $18 — a trade-off the team accepted after calculating the cost of a single lost order from a distributor who couldn’t access the store.

A second issue emerged around 2:30 AM on the second day: the wp-cron process, which WooCommerce uses for scheduled tasks like clearing expired carts and running inventory syncs, wasn’t running reliably inside the Cloud Run containers. The container lifecycle was too short for cron jobs that expected a persistent process. The solution was to move scheduled tasks to Google Cloud Scheduler, which triggered a dedicated cron endpoint every five minutes. It wasn’t the same architecture the store had been designed for, but it was more reliable — and it decoupled cron management from the application layer entirely.

$247 and a Staging Environment

One small mistake during the migration remained unresolved. The team had set up a staging environment on Cloud Run to test the migration before the cutover — a duplicate of the production environment that ran on a separate set of containers. After the cutover, the staging environment was left running for three more weeks, accruing costs and consuming Cloud SQL read-replica capacity. A routine billing review caught it eventually. The wasted spend came to $247 — not a huge amount, but the kind of oversight that happens when a team is focused on a single migration deadline and nobody owns the cleanup process afterward.

The person who eventually shut down the staging environment mentioned, in a retrospective meeting, that it would have taken five minutes to tag the resources with an expiration date from the beginning. Nobody argued with that observation. It was simply noted and filed.

The store’s operators, looking back at the migration, paused over the partial backup, the Ethernet cable on the floor of the office, the support ticket from the customer who got two confirmation emails, and the $247 that nobody had meant to spend. Would they do it again? Most who’d been through it said yes — and that they’d do at least one part of it differently next time.