X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 1F7989F9398 for ; Wed, 24 May 2006 17:05:28 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 30245-06 for ; Wed, 24 May 2006 17:05:15 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 2D5099F94EA for ; Wed, 24 May 2006 17:05:15 -0300 (ADT) Received: from eciweb.eldocomp.com (eciweb.eldocomp.com [205.159.99.39]) by svr4.postgresql.org (Postfix) with ESMTP id 69EF85AF04D for ; Wed, 24 May 2006 20:05:14 +0000 (GMT) Received: by eciweb.eldocomp.com with XWall v3.36 ; Wed, 24 May 2006 13:05:05 -0700 From: Naomi Walker To: Tom Lane Cc: Ruairi , "pgsql-admin@postgresql.org" Subject: Re: Restore of pg_dump taking a long time... Date: Wed, 24 May 2006 13:05:04 -0700 X-Assembled-By: XWall v3.36 Message-ID: <4474BC70.6080402@eldocomp.com> Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/343 X-Sequence-Number: 21875 I would typically drop the indicies before taking the dump, and recreate in the new instance. I know the loads go alot faster that way. Perhaps the index creation would be the same. Tom Lane wrote: > "Ruairi" writes: >> I'm currently restoring a fairly large DB from a pg_dump and it's taking=20 >> about 12 hours to finish. The main part of this time is spent creating=20 >> indexes. Is there anyway I can speed up the restore process, or do i just= =20 >> have to wait? >=20 > There's not much you can do to improve the performance of a restore > already in progress. Next time you might want to think about kicking up > maintenance_work_mem before you start it; that's about the only thing > you can really do to speed up index builds. >=20 > (Actually, if there are lots of indexes yet to be built, increasing > the setting in postgresql.conf and SIGHUP'ing the postmaster could > be worth doing, as that should affect the builds yet to be done.) >=20 > regards, tom lane >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster --=20 ---------------------------------------------------------------------------- Naomi Walker Chief Information Officer Eldorado Computing, Inc. nwalker@eldocomp.com 602-604-3100 ---------------------------------------------------------------------------- What lies behind us and what lies before us are tiny matters compared to what lies within us. - William Morrow ---------------------------------------------------------------------------- -- CONFIDENTIALITY NOTICE -- Information transmitted by this=A0e-mail is proprietary to MphasiS and/or it= s Customers and is intended for use only by the individual or entity to whic= h it is addressed, and may contain information that is=A0privileged, confide= ntial or exempt from disclosure under applicable law. If you are not the int= ended recipient or it appears that this mail has been forwarded to you witho= ut proper authority, you are notified that any use or dissemination of this = information in any manner is strictly prohibited. In such cases, please noti= fy us immediately at mailmaster@mphasis.com and delete this mail from your r= ecords.