Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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_name Apr 24 '13 at 20:53
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
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 Yar Qazi Apr 25 '13 at 9:22
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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