Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id E347F9FA248 for ; Wed, 20 Dec 2006 17:04:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 96652-10 for ; Wed, 20 Dec 2006 17:04:19 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.libertyrms.com (vgateway.libertyrms.info [207.219.45.62]) by postgresql.org (Postfix) with ESMTP id 974159FA182 for ; Wed, 20 Dec 2006 17:04:21 -0400 (AST) Received: from dba5.int.libertyrms.com ([10.1.3.44]) by mail.libertyrms.com with esmtp (Exim 4.22) id 1Gx8bo-0006g2-Mn; Wed, 20 Dec 2006 16:04:16 -0500 Subject: Re: Suggestions needed about how to dump/restore a database From: Brad Nicholson To: Olivier Boissard Cc: Chris Hoover , arnaulist@andromeiberica.com, pgsql-admin@postgresql.org In-Reply-To: <4589046D.60603@cerene.fr> References: <45881D1A.6090904@andromeiberica.com> <1d219a6f0612191344x2f772a7el8ef9117a057c9cd8@mail.gmail.com> <4589046D.60603@cerene.fr> Content-Type: text/plain; charset=ISO-8859-1 Date: Wed, 20 Dec 2006 16:04:16 -0500 Message-Id: <1166648656.32117.13.camel@dba5.int.libertyrms.com> Mime-Version: 1.0 X-Mailer: Evolution 2.6.3 (2.6.3-1.fc5.5) Content-Transfer-Encoding: quoted-printable X-SA-Exim-Mail-From: bnichols@ca.afilias.info X-SA-Exim-Scanned: No; SAEximRunCond expanded to false X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/282 X-Sequence-Number: 24074 On Wed, 2006-12-20 at 10:37 +0100, Olivier Boissard wrote: > Chris Hoover a =E9crit : > > One other option is to shut the database down competely, and then do a=20 > > copy of the file system the new server. I have done this when I need=20 > > to move a very large database to a new server. I can copy 500GB's in=20 > > a couple of hours, where restoring my large databases backups would=20 > > take 10+ hours. Just make sure you are keeping postgres at the same=20 > > 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 o= ne > > where I have restore the DB's dump. The problem is I can't afford t= o > > 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/resto= re > > 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 t= o > > choose an index scan if your joining column's datatypes do n= ot > > match > > > > > How many tables have you got in your database ? >=20 > If you have only a few tables you can dump them one at a time This approach can get you into serious trouble. Say you a have two tables (a and b) that reference one another - you dump table a at time t1. You dump table b at time t2. In between t1 and t2, you delete a tuple from a and it's referenced tuple from b. Your dump is garbage. --=20 Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp.