Re: 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.)

--  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,
Pennsylvania19026