We actually restored from backup

Nine hours and forty minutes. That’s how long one of our customers was without their data last Tuesday, from the moment they wrote in to the moment they could log in and see everything again. Five days earlier, I’d have told you we couldn’t have done it in a day at all.

Atlassian’s two weeks

If you missed it, starting on April 4th about 400 Atlassian Cloud customers lost access to Jira, Confluence and the rest, completely, and some of them for close to two weeks. About a week in, Atlassian’s CTO, Sri Viswanath, wrote up what had happened. They’d been retiring a legacy app, and a script was supposed to deactivate it for a list of customers. The team running the script was given the IDs of the customers’ entire cloud sites instead of the IDs of the app, and the script was run in a mode that deleted things permanently, not one that could be undone. So it deleted about 400 sites.

They had backups, and they could restore them. What took so long was explained in one sentence of the CTO’s post, which I’ve now read out to two different meetings: each of their data stores holds data from many customers, so they had to extract and restore individual pieces from their backups, one customer site at a time. They got the last customers back on the 18th. Gergely Orosz at The Pragmatic Engineer has been covering it closely, including how quiet Atlassian was for the first week, which I suspect will be the part customers remember longest.

The shared data stores were the bit that got my attention, because that’s exactly how we’re built. Every customer of ours lives in the same Postgres database, every table has a tenant_id column, and everything is filtered by it.

What our drill had been testing

After the afternoon our cloud went dark in 2012, when we restored from a dump for the first time ever in the middle of an outage, we started restoring the whole database into a scratch instance once a month, with a timer running. We still do. The database is a lot bigger and on a different engine now, and the drill takes a little under three hours, and it’s never failed.

But it answers one question, “can we get everything back?”, and it’s not the question you’ll actually be asking in nearly every real incident. Nobody loses the whole database. They lose one customer’s projects, or one table’s worth of rows from a bad migration, while the other several thousand customers are happily using the system and writing new data. We had never once restored a piece of the database into the live one.

The drill

So on Thursday the 14th, two of us and an engineer from the platform team blocked out the day and tried it with a test tenant we keep in production. The plan on the whiteboard was simple. Do a point-in-time restore of the database to a scratch instance at a time just before the “incident”. Pull out every row belonging to the tenant. Put them back into production.

It took the whole day and we didn’t finish. A short list of what we found:

We have 146 tables. 131 of them have a tenant_id. The other 15 belong to a tenant only through a parent, a join table or a child table, so “every row belonging to the tenant” is a set of queries someone has to write and get right, in the right order, because of foreign keys.

IDs. Most of our primary keys come from sequences, and rows we reinsert keep their old IDs, which is what we want, since other rows point at them. That works as long as the original rows really are gone. If any survived, you get conflicts, and then you’re merging, not restoring.

File attachments live in S3, not the database. We turned on versioning for that bucket years ago, and a deleted object is really just a delete marker on top of the old version, so they can be brought back. Nobody had ever written the script that does it for one tenant’s files.

And there’s the deletion list from GDPR week, the IDs of people who’ve asked to be erased, which our restore procedure re-applies before a restored database serves any traffic. That’s built into the whole-database drill. A partial restore has to apply it too, or we’d bring back someone who asked to be forgotten, and the first version of the runbook forgot it.

By the end of Thursday we had a runbook that was about two-thirds written, and a test tenant that was mostly restored, minus the attachments.

Tuesday

On Tuesday the 19th, at about 11 in the morning, a customer asked support to clear out their archived projects. We have an internal script for that. It takes a list of project IDs. The support engineer who ran it pasted in the ID of the customer’s whole workspace instead, which the script happily accepted, because it didn’t check what kind of ID it had been handed. I’m aware of how that sounds two weeks after Atlassian. It deleted 164 projects, everything the customer had, archived or not, along with tens of thousands of tasks and comments under them.

They noticed within minutes, and so did the support engineer, who was the first person to tell me, and who has nothing to apologize for. The script should never have taken that ID.

We started from the Thursday runbook. Point-in-time restore to two minutes before the script ran. Extract the tenant with the queries we’d written on Thursday, plus the three we found out on Tuesday we’d missed. Check that none of those rows still existed in production (none did, the delete had been thorough). Apply the deletion list, which removed two people from the restored data. Load it inside a transaction, in foreign key order. Restore their attachments by removing the delete markers. Then two of us compared project, task and attachment counts between production and the scratch restore, one by one, before we told them it was back.

They lost nothing they’d entered before the script ran. They lost about ten hours of being able to work, and they were gracious about it, more than we deserved.

What I keep doing the math on

The script now refuses anything that isn’t a project ID, runs as a dry run by default, and won’t delete more than twenty projects without a second person approving it. That part was easy.

The part I don’t have an answer to is this. The runbook works for one tenant, and it took three engineers most of a day to use it, call it 25 engineer-hours. If our version of Atlassian’s script had hit 400 customers, that’s 10,000 engineer-hours, which is about two months of our entire engineering team doing nothing else, and that’s before every customer starts asking where they are in the queue. Atlassian did it for 400 in two weeks with a far bigger team. We’d need the extraction to be automated, and parallel, and tested often enough that it stays true as the schema changes, and I’m not sure yet how much of that a company our size should build for something that has happened to us once.