Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 82FA29FA363 for ; Tue, 19 Dec 2006 17:44:21 -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 85675-01 for ; Tue, 19 Dec 2006 17:44:10 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.186]) by postgresql.org (Postfix) with ESMTP id 04F1A9F946C for ; Tue, 19 Dec 2006 17:44:07 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id x4so2583691nfb for ; Tue, 19 Dec 2006 13:44:06 -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:references; b=nBjHAX3wqpVJUVYKl8FnkfTubw0L3Na0TuRJjngelgAl2jiPBAZ8o+kG+RPKBePCUn0M7IfUc5m9Am3LPRlaNVooa8hiYceyjmZcvB6SiisKKsyiNX8cR2AF/3SYTLrOt40N1Itep8ij/+FC2Oo8LtP3zhf1nP84i9x+o0vI3hs= Received: by 10.82.113.6 with SMTP id l6mr1276925buc.1166564646080; Tue, 19 Dec 2006 13:44:06 -0800 (PST) Received: by 10.82.112.5 with HTTP; Tue, 19 Dec 2006 13:44:06 -0800 (PST) Message-ID: <1d219a6f0612191344x2f772a7el8ef9117a057c9cd8@mail.gmail.com> Date: Tue, 19 Dec 2006 16:44:06 -0500 From: "Chris Hoover" To: arnaulist@andromeiberica.com Subject: Re: Suggestions needed about how to dump/restore a database Cc: pgsql-admin@postgresql.org In-Reply-To: <45881D1A.6090904@andromeiberica.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_10390_8816604.1166564646046" References: <45881D1A.6090904@andromeiberica.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/274 X-Sequence-Number: 24066 ------=_Part_10390_8816604.1166564646046 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline One other option is to shut the database down competely, and then do a copy of the file system the new server. I have done this when I need to move a very large database to a new server. I can copy 500GB's in a couple of hours, where restoring my large databases backups would take 10+ hours. Just make sure you are keeping postgres at the same version level. HTH, Chris On 12/19/06, Arnau wrote: > > Hi all, > > 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. > > Any suggestion? > > Thanks > -- > Arnau > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > ------=_Part_10390_8816604.1166564646046 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline One other option is to shut the database down competely, and then do a copy of the file system the new server.  I have done this when I need to move a very large database to a new server.  I can copy 500GB's in a couple of hours, where restoring my large databases backups would take 10+ hours.  Just make sure you are keeping postgres at the same version level.

HTH,

Chris

On 12/19/06, Arnau <arnaulist@andromeiberica.com> wrote:
Hi all,

   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.

   Any suggestion?

Thanks
--
Arnau


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

------=_Part_10390_8816604.1166564646046--