Re: planned recovery from a certain transaction

On Thu, Jun 25, 2009 at 9:40 AM, Chris Spotts<rfusca(at)gmail(dot)com> wrote:
>
>
> We have a database which has one long involved procedure early in the
> morning that updates all sorts of things, moves data around, deletes some
> stuff, alters some DDL - you name it, it does it.  The rest of the day, the
> database is read only.  Autovacuum is not on, it was killing performance for
> it to kick on in the middle of the proc.  Vacuum is run right before the
> long procedure.  We typically wait until the guy onsite verifies the
> procedure went smoothly and then vacuum (Why? Because we’ve read enough of
> “well, you would have been able to restore that if the autovacuum wasn’t
> running”.  We have a backup so this is just a checking before vacuuming is
> technically unneeded.).  Don’t get me wrong, I’m a big autovacuum fan, just
> not for this specific case.
>
>
>
> 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.

Assuming that the data is mostly created from whole cloth each
morning, it might do to have two dbs, and rename one to replace the
other when you're ready. Gives you 20 or so hours to discover a screw
up and still have the backup db before you toss it away to build the
next day's db.