On Wed, Aug 25, 2010 at 8:48 PM, Craig Ringer <craig@xxxxxxxxxxxxxxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
