Postgres reverting back

Question

I did some changes to a postgres table and I want to revert it back to a previous state. There is no back up of the database. Is there a way to do it? As in, does postgres take auto snap shots and store it somewhere or the original data is lost forever?

Answer 1

If you don't have a backup, and did an operation on data, I don't think there is much you can do : the database now has your new data, and the old version of it has be replaced/deleted.

The goal of a database engine is to persist the data you store in it -- not the data you removed from it.

For the next time : if you need to try something, use a transaction, and don't commit it until you are sure what you did is OK -- juste beware not to wait for too long before commitint or rollbacking it, because it might lock some stuff, preventing other people from doing queries too.

Answer 2

By default PostgreSQL won't store all of your old data -- that would surprise many people of course. However, it has a built-in Point-in-time Recovery mechanism which does pretty much exactly what you want. You have to keep an archive of "write-ahead log files" which represent the changes made to the database, and you can take periodic base backups. When you want to recover you can recovery to a specific time or even a specific transaction ID.