Re: increasing checkpoint_timeout?

On Dec 15, 2007 6:51 AM, rihad <rihad(at)mail(dot)ru> wrote:
> http://www.postgresql.org/docs/8.3/static/wal-configuration.html
>
> Is it right that checkpoint_timeout means the amount of time up to which
> you agree to lose data in the event of a power crash?

No, dear god, no. :) Once something is committed, it won't be lost
due to a server crash unless your hardware is lying about fsync. The
data that hasn't been checkpointed has to be replayed upon a crash /
restart, that's all.

> What if I set it
> to 1 hour (and bump checkpoint_segments accordingly), does it mean that
> I'm willing to lose up to 1 hour of data?

Nope, but it could take a very long time to recover from a crash /
kill -9 situation as all that data gets replayed.

> I'm thinking about increasing
> checkpoint_timeout to mitigate the full_page_writes bloat.

You'd be better off working on tuning the background writer so that
you lose some small% of performance all the time but never have huge
spike in checkpointing.

> BTW how are transactions WAL logged? Do the logs include data too? In
> this case, am I right that the effects of full_page_writes=on serve as a
> starting data page on top of which to replay transactions when doing
> crash recovery?

full_page_writes = on = you CAN recover from a server crash / kill -9.
Off not so much guaranteed.