Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 994B09F9386 for ; Tue, 19 Dec 2006 13:42:02 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 69562-06-2 for ; Tue, 19 Dec 2006 13:41:50 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.168]) by postgresql.org (Postfix) with ESMTP id D02499FA373 for ; Tue, 19 Dec 2006 13:41:50 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so1840488ugf for ; Tue, 19 Dec 2006 09:41:49 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Va1U+uwsnQWkczex/SL4f1XqEW2Ez1Lf9tzcCnQmwXQUvCugmzB4r0DN2vqxXM89Ill8/oexSynz7V2Nq2Et9/zmcY6m9rSvZAcbXuH05EjHr0QOAqhCkgUsL3zHPB1IFvStHtsUiGKeR7QQ7ohvpqgYEBIka4nwGihmoEeWEQg= Received: by 10.67.22.14 with SMTP id z14mr7503955ugi.1166550109752; Tue, 19 Dec 2006 09:41:49 -0800 (PST) Received: by 10.66.216.15 with HTTP; Tue, 19 Dec 2006 09:41:49 -0800 (PST) Message-ID: Date: Tue, 19 Dec 2006 23:11:49 +0530 From: "Rajesh Kumar Mallah" To: arnaulist@andromeiberica.com Subject: Re: Suggestions needed about how to dump/restore a database Cc: pgsql-admin@postgresql.org In-Reply-To: <458821B9.80303@andromeiberica.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <45881D1A.6090904@andromeiberica.com> <458821B9.80303@andromeiberica.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/267 X-Sequence-Number: 24059 On 12/19/06, Arnau wrote: > 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. thats strange , we dump +20GB data in 2 hrs or so. 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, if the two biggest tables are *not critical* for application availability i can dump out their data separately into two SQL files and later restore it. once you dump out the data you can drop the tables from the production DB before dumping out and see how long it takes. pg_dump -t schema.table -h hostname -U user can dump out a specific schema.table. (the exact options are version dependent, which version btw u using?) it is always desired to know the root cause of why pg_dump is taking so long in your machine , but in worst case you could take the approach you suggested. 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, > >