pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedFinding 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>
2007-04-25 15:43 ` Re: Finding time in WAL logs Simon Riggs <simon@2ndquadrant.com>
2007-04-26 12:36 ` Re: Finding time in WAL logs Robert Treat <xzilla@users.sourceforge.net>
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: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 ` Re: Finding time in WAL logs 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 15:28 Finding time in WAL logs Pascal Robert <probert@os.ca>
2007-04-25 15:43 ` Re: Finding time in WAL logs Simon Riggs <simon@2ndquadrant.com>
@ 2007-04-25 17:13 ` Pascal Robert <probert@os.ca>
2007-04-25 18:01 ` Re: Finding time in WAL logs 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 15:28 Finding time in WAL logs Pascal Robert <probert@os.ca>
2007-04-25 15:43 ` Re: Finding time in WAL logs Simon Riggs <simon@2ndquadrant.com>
2007-04-25 17:13 ` Re: Finding time in WAL logs Pascal Robert <probert@os.ca>
@ 2007-04-25 18:01 ` Simon Riggs <simon@2ndquadrant.com>
2007-04-25 18:49 ` Re: Finding time in WAL logs Pascal Robert <probert@os.ca>
2007-04-27 14:52 ` Re: Finding time in WAL logs Jim Nasby <decibel@decibel.org>
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 15:28 Finding time in WAL logs Pascal Robert <probert@os.ca>
2007-04-25 15:43 ` Re: Finding time in WAL logs Simon Riggs <simon@2ndquadrant.com>
2007-04-25 17:13 ` Re: Finding time in WAL logs Pascal Robert <probert@os.ca>
2007-04-25 18:01 ` Re: Finding time in WAL logs Simon Riggs <simon@2ndquadrant.com>
@ 2007-04-25 18:49 ` Pascal Robert <probert@os.ca>
2007-04-25 19:20 ` Re: Finding time in WAL logs Pascal Robert <probert@os.ca>
2007-04-25 19:53 ` Re: Finding time in WAL logs 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 15:28 Finding time in WAL logs Pascal Robert <probert@os.ca>
2007-04-25 15:43 ` Re: Finding time in WAL logs Simon Riggs <simon@2ndquadrant.com>
2007-04-25 17:13 ` Re: Finding time in WAL logs Pascal Robert <probert@os.ca>
2007-04-25 18:01 ` Re: Finding time in WAL logs Simon Riggs <simon@2ndquadrant.com>
2007-04-25 18:49 ` Re: Finding time in WAL logs Pascal Robert <probert@os.ca>
@ 2007-04-25 19:20 ` 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 15:28 Finding time in WAL logs Pascal Robert <probert@os.ca>
2007-04-25 15:43 ` Re: Finding time in WAL logs Simon Riggs <simon@2ndquadrant.com>
2007-04-25 17:13 ` Re: Finding time in WAL logs Pascal Robert <probert@os.ca>
2007-04-25 18:01 ` Re: Finding time in WAL logs Simon Riggs <simon@2ndquadrant.com>
2007-04-25 18:49 ` Re: Finding time in WAL logs Pascal Robert <probert@os.ca>
@ 2007-04-25 19:53 ` Simon Riggs <simon@2ndquadrant.com>
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-25 15:28 Finding time in WAL logs Pascal Robert <probert@os.ca>
2007-04-25 15:43 ` Re: Finding time in WAL logs Simon Riggs <simon@2ndquadrant.com>
2007-04-25 17:13 ` Re: Finding time in WAL logs Pascal Robert <probert@os.ca>
2007-04-25 18:01 ` Re: Finding time in WAL logs Simon Riggs <simon@2ndquadrant.com>
@ 2007-04-27 14:52 ` Jim Nasby <decibel@decibel.org>
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
* Re: Finding time in WAL logs
2007-04-25 15:28 Finding time in WAL logs Pascal Robert <probert@os.ca>
@ 2007-04-26 12:36 ` Robert Treat <xzilla@users.sourceforge.net>
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
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