X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id CD5FF9DC98B for ; Tue, 17 Jan 2006 16:40:45 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 06940-01 for ; Tue, 17 Jan 2006 16:40:46 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 1E2C59DC988 for ; Tue, 17 Jan 2006 16:40:43 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 3CE7739830; Tue, 17 Jan 2006 14:40:44 -0600 (CST) Date: Tue, 17 Jan 2006 14:40:44 -0600 From: "Jim C. Nasby" To: Tom Lane Cc: Benjamin Arai , pgsql-performance@postgresql.org Subject: Re: Ensuring data integrity with fsync=off Message-ID: <20060117204044.GD17896@pervasive.com> References: <000601c61936$38548770$fa48fd0a@uni> <20897.1137264103@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20897.1137264103@sss.pgh.pa.us> X-Operating-System: FreeBSD 6.0-RELEASE amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.093 required=5 tests=[AWL=0.093] X-Spam-Score: 0.093 X-Spam-Level: X-Archive-Number: 200601/262 X-Sequence-Number: 16740 On Sat, Jan 14, 2006 at 01:41:43PM -0500, Tom Lane wrote: > "Benjamin Arai" writes: > > Right now I run "sync" afte the updates have finished to ensure that the > > data is synced to disk but I am concerned about the segment data and > > anything else I am missing that PostgreSQL explicitly handles. Is there > > something I can do in addition to sync to tell PostgreSQL exlplicitly that > > it is time to ensure everything is stored in its final destionation and etc? > > You need to give PG a CHECKPOINT command to flush stuff out of its > internal buffers. After that finishes, a manual "sync" commnd will > push everything down to disk. > > You realize, of course, that a system failure while the updates are > running might leave your database corrupt? As long as you are prepared > to restore from scratch, this might be a good tradeoff ... but don't > let yourself get caught without an up-to-date backup ... Another alternative that may (or may not) be simpler would be to run everything in one transaction and just let that commit at the end. Also, there is ongoing work towards allowing certain operations to occur without generating any log writes. Currently there is code submitted that allows COPY into a table that was created in the same transaction to go un-logged, though I think it's only in HEAD. In any case, there should be some features that could be very useful to you in 8.2. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461