Re: PITR potentially broken in 9.2

On 2012-12-04 19:20:44 -0500, Tom Lane wrote:
> Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> > I've reproduced it again using the just-tagged 9.2.2, and uploaded a
> > 135MB tarball of the /tmp/data_slave2 and /tmp/archivedir to google
> > drive. The data directory contains the recovery.conf which is set to
> > end recovery between the two critical time points.
>
> Hmmm ... I can reproduce this with current 9.2 branch tip. However,
> more or less by accident I first tried it with a 9.2-branch postmaster
> from a couple weeks ago, and it works as expected with that: the log
> output looks like
>
> LOG: restored log file "00000001000000000000001B" from archive
> LOG: restored log file "00000001000000000000001C" from archive
> LOG: restored log file "00000001000000000000001D" from archive
> LOG: database system is ready to accept read only connections
> LOG: recovery stopping before commit of transaction 305610, time 2012-12-02 15:08:54.000131-08
> LOG: recovery has paused
> HINT: Execute pg_xlog_replay_resume() to continue.
>
> and I can connect and do the pg_xlog_replay_resume() thing.
> Note the "ready to accept read only connections" line, which
> does not show up with branch tip.
>
> So apparently this is something we broke since Nov 18. Don't know what
> yet --- any thoughts? Also, I am still not seeing what the connection
> is to the original report against 9.1.6.

Maybe I am blind, but, looking at the backup label:

START WAL LOCATION: 0/110006A8 (file 000000010000000000000011)
CHECKPOINT LOCATION: 0/1D776B50
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2012-12-02 15:08:52 PST
LABEL: label

This was a straight archive based hot backup with manual
pg_start/stop_backup. Which means we ought to find a XLOG_BACKUP_END
record. Before that we shouldn't be consistent.

Satoshi's version of xlogdump outputs this (besides heaps of errors ...):
~/bin/xlogdump-9.2 ~/tmp/recover/tmp/archivedir/0000000100000000000000* 2>&1|grep "backup end"
[cur:0/22C361E0, xid:0, rmid:0(XLOG), len:8/40, prev:0/22C361B0] backup end: started at 0/110006A8.

But again, according to xlogdump:
[cur:0/1D861CD8, xid:305610, rmid:1(Transaction), len:12/44, prev:0/1D861C80] compact commit at 2012-12-03 00:08:54 CET

So the target xid far before the backend end, so it seems absolutely
correct not to permit access yet.

So maybe I am (again) missing something here, but everything looks fine.

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services