Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 199069FBF59 for ; Thu, 22 Feb 2007 12:25:28 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 90751-04-3 for ; Thu, 22 Feb 2007 12:25:22 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id E72939FBF5F for ; Thu, 22 Feb 2007 12:25:21 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l1MGPLhv005873; Thu, 22 Feb 2007 11:25:21 -0500 (EST) To: ogjunk-pgjedan@yahoo.com cc: pgsql-admin@postgresql.org Subject: Re: Fastest DB restore options In-reply-to: <821429.69798.qm@web50304.mail.yahoo.com> References: <821429.69798.qm@web50304.mail.yahoo.com> Comments: In-reply-to ogjunk-pgjedan@yahoo.com message dated "Thu, 22 Feb 2007 07:43:13 -0800" Date: Thu, 22 Feb 2007 11:25:21 -0500 Message-ID: <5872.1172161521@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.06 tagged_above=0 required=5 tests=AWL, BAYES_50, SPF_HELO_PASS, SPF_PASS X-Spam-Level: X-Archive-Number: 200702/291 X-Sequence-Number: 24756 ogjunk-pgjedan@yahoo.com writes: > I normally dump with these options: > -d MyDB --clean --inserts --column-inserts --format=P > But the last time I tried that, the restore took foreeeeeeeeeeeeeever. --inserts is pretty expensive. > So I'm looking for the fastest way to import data from the old DB to the new one. Judging from pg_dump man page the following should be the fastest dump & restore: > -d MyDB --format=c --ignore-version Don't use --ignore-version; it's a good way to shoot yourself in the foot. pg_dump's default behavior is about as good as you can get; there are no optional switches that will make it faster. What you *can* do is make sure that the receiving system is properly configured before you start the restore --- increase maintenance_work_mem and checkpoint_segments in particular. See this page, especially the last section: http://www.postgresql.org/docs/8.2/static/populate.html regards, tom lane