X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 298D69DCA11 for ; Wed, 18 Jan 2006 18:12:12 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 06002-04 for ; Wed, 18 Jan 2006 18:12:13 -0400 (AST) X-Greylist: delayed 00:07:10.682773 by SQLgrey- Received: from mail.gxs.com.au (mail.gxs.com.au [202.177.197.60]) by postgresql.org (Postfix) with ESMTP id 978209DCA1F for ; Wed, 18 Jan 2006 18:12:08 -0400 (AST) Received: from beastyfahr.gissyd.local ([211.26.160.154]) by mail.gxs.com.au (8.11.6/8.11.6) with ESMTP id k0IM4M005289; Thu, 19 Jan 2006 09:04:22 +1100 Subject: Re: Backup - filesystem snapshots From: Dmitri Kushak Reply-To: dmitri.kushak@globalx.com.au To: Chris Jewell Cc: "pgsql-admin@postgresql.org" In-Reply-To: <43CEA0D8.8060901@lancaster.ac.uk> References: <43CEA0D8.8060901@lancaster.ac.uk> Content-Type: multipart/alternative; boundary="=-yzyNvpaAD9YZIg16F2U/" Organization: GlobalX Information Services P/L Date: Thu, 19 Jan 2006 08:59:06 +1100 Message-Id: <1137621547.2740.126.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.2.3 (2.2.3-2.fc4) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200601/199 X-Sequence-Number: 20287 --=-yzyNvpaAD9YZIg16F2U/ Content-Type: text/plain Content-Transfer-Encoding: 7bit Chris, I am having the same problem here and I am considering using SVN for DB backup, i.e. every snapshot (whichever way you do it, pg_dump seems OK) gets checked in on a regular basis, then if a table is accidentally deleted, it can still be checked out and restored. In case you do not know, SVN (Subversion, http://subversion.tigris.org) is like CVS (Concurrent version system). Of course the SVN repository itself needs to be backed up somewhere. Best regards, Dmitri On Wed, 2006-01-18 at 20:11 +0000, Chris Jewell wrote: > Hi, > > I'm trying to implement a backup strategy for a research database in > order to prevent again users accidentally dropping their data. > > My preferred method would be to create regular snapshots of the data > directory, and then send this to the backup server using rsync, with > hard-linking backup rotation. The backup data directories could then be > examined using a postmaster running on the backup server to extract any > accidentally deleted tables. > > My problem is how to do these snapshots: is it enough to create a hard > link to the directory, or is there still a risk that a currently running > transaction might introduce inconsistencies? I guess I could use the > pg_ctl -m 'Smart' command to stop the database after all clients have > disconnected, but I sometimes have users leaving their clients connected > all night. Is there any other way to suspend the postmaster such that > it finishes its current transaction and queues any other transactions > while the snapshot is taking place? Any other ideas of how I can create > such snapshots? > > Thanks, > > Chris --=-yzyNvpaAD9YZIg16F2U/ Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit Chris,

I am having the same problem here and I am considering using SVN for DB backup, i.e. every snapshot (whichever way you do it, pg_dump seems OK)
gets checked in on a regular basis, then if a table is accidentally deleted, it can still be checked out and restored. In case you do not know,
SVN (Subversion, http://subversion.tigris.org) is like CVS (Concurrent version system).
Of course the SVN repository itself needs to be backed up somewhere.

Best regards,
Dmitri



On Wed, 2006-01-18 at 20:11 +0000, Chris Jewell wrote:
Hi,

I'm trying to implement a backup strategy for a research database in
order to prevent again users accidentally dropping their data.

My preferred method would be to create regular snapshots of the data
directory, and then send this to the backup server using rsync, with
hard-linking backup rotation.  The backup data directories could then be
examined using a postmaster running on the backup server to extract any
accidentally deleted tables.

My problem is how to do these snapshots: is it enough to create a hard
link to the directory, or is there still a risk that a currently running
transaction might introduce inconsistencies?  I guess I could use the
pg_ctl -m 'Smart' command to stop the database after all clients have
disconnected, but I sometimes have users leaving their clients connected
all night.  Is there any other way to suspend the postmaster such that
it finishes its current transaction and queues any other transactions
while the snapshot is taking place?  Any other ideas of how I can create
such snapshots?

Thanks,

Chris
--=-yzyNvpaAD9YZIg16F2U/--