Re: Reporting WAL file containing checkpoint's REDO record in pg_controldata's result

On Fri, Mar 23, 2012 at 05:06, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> Hi,
>
> I'd like to propose to change pg_controldata so that it reports the name
> of WAL file containing the latest checkpoint's REDO record, as follows:
>
>    $ pg_controldata $PGDATA
>    ...
>    Latest checkpoint's REDO location:    0/16D6ACC (file
> 000000010000000000000001)
>    Latest checkpoint's TimeLineID:       1
>    ...
>
> This simplifies very much the way to calculate the archive file cutoff point
> because the reported WAL file is just cutoff point itself. If the file name is
> not reported, we have to calculate the cutoff point from the reported
> location and timeline, which is complicated calculation. We can use
> pg_xlogfile_name function to calculate that, but it cannot be executed in
> the standby. Another problem is that pg_xlogfile_name always uses
> current timeline for the calculation, so if the reported timeline is not
> the same as current one, pg_xlogfile_name cannot return the correct WAL
> file name. Making pg_controldata report that WAL file name gets rid of
> such a complexity.
>
> You may think that archive_cleanup_command is usable for that purpose.
> That's true. But it's not usable simply for  the case where there are more
> than one standby servers. In this case, the archive file cutoff point needs
> to be calculated from each standby's REDO location and timeline.
>
> Attached patch changes pg_controldata as above. Thought?

Might it be a good idea to put it on it's own row instead of changing
the format of an existing row, in order not to break scripts and
programs that are parsing the previous output?

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/