Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id F03DC9FBA79 for ; Wed, 25 Apr 2007 15:06:41 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 63869-01-3 for ; Wed, 25 Apr 2007 15:06:34 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail01.enterprisedb.com (mail01.enterprisedb.com [63.246.7.168]) by postgresql.org (Postfix) with ESMTP id 7ACCD9FBF89 for ; Wed, 25 Apr 2007 15:06:11 -0300 (ADT) thread-index: AceHZHC1iZQ1NEjARR+J8rlZcuw7AQ== Received: from [192.168.0.22] ([85.211.22.118]) by mail01.enterprisedb.com with Microsoft SMTPSVC(6.0.3790.1830); Wed, 25 Apr 2007 14:06:25 -0400 Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826 Subject: Re: Finding time in WAL logs From: "Simon Riggs" To: "Pascal Robert" Cc: In-Reply-To: <7697F772-07E3-469C-8E51-1450AEBC5F39@os.ca> References: <5B0132B1-646A-432C-8261-973E4BB42999@os.ca> <1177515789.20637.160.camel@silverbirch.site> <7697F772-07E3-469C-8E51-1450AEBC5F39@os.ca> Content-Type: text/plain; charset="utf-8" Date: Wed, 25 Apr 2007 19:01:40 +0100 Message-ID: <1177524100.20637.167.camel@silverbirch.site> MIME-Version: 1.0 X-Mailer: Evolution 2.6.0 Content-Transfer-Encoding: quoted-printable X-OriginalArrivalTime: 25 Apr 2007 18:06:25.0781 (UTC) FILETIME=[70885250:01C78764] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/235 X-Sequence-Number: 25299 On Wed, 2007-04-25 at 13:13 -0400, Pascal Robert wrote: > Le 07-04-25 =C3=A0 11:43, Simon Riggs a =C3=A9crit : >=20 > > 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. >=20 > When I look at a log with xlogdump -s /tmp/NAMEOFWAL, I always get =20 > this at the end : >=20 > Unable to read continuation page? It's not that smart about where it stops. > BTW, the statements look like this : >=20 > 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 =20 > 1413 off 5 >=20 > (this is for a DELETE FROM table without any WHERE clause). I see =20 > 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. --=20 Simon Riggs =20 EnterpriseDB http://www.enterprisedb.com