Re: planned recovery from a certain transaction

Chris Spotts wrote:
>
> The transaction itself works flawlessly, but every once and awhile the data
> the it uploads from comes in flawed and we have to find a way to reset it.
> This reset involves restoring a backup that was taken right before the proc
> started. If we had the xid of the long running transaction, is there a
> better way to reset it right before that transaction happened? Restoring
> the backup is a lengthy process because several of the tables that are
> affected are rather large.

No way really to "rewind" to a previous transaction (although I believe
the original academic code PostgreSQL is based on could do this sort of
thing).

I'd look at doing a PITR backup (full+WAL) just before the long
transaction is started. Alternatively, if you can afford the downtime
you could just stop the database server and take a snapshot of all the
DB files (as for PITR).

Use of rsync or filesystems that handle snapshots would make both of
these reasonably fast. Restores are just a matter of moving the files /
pointing PG at the backup set and starting it - effectively instant.

--
Richard Huxton
Archonet Ltd