Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 0251E2E0097 for ; Wed, 7 May 2008 18:27:33 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 34101-03-2 for ; Wed, 7 May 2008 18:27:09 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from outmail137074.authsmtp.com (outmail137074.authsmtp.com [62.13.137.74]) by developer.postgresql.org (Postfix) with ESMTP id 4C9762E007E for ; Wed, 7 May 2008 18:27:13 -0300 (ADT) Received: from mail-c188.authsmtp.com (mail-c188.authsmtp.com [62.13.128.25]) by punt4.authsmtp.com (8.14.1/8.14.1/Kp) with ESMTP id m47LR9mA043123; Wed, 7 May 2008 22:27:09 +0100 (BST) Received: from [192.168.0.3] (85-211-73-85.dyn.gotadsl.co.uk [85.211.73.85]) (authenticated bits=0) by mail.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id m47LR76B007333; Wed, 7 May 2008 22:27:07 +0100 (BST) Subject: Re: Backup/Restore of single table in multi TB database From: Simon Riggs To: John Smith Cc: pgsql-general@postgresql.org In-Reply-To: References: Content-Type: text/plain Date: Wed, 07 May 2008 22:28:44 +0100 Message-Id: <1210195724.4268.231.camel@ebony.site> Mime-Version: 1.0 X-Mailer: Evolution 2.8.2 Content-Transfer-Encoding: 7bit X-Server-Quench: 5884801f-1c7c-11dd-aecc-001871e930f4 X-AuthRoute: OCdxZQATClZOTQEd DAteCiNZVAwpPBRK HVkIKg5MJUcNSQVJ NksadBtFaAJbYFhI HGQLW1xEUl97W2R/ aw0fZQBDYEtPQQJp TkBMXVBXCwUmXBgG enwZVGtydwJFe30+ ZEdrXz5eVEx+fUZ8 FFNVRmpVeGZhPDQC ARULJB5UcAJCeR4Q Y1N/AndDAzANdhEX MikeBRkVCH11IS9W XxlKF18JXUERE3Y7 SVgZHDMgGUBNWyw5 KxFuK1hUGVsVLkR6 DXFpVVsEOhUbEQA2 X-Authentic-SMTP: 61633235383639.cat.dmpriest.net.uk:953/Kp X-Report-SPAM: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200805/241 X-Sequence-Number: 132640 On Wed, 2008-05-07 at 13:02 -0700, John Smith wrote: > I have a large database (multiple TBs) where I'd like to be able to do > a backup/restore of just a particular table (call it foo). Because > the database is large, the time for a full backup would be > prohibitive. Also, whatever backup mechanism we do use needs to keep > the system online (i.e., users must still be allowed to update table > foo while we're taking the backup). Have a look at pg_snapclone. It's specifically designed to significantly improve dump times for very large objects. http://pgfoundry.org/projects/snapclone/ -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com