How do you handle "undo" in databases in general, and PostgreSQL specifically?

Question

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?

Answer 1

There is no way to undo things in a PostgreSQL database.

You really need to work on your backup strategy. With a proper online file system backup, you can restore the database to any given point in time, so you need not lose more data than you would with an undo operation. If restoring a backup seems scary to you, that is an indication that you don't have a proper restore procedure implemented, tested and exercised regularly. This is what you should improve.

Of course, restoring a backup is a time consuming affair and can cause substantial down time. If you want something faster to recover from — say — an accidentally dropped table, you could use a standby server with streaming replication and recovery_min_apply_delay enabled. In case of a problem, you could stop replication and let the standby catch up to a point before the problem occurred. However, this only works with problems that you notice pretty quickly, and it is not a substitute for a backup.

Answer 2

shut down the system ... try to restore a recent backup ... I would lose at least about a day's updates to the database.

Your Recovery Point Objective seems to be that you want to minimise data loss after a failure. That's good, but your once-a-day Backups don't meet that objective.
Simply put, you need to take backups more often or, at the very least, ensure that you are safely archiving away the WAL files so that you can perform a Point in Time recovery up to "a minute ago".

it would be very scary and tedious to restore the database, something which I want to do as infrequently as possible.

Absolutely agree! If only we could convince the likes of Developers and Senior Management that this were the case.
You should include "disruptive" in that list as well, because you have to knock out the database get it back, taking down every application that uses it!

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 ...

Less of a "Button", more of a "Toolkit". It's called a Point-in-Time Recovery.
It's still scary and it's still tedious to have to do it, but then you should be rehearsing exactly this kind of thing on a regular basis so that, when you do have to do it "for real", you're reaching for a set of tried and tested procedures and not "panicking" about how to do things.

There is no protection (in any DBMS) from that "Oops" moment when you blat an entire table. That's why nobody should be running bare SQL in Production - ever.

Transactions

You're absolutely right. These can't help you in this case.
They'll just ensure that all of the damage gets done or none of it.