Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 728DC9F9F08 for ; Tue, 19 Dec 2006 13:30:44 -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 32457-02 for ; Tue, 19 Dec 2006 13:30:35 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from fafnir.androme.com (fafnir.androme.com [62.58.96.158]) by postgresql.org (Postfix) with ESMTP id 76C4A9F946C for ; Tue, 19 Dec 2006 13:30:35 -0400 (AST) Received: by fafnir.androme.com (Postfix, from userid 1003) id 992B4B96221; Tue, 19 Dec 2006 18:30:34 +0100 (CET) Message-ID: <458821B9.80303@andromeiberica.com> Date: Tue, 19 Dec 2006 18:30:33 +0100 From: Arnau Reply-To: arnaulist@andromeiberica.com User-Agent: Thunderbird 1.5.0.8 (Windows/20061025) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: Suggestions needed about how to dump/restore a database References: <45881D1A.6090904@andromeiberica.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/264 X-Sequence-Number: 24056 Hi Jeff, > On Tue, 19 Dec 2006, Arnau wrote: > >> I've got a DB in production that is bigger than 2GB that dumping it >> takes more than 12 hours. I have a new server to replace this old one >> where I have restore the DB's dump. The problem is I can't afford to >> have the server out of business for so long, so I need your advice about >> how you'd do this dump/restore. The big amount of data is placed in two >> tables (statistics data), so I was thinking in dump/restore all except >> this two tables and once the server is running again I'd dump/restore >> this data. The problem is I don't know how exactly do this. > > Arnau, > > 2GB and it takes 12 hours? What sort of server is this running on? > Does your postgresql.conf have all default values perhaps? I routinely > dump DBs that are 4-8GB in size and it takes about 10-15 minutes. > It's a dual Xeon with 4 GB of ram and with a RAID 5. Probably it has the default values. Any suggestion about what parameters I should change to speed it up? -- Arnau