Re: PITR, checkpoint, and local relations

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman(at)candle(dot)pha(dot)pa(dot)us]
> Sent: Friday, August 02, 2002 8:51 PM
> To: Tom Lane
> Cc: richt(at)multera(dot)com; Mikheev, Vadim; J. R. Nield; PostgreSQL Hacker
> Subject: Re: [HACKERS] PITR, checkpoint, and local relations
>
>
> Tom Lane wrote:
> > Richard Tucker <richt(at)multera(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
>