X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id B862E9F9CC1 for ; Wed, 3 May 2006 14:43:06 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 12938-07 for ; Wed, 3 May 2006 14:42:54 -0300 (ADT) X-Greylist: delayed 00:44:14.084465 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id A9BAA9F9C1D for ; Wed, 3 May 2006 14:42:54 -0300 (ADT) Received: from ecintweb.eldocomp.com (ecintweb.eldocomp.com [205.159.99.8]) by svr4.postgresql.org (Postfix) with ESMTP id D727F5B6124 for ; Wed, 3 May 2006 16:58:37 +0000 (GMT) Received: by ecintweb.eldocomp.com with XWall v3.36 ; Wed, 3 May 2006 09:58:34 -0700 From: Naomi Walker To: Simon Riggs Cc: Steve Burrows , "pgsql-admin@postgresql.org" Subject: Re: Backing up large databases Date: Wed, 3 May 2006 09:58:32 -0700 X-Assembled-By: XWall v3.36 Message-ID: <4458E138.6010904@eldocomp.com> Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/28 X-Sequence-Number: 21560 Simon Riggs wrote: > On Fri, 2006-04-28 at 15:57 +0000, Steve Burrows wrote: >> I am struggling to find an acceptable way of backing up a PostgreSQL >> 7.4 database.=20 Depending on your OS and hardware setup, you may have other options, such as Veritas snapshotting. It is expensive, but saves time. >> >> The database is quite large, currently it occupies about 180GB, >> divided into two elements, a set of active tables and a set of archive >> tables which are only used for insertions.=20 >> >> I ran pg_dump -Fc recently, it took 23.5 hours to run, and output a >> single file of 126GB. Obviously as the database continues to grow it >> will soon be so large that it cannot be pg_dumped within a day. >> Running rsync to do a complete fresh copy of the pgsql file structure >> took 4 hours, but later that day running another iteration of rsync >> (which should have only copied changed files) took 3 hours, and I >> cannot afford to have the db down that long. >> >> Anybody with any ideas?=20 >=20 > You need not backup the whole database in one go. >=20 > You can copy only changed data out of the tables using your knowledge of > the mail store. That way you'll not need to have such a long running > backup and it won't be so large either. You can then reassemble the > pieces later into a new table in case of recovery. >=20 --=20 ---------------------------------------------------------------------------- Naomi Walker Chief Information Officer Eldorado Computing, Inc. nwalker@eldocomp.com 602-604-3100 ---------------------------------------------------------------------------- What lies behind us and what lies before us are tiny matters compared to what lies within us. - William Morrow ---------------------------------------------------------------------------- -- CONFIDENTIALITY NOTICE -- Information transmitted by this=A0e-mail is proprietary to MphasiS and/or it= s Customers and is intended for use only by the individual or entity to whic= h it is addressed, and may contain information that is=A0privileged, confide= ntial or exempt from disclosure under applicable law. If you are not the int= ended recipient or it appears that this mail has been forwarded to you witho= ut proper authority, you are notified that any use or dissemination of this = information in any manner is strictly prohibited. In such cases, please noti= fy us immediately at mailmaster@mphasis.com and delete this mail from your r= ecords.