Postmortem of database outage of January 31
I really hate to pile on, but after reading through this whole thread and the whole post-mortem, there are a few basic things that are troubling besides the widely-acknowledged backup methodology. I don&#x27;t see issues directly related to addressing these things.<p>1. notifications go through regular email. Email should be only one channel used to dispatch notifications of infrastructure events. Tools like VictorOps or PagerDuty should be employed as notification brokers&#x2F;coordinators and notifications should go to email, team chat, and phone&#x2F;SMS if severity warrants, and have an attached escalation policy so that it doesn&#x27;t all hinge on one guy&#x27;s phone not being dead.<p>2. there was a single database, whose performance problems had impacted production multiple times before (the post lists 4 incidents). One such performance problem was contributing to breakage at this very moment. I understand that was the thing that was trying to be fixed here, but what process allowed this to cause 4 outages over the preceding year without moving to the top of the list of things to address? Wouldn&#x27;t it be wise to tweak the PgSQL configuration and&#x2F;or upgrade the server before trying to integrate the hot standby to serve some read-only queries? And since a hot standby can only service reads (and afaik this is not a well-supported option in PgSQL), wouldn&#x27;t most of the performance issues, which appear write-related, remain? The process seriously needs to be reviewed here.<p>And am I reading this right, the one and only production DB server was restarted to change a configuration value in order to try to make pg_basebackup work? What impact did that have on the people trying to use the site a) while the database was restarting, and b) while the kernel settings were tweaked to accommodate the too-high max_connections value? Is it normal for GitLab to cause intermittent, few-minute downtimes like that? Or did that occur while the site was already down?<p>3. Spam reports can cause mass hard deletion of user data? Has this happened to other users? The target in this instance was a GitLab employee. Who has been trolled this way such that performance <i>wasn&#x27;t</i> impacted? What&#x27;s the remedy for wrongly-targeted persons? It&#x27;s clear that backups of this data are not available. And is the GitLab employee&#x27;s data gone now too? How could something so insufficient have been released to the public, and how can you disclose this apparently-unresolved vulnerability? By so doing, you&#x27;re challenging the public to come and try to empty your database. Good thing you&#x27;re surely taking good backups now! (We&#x27;re going to glance over the fact that GitLab just told everyone its logical DB backups are 3 days behind and that we shouldn&#x27;t worry because LVM snapshots now occur hourly, and that it only takes 16 hours to transfer LVM snapshots between environments :) )<p>4. the PgSQL master deleted its WALs within 4 hours of the replica &quot;beginning to lag&quot; (&lt;interrobang here&gt;). That <i>really</i> needs to be fixed. Again, you probably need a serious upgrade to your PgSQL server because it apparently doesn&#x27;t have enough space to hold more than a couple of hours of WALs (unless this was just a naive misconfiguration of the [min|max]_wal_size parameter, like the max_connections parameter?). I understand that transaction logs can get very large, but the disk needs to accommodate (usually a second disk array is used for WALs to ease write impact) and replication lag needs to be monitored and alarmed on.<p>There were a few other things (including someone else downthread who pointed out that your CEO re-revealed your DB&#x27;s hostnames in this write-up, and that they&#x27;re resolvable via public DNS and have running sshds on port 22), but these are the big standouts for me.<p>P.S. bonus point, just speculative:<p>Not sure how fast your disks were, but 300GB gone in &quot;a few seconds&quot; sounds like a stretch. Some data may&#x27;ve been recoverable with some disk forensics. Especially if your Postgres server was running at the time of the deletion, some data and file descriptors also likely could&#x27;ve been extracted from system memory. Linux doesn&#x27;t actually delete files if another process is holding their handle open; you can go into the &#x2F;proc virtual filesystem and grab the file descriptor again to redump the files to live disk locations. Since your database was 400GB and too big to keep 100% in RAM, this probably wouldn&#x27;t have been a full recovery, but it <i>may</i> have been able to provide a partial.<p>The theoretically best thing to do in such a situation would probably be to unplug the machine ASAP after ^C (<i>without</i> going through formal shutdown processes that may try to &quot;clean up&quot; unfinished disk work), remove the disk, attach it to a machine with a write blocker, and take a full-disk image for forensics purposes. This would maximize the ability to extract any data that the system was unable to eat&#x2F;destroy.<p>In theory, I believe pulling the plug while a process kept the file descriptor open should keep you in reasonably good shape, as far as that goes after you&#x27;ve accidentally deleted 3&#x2F;4 of your production database. The process never closes and the disk stops and the contents remain on disk, just pending unlink when the OS stops the process (this is one reason why it&#x27;d be important to block writes to the disk&#x2F;be extremely careful while mounting; if the journal plays back, it may destroy these files on the next boot anyway). But someone more familiar with the FS internals would have to say definitively if it works that way or not.<p>I recognize that such speculative&#x2F;experimental recovery measures may have been intentionally forgone since they&#x27;re labor intensive, may have delayed the overall recovery, and very possibly wouldn&#x27;t have returned useful data anyway. Mentioning it mainly as an option to remain aware of.