I have a complex PostgreSQL-based system which I made myself from the ground up. It contains tons of invaluable (to me) data which I don't want to lose or get corrupted.
I'm always paranoid about potential bugs which may, for example,
UPDATE every single record in a table instead of a specific one, or other things like that. Any mistake which I would notice shortly after making it, basically. (Not to mention the ones I wouldn't notice until much later... but that's a different question.)
Luckily, that has not yet really happened, but that's the keyword: luckily. It seems like it could happen at any time, by making a small mistake, just once.
If it did happen, I would first panic. Then I'd shut down the system ASAP so it's no longer "live" and messing with the PostgreSQL database, while I think through what to do next. Next, I'd probably try to restore a recent backup. I do keep those. But, inevitably, I would lose at least about a day's updates to the database. And it would be very scary and tedious to restore the database, something which I want to do as infrequently as possible.
I've spent countless hours of my life trying to achieve a more "robust" backup mechanism, where it continuously makes small updates to it, but every time I try to achieve that, I'm overwhelmed and confused by the documentation and online guides to the point where I shamefully go back to my "full dump about once a day" method which I at least am familiar with and which works.
Frequently, I wish there were some way to have an "undo" button which could actually restore the database to how it was just a minute ago, after I make a mistake. Perhaps even on a per-table basis, if that makes logical sense.
You're gonna say something about "transactions", but, well... I don't see how you can notice the error before the change has been "committed". Therein lays the main issue. And PostgreSQL (the only database I use and know anything about) doesn't appear to have a built-in "undo" feature or the ability to revert back to a recent state without fiddling with backups.
Is there something important which I've missed?