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?
Add a comment
|
2 Answers
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.
1 Comment
Greg Smith
Just to be clear: this only works if you turn on PITR before you delete the data you might want to restore later. By default the WAL files you need to archive for this to work aren't saved anywhere permanent, you have to set that up first.
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.
1 Comment
Abhi
Thanks Martin. This is not a production table. This is on my local box. So was curious as to whether postgres takes automatic snap shots every hour or day or something like that.
