Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from
	<pgsql-admin-owner+M40857=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
	id 1UOWAL-0002bk-Ha
	for pgsql-admin@arkaria.postgresql.org; Sat, 06 Apr 2013 16:40:33 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.72) (envelope-from
	<pgsql-admin-owner+M40857=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
	id 1UOWAL-0001BR-1E
	for pgsql-admin@arkaria.postgresql.org; Sat, 06 Apr 2013 16:40:33 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <jim@reptiles.org>) id 1UOWAK-0001BL-9T
	for pgsql-admin@postgresql.org; Sat, 06 Apr 2013 16:40:32 +0000
Received: from mail2.reptiles.org ([198.96.210.226])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <jim@reptiles.org>) id 1UOWAF-0007Hy-NB
	for pgsql-admin@postgresql.org; Sat, 06 Apr 2013 16:40:31 +0000
Received: from localhost by mailbox.reptiles.org
	via sendmail with STDIO (1668 bytes) (sender: <jim@reptiles.org>)
	(ident <jim> using UNIX) id <m1UOWAF-000t93C@mailbox.reptiles.org>
	for <pgsql-admin@postgresql.org>;
	Sat, 6 Apr 2013 12:40:27 -0400 (EDT)
	(Smail-3.2.0.121 2005-Nov-17 #10 built 2010-Jul-16)
Date: Sat, 6 Apr 2013 12:40:27 -0400
From: Jim Mercer <jim@reptiles.org>
To: pgsql-admin@postgresql.org
Subject: risks of backup using filesystem snapshot/dump ?
Message-ID: <20130406164027.GU24874@reptiles.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.20 (2009-06-14)
X-Pg-Spam-Score: -4.3 (----)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

hi,

i'm running pgsql 9.1.3 (soon to be upgraded to 9.2.x) on FreeBSD 8-STABLE.

i've got a db of some 550GB that i want to backup on a daily basis, as we
have had some nasty hardware/power/other issues of late.

with dump on FreeBSD, you can specify -L, which does a filesystem level
snapshot before the dump starts, and then clears it afterwards.

i did a backup using dump on the filesystems, except for $PGDATA, followed
by a pg_dumpall.
this took 4 hours, and ate some 30GB (after compression)

i'm currently doing a dump of the filesystems, including $PGDATA.
this is looking like it will take 6-7 hours, and looks like it will take
some 40-50GB (after compression)

if i were to attempt a restore by replaying the 'pg_dumpall', i suspect that
i would be looking at many hours, and possibly hiccups requiring re-runs, etc.

i'm alot more comfortable with the idea of just restoring the filesystem
dump.

so, the question becomes, if i restore a filesystem snapshot backup, the
DB will be in a 'crashed' state.

what, if any, risks are there with regards to corruption, or the inability to
actually use the restored DB?

-- 
Jim Mercer     Reptilian Research      jim@reptiles.org    +1 416 410-5633
"He who dies with the most toys is nonetheless dead"


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin