Re: Queries about PostgreSQL PITR

On Mon, Jul 12, 2010 at 5:29 PM, Jayadevan M
<Jayadevan(dot)Maymala(at)ibsplc(dot)com> wrote:
> Hi,
>>Because you didn't disable recovery_target_inclusive, I guess.
>>
> http://www.postgresql.org/docs/8.4/static/continuous-archiving.html#RECOVERY-TARGET-INCLUSIVE
> Thanks. I was almost sure this will fix it. But the issue seems to be
> something else. Even if I give a time that is a few more minutes before
> what I got from select now(), it is always moving upto/or just before
> (depending on the above parameter) transaction id 676. The ooutput reads
>  LOG:  recovery stopping before commit of transaction 676, time 2010-07-09
> 07:49:26.580518+05:30

A recovery stops when the commit time > or >= recovery_target_time.
So, unless it moves up to the newer commit than recovery_target_time,
it cannot stop.

> Is there a way to find out the transaction ids and corresponding SQLs,
> timeline etc? May be doing the recovery in debug/logging mode or something
> like that?

xlogviewer reads WAL files and displays the contents of them. But
it's been inactive for several years, so I'm not sure if it's available now.
http://pgfoundry.org/projects/xlogviewer/

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center