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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
