pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Finding time in WAL logs
9+ messages / 4 participants
[nested] [flat]

* Finding time in WAL logs
@ 2007-04-25 15:28  Pascal Robert <probert@os.ca>
  0 siblings, 2 replies; 9+ messages in thread

From: Pascal Robert @ 2007-04-25 15:28 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Finding time in WAL logs
@ 2007-04-25 15:43  Simon Riggs <simon@2ndquadrant.com>
  parent: Pascal Robert <probert@os.ca>
  1 sibling, 1 reply; 9+ messages in thread

From: Simon Riggs @ 2007-04-25 15:43 UTC (permalink / raw)
  To: Pascal Robert <probert@os.ca>; +Cc: pgsql-admin

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





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Finding time in WAL logs
@ 2007-04-25 17:13  Pascal Robert <probert@os.ca>
  parent: Simon Riggs <simon@2ndquadrant.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Pascal Robert @ 2007-04-25 17:13 UTC (permalink / raw)
  To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-admin


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 ?


^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Finding time in WAL logs
@ 2007-04-25 18:01  Simon Riggs <simon@2ndquadrant.com>
  parent: Pascal Robert <probert@os.ca>
  0 siblings, 2 replies; 9+ messages in thread

From: Simon Riggs @ 2007-04-25 18:01 UTC (permalink / raw)
  To: Pascal Robert <probert@os.ca>; +Cc: pgsql-admin

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





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Finding time in WAL logs
@ 2007-04-25 18:49  Pascal Robert <probert@os.ca>
  parent: Simon Riggs <simon@2ndquadrant.com>
  1 sibling, 2 replies; 9+ messages in thread

From: Pascal Robert @ 2007-04-25 18:49 UTC (permalink / raw)
  To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-admin

>> 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 ?



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Finding time in WAL logs
@ 2007-04-25 19:20  Pascal Robert <probert@os.ca>
  parent: Pascal Robert <probert@os.ca>
  1 sibling, 0 replies; 9+ messages in thread

From: Pascal Robert @ 2007-04-25 19:20 UTC (permalink / raw)
  To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-admin


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.


^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Finding time in WAL logs
@ 2007-04-25 19:53  Simon Riggs <simon@2ndquadrant.com>
  parent: Pascal Robert <probert@os.ca>
  1 sibling, 0 replies; 9+ messages in thread

From: Simon Riggs @ 2007-04-25 19:53 UTC (permalink / raw)
  To: Pascal Robert <probert@os.ca>; +Cc: pgsql-admin

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





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Finding time in WAL logs
@ 2007-04-26 12:36  Robert Treat <xzilla@users.sourceforge.net>
  parent: Pascal Robert <probert@os.ca>
  1 sibling, 0 replies; 9+ messages in thread

From: Robert Treat @ 2007-04-26 12:36 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Pascal Robert <probert@os.ca>

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-WH...

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Finding time in WAL logs
@ 2007-04-27 14:52  Jim Nasby <decibel@decibel.org>
  parent: Simon Riggs <simon@2ndquadrant.com>
  1 sibling, 0 replies; 9+ messages in thread

From: Jim Nasby @ 2007-04-27 14:52 UTC (permalink / raw)
  To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Pascal Robert <probert@os.ca>; pgsql-admin

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)





^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2007-04-27 14:52 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-25 15:28 Finding time in WAL logs Pascal Robert <probert@os.ca>
2007-04-25 15:43 ` Simon Riggs <simon@2ndquadrant.com>
2007-04-25 17:13   ` Pascal Robert <probert@os.ca>
2007-04-25 18:01     ` Simon Riggs <simon@2ndquadrant.com>
2007-04-25 18:49       ` Pascal Robert <probert@os.ca>
2007-04-25 19:20         ` Pascal Robert <probert@os.ca>
2007-04-25 19:53         ` Simon Riggs <simon@2ndquadrant.com>
2007-04-27 14:52       ` Jim Nasby <decibel@decibel.org>
2007-04-26 12:36 ` Robert Treat <xzilla@users.sourceforge.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox