> > > As long as whole block is saved in log on first after
> > > checkpoint (you made before backup) change to block.
> >
> I thought half the point of PITR was to be able to
> turn off pre-image logging so you can trade potential
> recovery time for speed without fear of data-loss.
> Didn't we have this discussion before?

> Suppose you can turn off/on PostgreSQL's atomic write on
> the fly.  Which means turning on or off whether XLoginsert
> writes a copy of the block into the log file upon first
> modification after a checkpoint.
> So ALTER SYSTEM BEGIN BACKUP would turn on atomic write
> and then checkpoint the database.
> So while the OS copy of the data files is going on the
> atomic write would be enabled. So any read of a partial
> write would be fixed up by the usual crash recovery mechanism.

Yes, simple way to satisfy everyone.

Vadim

Fri, 2 Aug 2002 15:15:32 -0700 
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM>
Re: PITR, checkpoint, and local relations


> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Mikheev, Vadim
> Sent: Friday, August 02, 2002 6:16 PM
> To: 'richt@multera.com'; J. R. Nield
> Cc: Tom Lane; Bruce Momjian; PostgreSQL Hacker
> Subject: Re: [HACKERS] PITR, checkpoint, and local relations
>
>
> > > > As long as whole block is saved in log on first after
> > > > checkpoint (you made before backup) change to block.
> > >
> > I thought half the point of PITR was to be able to
> > turn off pre-image logging so you can trade potential
> > recovery time for speed without fear of data-loss.
> > Didn't we have this discussion before?
>
> > Suppose you can turn off/on PostgreSQL's atomic write on
> > the fly.  Which means turning on or off whether XLoginsert
> > writes a copy of the block into the log file upon first
> > modification after a checkpoint.
> > So ALTER SYSTEM BEGIN BACKUP would turn on atomic write
> > and then checkpoint the database.
> > So while the OS copy of the data files is going on the
> > atomic write would be enabled. So any read of a partial
> > write would be fixed up by the usual crash recovery mechanism.
>
> Yes, simple way to satisfy everyone.

By the way I could supply a patch which turns off the atomic write feature.
It is disabled via a configuration parameter.  If the flag enabling /
disabling the feature were added to shared memory, XLogCtl struture, then it
could be toggled at runtime.

So I think what will work then is pg_copy (hot backup) would:
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?

BTW I will be on vacation until next Wednesday.

>
> Vadim
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>


Fri, 02 Aug 2002 18:40:27 -0400
Richard Tucker <richt@multera.com>
Re: PITR, checkpoint, and local relations
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.

			regards, tom lane

Fri, 02 Aug 2002 19:49:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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, Pennsylvania 19026

Fri, 2 Aug 2002 20:50:34 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: PITR, checkpoint, and local relations
Maybe we don't have to turn off checkpointing but we DO have to make sure no
wal files get re-used while the backup is running.  The wal-files must be
archived after everything else has been archived.  Futhermore if we don't
stop checkpointing then care must be taken to backup the pg_control file
first.
-regards
richt

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Friday, August 02, 2002 7:49 PM
> To: richt@multera.com
> Cc: Mikheev, Vadim; J. R. Nield; Bruce Momjian; PostgreSQL Hacker
> Subject: Re: [HACKERS] PITR, checkpoint, and local relations
>
>
> 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.
>
> 			regards, tom lane
>


Wed, 07 Aug 2002 11:01:59 -0400
Richard Tucker <richt@multera.com>
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
>


Wed, 07 Aug 2002 11:12:00 -0400
Richard Tucker <richt@multera.com>
Re: PITR, checkpoint, and local relations