Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id EAB629F939C for ; Thu, 21 Dec 2006 13:40:37 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 99677-08 for ; Thu, 21 Dec 2006 13:40:26 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mout.perfora.net (mout.perfora.net [217.160.230.41]) by postgresql.org (Postfix) with ESMTP id A37C09FA217 for ; Thu, 21 Dec 2006 13:40:27 -0400 (AST) Received: from [12.192.126.13] (helo=hawman.anticsoffline.com) by mrelay.perfora.net (node=mrelayus1) with ESMTP (Nemesis), id 0MKp2t-1GxRtt2QYq-0000Hz; Thu, 21 Dec 2006 12:40:27 -0500 Subject: Re: Very Very Slow Database Restore From: Joseph McClintock Reply-To: joe.mcclintock@antics.com To: Tom Lane Cc: pgsql-admin@postgresql.org In-Reply-To: <14149.1166680064@sss.pgh.pa.us> References: <1166646860.3941.4.camel@mcclintock.anticsoffline.com> <14149.1166680064@sss.pgh.pa.us> Content-Type: text/plain Organization: Antice On-Line Date: Thu, 21 Dec 2006 09:43:23 -0800 Message-Id: <1166723003.4208.6.camel@mcclintock.anticsoffline.com> Mime-Version: 1.0 X-Mailer: Evolution 2.6.0 Content-Transfer-Encoding: 7bit X-Provags-ID: perfora.net abuse@perfora.net login:e767c07d8b8e9b65c1eb585a36287271 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL, BAYES_50, SPF_HELO_PASS X-Spam-Level: X-Archive-Number: 200612/289 X-Sequence-Number: 24081 Thanks for the feed back. Doubling the work_men from 10240 to 20480 seemed to make the process faster but then I ran out of disk space. Shared buffers are at 2000 which I can try increasing. I now need to move the database off the system partition to my working partition which has more space. I hope don't have to re-install Postges, that would be a real bummer. --Joe On Thu, 2006-12-21 at 00:47 -0500, Tom Lane wrote: > Joseph McClintock writes: > > Now our database has grown and the pg_dump give me a 2.1 GB file which > > is taking 12 hours or more to restore, Yickes! I've tried compressing > > the dump file: > > Compressing the dump file will make the restore slower, most likely, > because of the extra CPU effort to decompress. It certainly won't > create any great savings. > > > The database configuration is pretty much out of the box. > > You probably want to increase maintenance_work_mem (to speed index > creation) and checkpoint_segments (general rule for any update-intensive > situation) ... and for 8.0 you'd maybe need larger shared_buffers etc. > The postgresql techdocs website has lots of general advice about > parameter settings --- the only thing specific to restore is you > want high maintenance_work_mem. > > regards, tom lane