Обсуждение: question about wal and point in time recovery

hello all,

i work in an organization that has a handful of people accessing a few ms
access database files.  i'm strongly considering porting everything to
postgre (as the backend for access) for a number of obvious reasons, one of
the most prominent ones would be the availability of a back up via the write
ahead log.  i've been reading up on the wal and point in time recovery in
the online postgre manual. but i have a question about using it:

is it possible to visually read the entries in the log? is it in binary
format? can the log be modified before recovery occurs.  my biggest concern
is that somebody in my org, for example, deletes a field or fudges an update
query that changes a foreign key to a single value - and therefore destroys
table links.   so what if this happens and nobody notices it for several
weeks?  is it possible to read the log and remove this field delete (i.e.,
remove the 'alter table x drop column y' statement)?  or in the update the
scenario, find the statement that reads 'update table x set x.y = "corrupt"'
and then remove it from the log?  then can we 'replay' the log and restore
our database, sans the destructive statements?

i am aware of the possibility of data corruption when attempting something
like this, especially when multi-changes transactions were used.  i don't
intend to use the wal and pitr in these cases.

.....anything else you feel that i should know about using postgre as a back
end for access?

thanks!

az

"Alex Zendel" <alexzendel@hotmail.com> writes:
> ... i've been reading up on the wal and point in time recovery in
> the online postgre manual. but i have a question about using it:

[ obligatory gripe: it's postgres or postgresql, not postgre ]

> is it possible to visually read the entries in the log?

Not readily.  If you find yourself backed into a corner, I have a
rough-and-ready debug tool for printing out the contents of a WAL
log, but I'd surely not want to call it "supported" in its current
form...

> can the log be modified before recovery occurs.

Not easily.  If you were to hack any one log entry, you'd have to
recompute the CRC checksum for it, and there's no way at all to make
a change that would involve changing the size of a log entry.  So
really the only useful option you have here is to stop recovery short
of a particular log entry you don't like.

> my biggest concern is that somebody in my org, for example, deletes a
> field or fudges an update query that changes a foreign key to a single
> value - and therefore destroys table links.  so what if this happens
> and nobody notices it for several weeks?

There is no automatic recovery mechanism that is going to help you in
such a scenario.  You can't expect to undo a long-ago change and not
have that break some unknown number of consequent decisions.

            regards, tom lane

Tom,

Is your (?) tool publically available?

<awkward pause>

Maybe I could have phrased that better....<sigh>


In any case, do you have a pointer to this R&R WAL tool?

Thanks,

Ross

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Wednesday, April 13, 2005 12:29 AM
To: Alex Zendel
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] question about wal and point in time recovery


"Alex Zendel" <alexzendel@hotmail.com> writes:
> ... i've been reading up on the wal and point in time recovery in
> the online postgre manual. but i have a question about using it:

[ obligatory gripe: it's postgres or postgresql, not postgre ]

> is it possible to visually read the entries in the log?

Not readily.  If you find yourself backed into a corner, I have a rough-and-ready debug tool for printing out the
contentsof a WAL log, but I'd surely not want to call it "supported" in its current form... 

> can the log be modified before recovery occurs.

Not easily.  If you were to hack any one log entry, you'd have to recompute the CRC checksum for it, and there's no way
atall to make a change that would involve changing the size of a log entry.  So really the only useful option you have
hereis to stop recovery short of a particular log entry you don't like. 

> my biggest concern is that somebody in my org, for example, deletes a
> field or fudges an update query that changes a foreign key to a single
> value - and therefore destroys table links.  so what if this happens
> and nobody notices it for several weeks?

There is no automatic recovery mechanism that is going to help you in such a scenario.  You can't expect to undo a
long-agochange and not have that break some unknown number of consequent decisions. 

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

"Mohan, Ross" <RMohan@arbinet.com> writes:
> In any case, do you have a pointer to this R&R WAL tool?

I haven't posted it anywhere, but here is the 8.0 version.  You'd build
it something like this:

PGSQL=(location of PG source tree)
CC=gcc
CFLAGS=-Wall -O -I $(PGSQL)/src/include

xlogdump: xlogdump.c
    $(CC) $(CFLAGS) -o xlogdump xlogdump.c $(PGSQL)/src/backend/utils/hash/pg_crc.c

It needs improvement to understand more WAL record types, but what it
does has been sufficient for my debugging needs to date.  Ideally
someone would clean it up and make it into a contrib module so it could
get into the source tree and be maintained properly ...

            regards, tom lane

Thank you!

( would send this to yer eddress directly, but it's decided
   my biz email is a spam address, or somesuch:
 <imail01.arbinet.com #5.7.1 SMTP; 550 5.7.1 Probable spam from 64.74.47.121
  refused - see http://www.five-ten-sg.com/blackhole.php? )


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, April 13, 2005 10:26 AM
To: Mohan, Ross
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] question about wal and point in time recovery


"Mohan, Ross" <RMohan@arbinet.com> writes:
> In any case, do you have a pointer to this R&R WAL tool?

I haven't posted it anywhere, but here is the 8.0 version.  You'd build it something like this:

PGSQL=(location of PG source tree)
CC=gcc
CFLAGS=-Wall -O -I $(PGSQL)/src/include

xlogdump: xlogdump.c
    $(CC) $(CFLAGS) -o xlogdump xlogdump.c $(PGSQL)/src/backend/utils/hash/pg_crc.c

It needs improvement to understand more WAL record types, but what it does has been sufficient for my debugging needs
todate.  Ideally someone would clean it up and make it into a contrib module so it could get into the source tree and
bemaintained properly ... 

            regards, tom lane

Zitat von "Mohan, Ross" <RMohan@arbinet.com>:

>
> Thank you!
>
> ( would send this to yer eddress directly, but it's decided
>   my biz email is a spam address, or somesuch:
> <imail01.arbinet.com #5.7.1 SMTP; 550 5.7.1 Probable spam from 64.74.47.121
>  refused - see http://www.five-ten-sg.com/blackhole.php? )

I guess it is the IP you are sending from :

http://openrbl.org/ip/64/74/47/121.htm


Regards

Andreas

Tom Lane wrote:
> > can the log be modified before recovery occurs.
>
> Not easily.  If you were to hack any one log entry, you'd have to
> recompute the CRC checksum for it, and there's no way at all to make
> a change that would involve changing the size of a log entry.  So
> really the only useful option you have here is to stop recovery short
> of a particular log entry you don't like.
>
> > my biggest concern is that somebody in my org, for example, deletes a
> > field or fudges an update query that changes a foreign key to a single
> > value - and therefore destroys table links.  so what if this happens
> > and nobody notices it for several weeks?
>
> There is no automatic recovery mechanism that is going to help you in
> such a scenario.  You can't expect to undo a long-ago change and not
> have that break some unknown number of consequent decisions.

I think it might be easier to somehow hack the binary that reads the WAL
file on recovery to adjust how it handles a certain WAL entry, rather
than modifying the WAL files itself.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073