Re: planned recovery from a certain transaction

Chris Spotts escribió:

> 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.

You could mark it aborted in pg_clog, assuming none of the tuples it
touched have been examined by anyone else after it finished. Since you
likely want to crosscheck the data (thus examine it, which sets its hint
bits), it's going to be very hard to do.

Another idea would be to use PITR to restore to the time just before the
transaction, but that's going to be painful too because restoring from a
base backup is going to take long for your big tables.

Lastly, you could use a filesystem snapshot taken just before the long
procedure, to which to revert if you don't like how it went.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support