4 ms·
Early in one of my companies, we had a cloud hosted database provider that only provided (at our meager level of spend) 7 days of daily backups for the main db.
by 015a 4y ago
Early in one of my companies, we had a cloud hosted database provider that only provided (at our meager level of spend) 7 days of daily backups for the main db. We had a specific customer, one of maybe ten, and a higher percentage of our revenue, who made an big bulk import oopsie-whoopsie; and given the specifics of this software product, did not notice for weeks. They wanted everything back to the way it was.
We ended up being saved by our traditionally "crappy" local dev process; me and the other dev had copies of the prod db on our development machines. And, naturally, we rarely updated them; the last time I had sync'ed it down was, conveniently, about four weeks ago. So they had some data loss between that "backup" and the oopsie-whoopsie, but far less major than their issues with the current state.
The most interesting thing to really come out of that was a set of tooling to precision restore an individual customer's data from a backup of this multi-tenanted database. That was something I see as pretty rare for engineers to consider in their backup strategy; you may have the backup, it may be easily restored, but it will take the world with it and maybe you don't want the whole world. We ended up using that a half dozen or more times over the next few years... against a far more comprehensive set of backups of course. Yeah, maybe it makes sense to store a history of every change made to every row; but for our needs it made a lot more sense to leverage backups and some glue scripts.
- bombcar 4y agoOne of the important considerations of database design is “what might I want to restore” and there are often way more answers than just “how it was at a point in time”. And some of those restores should happen in the application layer not the db backup.
- silvestrov 4y ago100% of my restores have been due to user/developer fuckups. 0% has been because of hardware failures. This is why I love that PostgreSQL backup files are plain text files. It is so easy to extract a single table (or parts of a table) using a simple perl/python/whatever script.
- andrewf 4y agoSounds like the thing Atlassian had to build while on fire this year.
- berjin 4y agoIf you're going to pay for 7 backups I wonder if it'd be better space them out a bit. You could keep 1x 12 hourly, 2x daily, 2x weekly, 1x monthly etc. Or just use some form of event sourcing so your DB becomes a materialized view you can restore. These days devs often aren't allowed to have copies of sensitive customer data.
- amelius 4y agoSuch tape rotation schemes are quite common. There is even a tower-of-Hanoi tape rotation scheme: https://www.techtarget.com/searchdatabackup/tip/An-introduction-to-data-backup-tape-rotation-schemes https://www.techtarget.com/searchdatabackup/tip/An-introduct...
- throw0101c 4y agoThe tables here do a better job of illustrating / explaining it IMHO: * https://en.wikipedia.org/wiki/Backup_rotation_scheme#Tower_of_Hanoi https://en.wikipedia.org/wiki/Backup_rotation_scheme#Tower_o...