2

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

5

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.

  • 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 – Asfand Qazi Apr 25 '13 at 9:22
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.