Thread: Finding time in WAL logs
Hi, I'm writing our backup procedure for using WAL and PITR, but to be able to do a (mostly) perfect PITR, I need to find the time when a error (DELETE FROM, DROP TABLE, etc.) was made so that I can do a restore just before the error. Does PostgreSQL has something similar to "mysqlbinlog" so that I can look at the content of a WAL archive ? I tried enabling "log_min_messages" at "info" level, but it doesn't log the date and time of a executed statement, so that's not a solution. pgsql 8.2.4 on RedHat Linux ES 4
On Wed, 2007-04-25 at 11:28 -0400, Pascal Robert wrote: > I'm writing our backup procedure for using WAL and PITR, but to be > able to do a (mostly) perfect PITR, I need to find the time when a > error (DELETE FROM, DROP TABLE, etc.) was made so that I can do a > restore just before the error. > > Does PostgreSQL has something similar to "mysqlbinlog" so that I can > look at the content of a WAL archive ? I tried enabling > "log_min_messages" at "info" level, but it doesn't log the date and > time of a executed statement, so that's not a solution. http://pgfoundry.org/projects/xlogviewer/ I'd appreciate some feedback. I'll be looking to release a new version within next few months. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Le 07-04-25 à 11:43, Simon Riggs a écrit : > On Wed, 2007-04-25 at 11:28 -0400, Pascal Robert wrote: > >> I'm writing our backup procedure for using WAL and PITR, but to be >> able to do a (mostly) perfect PITR, I need to find the time when a >> error (DELETE FROM, DROP TABLE, etc.) was made so that I can do a >> restore just before the error. >> >> Does PostgreSQL has something similar to "mysqlbinlog" so that I can >> look at the content of a WAL archive ? I tried enabling >> "log_min_messages" at "info" level, but it doesn't log the date and >> time of a executed statement, so that's not a solution. > > http://pgfoundry.org/projects/xlogviewer/ > > I'd appreciate some feedback. I'll be looking to release a new version > within next few months. When I look at a log with xlogdump -s /tmp/NAMEOFWAL, I always get this at the end : Unable to read continuation page? BTW, the statements look like this : 0/15FFE470: prv 0/15FFE440; xid 2332; HEAP info 10 len 18 tot_len 46 DELETE FROM 16612 WHERE ...delete: ts 1663 db 16384 rel 16612 block 1413 off 5 (this is for a DELETE FROM table without any WHERE clause). I see the XID, but can you also display date and time in the ouput ?
On Wed, 2007-04-25 at 13:13 -0400, Pascal Robert wrote: > Le 07-04-25 à 11:43, Simon Riggs a écrit : > > > On Wed, 2007-04-25 at 11:28 -0400, Pascal Robert wrote: > > > >> I'm writing our backup procedure for using WAL and PITR, but to be > >> able to do a (mostly) perfect PITR, I need to find the time when a > >> error (DELETE FROM, DROP TABLE, etc.) was made so that I can do a > >> restore just before the error. > >> > >> Does PostgreSQL has something similar to "mysqlbinlog" so that I can > >> look at the content of a WAL archive ? I tried enabling > >> "log_min_messages" at "info" level, but it doesn't log the date and > >> time of a executed statement, so that's not a solution. > > > > http://pgfoundry.org/projects/xlogviewer/ > > > > I'd appreciate some feedback. I'll be looking to release a new version > > within next few months. > > When I look at a log with xlogdump -s /tmp/NAMEOFWAL, I always get > this at the end : > > Unable to read continuation page? It's not that smart about where it stops. > BTW, the statements look like this : > > 0/15FFE470: prv 0/15FFE440; xid 2332; HEAP info 10 len 18 tot_len 46 > DELETE FROM 16612 WHERE ...delete: ts 1663 db 16384 rel 16612 block > 1413 off 5 > > (this is for a DELETE FROM table without any WHERE clause). I see > the XID, but can you also display date and time in the ouput ? Original date/time is available only on COMMIT/ABORT records, so you'll need to search ahead/behind. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
>> BTW, the statements look like this : >> >> 0/15FFE470: prv 0/15FFE440; xid 2332; HEAP info 10 len 18 >> tot_len 46 >> DELETE FROM 16612 WHERE ...delete: ts 1663 db 16384 rel 16612 block >> 1413 off 5 >> >> (this is for a DELETE FROM table without any WHERE clause). I see >> the XID, but can you also display date and time in the ouput ? > > Original date/time is available only on COMMIT/ABORT records, so > you'll > need to search ahead/behind. Ok, so I guess that auto commited statements will never have date/time ?
Le 07-04-25 à 14:49, Pascal Robert a écrit :
>>> BTW, the statements look like this :
>>>
>>> 0/15FFE470: prv 0/15FFE440; xid 2332; HEAP info 10 len 18
>>> tot_len 46
>>> DELETE FROM 16612 WHERE ...delete: ts 1663 db 16384 rel 16612 block
>>> 1413 off 5
>>>
>>> (this is for a DELETE FROM table without any WHERE clause). I see
>>> the XID, but can you also display date and time in the ouput ?
>>
>> Original date/time is available only on COMMIT/ABORT records, so
>> you'll
>> need to search ahead/behind.
>
> Ok, so I guess that auto commited statements will never have date/
> time ?
Ok, I didn't see the commit line after the INSERT.
How can I use the -r option ? From my understanding, I can use it to
find specific DML, but I tried :
-r "INSERT"
-r INSERT
-r insert
without any success, even if I know that I have INSERT in the log.
On Wed, 2007-04-25 at 14:49 -0400, Pascal Robert wrote: > >> BTW, the statements look like this : > >> > >> 0/15FFE470: prv 0/15FFE440; xid 2332; HEAP info 10 len 18 > >> tot_len 46 > >> DELETE FROM 16612 WHERE ...delete: ts 1663 db 16384 rel 16612 block > >> 1413 off 5 > >> > >> (this is for a DELETE FROM table without any WHERE clause). I see > >> the XID, but can you also display date and time in the ouput ? > > > > Original date/time is available only on COMMIT/ABORT records, so > > you'll > > need to search ahead/behind. > > Ok, so I guess that auto commited statements will never have date/time ? There'll be a COMMIT record for every transaction, however it was initiated, unless the server crashed/shutdown before it could be issued. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
On Wednesday 25 April 2007 11:28, Pascal Robert wrote: > Hi, > > I'm writing our backup procedure for using WAL and PITR, but to be > able to do a (mostly) perfect PITR, I need to find the time when a > error (DELETE FROM, DROP TABLE, etc.) was made so that I can do a > restore just before the error. > > Does PostgreSQL has something similar to "mysqlbinlog" so that I can > look at the content of a WAL archive ? I tried enabling > "log_min_messages" at "info" level, but it doesn't log the date and > time of a executed statement, so that's not a solution. > Look at the log_line_prefix option in the docs... http://www.postgresql.org/docs/8.2/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT -- Robert Treat Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
On Apr 25, 2007, at 7:01 PM, Simon Riggs wrote: >> (this is for a DELETE FROM table without any WHERE clause). I see >> the XID, but can you also display date and time in the ouput ? > > Original date/time is available only on COMMIT/ABORT records, so > you'll > need to search ahead/behind. Additionally, you can specify an XID when doing a point-in-time restore; you don't need to find an exact time to restore to. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)