Re: Saving snapshots for later use

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> On 15.06.2012 06:19, Nikolas Everett wrote:
>> I'd like to be able to save the current snapshot and then at a later date
>> roll the entire database back to that snapshot, essentially erasing
>> everything that happened since the snapshot.

> To revert the database to the earlier state, you'll also need to somehow
> roll back all the already-committed transactions. At first sight, that
> seems easy - just modify clog to mark them as aborted. However, it's not
> that easy, because you'd also need to somehow clear hint bits that claim
> those transactions to be committed.

Not to mention prevent VACUUM from removing rows deleted by those
committed transactions. A saved snapshot of this sort would have to act
like an open transaction from the standpoint of resource reclamation,
which makes it (a) complicated and (b) very expensive if you intend to
hold that snapshot for a long time.

I wonder whether your actual use-case could be solved with 9.2's
exportable-snapshots feature, though.

regards, tom lane