Re: Finding time in WAL logs

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 ?