Re: Question about durability and postgresql.

On Fri, Feb 20, 2015 at 5:09 PM, Alfred Perlstein <bright(at)mu(dot)org> wrote:
> Hello,
>
> We have a combination of 9.3 and 9.4 databases used for logging of data.
>
> We do not need a strong durability guarantee, meaning it is ok if on crash a
> minute or two of data is lost from our logs. (This is just stats for our
> internal tool).
>
> I am looking at this page:
> http://www.postgresql.org/docs/9.4/static/non-durability.html
>
> And it's not clear which setting I should turn on.
>
> What we do NOT want is to lose the entire table or corrupt the database. We
> do want to gain speed though by not making DATA writes durable.
>
> Which setting is appropriate for this use case?
>
> At a glance it looks like a combination of
> 1) "Turn off synchronous_commit"
> and possibly:
> 2) Increase checkpoint_segments and checkpoint_timeout ; this reduces the
> frequency of checkpoints, but increases the storage requirements of
> /pg_xlog.

I feel changing above two configuration points are enough for your requirement.

> 3) Turn off full_page_writes; there is no need to guard against partial page
> writes.

Turning off this may lead to a corrupted database in case if the
system crash during the
page write until unless your file system supports guard against
partial page writes.

Regards,
Hari Babu
Fujitsu Australia