Re: Determining oldest WAL for Archiving PITR Standby - SOLVED

On 17-Oct-07, at 12:01 AM, Brian Wipf wrote:
> I'm working on a script that takes backups in intervals from our
> warm PITR stand by server (both servers running PG 8.2.5). The
> documentation advises "running pg_controldata on the standby server
> to inspect the control file and determine the current checkpoint
> WAL location". I am hoping someone can confirm how to perform this
> step.
>
> From pg_controldata:
> Latest checkpoint location: 8E/624808
> Latest checkpoint's TimeLineID: 1
> Using the timeline id of 1, log id of 8E and log segment of 0, the
> oldest WAL needed for a recoverable backup is 000000010000008E00000000
>
> It's not obvious to me why the output in this example doesn't
> indicate a log segment of 62 and offset of 4808, or a log segment
> of 6 and offset of 24808.

After watching more output from pg_controldata, I can now answer the
question I posted above. (Note: this is for PG 8.2.5. The behavior
may be different for other PG versions.)

The offset is the last 6 hex digits of the checkpoint location value.
The offset contains leading zeros to make it 6 digits if its actual
value is less than 6 digits. Therefore, the digits between the slash
and the last 6 digits are the log segment value. If there are no
digits between the slash and the last 6 hex digits, the log segment
value is simply 0.

If the checkpoint location is 2/3000020 and the timeline id is 1, the
corresponding WAL is 000000010000000200000000

Hope this helps,

Brian Wipf
ClickSpace Interactive Inc.
<brian(at)clickspace(dot)com>