From: Chris Jewell <c.jewell@lancaster.ac.uk>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Backup - filesystem snapshots
Date: Wed, 18 Jan 2006 20:11:04 +0000
Message-ID: <43CEA0D8.8060901@lancaster.ac.uk> (raw)
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
--
Chris Jewell
Dept of Maths and Statistics
Fylde College
Lancaster University
Lancaster
Lancs
LA1 4YF
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: c.jewell@lancaster.ac.uk
Subject: Re: Backup - filesystem snapshots
In-Reply-To: <43CEA0D8.8060901@lancaster.ac.uk>
References: <43CEA0D8.8060901@lancaster.ac.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox