increasing checkpoint_timeout?

On Dec 15, 2007 6:51 AM, rihad <rihad@mail.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.

Oh, and another point I forgot to mention, is that you'd need to
increase checkpoint segments to get a longer time between checkpoints
as well.

But honestly, do a search on the archives for tuning the background
writer, it's a much better option for most workloads.