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?
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 only takes a minute to sign up.Sign up to join this community
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.
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.