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



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: question about wal and point in time recovery

"Alex Zendel" 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

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

Re: question about wal and point in time recovery

Tom,=20

Is your (?) tool publically available?



Maybe I could have phrased that better....


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

Thanks,=20

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=20


"Alex Zendel" 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 surel=
y 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 w=
ould involve changing the size of a log entry. So really the only useful o=
ption 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=20
> field or fudges an update query that changes a foreign key to a single=20
> value - and therefore destroys table links. so what if this happens=20
> 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 b=
reak some unknown number of consequent decisions.

regards, tom lane

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: question about wal and point in time recovery

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <7026.1113402244.1@sss.pgh.pa.us>

"Mohan, Ross" 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


------- =_aaaaaaaaaa0
Content-Type: application/octet-stream
Content-ID: <7026.1113402244.2@sss.pgh.pa.us>
Content-Description: xlogdump.c.gz
Content-Transfer-Encoding: base64

H4sICNUqXUICA3hsb2dkdW1wLmMA7RprcxpH8jP8igk5bJDQw47PcSTLKYSw
RAUBBnxR4rioZXeALS271D5kKYnut193z8zO7ANJd/Hdp0u5InZmuqdf06+Z
g529r/Vfle3AP3brBUsnWW/2bfiqVCbueuNxlsSu58Z3bBGEDGddf8lGQRQv
Qz750GdX/eE5W7gej/Ylmo+RteRHKTb6gQvYJ/1zf3+ffWbvgiTeJLGAQ9BR
EMZu4EesE2zuQne5ilnDbrIXP/zweu/l4eGrlrnzuRfMLY+d8RvuBZs192N2
HgbJ5mFEgGPMl7A4YsGCxSvOPvruDQ8j5BFGOpbnAqu+a0ma/qa3/JsY+ppy
P6hWv3V920sczmobsVO0v6oZw28Xth97+6t3xlDiu1Hs4JgBbtk2j6IDG4SM
GAoTqzjZlE7cWnZcPgGoZq4f89C3vOwK24otmD3YLGd24MdhQPPVahRbsWsz
AKpUKrDgvXNcqRzssGvAwT32/owMyU7CEBXm+qB/Mh8UhYSdumved33eO0ME
035PYFAwMCYgYljnwToDNIF9v3tJG/ecLWCuUw4w4cstEBFfknlpMA01AmMf
LhbHCBgsFhGP0YoUig3MwuI8h6l0xtwuAvv8NmYht4PQQVjCoWHtlRUCMI6e
JosFDz+d9js/TX79fGzSngO66tNeoziswArxi9YDDa4Np8hyHDC8yKRdUlCK
ZBPym4ex4Ao3SKIiGsEAY2wn5JYzpmnghJ2wwcd+/5gBvnE6AeBR4sXMgrUl
astgmLi/c8ByeJxaIVhmFNOGcJDFx/hy5ltrHn2CH5ftq1nvbPfFZ4D6o1qp
kS+rtSr4H1BxiBvCaLszNUZfiNHJ5fnYGH0pRjtZDN+J0bPT9qSrR1+J0enp
ZNTRo38Xo+NLxl7r0dd69Hs9+r0efaNH3+jRH/ToD2L0otsemVxI5k6n465B
2gvJ3UV7cmGuluyNc6slf+e9SUZCksFJ9wNjtUo6TBzeg3aqUsXM8gPwwCFZ
a4u5YDVBFLnzzFmZB4FXRT2j+eFpa9wErtOsgsIiUPgsZj7OggrxT4NcTovp
49Fi4nw0YeMKbNGQy0/UeLUCqIyjzHbV1DGMI0Sjofa+AFAeNg30zb13t95m
trbgBLBvTigezkbt8y5Y13mvA9gJPZwYMNlFo3YaLOFQ0PEUMH6ynoMI6oev
rpgVKzdQv/rNr7UQEE/KUwloMc1GE6m/r+IZiZPQZ3GYcBi6N4UA9B5K/heS
QAgrPASh1UYWhFA400QqHOi6w+Z3MY+Yu/SDkDtInxB9U6CV+ywsL4KN7lHL
GD87486eveL2NWhbZAvCI+wz9jNnTsDABtgcHDnEcYrHGE5UfJYgiEf6kYaw
mHgFU3FAAGvXd9dAKbhN9FvowoI1xBXMVHDeWgcJjSMaB+IWAhL/rt9kMOV6
7At/DtsTndwhKKA7TWmAUCuKkjX88TyVN0iCVlYE5HNfYQTca74OwjvU5o7k
tZo3aOG0etE/IN1wGtKzkp8UEC3D2+JOmzgkk7dD+/WrCv451h/zZAFfIqi4
8CsNakDViaQTrWQGAwiGDhj979y7xkMBp7FDCgKWkblwvQyFnCzfSdkkw6OD
3Rv0pjNY+/pVAyhA7XeGlyM9ktJ+zuMzQNMQKJpgndwvW95Ainaaaqtdhgc7
WDSIvyadAiAX3ftwYYhqL7sOMb/vDTKUCWP/pvtBjhqyoJ1xlTwA6oDq2HPE
5qBRLQ0Uj+srKkG79eSgfvgmPalCTfuYNLmgQPWJ6+VxFEekQUdEnJqs9Mnm
55Z9nWzY3Avs64i8ouXfkeBBX6BPKa1yGYPwhJIxy2q4FA6Zy96CSsYU705/
Gs1O+0NwbzC+uyuZJymZ0nH9RcCeEdSkO9VQDbeJAgOjg1Pq+uhS4KtoEQUd
I+2pYk+vN6fIXtNwzo+AGLaAxpBHVGYMBWuoVOBk2pu7hhThMzw5tE+rAK2E
kpqOWCsNJuvT8yYzv5bqQ6f5mNVUQD277IW2l+3mU7Af8u8kppOiRHZ1FEM7
k7AYBpqGe27HMV9v4tQlZp208GeZLKvMl6lpHZq1j6EgBTQUfZwc7IAlqQkq
JdSk4caEpOIAag7pxBI/gkDEHXJ4wvUBh6ELAUqkgJA4xhDCjqqVLyvMvhtp
us3ewgr2559Mj7yTsgIzyrsZeULgmA44hoYAstwby7c5/vT5lzTVlgbDMrmK
OCzZ0Ghk/uyEZSM77v5gtNc5yeM5gTzE/7zqj2b94eB8BkngWXfcVHE/a8Uf
oe7YcDsGJmXZAtALz1pGfzk5QVSF3ER7PvRVFpMexcLC3agaKk/OwVKfNZq9
740n01lvMusMB5CzdobjM1IEMZxyPLl2N9TPSDTrZVSUco6s5zmqaPtFR523
7iZraKLRUyszkNDaLOA8g7Nu93vng4bGSZ455Gs8AgCuLVVvIjDdC9eAxz4t
96RjAbpEcZyZEk5GzEEdDH4B0cIvqql2mZkda7KP0auYzD6FURGSs0kJZuOH
D8RhucsXN16x33kYYIwrcaWgkTy7LVbgkkR0kPN8dnADlILK1YmGsJDEbOlC
VgjB2FrEHG10wb9g64rM0jzEh+pY7u4qL/SOvSo9+8uANiTnJBJncMQVTJQp
aUW3Al4OOVwCt5iOKU+MnRe0VkwHrM2G+w7YayZb2EdEQFvqKIG0Qtq0W8gI
v3UXgGvBhv2zWb87OJ9ezH5u//K/zCEM3/54LPsWOHcX1VRwbQ8WWSA5IJf7
nhUueTZkQfIOeuUgLig6puDAMUyxJOIejyJCEa0xsQcRA1zEIesKoWQAmUMV
grSQ12frxItdbImCUgQ5LcqQ19Y1F1iSENxm/DxC2/QAU8xe7cjAAhhI5WTE
QEZjuoJFbgQUB8lyJXyg5xGeGhRYQFBNKioStggGescEd1DfrN0Y+bXmQaha
NRFUQtjpRFYJD1QhnEUby+b7TWkZqCQt6nes0ECRylSxF46DbKsYwbeqx9Na
GSJnQw3WzaxOY7i0btWSFjZBzFXCJxi0kIEsQs5zwyKNzvaNGqLD02SoxcBW
m6SIvylg/kOczNLmkYhMUPKB1wUVxStQEhiQxWpzqt2xFqhhnHBcChIiRhXq
Z3ls5TmuJ+Svrg7qVyBJcGCBv9ShJJVt68kOrOBX7qvlHElhyCxQpGOyWZIu
hUnhLFJdFmODUio16Kwby/UsbNRAaitS3TLrwnqvkD7hEYv4GoHNKK8sDh2k
IEbZmkza57KdowpkWUxuDUFp5qmY3pUoK8KxHR+nliD33mTSMNp7e04nUgng
6+rqiuERpkgy54zEUkwTdav7G2kyRZv56CtgysUzuQgS9yOYjMwV8uon/Usf
/NdzpZJkyYzH2ILh6Lz8gBkJDiaLaBDqlgeMPoJkAv6QASX53KnntGTCUUwO
tzCYVdF/lj7/m1ladkuBAeW8JS87kcVFtmLBlHt7JidM/jGpu/4NdovILPa0
e0H5PlXo5RS0/kNl5HxGTjd7pQnqY+JTTkMJInf4las3VFieCGv/kHoUff5L
XEI2GxH8bdmbPb559UF5C7LM/DFN9nMyLEcPww8hnoPzuFYhgTxCttuoHGjq
48uL1ELfmLr2IheNiADLhQIcbY9iZFoHb/HX6VloPlA3pE67vBbK4HgyaznO
MozdV817KbYDX+EMbxYb+L8ZOHU/+CJuG4xlQOan715+xhAB6xe0nvpDMnOF
37JvWav/sldf79UdVr84ql8e1Ses/qssVzBt9Qj2Ge1C1ZGkjnq6mjjMWKt4
na7FVdI1JjqpGeLKPm/a5qUY96ZCfj/TE9ZNgvGsN3g/hAx/8hMSolyQLLKO
2Ca8URUXMfC0ckuuNHbEq0GzzZUdT3McqeCr/uldzLsfGrmVLaYvIUnLit7G
j81a02TgmN2C16wnx6yW2Q9Gy+rRcA2r356w9ILQRF6PAEd6h5iD+oxWycHI
zDL2ktVfOtmNcW2GQtFrqR++vJIOnS5Xckpq5eo2wvC0lnDhCuDfq+xUHcbY
/HquW6MltV6jvNgrVHvSRTwDfNn+a1rykStT8plfb1Q/FZKPCIObM8f/h9zD
P3IyMRoB7u4L9RM22fcDh+9HG3sAf1t6xJnnBgAhjRighJzIeWKnNYISz14V
bEoK04YCEy2ILgp7Z0cqHoqTKS8QOxfdzk+jYW8wnU0uPk7Phj8PIKegqLJt
3XDQ7w26RuSk3tooQHewQxdbG/yN1qJnwOOWG81xJgvR4NiLcQLlBo4ZVMnG
V+y5eM5ADWnGUWP0nkGdQPwdyN91qpPrUTZDSbfae4dbacdSmND+pQiKdJWC
iomHQKcQzPQTlCw0kn+FKEshcXboOhrto0ptsh+1pKJVEjvBF7/Gjlgt8PFp
iyGYNC6ZO+JAU/f8yPmwgjUNulfTYc/sgAKVWJkTuaLOSY+kYmLrJZ40f1jT
zJmJBD1SDkx+G/SlqUl6Ctqd6dZTQJOd4eVlb2rQjp7bsuOZaILAJ1CW44HG
HuWAVuV5EFiPVMEe5T3xranfVCeEav/2aeogttqnw3EZV9TQ+dpMEdKvxFNB
hZ0tjozGf+2Oh/gawuCU8hNMFv0gx6EYfJRFQJBnkJrC+PBNZKGHt/XDV7fE
psD5EP34kkfRX5jEZzNqUvn1tHYenov54QhzJoNFgtbzvcGkO54SDrkA1b3i
FtbhEQ9jZur7ryg8FYjAW48eDJeFClH7rGcZ8ntTetIC3orVdvGTXJQJJYwl
xu5knA22D62R4fehJWZAFmt6MV9T8OIhyIRC8IDe0EiJKfAYPM9WsCHd52yF
U+JM7UEaT16zZ91+d9ot1azDPR7zr69Zgff/mv0va/bj6KytNGsuzi27HP7j
8UWdfrc9KKwq90b0IG+7O2pPLrZOjh8ExUd6Wycn3Q/5uXusPkvLT3TAbEF5
FVQdbIG1kL7036HvuRVxs8L5zQeL/c0nlywAjtP7m+5tHELoSztYrkPXKurV
7SIM1uIBLcLJe4x0E8hnIWKF9ips0FiLPT94rroD6Sr0zenH7q4u00w89FtC
RpFt+QuNoAWF/Jtb8Q9YeCYeJosf2Dl7JlpndLX+3bYndR3Lfy4ua5Y+dncU
w7gJe16PnmvxENHyqQBspG5t9Q2tuKq4r4o31ki9I7om6p72hO3JqoS61FHA
XkSxuOJai6fXma42XXVG8s1r/mpTvqAwHnqoV1O5lkSum1O8utE1O6DWT5Gl
uYG4qmvL9cnC4Mza0sZ28ONGdTeYi5aVlrAvRAmLy82iVbRzSJiwCOE/uZ/p
RoisF8aCDfeV0Qxn4zOooH5hf7LR+ey0N2iPf2mxQ/0QCCDemo8mQKOBNDnd
VFbl7b0hmQaeFL0OkpSIw1gz8ywHpXxfVW9yxgE+XuTq0hIzSPH8LmJfsPEO
Wy3cZQJVEF0btieYXtCzHHyPU6ng/UoYzzjdJiCnotVF4u3e2nyDFwqW11FX
WLKxmV5pDVAkMiEUU8Tt9G6TG0brNRejaLFsEd6X9FU4BNNxewSpaNT4rQaZ
KNj7e0ByxNQXVl0w7TRV7NJbsyKBKQHmtqgzyngbzeOsgP8FX6ArozQzAAA=

------- =_aaaaaaaaaa0
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

------- =_aaaaaaaaaa0--

Re: question about wal and point in time recovery

Thank you!

( would send this to yer eddress directly, but it's decided
my biz email is a spam address, or somesuch:=20
1=20
refused - see http://www.five-ten-sg.com/blackhole.php? )=20


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
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=20


"Mohan, Ross" 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=3D(location of PG source tree)
CC=3Dgcc
CFLAGS=3D-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 sour=
ce tree and be maintained properly ...

regards, tom lane


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: question about wal and point in time recovery

Zitat von "Mohan, Ross" :

>
> Thank you!
>
> ( would send this to yer eddress directly, but it's decided
> my biz email is a spam address, or somesuch:
> > 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



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: question about wal and point in time recovery

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

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