Re: planned recovery from a certain transaction - Mailing list pgsql-general

From Richard Huxton
Subject Re: planned recovery from a certain transaction
Date
Msg-id 4A439F1D.8010400@archonet.com
Whole thread Raw
In response to planned recovery from a certain transaction  ("Chris Spotts" <rfusca@gmail.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Dave Page
Date:
Subject: Re: Vacuum on the database versus individual tables.
Next
From: Merlin Moncure
Date:
Subject: Re: Vacuum on the database versus individual tables.