Re: Seeking datacenter PITR backup procedures [RESENDING]

Decibel! <decibel@decibel.org> wrote:
>
> On Aug 19, 2007, at 7:23 AM, Bill Moran wrote:
> >> Assumptions:
> >> a. After pg_stop_backup(), Pg immediately recycles log files and
> >> hence wal
> >> logs can be copied to backup. This is a clean start.
> >
> > I don't believe so.  ARAIK, all pg_stop_backup() does is remove the
> > marker that pg_start_backup() put in place to tell the recovery
> > process
> > when the filesystem backup started.
>
> I'm pretty certain that's not the case. For a PITR to ensure that
> data is back to a consistent state after a recovery, it has to replay
> all the transactions that took place between pg_start_backup and
> pg_stop_backup; so it needs to know when pg_stop_backup() was
> actually run.

Sounds likely ... but I don't believe that forces any specific log
cycling activity, like the OP suggested.

Be nice if someone who knew for sure would chime in ;)

> > By not backing up pg_xlog, you are
> > going to be behind by however many transactions are in the most recent
> > transaction log that has not yet been archived.  Depending on how
> > often
> > your databases are updated, this is likely acceptable.  If you need
> > anything more timely than that, you'll probably want to implement
> > Slony or some other replication system.
>
> Just keep in mind that Slony is *not* a backup solution (though you
> could possibly argue that it's log shipping is).

True.  This rides the fine line of the difference between an HA setup
and backup.  Specifically: HA won't allow you to recovery from user
error.

--
Bill Moran
http://www.potentialtech.com