Thread
-
Re: PITR, checkpoint, and local relations
Vadim Mikheev <vmikheev@sectorbase.com> — 2002-08-02T22:15:32Z
> > > 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
-
Re: PITR, checkpoint, and local relations
Richard Tucker <richt@multera.com> — 2002-08-02T22:40:27Z
> -----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 >
-
Re: PITR, checkpoint, and local relations
Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-02T23:49:07Z
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
-
Re: PITR, checkpoint, and local relations
Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-03T00:50:34Z
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
-
Re: PITR, checkpoint, and local relations
Richard Tucker <richt@multera.com> — 2002-08-07T15:01:59Z
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 >
-
Re: PITR, checkpoint, and local relations
Richard Tucker <richt@multera.com> — 2002-08-07T15:12:00Z
> -----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 >