Skip site navigation (1) Skip section navigation (2)

Re: Re: Backup and Recovery

From: ncm(at)zembu(dot)com (Nathan Myers)
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Re: Backup and Recovery
Date: 2001-07-03 20:17:40
Message-ID: 20010703131740.I1466@store.zembu.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: Dmitry G. MastrukovDate: 2001-07-03 20:19:23
Subject: Re: Re: New data type: uniqueidentifier
Previous:From: Joe BrennerDate: 2001-07-03 20:13:08
Subject: Re: [OT] Any major users of postgresql?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group