On Wed, Aug 25, 2010 at 8:48 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> synchronous_commit also has effects on data safety. It permits the loss of
> transactions committed within the commit delay interval if the server
> crashes. If you turn it on, you need to decide how much recent work you can
> afford to lose if the database crashes. Not sure if it can be applied with a
> reload or whether it requires a full server restart.
>
I routinely set synchronous_commit = off on a per-connection or
per-transaction basis. The beauty of it is that it still honors
transaction boundaries. That is, if there is a server crash the
transaction will be either there or not as a whole; it will not be
partially applied. This works great for bulk imports and changes to
the DB for me, since I can always just re-run my programs on such
failure and everything will pick up where it left off. It takes some
planning but is worth it.
> So: if you don't know exactly what you're doing, leave fsync alone.
I agree -- leave fsync alone. You get benefit from synchronous_commit
without the corruption risk.
The other advice on boosting checkpoint segments and timeout are spot
on. Make them pretty big and it will make your import go way faster.
If you have a spare disk on which to move the checkpoint segments so
that you eliminate the seek time on them, move them to get even more
speed. After your import, you can make the number of segments smaller
again if that suits your workload.