X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 4AE68D1B50F for ; Thu, 9 Oct 2003 23:33:06 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 59308-08 for ; Thu, 9 Oct 2003 20:32:23 -0300 (ADT) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id 34A33D1B4E2 for ; Thu, 9 Oct 2003 20:32:20 -0300 (ADT) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id A5A183697D; Thu, 9 Oct 2003 19:32:22 -0400 (EDT) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 1A7kGc-0007Yg-00; Thu, 09 Oct 2003 19:32:22 -0400 To: Jeff Cc: pgsql-performance@postgresql.org Subject: Re: backup/restore - another area. References: In-Reply-To: From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 09 Oct 2003 19:32:22 -0400 Message-ID: <878ynu9ed5.fsf@stark.dyndns.tv> Lines: 40 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/312 X-Sequence-Number: 4060 Jeff writes: > Idea #1: > Use an LVM and take a snapshop - archive that. > From the way I see it. the downside is the LVM will use a lot of space > until the snapshot is removed. Also PG may be in a slightly inconsistant > state - but this should "appear" to PG the same as if the power went out. > > For restore, simply unarchive this snapshot and point postgres at it. Let > it recover and you are good to go. > > Little overhead from what I see... > I'm leaning towards this method the more I think of it. I don't quite follow your #2 so I can only comment on the above idea of using an LVM snapshot. If you have the hardware and the LVM-fu to be able to do this properly I would recommend it. We actually used to do this with veritas even on Oracle which has full online backup support simply because it was much much faster and the snapshot could be backed up during peak times without any significant performance impact. That's partly because Veritas and Hitachi storage systems kick butt though. Depending on the systems you're considering you may or may not have nearly the same success. Note, you should *test* this backup. You're depending on some subtle semantics with this. If you do it slightly wrong or the LVM does something slightly wrong and you end up with an inconsistent snapshot or missing some critical file the whole backup could be useless. Also, I wouldn't consider this a replacement for having a pg_dump export. In a crisis when you want to restore everything *exactly* the way things were you want the complete filesystem snapshot. But if you just want to load a table the way it was the day before to compare, or if you want to load a test box to do some performance testing, or whatever, you'll need the logical export. -- greg