Re: PITR, checkpoint, and local relations

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> Sent: Friday, August 02, 2002 8:51 PM
> To: Tom Lane
> Cc: richt@multera.com; Mikheev, Vadim; J. R. Nield; PostgreSQL Hacker
> Subject: Re: [HACKERS] PITR, checkpoint, and local relations
>
>
> Tom Lane wrote:
> > Richard Tucker <richt@multera.com> writes:
> > > 1) Issue an ALTER SYSTEM BEGIN BACKUP command which turns on
> atomic write,
> > > checkpoints the database and disables further checkpoints (so
> wal files
> > > won't be reused) until the backup is complete.
> > > 2) Change ALTER SYSTEM BACKUP DATABASE TO <directory> read
> the database
> > > directory to find which files it should backup rather than
> pg_class and for
> > > each file just use system(cp...) to copy it to the backup directory.
> > > 3) ALTER SYSTEM FINISH BACKUP does at it does now and backs
> up the pg_xlog
> > > directory and renables database checkpointing.
> >
> > > Does this sound right?
> >
> > I really dislike the notion of turning off checkpointing.  What if the
> > backup process dies or gets stuck (eg, it's waiting for some operator to
> > change a tape, but the operator has gone to lunch)?  IMHO, backup
> > systems that depend on breaking the system's normal operational behavior
> > are broken.  It should be sufficient to force a checkpoint when you
> > start and when you're done --- altering normal operation in between is
> > a bad design.
>
> Yes, and we have the same issue with turning on/off  after-image writes.
> How do we reset this from a PITR crash?;  however, the failure mode is
> only poorer performance, but it may be that way for a long time without
> the administrator knowing it.
>
> I wonder if we could SET the value in a transaction and keep the session
> connection open.  When we complete, we abort the transaction and
> disconnect.  If we die, the session terminates and the SET variable goes
> back to the original value.  (I am using the ignore SET in aborted
> transactions feature.)
I think all these concerns are addressed if the ALTER SYSTEM BACKUP is done
as a single command.  In what I implemented the checkpoint process while
polling for the checkpoint lock tested if backup processing was still alive
and if not reset everything back to the pre-backup settings.

>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
>