Regarding Checkpoint Redo Record

Why PostgreSQL needs to write WAL record for Checkpoint when it maintains
same information in pg_control file?

This may be required in case we need information about more than one
checkpoint as pg_control can hold information of only recent checkpoint. But
I could not think of a case where more than one checkpoint information will
be required.

Could anybody let me know the cases where it is required.

On 04.01.2012 08:42, Amit Kapila wrote:
> Why PostgreSQL needs to write WAL record for Checkpoint when it maintains
> same information in pg_control file?

I guess it wouldn't be strictly necessary...

> This may be required in case we need information about more than one
> checkpoint as pg_control can hold information of only recent checkpoint. But
> I could not think of a case where more than one checkpoint information will
> be required.

There is a pointer in the control file to the previous checkpoint, too.
It's not normally needed, but we fall back to that if the latest
checkpoint cannot be read for some reason, like disk failure. If you
have a disk failure and cannot read the latest checkpoint, chances are
that you have a corrupt database anyway, but at least we try to recover
as much as we can using the previous checkpoint.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Wed, Jan 4, 2012 at 3:56 PM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> On 04.01.2012 08:42, Amit Kapila wrote:
>>
>> Why PostgreSQL needs to write WAL record for Checkpoint when it maintains
>> same information in pg_control file?
>
>
> I guess it wouldn't be strictly necessary...

Apart from replicated standbys, which need that info for running restartpoints.

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

On Wed, Jan 4, 2012 at 11:02 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Wed, Jan 4, 2012 at 3:56 PM, Heikki Linnakangas
> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>> On 04.01.2012 08:42, Amit Kapila wrote:
>>>
>>> Why PostgreSQL needs to write WAL record for Checkpoint when it maintains
>>> same information in pg_control file?
>>
>>
>> I guess it wouldn't be strictly necessary...
>
> Apart from replicated standbys, which need that info for running restartpoints.

Yeah.

But, the OP makes me wonder: why can a standby only perform a
restartpoint where the master performed a checkpoint? It seems like a
standby ought to be able to create a restartpoint anywhere, just by
writing everything, flushing it to disk, and update pg_control. I
assume there's some reason that doesn't work, I just don't know what
it is...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> But, the OP makes me wonder: why can a standby only perform a
> restartpoint where the master performed a checkpoint? It seems like a
> standby ought to be able to create a restartpoint anywhere, just by
> writing everything, flushing it to disk, and update pg_control.

Perhaps, but then crash restarts would have to accept start pointers
that point at any random place in the WAL. I like the additional error
checking of verifying that there's a checkpoint recod there. Also
I think the full-page-write mechanism would no longer protect against
torn pages during replay if you did that.

regards, tom lane

On Wed, Jan 4, 2012 at 4:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> But, the OP makes me wonder: why can a standby only perform a
>> restartpoint where the master performed a checkpoint?  It seems like a
>> standby ought to be able to create a restartpoint anywhere, just by
>> writing everything, flushing it to disk, and update pg_control.
>
> Perhaps, but then crash restarts would have to accept start pointers
> that point at any random place in the WAL.  I like the additional error
> checking of verifying that there's a checkpoint recod there.

I could go either way on that one, but...

> Also
> I think the full-page-write mechanism would no longer protect against
> torn pages during replay if you did that.

...this is a very good point.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company