Обсуждение: Rollback using WAL files?

Hi all!

First of all I'm new to this list, please be gentle :-) Next I'd like to
mention that I've already searched the documentation and the archives,
but couldn't find the answer to my question.

I'm running a production/development database using PostgreSQL 8.1 on a
Debian server. Due to some bad code in one of our applications who use
this database, some of the data was modified incorrectly the last few
days. The idea is that I would like to restore the entire database as
much as possible, meaning I would like to undo all transactions that
were performed on it.

Now I've found the WAL files in the pg_xlog directory, and started
browsing around for documentation on what I can do with those. But all I
can find is that you can use them to restore your database after a crash
or a custom backup. But I would like to do it the other way around (not
use them to restore a database, but to roll it back entirely a few
days). I have 4 WAL files, from last Tuesday to today. Can I use these
files to ROLLBACK the current database, so that it's restored to the
situation it was in on Tuesday?

Thanks a lot for any help,


Regards,

Marc

On 1/26/07, M.A. Oude Kotte <marc@solcon.nl> wrote:
> Now I've found the WAL files in the pg_xlog directory, and started
> browsing around for documentation on what I can do with those. But all I
> can find is that you can use them to restore your database after a crash
> or a custom backup. But I would like to do it the other way around (not
> use them to restore a database, but to roll it back entirely a few
> days). I have 4 WAL files, from last Tuesday to today. Can I use these
> files to ROLLBACK the current database, so that it's restored to the
> situation it was in on Tuesday?

probably not.  While such things are possible with WAL files, it is
only for a relatively short duration unless your server is extremely
inactive or you planned for this contingency in advance -- by setting
up a a PITR snapshot and archiving as many days WAL files back as you
would like to be able to go back in time.  I'm guessing you didn't do
this.

Next we look at standard dumps (pg_dump) and possibly logs (are you
logging statements?) to recover your data.  If those don't produce
results, and you have no other way of recovering your information,
this has officially become a learning experience :(.

merlin

* M. A. Oude Kotte:

> I'm running a production/development database using PostgreSQL 8.1 on a
> Debian server. Due to some bad code in one of our applications who use
> this database, some of the data was modified incorrectly the last few
> days. The idea is that I would like to restore the entire database as
> much as possible, meaning I would like to undo all transactions that
> were performed on it.

In theory, this should be possible (especially if you haven't switched
off full page writes).  But I don't know a ready-made solution for
this kind of task.

--
Florian Weimer                <fweimer@bfk.de>
BFK edv-consulting GmbH       http://www.bfk.de/
Kriegsstraße 100              tel: +49-721-96201-1
D-76133 Karlsruhe             fax: +49-721-96201-99

Florian Weimer <fweimer@bfk.de> writes:
> In theory, this should be possible (especially if you haven't switched
> off full page writes).

Not really --- the WAL records are not designed to carry full
information about the preceding state of the page, so you can't use them
to undo.  (Example: a DELETE record says which tuple was deleted, but
not what was in it.)

            regards, tom lane

On Jan 26, 9:45 am, t...@sss.pgh.pa.us (Tom Lane) wrote:
> Florian Weimer <fwei...@bfk.de> writes:
> > In theory, this should be possible (especially if you haven't switched
> > off full page writes).Not really --- the WAL records are not designed to carry full
> information about the preceding state of the page, so you can't use them
> to undo.  (Example: a DELETE record says which tuple was deleted, but
> not what was in it.)

It would be really useful if one had the option of allowing the WAL
records to keep track of what was in a tuple as evidenced here.   I use
triggers on every production table to record every change to log tables
(which have rules to prevent deleting and updating).     Allowing the
option of having the  WAL  do this seems like a good idea...

regards,
karen