Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Ueole-0004lg-28 for pgsql-performance@arkaria.postgresql.org; Tue, 21 May 2013 15:46:26 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1Ueold-0007YO-Hc for pgsql-performance@arkaria.postgresql.org; Tue, 21 May 2013 15:46:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Ueolc-0007Wz-0Y for pgsql-performance@postgresql.org; Tue, 21 May 2013 15:46:24 +0000 Received: from proofpoint1.mail.rice.edu ([128.42.201.100] helo=pp1.rice.edu) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UeolW-0007gS-C3 for pgsql-performance@postgresql.org; Tue, 21 May 2013 15:46:23 +0000 Received: from pps.filterd (pp1.rice.edu [127.0.0.1]) by pp1.rice.edu (8.14.5/8.14.5) with SMTP id r4L9I3Pk014561; Tue, 21 May 2013 10:46:13 -0500 Received: from mh2.mail.rice.edu (mh2.mail.rice.edu [128.42.201.21]) by pp1.rice.edu with ESMTP id 1c3847s74a-1; Tue, 21 May 2013 10:46:13 -0500 X-Virus-Scanned: by amavis-2.7.0 at mh2.mail.rice.edu, auth channel X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no Received: from aart.rice.edu (aart.rice.edu [168.7.56.48]) by mh2.mail.rice.edu (Postfix) with ESMTP id C7953500125; Tue, 21 May 2013 10:46:13 -0500 (CDT) Received: by aart.rice.edu (Postfix, from userid 18612) id BEC521006EE; Tue, 21 May 2013 10:46:13 -0500 (CDT) Date: Tue, 21 May 2013 10:46:13 -0500 From: "ktm@rice.edu" To: Evgeny Shishkin Cc: Jeison Bedoya , pgsql-performance@postgresql.org Subject: Re: performance database for backup/restore Message-ID: <20130521154613.GE12507@aart.rice.edu> References: <519B743F.7040407@audifarma.com.co> <39946120-F3C6-4EEF-8686-E541F865ED5A@gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <39946120-F3C6-4EEF-8686-E541F865ED5A@gmail.com> User-Agent: Mutt/1.5.20 (2009-12-10) X-Pg-Spam-Score: -3.0 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On Tue, May 21, 2013 at 05:28:31PM +0400, Evgeny Shishkin wrote: > > On May 21, 2013, at 5:18 PM, Jeison Bedoya wrote: > > > Hi people, i have a database with 400GB running in a server with 128Gb RAM, and 32 cores, and storage over SAN with fiberchannel, the problem is when i go to do a backup whit pg_dumpall take a lot of 5 hours, next i do a restore and take a lot of 17 hours, that is a normal time for that process in that machine? or i can do something to optimize the process of backup/restore. > > > > I'd recommend you to dump with > > pg_dump --format=c > > It will compress the output and later you can restore it in parallel with > > pg_restore -j 32 (for example) > > Right now you can not dump in parallel, wait for 9.3 release. Or may be someone will back port it to 9.2 pg_dump. > > Also during restore you can speed up a little more by disabling fsync and synchronous_commit. > If you have the space and I/O capacity, avoiding the compress option will be much faster. The current compression scheme using zlib type compression is very CPU intensive and limits your dump rate. On a system that we have, a dump without compression takes 20m and with compression 2h20m. The parallel restore make a big difference as well. Regards, Ken -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance