News for hackers. Stuff that matters.
Re:GitLab’s Secret to Managing Employees in 160 Locations: W... by detaro on Thursday February 09, 2017 @12:25PM (#13608757)
The "trigger" of the failure (accidentally deleting the wrong database) is bad luck that happens, but the lack of preparedness for it isn't. If I understood the document right even if the regular backups would have worked, they only happened every 24 hours (same as the staging replicas they recovered from, except one was manually created out of schedule that day).

Maybe being fully remote helps to let stuff like this "slip through the cracks". (Not great phrasing, but I don't think anyone made a conscious decision "loosing X hours of data is ok", and nobody questioned what goal the current practices could (not) achieve.) I don't know, but it seems at least a question one might ask.

Re:GitLab’s Secret to Managing Employees in 160 Locations: W... by geofft on Thursday February 09, 2017 @12:35PM (#13608870)
What's the best-practice approach for taking backups that are significantly more frequent than every 24 hours, but also robust to things like an rm -rf or a DELETE FROM table;? Something like continuous data protection seems like it would be far too much data for an active database server, no?

(Or are we just saying that they should have been taking backups every 15 minutes or hour or so?)

Re:GitLab’s Secret to Managing Employees in 160 Locations: W... by cookiecaper on Thursday February 09, 2017 @05:50PM (#13611773)
With modern database servers, people will take checkpoints at some regular interval and automatically stream append-only copies of all intermediate transaction logs to a storage server some place (the same transaction logs that the database servers use for replication).

In the event of accidental deletion, the most recent snapshot will be restored and the transactions up to a certain timestamp will be replayed on top of it. If done correctly, this can prevent all but a few minutes of data loss.

AWS Aurora (and possibly other database-as-a-service providers) has this functionality built-in.

Re:GitLab’s Secret to Managing Employees in 160 Locations: W... by detaro on Thursday February 09, 2017 @01:02PM (#13609167)
I'm not really qualified to confidently talk about best practices, so these points have question marks attached, since I can't judge what's possible in their setup and what's not. I guess I'm not sure if they did judge it, see last paragraph.

More frequent backups would of course help, if their impact is tolerable. They also need to be tested to actually exist and work.

At least LVM snapshots apparently are cheap enough that they can be done out-of-schedule just to get slightly newer data to staging, so they likely could have been done more often (but they probably weren't thought of as backups, which is why 24 hours seemed enough and nobody had a prepared plan to get back to production from them).

Similarly, Azure-side snapshots are mentioned as not enabled for the database hosts. Maybe that's not viable to do (they had performance issues with Azure, and I don't know how much overhead they case), maybe just something they forgot to set up.

Others have asked "why is there only one replica", which also seems like a good question (but my experience with database replication is close to non-existent, and maybe the higher load of more replicas would have caused other issues. Don't know.).

My point is more that I don't have the impression that the 24 hours are a figure that they arrived on by evaluating what "service level" they could achieve, but more a result of someone at some point setting up a backup with some interval. I don't think 24 hours is a figure where they can say "that's the best we currently can achieve", or even "that's what we planned for", but "that's what we have because that's what we have and nobody has paid closer attention". There is at the very least a cultural angle to it, which is why how they work could be relevant.

Re:GitLab’s Secret to Managing Employees in 160 Locations: W... by jon-wood on Friday February 10, 2017 @03:43AM (#13614086)
One job I worked in had a slave set up with 30 minutes if replication lag intentionally applied to reduce the impact of someone accidentally nuking a table.

Then as someone else mentioned you can stream your replication log to storage elsewhere giving you the ability to do point in time restores.

Re:GitLab’s Secret to Managing Employees in 160 Locations: W... by problems on Thursday February 09, 2017 @12:41PM (#13608941)
Use that live database replication along with LVM, Btrfs or ZFS snapshotting every hour and purging the old one. Take a full, proper backup every hour if you can or every day if you can't. If you can afford to, do a SQL dump at reduced priority and compress it instead of a binary copy as it's easier to check a text dump.

Anyone with more experience with large databases have anything to add or any concerns with this sort of scheme?

Re:GitLab’s Secret to Managing Employees in 160 Locations: W... by toomuchtodo on Thursday February 09, 2017 @12:37PM (#13608899)
Hourly snapshots that you purge every 24 hours (after the nightly is successfully taken).