I have heard that Postgresql uses an append-only format for it's databases, and if this is true I should be able to 'rewind' the database to a previous point in time by removing the commits that came after it.
Anyone know how I can do this?
Thanks
1
-
You could use point in time recovery to restore a backup to a specific time: postgresql.org/docs/current/static/continuous-archiving.html– a_horse_with_no_nameApr 24, 2013 at 20:53
Add a comment
|
2 Answers
At one point, this was in principle possible. All you had to do in theory was to reset the current transaction ID, and you would see a previous state of the database. Search for "time travel" in very old documents.
That, however, was before WAL, HOT, lazy vacuum, freespace map, virtual transaction IDs, and all the other goodies that were introduced among other things to work around the drawbacks of the append-only system for the majority of applications that don't need to look back into the past. Now, the system is more like append-mostly-but-clean-up-old-stuff-as-quickly-as-possible.
So, if you want to look back at an old state of the database, you need to use point-in-time recovery, which is a log-based mechanism and has nothing to do with the nature of the storage management.
answered Apr 24, 2013 at 21:02
-
Hmm... I think I'll use the solution you posted on another question (dba.stackexchange.com/a/3447/22979) - create a new database based on the old one, then throw it away if I don't need it. Not quite what I was looking for but it'll work Apr 25, 2013 at 9:22
I think the only solution you have is to create a second database that receive updates from the first one, but use them keeping some "time distance" from the first one. The second database should be configured as a hot standby database. You may choose how much delayed should be the second database, and start feeding it based on this. The trick is to transfer all WAL files required for the standby replication, but store them elsewhere until the delay is off.
