SproutStackSproutStack home
Case studyIntermediatePostgreSQLGitLab · January 2017

GitLab deletes its production database

Replication breaks under load, a late-night rebuild goes wrong, and the backups turn out not to work.

The setup at GitLab

GitLab.com ran on a primary PostgreSQL server with one streaming replica, used for failover. Several kinds of backup existed on paper: daily pg_dump uploads to S3, Azure disk snapshots, and a daily LVM snapshot used to refresh the staging environment.

How it was configured

primary + 1 streaming replica (PostgreSQL 9.6)
WAL archiving: not configured
backups: daily pg_dump -> S3; Azure disk snapshots; daily LVM snapshot for staging

What you know

  • A replica that falls too far behind needs WAL segments the primary may already have deleted.
  • Rebuilding a replica means wiping its data directory and copying the primary again with pg_basebackup.

What happened next

On January 31, 2017, a load spike (spam plus a heavy background job) made replication fall behind until the primary had removed WAL the replica still needed. Late at night, after several failed attempts to rebuild the replica, an engineer ran the command to wipe the data directory in a terminal connected to the primary instead of the replica.

Make the call

2 questions · first-try answers earn the most XP

1. Replication broke because the primary deleted WAL before the replica read it. What would have let the replica catch up instead of being rebuilt?
2. The team turns to the backups and finds them broken. Which practice would have caught that months earlier?
What actually happened

About 300 GB of data had been removed from the primary before the command was stopped, and the replica's data had already been wiped as part of the rebuild. There was no healthy database left to fail over to.

The backups did not save them. The pg_dump job had been running with PostgreSQL 9.2 tools against a 9.6 database, so it failed every time and the S3 bucket was empty. Azure disk snapshots had not been enabled for the database servers. The only usable copy was the LVM snapshot taken for staging about six hours earlier.

Copying that data from slow network disks took around 18 hours. GitLab.com was restored on February 1 at 17:00 UTC, to the state of January 31 at 17:20 UTC. GitLab estimated it lost at least 5,000 projects, 5,000 comments and about 700 new user accounts created in that window.

GitLab kept a public document of the recovery and streamed it on YouTube, and published a detailed postmortem with a list of improvements.

Sources

Lessons to take away
  • Make destructive commands hard to run on the wrong host: distinct prompts, confirmation steps, and separate credentials for production.
  • Test restores on a schedule and measure how long they take; that number is your real recovery time.
  • Know which backup is for disaster recovery. Staging snapshots and failover replicas are not backups.
  • Tired engineers late at night make mistakes; hand over or stop before running risky steps.
Variations to think through

Change one fact and decide again. There is no single right answer here.

  • Your only recent copy is on slow disks in another region. How do you decide between waiting for it and accepting more data loss?
  • The replica is healthy but a bad migration corrupted data an hour ago. Which backup helps now?
  • How would point-in-time recovery from archived WAL have changed the outcome?

The background

More case studies