Is there some kind of "undo" or "rewind" feature in PostgreSQL?

Question

Although it has not actually happened in quite a long time, every day, I fear that I will make a mistake such as accidentally deleting the wrong table, or deleting too many records, or doing some kind of mistake which I immediately realize afterwards.

In such situations, it would be wonderful to have some kind of "undo" or "rewind" feature.

I'm of course not talking about BEGIN...COMMIT/ROLLBACK. I'm talking about something which, I guess, would keep track of everything done in the last X minutes and offer you to "rewind" or "undo" the database to the state it was recently, preferably in some user-friendly manner such as allowing you to fetch a list of recent "commits" and "un-commit" them at a given point.

Naturally, I have backups, but they are only daily. If I were to make a fatal mistake right now, I might be able to revert to a backup which is less than 24 hours old, BUT that means I will lose all the changes since then, and it's anything but crystal-clear how I would do this. I basically would first panic, then shut down my system (including PostgreSQL), then start only PG back up again, and... restore from my backups... It's foggy at best, but I could do it.

Imagine if, instead, I only had to execute something like:

REWIND '5 minutes';

And then PG would automatically put itself back to how everything was only 5 minutes ago!

That would be so sweet. And even if I rarely had to use it, just knowing that it exists would be such a relief. Basically, like knowing that there are life boats on the cruise ship, versus realizing that there are none and that you will definitely go down with the ship if it hits an ice-berg.

To make it clear, this would be one level "above" (or is it underneath?) the concept of "transactions". Transactions would happen just like now, "inside" of this security system/framework.

If no such thing exists, what is the best solution/workaround other than "always be super careful and simply don't make mistakes"? It is not practical for me to make backups more often than once a day, and gradual/partial backups, well, they are frankly a big mystery to me and I've always given up on them each time I've tried to read up on how to set them up and use them.

PS: I did find a very old, kind of similar question, but it had virtually no answers and none that were useful, and things may have changed since 7+ years ago.

• Vérace: Lookup AS OF queries! Check out the wiki page on temporal databases!

Answer 1

What you are asking for doesn't exist. I think that if you regularly have to run interactive SQL statements manually in an important database, there is something wrong with your operational procedures.

The best we can offer is:

• online backups with point-in-time recovery and an automated restore procedure

• set autocommit to off in your favorite interactive client

  This is somewhat dangerous, as long transactions threaten the health of your database. Make sure to set idle_in_transaction_timeout appropriately on the server.

Answer 2

From Comments:

This is usually referred as "point in time recovery". See a rather old article: https://pgdash.io/blog/postgres-incremental-backup-recovery.html. This procedure would not actually "rewind" but restore to a previous backup and then "replay" up to your wanted point in time. See also e-maj which offers something similar to what you want. Mentioned in this answer: selective undoing of commited transactions in PostgreSQL - users/993

Answer 3

such time reversal would violate the ACID property (https://www.ibm.com/docs/en/cics-ts/5.4?topic=processing-acid-properties-transactions ), therefore it is NOT implemented in traditional SQL databases. However, there are some tricks and practices, such as those described above, that can be used as alternatives to "time reversal".

• user1822: This feature is actually part of the SQL standard (since 2011) and is called "temporal tables", where you can e.g. run something along the lines of select * from some_table as of timestamp '2021年10月11日 17:45:00'. This feature is available in SQL Server, Oracle and I think MariaDB. So this contradicts your statement that this feature is not implemented in traditional SQL databases.
• YW T: there is a difference between a backup copy (which, once created, exists outside an ACID compliant database), and a true-time reversal of the SAME database. users/933 gave correct answer. I just wanted to clarify the difference.