On Fri, Dec 01, 2000 at 11:48:23AM -0800, Don Baccus wrote:
> At 11:09 AM 12/1/00 -0800, Nathan Myers wrote:
> >On Fri, Dec 01, 2000 at 10:01:15AM +0100, Zeugswetter Andreas SB wrote:
>
> >> If you need to restore from offsite backup you loose transactions
> >> unless you transfer the WAL synchronously with every commit.
>
> >Currently the only way to avoid losing those transactions is by
> >replicating transactions at the application layer. That is, the
> >application talks to two different database instances, and enters
> >transactions into both. That's pretty hard to retrofit into an
> >existing application, so you'd really rather have replication in
> >the database. Of course, that's something PostgreSQL, Inc. is also
> >working on.
>
> Recovery alone isn't quite that difficult. You don't need to instantiate
> your database instance until you need to apply the archived transactions,
> i.e. after catastrophic failure destroys your db server.
True, it's sufficient for the application just to log the text of
its updating transactions off-site. Then, to recover, instantiate
a database from a backup and have the application re-run its
transactions.
> You need to do two things:
(Remember, we're talking about what you could do *now*, with 7.1.
Presumably with 7.2 other options will open.)
> 1. Transmit a consistent (known-state) snapshot of the database offsite.
>
> 2. Synchronously tranfer the WAL as part of every commit (question, do
> wait to log a "commit" locally until after the remote site acks that
> it got the WAL?)
>
> Then you take a new machine, build a database out of the snapshot, and
> apply the archived redo logs and off you go. If you get tired of saving
> oodles of redo archives, you make a new snapshot and accumulate the
> WAL from that point forward.
I don't know of any way to synchronously transfer the WAL, currently.
Anyway, I would expect doing it to interfere seriously with performance.
The "wait to log a 'commit' locally until after the remote site acks that
it got the WAL" is (akin to) the familiar two-phase commit.
Nathan Myers
ncm@zembu.com