[ On Friday, June 21, 2002 at 18:15:28 (+0100), Paul Jakma wrote: ]
> Subject: Re: Backing up PostgreSQL?
>
> On Fri, 14 Jun 2002, Greg A. Woods wrote:
>
> > That's irrelevant from PostgreSQL's point of view. There's no sure
> > way to tell the postgresql process(es) to make the on-disk database
> > image consistent before you create the snapshot.
>
> if it's pg 7.1+ then it has write-ahead-logging, and the on-disk state
> /is/ guaranteed to be recoverable to a consistent state.
>
> in which case snapshot+backup of snapshot is a valid way to backup
> pgsql.
Yes, I agree with this, assuming you're talking about an increment to
the currently available release that puts you up ahead to some mythical
future vapour-ware. The ability to reliably restore consistency in such
a backup copy of the files not only depends on write-ahead-logging
support, but also on properly functioning REDO _and_ UNDO functions.
Note though that currently the UNDO operation is currently still
unimplemented in PostgreSQL-7.2.1 -- thus my original concern, and along
with other issues why I say that pg_dump really is the only best way to
do a backup of a pgsql database.
I.e. PostgreSQL releases up to, and including, 7.2.1 do not provide a
way to guarantee a database is always recoverable to a consistent state.
Current releases do not have a built-in way to remove changes already
made to data pages by uncomitted transactions. From what I've read and
heard from pgsql developers you sholdn't hold your breath for this
feature either -- it probably requires a re-design of the
W.A.L. internals to realise in any reasonable implementation.
When you restore a database from backups you really do want to just
immediately start the database engine and know that the restored files
have full integrity. You realy don't want to have to rely on W.A.L.
When you restore a database from backups during a really bad disaster
recovery procedure you sometimes don't even want to have to depend on
the on-disk-file format being the same. You want to be able to load the
data into an arbitrary version of the software on an arbitrary platform.
--
Greg A. Woods
+1 416 218-0098; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Planix, Inc. <[EMAIL PROTECTED]>; VE3TCP; Secrets of the Weird <[EMAIL PROTECTED]>
