X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8671B3A604E for ; Mon, 13 Dec 2004 16:37:37 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 81766-08 for ; Mon, 13 Dec 2004 16:37:30 +0000 (GMT) Received: from lorax.kcilink.com (lorax.kciLink.com [206.112.95.1]) by svr1.postgresql.org (Postfix) with ESMTP id 65CE13A62C1 for ; Mon, 13 Dec 2004 16:37:30 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by lorax.kcilink.com (Postfix) with ESMTP id E21983E44 for ; Mon, 13 Dec 2004 11:37:29 -0500 (EST) Received: from lorax.kcilink.com ([127.0.0.1]) by localhost (lorax.kcilink.com [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 34386-04-3 for ; Mon, 13 Dec 2004 11:37:28 -0500 (EST) Received: by lorax.kcilink.com (Postfix, from userid 8) id 3B4E73E42; Mon, 13 Dec 2004 11:37:28 -0500 (EST) To: pgsql-performance@postgresql.org Path: not-for-mail From: Vivek Khera Newsgroups: ml.postgres.performance Subject: Re: pg_restore taking 4 hours! Date: Mon, 13 Dec 2004 11:37:28 -0500 Organization: Khera Communications, Inc., Rockville, MD Lines: 25 Message-ID: References: <41ADA82A.7070405@carvalhaes.net> NNTP-Posting-Host: yertle.kcilink.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Trace: lorax.kcilink.com 1102955848 23890 65.205.34.180 (13 Dec 2004 16:37:28 GMT) X-Complaints-To: daemon@kciLink.com NNTP-Posting-Date: Mon, 13 Dec 2004 16:37:28 +0000 (UTC) User-Agent: Gnus/5.1002 (Gnus v5.10.2) XEmacs/21.4 (Security Through Obscurity, berkeley-unix) Cancel-Lock: sha1:WElxwPPo+t3rfVDgripWtA4nV8M= X-Virus-Scanned: amavisd-new at kcilink.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200412/160 X-Sequence-Number: 9535 >>>>> "RC" == Rodrigo Carvalhaes writes: RC> Hi! RC> I am using PostgreSQL with a proprietary ERP software in Brazil. The RC> database have around 1.600 tables (each one with +/- 50 columns). RC> My problem now is the time that takes to restore a dump. My customer RC> database have arount 500mb (on the disk, not the dump file) and I am RC> making the dump with pg_dump -Fc, my dumped file have 30mb. To make RC> the dump, it's taking +/- 1,5 hours BUT to restore (using pg_restore ) RC> it it takes 4 - 5 hours!!! I regularly dump a db that is compressed at over 2Gb. Last time I did a restore on the production box it took about 3 hours. Restoring it into a development box with a SATA RAID0 config takes like 7 hours or so. The biggest improvement in speed to restore time I have discovered is to increase the checkpoint segments. I bump mine to about 50. And moving the pg_xlog to a separate physical disk helps a lot there, too. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-301-869-4449 x806 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/