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.
You need to do two things:
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
remotesite 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.
Of course, that's not a fast failover solution. The scenario you describe
leads to being able to quickly switch over to a backup server when the
primary server fails. Much better for 24/7/365-style computing.
Exactly what is PostgreSQL, Inc doing in this area? I've not seen
discussions about it here, and the two of the three most active developers
(Jan and Tom) work for Great Bridge, not PostgreSQL, Inc...
I should think Vadim should play a large role in any effort to add WAL-based
replication to Postgres.
- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.