Postmortem of database outage of January 31

On January 31st 2017, we experienced a major service outage for one of our products, the online service GitLab.com. The outage was caused by an accidental removal of data from our primary database server.

This incident caused the GitLab.com service to be unavailable for many hours. We also lost some production data that we were eventually unable to recover. Specifically, we lost modifications to database data such as projects, comments, user accounts, issues and snippets, that took place between 17:20 and 00:00 UTC on January 31. Our best estimate is that it affected roughly 5,000 projects, 5,000 comments and 700 new user accounts. Code repositories or wikis hosted on GitLab.com were unavailable during the outage, but were not affected by the data loss. GitLab Enterprise customers, GitHost customers, and self-managed GitLab CE users were not affected by the outage, or the data loss.

Losing production data is unacceptable. To ensure this does not happen again we're working on multiple improvements to our operations & recovery procedures for GitLab.com. In this article we'll look at what went wrong, what we did to recover, and what we'll do to prevent this from happening in the future.

To the GitLab.com users whose data we lost and to the people affected by the outage: we're sorry. I apologize personally, as GitLab's CEO, and on behalf of everyone at GitLab.

Database setup

GitLab.com currently uses a single primary and a single secondary in hot-standby
mode. The standby is only used for failover purposes. In this setup a single
database has to handle all the load, which is not ideal. The primary's hostname
is db1.cluster.gitlab.com, while the secondary's hostname is
db2.cluster.gitlab.com.

In the past we've had various other issues with this particular setup due to
db1.cluster.gitlab.com being a single point of failure. For example:

• A database outage on November 28th, 2016 due to project_authorizations having too much bloat
• CI distributed heavy polling and exclusive row locking for seconds takes GitLab.com down
• Scary DB spikes

Timeline

On January 31st an engineer started setting up multiple PostgreSQL servers in
our staging environment. The plan was to try out
pgpool-II to see if it
would reduce the load on our database by load balancing queries between the
available hosts. Here is the issue for that plan:
infrastructure#259.

± 17:20 UTC: prior to starting this work, our engineer took an LVM snapshot
of the production database and loaded this into the staging environment. This was
necessary to ensure the staging database was up to date, allowing for more
accurate load testing. This procedure normally happens automatically once every
24 hours (at 01:00 UTC), but they wanted a more up to date copy of the
database.

± 19:00 UTC: GitLab.com starts experiencing an increase in database load due
to what we suspect was spam. In the week leading up to this event GitLab.com had
been experiencing similar problems, but not this severe. One of the problems
this load caused was that many users were not able to post comments on issues
and merge requests. Getting the load under control took several hours.

We would later find out that part of the load was caused by a background job
trying to remove a GitLab employee and their associated data. This was the
result of their account being flagged for abuse and accidentally scheduled for removal. More information regarding this particular problem can be found in the
issue "Removal of users by spam should not hard
delete".

± 23:00 UTC: Due to the increased load, our PostgreSQL secondary's
replication process started to lag behind. The replication failed as WAL
segments needed by the secondary were already removed from the primary. As
GitLab.com was not using WAL archiving, the secondary had to be re-synchronised
manually. This involves removing the
existing data directory on the secondary, and running
pg_basebackup
to copy over the database from the primary to the secondary.

One of the engineers went to the secondary and wiped the data directory, then
ran pg_basebackup. Unfortunately pg_basebackup would hang, producing no
meaningful output, despite the --verbose option being set. After a few tries
pg_basebackup mentioned that it could not connect due to the master not having
enough available replication connections (as controlled by the max_wal_senders
option).

To resolve this our engineers decided to temporarily increase
max_wal_senders from the default value of 3 to 32. When applying the
settings, PostgreSQL refused to restart, claiming too many semaphores were being
created. This can happen when, for example, max_connections is set too high. In
our case this was set to 8000. Such a value is way too high, yet it had been
applied almost a year ago and was working fine until that point. To resolve this
the setting's value was reduced to 2000, resulting in PostgreSQL restarting
without issues.

Unfortunately this did not resolve the problem of pg_basebackup not starting
replication immediately. One of the engineers decided to run it with strace to
see what it was blocking on. strace showed that pg_basebackup was hanging in
a poll call, but that did not provide any other meaningful information that might
have explained why.

± 23:30 UTC: one of the engineers thinks that perhaps pg_basebackup
created some files in the PostgreSQL data directory of the secondary during the
previous attempts to run it. While normally pg_basebackup prints an error when
this is the case, the engineer in question wasn't too sure what was going on. It
would later be revealed by another engineer (who wasn't around at the time) that
this is normal behaviour: pg_basebackup will wait for the primary to start
sending over replication data and it will sit and wait silently until that time.
Unfortunately this was not clearly documented in our engineering
runbooks nor in the official
pg_basebackup document.

Trying to restore the replication process, an engineer proceeds to wipe the
PostgreSQL database directory, errantly thinking they were doing so on the
secondary. Unfortunately this process was executed on the primary instead. The
engineer terminated the process a second or two after noticing their mistake,
but at this point around 300 GB of data had already been removed.

Hoping they could restore the database the engineers involved went to look for
the database backups, and asked for help on Slack. Unfortunately the process of
both finding and using backups failed completely.

Broken recovery procedures

This brings us to the recovery procedures. Normally in an event like this, one
should be able to restore a database in relatively little time using a recent
backup, though some form of data loss can not always be prevented. For
GitLab.com we have the following procedures in place:

1. Every 24 hours a backup is generated using pg_dump, this backup is uploaded
   to Amazon S3. Old backups are automatically removed after some time.
2. Every 24 hours we generate an LVM snapshot of the disk storing the production
   database data. This snapshot is then loaded into the staging environment,
   allowing us to more safely test changes without impacting our production
   environment. Direct access to the staging database is restricted, similar to
   our production database.
3. For various servers (e.g. the NFS servers storing Git data) we use Azure disk
   snapshots. These snapshots are taken once per 24 hours.
4. Replication between PostgreSQL hosts, primarily used for failover purposes
   and not for disaster recovery.

At this point the replication process was broken and data had already been wiped
from both the primary and secondary, meaning we could not restore from either
host.

Database backups using pg_dump

When we went to look for the pg_dump backups we found out they were not there.
The S3 bucket was empty, and there was no recent backup to be found anywhere.
Upon closer inspection we found out that the backup procedure was using
pg_dump 9.2, while our database is running PostgreSQL 9.6 (for Postgres, 9.x
releases are considered major). A difference in major versions results in
pg_dump producing an error, terminating the backup procedure.

The difference is the result of how our Omnibus package works. We currently
support both PostgreSQL 9.2 and 9.6, allowing users to upgrade (either manually
or using commands provided by the package). To determine the correct version to
use the Omnibus package looks at the PostgreSQL version of the database cluster
(as determined by $PGDIR/PG_VERSION, with $PGDIR being the path to the data
directory). When PostgreSQL 9.6 is detected Omnibus ensures all binaries use
PostgreSQL 9.6, otherwise it defaults to PostgreSQL 9.2.

The pg_dump procedure was executed on a regular application server, not the
database server. As a result there is no PostgreSQL data directory present on
these servers, thus Omnibus defaults to PostgreSQL 9.2. This in turn resulted in
pg_dump terminating with an error.

While notifications are enabled for any cronjobs that error, these notifications
are sent by email. For GitLab.com we use DMARC.
Unfortunately DMARC was not enabled for the cronjob emails, resulting in them
being rejected by the receiver. This means we were never aware of the backups
failing, until it was too late.

Azure disk snapshots

Azure disk snapshots are used to generate a snapshot of an entire disk. These
snapshots don't make it easy to restore individual chunks of data (e.g. a lost
user account), though it's possible. The primary purpose is to restore entire
disks in case of disk failure.

In Azure a snapshot belongs to a storage account, and a storage account in turn
is linked to one or more hosts. Each storage account has a limit of roughly 30
TB. When restoring a snapshot using a host in the same storage account, the
procedure usually completes very quickly. However, when using a host in a
different storage account the procedure can take hours if not days to complete.
For example, in one such case it took over a week to restore a snapshot. As a
result we try not to rely on this system too much.

While enabled for the NFS servers, these snapshots were not enabled for any of
the database servers as we assumed that our other backup procedures were
sufficient enough.

LVM snapshots

The LVM snapshots are primarily used to easily copy data from our production
environment to our staging environment. While this process was working as
intended, the produced snapshots are not really meant to be used for disaster
recovery. At the time of the outage we had two snapshots available:

1. A snapshot created for our staging environment every 24 hours, almost 24
   hours before the outage happened.
2. A snapshot created manually by one of the engineers roughly 6 hours before
   the outage.

When we generate a snapshot the following steps are taken:

1. Generate a snapshot of production.
2. Copy the snapshot to staging.
3. Create a new disk using this snapshot.
4. Remove all webhooks from the resulting database, to prevent them from being
   triggered by accident.

Recovering GitLab.com

To recover GitLab.com we decided to use the LVM snapshot created 6 hours before
the outage, as it was our only option to reduce data loss as much as possible
(the alternative was to lose almost 24 hours of data). This process would
involve the following steps:

1. Copy the existing staging database to production, which would not contain any
   webhooks.
2. In parallel, copy the snapshot used to set up the database as this snapshot
   might still contain the webhooks (we weren't entirely sure).
3. Set up a production database using the snapshot from step 1.
4. Set up a separate database using the snapshot from step 2.
5. Restore webhooks using the database set up in the previous step.
6. Increment all database sequences by 100,000 so one can't re-use IDs that
   might have been used before the outage.
7. Gradually re-enable GitLab.com.

For our staging environment we were using Azure classic, without Premium Storage.
This is primarily done to save costs as premium storage is quite expensive. As a
result the disks are very slow, resulting in them being the main bottleneck in
the restoration process. Because LVM snapshots are stored on the hosts they are
taken for we had two options to restore data:

1. Copy over the LVM snapshot
2. Copy over the PostgreSQL data directory

In both cases the amount of data to copy would be roughly the same. Since
copying over and restoring the data directory would be easier we decided to go
with this solution.

Copying the data from the staging to the production host took around 18 hours. These disks are network disks and are throttled to a really low number (around 60Mbps), there is no way to move from cheap storage to premium, so this was the performance we would get out of it. There was no network or processor bottleneck, the bottleneck was in the drives.
Once copied we were able to restore the database (including webhooks) to the
state it was at January 31st, 17:20 UTC.

On February 1st at 17:00 UTC we managed to restore the GitLab.com database
without webhooks. Restoring webhooks was done by creating a separate staging
database using the LVM snapshot, but without triggering the removal of webhooks.
This allowed us to generate a SQL dump of the table and import this into the
restored GitLab.com database.

Around 18:00 UTC we finished the final restoration procedures such as restoring
the webhooks and confirming everything was operating as expected.

Publication of the outage

In the spirit of transparency we kept track of progress and notes in a
publicly visible Google document.
We also streamed the recovery procedure on YouTube, with a peak viewer count of
around 5000 (resulting in the stream being the #2 live stream on YouTube for
several hours). The stream was used to give our users live updates about the
recovery procedure. Finally we used Twitter (https://twitter.com/gitlabstatus)
to inform those that might not be watching the stream.

The document in question was initially private to GitLab employees and contained
name of the engineer who accidentally removed the data. While the name was added
by the engineer themselves (and they had no problem with this being public), we
will redact names in future cases as other engineers may not be comfortable with
their name being published.

Data loss impact

Database data such as projects, issues, snippets, etc. created between January
31st 17:20 UTC and 23:30 UTC has been lost. Git repositories and Wikis were not
removed as they are stored separately.

It's hard to estimate how much data has been lost exactly, but we estimate we
have lost at least 5000 projects, 5000 comments, and roughly 700 users. This
only affected users of GitLab.com, self-managed instances or GitHost instances
were not affected.

Impact on GitLab itself

Since GitLab uses GitLab.com to develop GitLab the outage meant that for some it
was harder to get work done. Most developers could continue working using their
local Git repositories, but creating issues and such had to be delayed. To
publish the blog post "GitLab.com Database
Incident"
we used a private GitLab instance we normally use for private/sensitive
workflows (e.g. security releases). This allowed us to build and deploy a new
version of the website while GitLab.com was unavailable.

We also have a public monitoring website located at
https://dashboards.gitlab.com/. Unfortunately the current setup for this website
was not able to handle the load produced by users using this service during the
outage. Fortunately our internal monitoring systems (which dashboards.gitlab.com is
based on) were not affected.

Root cause analysis

To analyse the root cause of these problems we'll use a technique called "The 5
Whys". We'll break up the incident into 2
main problems: GitLab.com being down, and it taking a long time to restore
GitLab.com.

Problem 1: GitLab.com was down for about 18 hours.

1. Why was GitLab.com down? - The database directory of the primary database
   was removed by accident, instead of removing the database directory of the
   secondary.
2. Why was the database directory removed? - Database replication stopped,
   requiring the secondary to be reset/rebuilt. This in turn requires that the
   PostgreSQL data directory is empty. Restoring this required manual work as
   this was not automated, nor was it documented properly.
3. Why did replication stop? - A spike in database load caused the database
   replication process to stop. This was due to the primary removing WAL
   segments before the secondary could replicate them.
4. Why did the database load increase? - This was caused by two events
   happening at the same time: an increase in spam, and a process trying to
   remove a GitLab employee and their associated data.
5. Why was a GitLab employee scheduled for removal? - The employee was
   reported for abuse by a troll. The current system used for responding to
   abuse reports makes it too easy to overlook the details of those reported. As
   a result the employee was accidentally scheduled for removal.

Problem 2: restoring GitLab.com took over 18 hours.

1. Why did restoring GitLab.com take so long? - GitLab.com had to be
   restored using a copy of the staging database. This was hosted on slower
   Azure VMs in a different region.
2. Why was the staging database needed for restoring GitLab.com? - Azure
   disk snapshots were not enabled for the database servers, and the periodic
   database backups using pg_dump were not working.
3. Why could we not fail over to the secondary database host? - The
   secondary database's data was wiped as part of restoring database
   replication. As such it could not be used for disaster recovery.
4. Why could we not use the standard backup procedure? - The standard backup
   procedure uses pg_dump to perform a logical backup of the database. This
   procedure failed silently because it was using PostgreSQL 9.2, while
   GitLab.com runs on PostgreSQL 9.6.
5. Why did the backup procedure fail silently? - Notifications were
   sent upon failure, but because of the Emails being rejected there was no
   indication of failure. The sender was an automated process with no other
   means to report any errors.
6. Why were the Emails rejected? - Emails were rejected by the receiving
   mail server due to the Emails not being signed using DMARC.
7. Why were Azure disk snapshots not enabled? - We assumed our other backup
   procedures were sufficient. Furthermore, restoring these snapshots can take
   days.
8. Why was the backup procedure not tested on a regular basis? - Because
   there was no ownership, as a result nobody was responsible for testing this
   procedure.

Improving recovery procedures

We are currently working on fixing and improving our various recovery
procedures. Work is split across the following issues:

1. Overview of status of all issues listed in this blog post (#1684)
2. Update PS1 across all hosts to more clearly differentiate between hosts and environments (#1094)
3. Prometheus monitoring for backups (#1095)
4. Set PostgreSQL's max_connections to a sane value (#1096)
5. Investigate Point in time recovery & continuous archiving for PostgreSQL (#1097)
6. Hourly LVM snapshots of the production databases (#1098)
7. Azure disk snapshots of production databases (#1099)
8. Move staging to the ARM environment (#1100)
9. Recover production replica(s) (#1101)
10. Automated testing of recovering PostgreSQL database backups (#1102)
11. Improve PostgreSQL replication documentation/runbooks (#1103)
12. Investigate pgbarman for creating PostgreSQL backups (#1105)
13. Investigate using WAL-E as a means of Database Backup and Realtime Replication (#494)
14. Build Streaming Database Restore
15. Assign an owner for data durability

We are also working on setting up multiple secondaries and balancing the load
amongst these hosts. More information on this can be found at:

• Bundle pgpool-II 3.6.1 (!1251)
• Connection pooling/load balancing for PostgreSQL (#259)

Our main focus is to improve disaster recovery, and making it more obvious as to
what host you're using; instead of preventing production engineers from running
certain commands. For example, one could alias rm to something safer but in
doing so would only protect themselves against accidentally running rm -rf /important-data, not against disk corruption or any of the many other ways you
can lose data.

An ideal environment is one in which you can make mistakes but easily and
quickly recover from them with minimal to no impact. This in turn requires you
to be able to perform these procedures on a regular basis, and make it easy to
test and roll back any changes. For example, we are in the process of setting up
procedures that allow developers to test their database migrations. More
information on this can be found in the issue
"Tool for executing and reverting Rails migrations on staging".

We're also looking into ways to build better recovery procedures for the entire
GitLab.com infrastructure, and not just the database; and to ensure there is
ownership of these procedures. The issue for this is
"Disaster recovery for everything that is not the database".

Monitoring wise we also started working on a public backup monitoring dashboard,
which can be found at https://dashboards.gitlab.com/dashboard/db/postgresql-backups.
Currently this dashboard only contains data of our pg_dump backup procedure,
but we aim to add more data over time.

One might notice that at the moment our pg_dump backups are 3 days old. We
perform these backups on a secondary as pg_dump can put quite a bit of
pressure on a database. Since we are in the process of rebuilding our
secondaries the pg_dump backup procedure is suspended for the time being. Fear
not however, as LVM snapshots are now taken every hour instead of once per 24
hours. Enabling Azure disk snapshots is something we're still looking into.

Finally, we're looking into improving our abuse reporting and response system.
More information regarding this can be found in the issue
"Removal of users by spam should not hard delete".

If you think there are additional measures we can take to prevent incidents like this please let us know in the comments.

Troubleshooting FAQ

Some of my merge requests are shown as being open, but their commits have already been merged into the default branch. How can I resolve this?

Pushing to the default branch will automatically update the merge request so
that it's aware of there not being any differences between the source and target
branch. At this point you can safely close the merge request.

My merge request has not yet been merged, and I am not seeing my changes. How can I resolve this?

There are 3 options to resolve this:

1. Close the MR and create a new one
2. Push new changes to the merge request's source branch
3. Rebase/amend, and force push to the merge request's source branch

My GitLab Pages website was not updated. How can I solve this?

Go to your project, then "Pipelines", "New Pipeline", use "master" as the
branch, then create the pipeline. This will create and start a new pipeline
using your master branch, which should result in your website being updated.

My Pipelines were not executed

Most likely they were, but the database is not aware of this. To solve this,
create a new pipeline using the right branch and run it.

Some commits are not showing up

Pushing new commits should automatically solve this. Alternatively you can try
force pushing to the target branch.

I created a project after 17:20 UTC and it shows up, but my issues are gone. What happened?

Project details are stored in the database. This meant that this data was lost
for projects created after 17:20. We ran a procedure to restore these
projects based on their Git repositories that were still stored in our NFS
cluster. This procedure however was only able to restore projects in their most
basic form, without associated data such as issues and merge requests.