[ 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]>