Re: Seeking datacenter PITR backup procedures [RESENDING]

On Thu, Aug 23, 2007 at 06:58:36PM -0400, Bill Moran wrote:
> Decibel! <decibel(at)decibel(dot)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 ;)

Oh, that one's easy... it was changed in 8.2. Previously, you had to
either manually copy the active WAL file or wait for it to roll over
before you had a valid PITR backup. In 8.2, pg_stop_backup forces WAL
rotation (but note that you still have to wait for the archive command
to complete before the backup is valid).
--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)