Re: Re: Backup and Recovery

On Thu, Jun 28, 2001 at 08:33:45AM -0700, John Moore wrote:
> matthew(at)hairy(dot)beasts(dot)org (Matthew Kirkwood) wrote in message news:<Pine(dot)LNX(dot)4(dot)33(dot)0106201212240(dot)25630-100000(at)sphinx(dot)mythic-beasts(dot)com>...
> > On Tue, 19 Jun 2001, Naomi Walker wrote:
> >
> > > Even more important that uptime to us, is to never put ourselves in a
> > > position where we could lose data. I understand I can do a hot backup
> > > with pg_dumpall. What we need on top of that is the ability to replay
> > > the transaction logs against the previous database archive. Without
> > > such a feature, even if I did a full backup a few times a day, we
> > > would be vulnerable to losing hours of data (which would not be
> > > acceptable to our users).
> >
> > This is what I'd like too (though I'm not that bothered about
> > rolling forward from a dump if I can just do it by replaying
> > logs onto real datafiles).
>
> With stock PostgreSQL... how many committed transactions can one lose
> on a simple system crash/reboot? With Oracle or Informix, the answer
> is zero. Is that true with PostgreSQL in fsync mode? If not, does it
> lose all in the log, or just those not yet written to the DB?

The answer is zero for PG as well. However, what happens if the
database becomes corrupted (e.g. because of bad RAM or bad disk)?

With Informix and Oracle, you can restore from a snapshot backup
and replay the "redo" logs since that backup, if you kept them.

Alternatively, you can keep a "failover" server that is up to date
with the last committed transaction. If it matters, you do both.
(If you're lucky, the disk or memory failure won't have corrupted
all your backups and failover servers before you notice.)

There is currently no builtin support for either in PG. Of course
both can be simulated in the client. Also, for any particular
collection of tables, a redo or replication log may be produced with
triggers; that's how the currently available replication add-ons
for PG work. Something built in could be much faster and much less
fragile.

I imagine a daemon extracting redo log entries from WAL segments,
asynchronously. Mixing redo log entries into the WAL allows the WAL
to be the only synchronous disk writer in the system, a Good Thing.

Nathan Myers
ncm(at)zembu(dot)com