Thread: Determining oldest WAL for Archiving PITR Standby
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. It would be less ambiguous if pg_controldata didn't strip leading zeros from the log segment so that the first two hex digits after the slash would be the log segment. What is the rule for determining the log segment from pg_controldata's output? Thanks for the help! Brian Wipf ClickSpace Interactive Inc. <brian@clickspace.com>
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@clickspace.com>
On 18-Oct-07, at 3:15 PM, Brian Wipf wrote: > 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 Sorry, typo. The corresponding WAL for a checkpoint location of 2/3000020 is 000000010000000200000003 Brian Wipf ClickSpace Interactive Inc. <brian@clickspace.com>