[ On Friday, June 21, 2002 at 23:59:27 (+0200), Ragnar Kjørstad wrote: ]
> Subject: Re: Backing up PostgreSQL?
>
> > Remember I've only been talking about the backed up files being in a
> > self-consistent state and not requiring roll-back or roll-forward of
> > any transaction logs after restore.
>
> If you don't want your database to do roll-back or roll-forward
> backup-snapshots will work for you. Neither will power-failures. And in
> theory a clean shutdown could also leave the database in a state where
> it required roll-forward on restart, but my guess is that's not the
> case.
Actually I'd assume the opposite -- that even a clean shutdown of the
engine could leave an uncommitted transaction in the database image and
thus might require a log UNDO (not a log REDO).....
The difficulty of course being those pesky long-running transactions,
and the fact that you can't always shut down the clients first (and thus
hopefully trigger a proper undo of the open transactions before the
database engine itself is shut down).
> There are no writes to the filesystem at all while the snapshot is in
> progress. The LVM-driver will block all data-access until it is
> completed. If not, it wouldn't be a snapshot, would it?
I realize that -- but it doesn't mean the snapshot couldn't begin
between two inter-related writes to two separate files (or even two
separate but inter-related writes to two separate pages in the same
file). If both writes are necessary to result in a consistent DB then
the snapshot will necessarily be inconsistent by definition. If you
want the snapshot to be in a self-consistent state you have to somehow
pause the database at a point where there are no open transactions and
make sure the database engine has issued writes for every bit of data
for all committed transactions (and potentially even that it has closed
all its open files so that FS metadata is also updated). The best way
to do this seems to be to shut down the DB engine, and it's certainly
the only application-independent procedure I can think of.
> Huh? Why would you want a seperate backup of the database transaction
> log? The log is stored in a file together with the rest of the database,
> and will be included in the snapshot and the backup.
Yeah, but I'm assuming that I'll have to manually UNDO any uncommitted
transactions, as per the 7.2.1 manual.....
> If you didn't backup the transaction-log at the exact same time you
> backed up the rest of the files it would not work - it would be
> inconsistent and there could be data missing.
indeed -- however I've been trying to think of a way to find open
transactions in an incomplete transaction log. If an open transaction
was not opened in the current log then I've got to look for it to be
closed to know that it was incomplete (and thus needs to be undone) at
the time the FS snapshot was taken. I was probably barking up the wrong
tree with that idea though....
--
Greg A. Woods
+1 416 218-0098; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Planix, Inc. <[EMAIL PROTECTED]>; VE3TCP; Secrets of the Weird <[EMAIL PROTECTED]>
