Hello:

I've got the logging system to the point where I can take a shutdown
consistent copy of a system, and play forward through multiple
checkpoints. It seems to handle CREATE TABLE/DROP TABLE/TRUNCATE
properly, and things are moving forward well. Recovery to an arbitrary
point-in-time should be just as easy, but will need some administrative
interface for it.

At this point, some input would be useful on how I should handle things.

The most important questions that need answering are in sections 2 & 5,
since they impact the most other parts of the system. They will also
require good documentation for sysadmins.



Issues Outstanding for Point In Time Recovery (PITR)

    $Date: 2002/07/04 14:23:37 $

    $Revision: 1.4 $

    J.R. Nield

    (Enc: ISO 8859-15 Latin-9)


§0 - Introduction

    This file is where I'm keeping track of all the issues I run into while
    trying to get PITR to work properly. Hopefully it will evolve into a
    description of how PITR actually works once it is implemented.

    I will also try to add feedback as it comes in.

    The big items so-far are:
        §1 - Logging Relation file creation, truncation, and removal 
            This is mostly done. Can do infinte play-forward from
            online logs.
        §2 - Partial-Write and Bad Block detection
            Need input before starting. Migration issues.
        §3 - Detecting Shutdown Consistent System Recovery
            Mostly done.
        §4 - Interactive Play-Forward Recovery for an Entire System
            Need input before starting.
        §5 - Individual file consistent recovery
            Need input. Semi-Major changes required.

§1 - Logging Relation file creation, truncation, and removal 

  §1.1 - Problem:

    Without file creation in the log, we can't replay committed 
    transactions that create relations. 
    
    The current code assumes that any transaction reaching commit has already
    ensured it's files exist, and that those files will never be removed. This
    is true now, but not for log-replay from an old backup database system. 
    
    The current XLOG code silently ignores block-write requests for
    non-existent files, and assumes that the transaction generating those
    requests must have aborted.

    Right now a crash during TRUNCATE TABLE will leave the table in an
    inconsistent state (partially truncated). This would not work when doing
    replay from before the last checkpoint.

    §1.1.1 - CREATE DATABASE is also unlogged

      This will cause the same replay problems as above.
    
  §1.2 - Proposal:

    a) Augment the SMGR code to log relation file operations, and to handle
    redo requests properly. This is simple in the case of create. Drop must be
    logged only IN the commit record. For truncate see (b).

    The 'struct f_smgr' needs new operations 'smgr_recreate', 'smgr_reunlink',
    and 'smgr_retruncate'. smgr_recreate should accept a RelFileNode instead
    of a Relation.

    Transactions that abort through system failure (ie.  unlogged aborts) 
    will simply continue to leak files.

    b) If TRUNCATE TABLE fails, the system must PANIC. Otherwise, the table
    may be used in a future command, and a replay-recovered database may
    end-up with different data than the original.

    WAL must be flushed before truncate as well.

    WAL does not need to be flushed before create, if we don't mind 
    leaking files sometimes.

    c) Redo code should treat writes to non-existent files as an error.
    Changes affect heap & nbtree AM's. [Check others]

    d) rtree [and GiST? WTF is GiST? ] is not logged. A replay recovery of
      a database should mark all the rtree indices as corrupt.
      [ actually we should do that now, are we? ]

    e) CREATE DATABASE must be logged properly, not use system(cp...)

  §1.3 - Status:

    All logged SMGR operations are now in a START_CRIT_SECTION()/
    END_CRIT_SECTION() pair enclosing the XLogInsert() and the underlying fs
    operations.

    Code has been added to smgr and xact modules to log:
        create (no XLogFlush)
        truncate (XLogFlush)
        pending deletes on commit record
        files to delete on abort record

    Code added to md.c to support redo ops

    Code added to smgr for RMGR redo/desc callbacks

    Code added to xact RMGR callbacks for redo/desc

    Database will do infinite shutdown consistent system recovery from the
    online logs, if you manually munge the control file to set state ==
    DB_IN_PRODUCTION instead of DB_SHUTDOWNED.

    Still need to do:
        Item (c), recovery cleanup in all AM's
        Item (d), logging in other index AM's
        Item (e), CREATE DATABASE stuff



§2 - Partial-Write and Bad Block detection

  §2.1 - Problem:

    In order to protect against partial writes without logging pages
    twice, we need to detect partial pages in system files and report them
    to the system administrator. We also might want to be able to detect
    damaged pages from other causes, like memory corruption, OS errors,
    etc. or in the case where the disk doesn't report bad blocks, but
    returns bad data.

    We should also decide what should happen when a file is marked as
    containing corrupt pages, and requires log-archive recovery from a
    backup.

  §2.2 - Proposal:

    Add a 1 byte 'pd_flags' field to PageHeaderData, with the following
    flag definitions:

        PD_BLOCK_CHECKING           (1)
        PD_BC_METHOD_BIT            (1<<1)

        PageHasBlockChecking(page)     ((page)->pd_flags & PD_BLOCK_CHECKING)
        PageBCMethodIsCRC64(page)      ((page)->pd_flags & PD_BC_METHOD_BIT)
        PageBCMethodIsLSNLast(page)    (!PageBCMethodIsCRC64(page))

    The last 64 bits of a page are reserved for use by the block checking 
    code.

    [ Is it worth the trouble to allow the last 8 bytes of a
      page to contain data when block checking is turned off for a Page? 

      This proposal does not allow that. ]

    If the block checking method is CRC64, then that field will contain
    the CRC64 of the block computed at write time.

    If the block checking method is LSNLast, then the field contains a
    duplicate of the pd_lsn field.

    §2.2.1 - Changes to Page handling routines

     All the page handling routines need to understand that 
     pd_special == (pd_special - (specialSize + 8))

     Change header comment in bufpage.h to reflect this.

    §2.2.2 - When Reading a Page

     Block corruption is detected on read in the obvious way with CRC64.

     In the case of LSNLast, we check to see if pd_lsn == the lsn in the
     last 64 bits of the page. If not, we assume the page is corrupt from
     a partial write (although it could be something else).

     IMPORTANT ASSUMPTION:
        The OS/disk device will never write both the first part and
        last part of a block without writing the middle as well.
        This might be wrong in some cases, but at least it's fast.

    §2.2.4 - GUC Variables

     The user should be able to configure what method is used:

       block_checking_write_method  = [ checksum | torn_page_flag | none ]

          Which method should be used for blocks we write?

       check_blocks_on_read      = [ true | false ]

          When true, verify that the blocks we read are not corrupt, using
          whatever method is in the block header.

          When false, ignore the block checking information.

  §2.3 - Status:

    Waiting for input from pgsql-hackers.

    Questions:

        Should we allow the user to have more detailed control over
        which parts of a database use block checking?

        For example: use 'checksum' on all system catalogs in all databases, 
        'torn_page_flag' on the non-catalog parts of the production database,
        and 'none' on everything else?

§3 - Detecting Shutdown Consistent System Recovery

    §3.1 - Problem:

     How to notice that we need to do log-replay for a system backup, when the
     restored control file points to a shutdown checkpoint record that is
     before the most recent checkpoint record in the log, and may point into
     an archived file.

    §3.2 - Proposal:

     At startup, after reading the ControlFile, scan the log directory to
     get the list of active log files, and find the lowest logId and
     logSeg of the files. Ensure that the files cover a contiguous range
     of LSN's.

     There are three cases:

      1) ControlFile points to the last valid checkpoint (either
         checkPoint or prevCheckPoint, but one of them is the greatest
         valid checkpoint record in the log stream).

      2) ControlFile points to a valid checkpoint record in an active
         log file, but there are more valid checkpoint records beyond
         it.

      3) ControlFile points to a checkpoint record that should be in the
         archive logs, and is presumably valid.

     Case 1 is what we handle now.

     Cases 2 and 3 would result from restoring an entire system from
     backup in preparation to do a play-forward recovery.

     We need to:

        Detect cases 2 and 3.

        Alert the administrator and abort startup.
        [Question: Is this always the desired behavior?  We can
         handle case 2 without intervention. ]

        Let the administrator start a standalone backend, and
        perform a play-forward recovery for the system.

    §3.3 - Status:

       In progress.

§4 - Interactive Play-Forward Recovery for an Entire System

    Play-Forward File Recovery from a backup file must be interactive,
    because not all log files that we need are necessarily in the 
    archive directory. It may be possible that not all the archive files
    we need can even fit on disk at one time.

    The system needs to be able to prompt the system administrator to feed
    it more log files.

    TODO: More here

§5 - Individual file consistent recovery

  §5.1 - Problem:

    If a file detects corruption, and we restore it from backup, how do 
    we know what archived files we need for recovery?

    Should file corruption (partial write, bad disk block, etc.) outside 
    the system catalog cause us to abort the system, or should we just 
    take the relation or database off-line?

    Given a backup file, how do we determine the point in the log 
    where we should start recovery for the file? What is the highest LSN
    we can use that will fully recover the file?

  §5.2 - Proposal: 
    
    Put a file header on each file, and update that header to the last
    checkpoint LSN at least once every 'file_lsn_time_slack' minutes, or
    at least once every dbsize/'file_lsn_log_slack' megabytes of log
    written, where dbsize is the estimated size of the database. Have
    these values be settable from the config file. These updates would be
    distributed throughout the hour, or interspersed between regular
    amounts of log generation.

    If we have a database backup program or command, it can update the
    header on the file before backup to the greatest value it can assure
    to be safe.

  §5.3 - Status:

    Waiting for input from pgsql-hackers.

    Questions:

        There are alternate methods than using a file header to get a
        known-good LSN lower bound for the starting point to recover a backup
        file. Is this the best way?

A) The Definitions

    This stuff is obtuse, but I need it here to keep track of what I'm 
    saying. Someday I should use it consistently in the rest of this
    document.

    "system" or "database system":

        A collection of postgres "databases" in one $PGDATA directory,
        managed by one postmaster instance at a time (and having one WAL
        log, etc.)

        All the files composing such a system, as a group.

    "up to date" or "now" or "current" or "current LSN":

        The most recent durable LSN for the system.

    "block consistent copy":

        When referring to a file:

        A copy of a file, which may be written to during the process of
        copying, but where each BLCKSZ size block is copied atomically.

        When referring to multiple files (in the same system):

        A copy of all the files, such that each is independently a "block
        consistent copy"

    "file consistent copy":

        When referring to a file:

        A copy of a file that is not written to between the start and end
        of the copy operation.

        When referring to multiple files (in the same system):

        A copy of all the files, such that each is independently a "file
        consistent copy"

    "system consistent copy":

        When referring to a file:
        
        A copy of a file, where the entire system of which it is a member
        is not written to during the copy.

        When referring to multiple files (in the same system):

        A copy of all the files, where the entire system of which they are
        members was not written to between the start and end of the
        copying of all the files, as a group.

    "shutdown consistent copy":

        When referring to a file:

        A copy of a file, where the entire system of which it is a member
        had been cleanly shutdown before the start of and for the duration
        of the copy.

        When referring to multiple files (in the same system):

        A copy of all the files, where the entire system of which they are
        members had been cleanly shutdown before the start of and for the
        duration of the copying of all the files, as a group.

    "consistent copy":

        A block, file, system, or shutdown consistent copy.

    "known-good LSN lower bound" 
    or "LSN lower bound"
    or "LSN-LB":

        When referring to a group of blocks, a file, or a group of files:

        An LSN known to be old enough that no log entries before it are needed
        to bring the blocks or files up-to-date.

    "known-good LSN greatest lower bound" 
    or "LSN greatest lower bound" 
    or "LSN-GLB":

        When referring to a group of blocks, a file, or a group of files:

        The greatest possible LSN that is a known-good LSN lower bound for
        the group.

    "backup file":

        A consistent copy of a data file used by the system, for which 
        we have a known-good LSN lower bound.

    "optimal backup file":

        A backup file, for which we have the known-good LSN greatest lower
        bound.

    "backup system":
        


    "Play-Forward File Recovery" or "PFFR":

        The process of bringing an individual backup file up to date.

        
-- 
J. R. Nield
jrnield@usol.com






04 Jul 2002 11:45:49 -0400
"J. R. Nield" <jrnield@usol.com>
Issues Outstanding for Point In Time Recovery (PITR)
On Thu, 2002-07-04 at 11:45, J. R. Nield wrote:

One other item that should be here:
>     The big items so-far are:
>         §1 - Logging Relation file creation, truncation, and removal 
>             This is mostly done. Can do infinte play-forward from
>             online logs.
>         §2 - Partial-Write and Bad Block detection
>             Need input before starting. Migration issues.
>         §3 - Detecting Shutdown Consistent System Recovery
>             Mostly done.
>         §4 - Interactive Play-Forward Recovery for an Entire System
>             Need input before starting.
>         §5 - Individual file consistent recovery
>             Need input. Semi-Major changes required.
          §6 - btbuild is not logged
              Not logged because of same assumptions as for file create.
              Only need to log the index build parameters to recreate
              the index, not each page change.


-- 
J. R. Nield
jrnield@usol.com




04 Jul 2002 13:07:22 -0400
"J. R. Nield" <jrnield@usol.com>
Re: Issues Outstanding for Point In Time Recovery (PITR)

I noticed no one has responded to your questions yet.

I think it is because we are sort of in shock.  We have needed
point-in-time recovery for a long time, but the people who were capable
of doing it weren't able to focus on it.  Then, all of a sudden, we get
an email from someone who is focusing on it and wants to get the job
done.  GREAT!

I will give you my short analysis and see how many other questions I can
answer.

We have always known there was a way to do PITR with WAL, but WAL needed
a few extra pieces of information.  Unfortunately, we weren't able to
analyze what was required.  Seems you have gotten very far here, and
that is great.

Also, we thought about having PITR as part of replication (reply of
replication traffic logs) but having it tied to WAL is much cleaner and
has better performance, I bet.

I will do whatever I can to help.  My chat addresses are:
	
	AIM	bmomjian
	ICQ	151255111
	Yahoo	bmomjian
	MSN	root@candle.pha.pa.us
	IRC	#postgresql vis efnet

---------------------------------------------------------------------------

J. R. Nield wrote:
> Hello:
> 
> I've got the logging system to the point where I can take a shutdown
> consistent copy of a system, and play forward through multiple
> checkpoints. It seems to handle CREATE TABLE/DROP TABLE/TRUNCATE

Yes, that was always something we knew was lacking in the current WAL
contents.

> properly, and things are moving forward well. Recovery to an arbitrary
> point-in-time should be just as easy, but will need some administrative
> interface for it.

The adminstrative part can be done easily.  We can get that part done. 
It is the low-level stuff we always needed help with.

> At this point, some input would be useful on how I should handle things.
> 
> The most important questions that need answering are in sections 2 & 5,
> since they impact the most other parts of the system. They will also
> require good documentation for sysadmins.

> ?0 - Introduction
> 
>     This file is where I'm keeping track of all the issues I run into while
>     trying to get PITR to work properly. Hopefully it will evolve into a
>     description of how PITR actually works once it is implemented.
> 
>     I will also try to add feedback as it comes in.
> 
>     The big items so-far are:
>         ?1 - Logging Relation file creation, truncation, and removal 
>             This is mostly done. Can do infinte play-forward from
>             online logs.

Excellent!

>         ?2 - Partial-Write and Bad Block detection
>             Need input before starting. Migration issues.

Uh, we do log do pre-page writes to WAL to recover from partial page
writes to disk.  Is there something more we need here?

As for bad block detection, we have thought about adding a CRC to each
page header, or at least making it optional.  WAL already has a CRC.


>         ?3 - Detecting Shutdown Consistent System Recovery
>             Mostly done.
>         ?4 - Interactive Play-Forward Recovery for an Entire System
>             Need input before starting.

You mean user interface?  My idea would be to just get some command-line
tool working and we can write some GUI app to manage it and use the
command-line tool as an interface into the system.

>         ?5 - Individual file consistent recovery
>             Need input. Semi-Major changes required.
> 

OK, here are the specific questions.  Got it.

> ?1 - Logging Relation file creation, truncation, and removal 
> 
>   ?1.1 - Problem:
> 
>     Without file creation in the log, we can't replay committed 
>     transactions that create relations. 
>     
>     The current code assumes that any transaction reaching commit has already
>     ensured it's files exist, and that those files will never be removed. This
>     is true now, but not for log-replay from an old backup database system. 
>     
>     The current XLOG code silently ignores block-write requests for
>     non-existent files, and assumes that the transaction generating those
>     requests must have aborted.
> 
>     Right now a crash during TRUNCATE TABLE will leave the table in an
>     inconsistent state (partially truncated). This would not work when doing
>     replay from before the last checkpoint.

Yes, there are a few places where we actually create file, and if the
server crashes, the file remains out the forever.  We need to track that
better.  I think we may need some utility that compares pg_class with
the files in the directory and cleans out unused files on server
startup.  I started working on such code as part of VACUUM but it made
too many assumptions because it knew other backends were working at the
same time.  On recovery, you don't have that problem and can easily do
almost an 'ls' and clean out just left over from the crash.  Seems that
would solve several of those problems.

> 
>     ?1.1.1 - CREATE DATABASE is also unlogged
> 
>       This will cause the same replay problems as above.

Yep.  Again, seems a master cleanup on startup is needed.

>   ?1.2 - Proposal:
> 
>     a) Augment the SMGR code to log relation file operations, and to handle
>     redo requests properly. This is simple in the case of create. Drop must be
>     logged only IN the commit record. For truncate see (b).

Yep, we knew we needed that.

>     The 'struct f_smgr' needs new operations 'smgr_recreate', 'smgr_reunlink',
>     and 'smgr_retruncate'. smgr_recreate should accept a RelFileNode instead
>     of a Relation.

No problem.  Clearly required.

>     Transactions that abort through system failure (ie.  unlogged aborts) 
>     will simply continue to leak files.

Yep, need a cleanup process on start.

>     b) If TRUNCATE TABLE fails, the system must PANIC. Otherwise, the table
>     may be used in a future command, and a replay-recovered database may
>     end-up with different data than the original.

We number based on oids.  You mean oid wraparound could cause the file
to be used again?

>     WAL must be flushed before truncate as well.
> 
>     WAL does not need to be flushed before create, if we don't mind 
>     leaking files sometimes.

Cleanup?

>     c) Redo code should treat writes to non-existent files as an error.
>     Changes affect heap & nbtree AM's. [Check others]

Yep, once you log create/drop, if something doesn't match, it is an
error, while before, we could ignore it.

>     d) rtree [and GiST? WTF is GiST? ] is not logged. A replay recovery of
>       a database should mark all the rtree indices as corrupt.
>       [ actually we should do that now, are we? ]

Known problem.  Not sure what is being done.  TODO has:

	* Add WAL index reliability improvement to non-btree indexes

so it is a known problem, and we aren't doing anything about it.  What
more can I say?  ;-)

>     e) CREATE DATABASE must be logged properly, not use system(cp...)

OK, should be interesting.

>   ?1.3 - Status:
> 
>     All logged SMGR operations are now in a START_CRIT_SECTION()/
>     END_CRIT_SECTION() pair enclosing the XLogInsert() and the underlying fs
>     operations.
> 
>     Code has been added to smgr and xact modules to log:
>         create (no XLogFlush)
>         truncate (XLogFlush)
>         pending deletes on commit record
>         files to delete on abort record
> 
>     Code added to md.c to support redo ops
> 
>     Code added to smgr for RMGR redo/desc callbacks
> 
>     Code added to xact RMGR callbacks for redo/desc
> 
>     Database will do infinite shutdown consistent system recovery from the
>     online logs, if you manually munge the control file to set state ==
>     DB_IN_PRODUCTION instead of DB_SHUTDOWNED.

Wow, how did you get so far?

>     Still need to do:
>         Item (c), recovery cleanup in all AM's
>         Item (d), logging in other index AM's
>         Item (e), CREATE DATABASE stuff
> 
> 
> 
> ?2 - Partial-Write and Bad Block detection
> 
>   ?2.1 - Problem:
> 
>     In order to protect against partial writes without logging pages
>     twice, we need to detect partial pages in system files and report them
>     to the system administrator. We also might want to be able to detect
>     damaged pages from other causes, like memory corruption, OS errors,
>     etc. or in the case where the disk doesn't report bad blocks, but
>     returns bad data.

Interesting.  We just had a discussion about MSSQL page tare bits on
every 512-byte block that are set to the same value before the write. 
On recover, if any the bits in a block are different, they recommend
recover using PITR.  We don't have PITR (yet) so there was no need to
implement it (we just logged whole pages to WAL before writing).  I
think we may go with just a CRC per page for partial-write detection.

>     We should also decide what should happen when a file is marked as
>     containing corrupt pages, and requires log-archive recovery from a
>     backup.

We can offer the option of no-wal change-page writing which will require
PITR on partial-write detection or they can keep the existing system
with the performance hit.

> 
>   ?2.2 - Proposal:
> 
>     Add a 1 byte 'pd_flags' field to PageHeaderData, with the following
>     flag definitions:
> 
>         PD_BLOCK_CHECKING           (1)
>         PD_BC_METHOD_BIT            (1<<1)
> 
>         PageHasBlockChecking(page)     ((page)->pd_flags & PD_BLOCK_CHECKING)
>         PageBCMethodIsCRC64(page)      ((page)->pd_flags & PD_BC_METHOD_BIT)
>         PageBCMethodIsLSNLast(page)    (!PageBCMethodIsCRC64(page))
> 
>     The last 64 bits of a page are reserved for use by the block checking 
>     code.

OK, so you already are on the CRC route.

>     [ Is it worth the trouble to allow the last 8 bytes of a
>       page to contain data when block checking is turned off for a Page? 
>       This proposal does not allow that. ]

You can leave the 8-byte empty if no CRC.  You may want to turn CRC
on/off without dump.
>     If the block checking method is CRC64, then that field will contain
>     the CRC64 of the block computed at write time.

Cool.

>     If the block checking method is LSNLast, then the field contains a
>     duplicate of the pd_lsn field.
> 
>     ?2.2.1 - Changes to Page handling routines
> 
>      All the page handling routines need to understand that 
>      pd_special == (pd_special - (specialSize + 8))
> 
>      Change header comment in bufpage.h to reflect this.

Yes, we should add a format version to the heap page tail anyway like
btree has, i.e. some constant on every page that describes the format
used in that PostgreSQL version.

>     ?2.2.2 - When Reading a Page
> 
>      Block corruption is detected on read in the obvious way with CRC64.
> 
>      In the case of LSNLast, we check to see if pd_lsn == the lsn in the
>      last 64 bits of the page. If not, we assume the page is corrupt from
>      a partial write (although it could be something else).

LSN?

>      IMPORTANT ASSUMPTION:
>         The OS/disk device will never write both the first part and
>         last part of a block without writing the middle as well.
>         This might be wrong in some cases, but at least it's fast.
> 
>     ?2.2.4 - GUC Variables
> 
>      The user should be able to configure what method is used:
> 
>        block_checking_write_method  = [ checksum | torn_page_flag | none ]
> 
>           Which method should be used for blocks we write?

Do we want torn page flag?  Seems like a pain to get that on every 512
byte section of the 8k page.

>        check_blocks_on_read      = [ true | false ]
> 
>           When true, verify that the blocks we read are not corrupt, using
>           whatever method is in the block header.
> 
>           When false, ignore the block checking information.
> 

Good idea.  We always check on crash, but check on read only when set. 
Good for detecting hardware problems.


>   ?2.3 - Status:
> 
>     Waiting for input from pgsql-hackers.
> 
>     Questions:
> 
>         Should we allow the user to have more detailed control over
>         which parts of a database use block checking?

I don't think that is needed;  installation-wide settings are fine.

>         For example: use 'checksum' on all system catalogs in all databases, 
>         'torn_page_flag' on the non-catalog parts of the production database,
>         and 'none' on everything else?

Too complicated.  Let's get it implemented and in the field and see what
people ask for.

> ?3 - Detecting Shutdown Consistent System Recovery
> 
>     ?3.1 - Problem:
> 
>      How to notice that we need to do log-replay for a system backup, when the
>      restored control file points to a shutdown checkpoint record that is
>      before the most recent checkpoint record in the log, and may point into
>      an archived file.
> 
>     ?3.2 - Proposal:
> 
>      At startup, after reading the ControlFile, scan the log directory to
>      get the list of active log files, and find the lowest logId and
>      logSeg of the files. Ensure that the files cover a contiguous range
>      of LSN's.
> 
>      There are three cases:
> 
>       1) ControlFile points to the last valid checkpoint (either
>          checkPoint or prevCheckPoint, but one of them is the greatest
>          valid checkpoint record in the log stream).
> 
>       2) ControlFile points to a valid checkpoint record in an active
>          log file, but there are more valid checkpoint records beyond
>          it.
> 
>       3) ControlFile points to a checkpoint record that should be in the
>          archive logs, and is presumably valid.
> 
>      Case 1 is what we handle now.
> 
>      Cases 2 and 3 would result from restoring an entire system from
>      backup in preparation to do a play-forward recovery.
> 
>      We need to:
> 
>         Detect cases 2 and 3.
> 
>         Alert the administrator and abort startup.
>         [Question: Is this always the desired behavior?  We can
>          handle case 2 without intervention. ]
> 
>         Let the administrator start a standalone backend, and
>         perform a play-forward recovery for the system.
> 
>     ?3.3 - Status:
> 
>        In progress.

Sorry, I was confused by this.

> ?4 - Interactive Play-Forward Recovery for an Entire System
> 
>     Play-Forward File Recovery from a backup file must be interactive,
>     because not all log files that we need are necessarily in the 
>     archive directory. It may be possible that not all the archive files
>     we need can even fit on disk at one time.
> 
>     The system needs to be able to prompt the system administrator to feed
>     it more log files.
> 
>     TODO: More here

Yes, we can have someone working on the GUI once the command-line
interface is defined.

> ?5 - Individual file consistent recovery
> 
>   ?5.1 - Problem:
> 
>     If a file detects corruption, and we restore it from backup, how do 
>     we know what archived files we need for recovery?
> 
>     Should file corruption (partial write, bad disk block, etc.) outside 
>     the system catalog cause us to abort the system, or should we just 
>     take the relation or database off-line?

Offline is often best so they can get in there and recover if needed. 
We usually allow them in with a special flag or utility like
pg_resetxlog.


>     Given a backup file, how do we determine the point in the log 
>     where we should start recovery for the file? What is the highest LSN
>     we can use that will fully recover the file?

That is tricky.  We have discussed it and your backup has to deal with
some pretty strange things that can happen while 'tar' is traversing the
directory.


>   ?5.2 - Proposal: 
>     
>     Put a file header on each file, and update that header to the last
>     checkpoint LSN at least once every 'file_lsn_time_slack' minutes, or
>     at least once every dbsize/'file_lsn_log_slack' megabytes of log
>     written, where dbsize is the estimated size of the database. Have
>     these values be settable from the config file. These updates would be
>     distributed throughout the hour, or interspersed between regular
>     amounts of log generation.
> 
>     If we have a database backup program or command, it can update the
>     header on the file before backup to the greatest value it can assure
>     to be safe.

Not sure.

>   ?5.3 - Status:
> 
>     Waiting for input from pgsql-hackers.
> 
>     Questions:
> 
>         There are alternate methods than using a file header to get a
>         known-good LSN lower bound for the starting point to recover a backup
>         file. Is this the best way?

Not sure.

I am sure others will chime in with more information.

-- 
  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, 5 Jul 2002 01:42:28 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Issues Outstanding for Point In Time Recovery (PITR)
J. R. Nield wrote:
> ?5 - Individual file consistent recovery
> 
>   ?5.1 - Problem:
> 
>     If a file detects corruption, and we restore it from backup, how do 
>     we know what archived files we need for recovery?
> 
>     Should file corruption (partial write, bad disk block, etc.) outside 
>     the system catalog cause us to abort the system, or should we just 
>     take the relation or database off-line?
> 
>     Given a backup file, how do we determine the point in the log 
>     where we should start recovery for the file? What is the highest LSN
>     we can use that will fully recover the file?
> 
>   ?5.2 - Proposal: 
>     
>     Put a file header on each file, and update that header to the last
>     checkpoint LSN at least once every 'file_lsn_time_slack' minutes, or
>     at least once every dbsize/'file_lsn_log_slack' megabytes of log
>     written, where dbsize is the estimated size of the database. Have
>     these values be settable from the config file. These updates would be
>     distributed throughout the hour, or interspersed between regular
>     amounts of log generation.
> 
>     If we have a database backup program or command, it can update the
>     header on the file before backup to the greatest value it can assure
>     to be safe.

I know there was discussion about this.  The issue was when you are
doing the backup, how do you handle changes in the file that happen
during the backup?    I think there was some idea of remembering the WAL
pointer location at the start of the tar backup, and somehow playing
forward from that point.  Now, the trick is to know how much of the WAL
is duplicated in the backup, and how much needs to be applied to roll
forward.

Because we have a non-overwriting storage manager, I think one idea was
to just replay everything, knowing that some of it may be unnecessary. 
However, VACUUM FULL would complicate that because it does overwrite
tuples and you may get into trouble playing all of that back.

I am sure someone has analyzed this better than me.


-- 
  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, 5 Jul 2002 02:01:00 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Issues Outstanding for Point In Time Recovery (PITR)
On Fri, 2002-07-05 at 01:42, Bruce Momjian wrote:
> 
> We have needed
> point-in-time recovery for a long time, 

Most thanks should go to vadim (and whoever else worked on this), since
his WAL code already does most of the work. The key thing is auditing
the backend to look for every case where we assume some action is not
visible until after commit, and therefore don't log its effects. Those
are the main cases that must be changed.


> ---------------------------------------------------------------------------
> 
> J. R. Nield wrote:
> > Hello:
> > 
> > I've got the logging system to the point where I can take a shutdown
> > consistent copy of a system, and play forward through multiple
> > checkpoints. It seems to handle CREATE TABLE/DROP TABLE/TRUNCATE

But notably not for the btree indexes! It looked like they were working,
because the files were there, and all indexes created before the backup
would work under insert/delete (including sys catalog indexes). This is
because btree insert/delete is logged, just not during build. So I
missed that one case.

You will end-up with up-to-date table data though, so it is something.

Adding logging support to btbuild is the next step, and I don't think it
should be too hard. I am working this now.

It is also a major advantage that most everything in the system gets
stored in the catalog tables, and so is logged already.


> Uh, we do log pre-page writes to WAL to recover from partial page
> writes to disk.  Is there something more we need here?
> 
> As for bad block detection, we have thought about adding a CRC to each
> page header, or at least making it optional.  WAL already has a CRC.
>

Yes this should be last to do, because it is not necessary for PITR,
only for performance (the option not to write pre-images without fear of
data loss). 
 
> Yes, there are a few places where we actually create a file, and if the
> server crashes, the file remains out there forever.  We need to track that
> better.  

OK, there is a bigger problem then just tracking the file though. We
sometimes do stuff to that file that we don't log. We assume that if we
commit, the file must be OK and will not need replay because the
transaction would not have committed if the file was not in a commitable
state. If we abort, the system never sees the file, so in a sense we
undo everything we did to the file. It is a kind of poor-man's rollback
for certain operations, like btbuild, create table, etc. But it means
that we cannot recover the file from the log, even after a commit.

> 
> > 
> >     ?1.1.1 - CREATE DATABASE is also unlogged
> > 
> >       This will cause the same replay problems as above.
> 
> Yep.  Again, seems a master cleanup on startup is needed.

The cleanup is not the problem, only a nuisance. Creating the files
during replay is the problem. I must recreate CREATE DATABASE from the
log exactly as it was done originally. I think just logging the
parameters to the command function should be sufficient, but I need to
think more about it.

> 
> >     b) If TRUNCATE TABLE fails, the system must PANIC. Otherwise, the table
> >     may be used in a future command, and a replay-recovered database may
> >     end-up with different data than the original.
> 
> We number based on oids.  You mean oid wraparound could cause the file
> to be used again?

That's not what I meant. Let's say I issue 'TRUNCATE TABLE foo'. Then,
right before smgrtruncate is called, I do an XLogInsert saying "Redo a
TRUNCATE TABLE on foo to nblocks if we crash". Then smgrtruncate fails
and we do an elog(ERROR)

Now the user decides that since TRUNCATE TABLE didn't work, he might as
well use the table, so he inserts some records into it, generating log
entries.

When I replay this log sequence later, what happens if the TRUNCATE
succeeds instead of failing?

I admit that there are other ways of handling it than to PANIC if the
truncate fails. All the ones I can come up with seem to amount to some
kind of ad-hoc UNDO log.


> 
> >     WAL must be flushed before truncate as well.
> > 
> >     WAL does not need to be flushed before create, if we don't mind 
> >     leaking files sometimes.
> 
> Cleanup?

Yes, we could garbage-collect leaked files. XLogFlush is not that
expensive though, so I don't have an opinion on this yet.

> 
> >     c) Redo code should treat writes to non-existent files as an error.
> >     Changes affect heap & nbtree AM's. [Check others]
> 
> Yep, once you log create/drop, if something doesn't match, it is an
> error, while before, we could ignore it.
> 
> >     d) rtree [and GiST? WTF is GiST? ] is not logged. A replay recovery of
> >       a database should mark all the rtree indices as corrupt.
> >       [ actually we should do that now, are we? ]
> 
> Known problem.  Not sure what is being done.  TODO has:
> 
> 	* Add WAL index reliability improvement to non-btree indexes
> 
> so it is a known problem, and we aren't doing anything about it.  What
> more can I say?  ;-)

Once the other stuff works reliably, I will turn to rtree logging, which
I have looked at somewhat, although I could really use a copy of the
paper it is supposed to be based on. I have not figured out GiST enough
to work on it yet.

> 
> >     e) CREATE DATABASE must be logged properly, not use system(cp...)
> 
> OK, should be interesting.
> 
> >   ?1.3 - Status:
> > 
> >     All logged SMGR operations are now in a START_CRIT_SECTION()/
> >     END_CRIT_SECTION() pair enclosing the XLogInsert() and the underlying fs
> >     operations.
> > 
> >     Code has been added to smgr and xact modules to log:
> >         create (no XLogFlush)
> >         truncate (XLogFlush)
> >         pending deletes on commit record
> >         files to delete on abort record
> > 
> >     Code added to md.c to support redo ops
> > 
> >     Code added to smgr for RMGR redo/desc callbacks
> > 
> >     Code added to xact RMGR callbacks for redo/desc
> > 
> >     Database will do infinite shutdown consistent system recovery from the
> >     online logs, if you manually munge the control file to set state ==
> >     DB_IN_PRODUCTION instead of DB_SHUTDOWNED.
> 
> Wow, how did you get so far?

Because it was almost there to start with :-)

Besides, it sounded better before I realized there was still a remaining
problem with btree logging to fix. 


> >      In the case of LSNLast, we check to see if pd_lsn == the lsn in the
> >      last 64 bits of the page. If not, we assume the page is corrupt from
> >      a partial write (although it could be something else).
> 
> LSN?

Log Sequence Number (XLogRecPtr)

> 
> >      IMPORTANT ASSUMPTION:
> >         The OS/disk device will never write both the first part and
> >         last part of a block without writing the middle as well.
> >         This might be wrong in some cases, but at least it's fast.
> > 
> >     ?2.2.4 - GUC Variables
> > 
> >      The user should be able to configure what method is used:
> > 
> >        block_checking_write_method  = [ checksum | torn_page_flag | none ]
> > 
> >           Which method should be used for blocks we write?
> 
> Do we want torn page flag?  Seems like a pain to get that on every 512
> byte section of the 8k page.

Ok, this section (2.2) was badly written and hard to understand. What I
am proposing is that we put a copy of the log sequence number, which is
at the head of the page, into the 8 byte field that we are creating at
the end of the page, in place of the CRC. The log sequence number
increases every time the page is written (it already does this). I have
called this method 'LSNLast' internally, and the user would call it the
'torn_page_flag' method.

So when we read the page, we compare the Log Sequence Number at the
beginning and end of the page, and if they are different we assume a
torn page.

This version is weaker than the MS one we were talking about, because it
is not on every 512 byte section of the page, only the beginning and the
end. I'm simply looking for a fast alternative to CRC64, that doesn't
require massive reorganization of the page layout code.


> 
> >   ?2.3 - Status:
> > 
> >     Waiting for input from pgsql-hackers.
> > 
> >     Questions:
> > 
> >         Should we allow the user to have more detailed control over
> >         which parts of a database use block checking?
> 
> I don't think that is needed;  installation-wide settings are fine.
> 
> >         For example: use 'checksum' on all system catalogs in all databases, 
> >         'torn_page_flag' on the non-catalog parts of the production database,
> >         and 'none' on everything else?
> 
> Too complicated.  Let's get it implemented and in the field and see what
> people ask for.

Ok. I agree.

> 
> > ?3 - Detecting Shutdown Consistent System Recovery

> > 
> >     ?3.3 - Status:
> > 
> >        In progress.
> 
> Sorry, I was confused by this.

Let me re-write it, and I'll post it in the next version. The section
dealt with what to do when you have a valid restored controlfile from a
backup system, which is in the DB_SHUTDOWNED state, and that points to a
valid shutdown/checkpoint record in the log; only the checkpoint record
happens not to be the last one in the log. This is a situation that
could never happen now, but would in PITR.


> 
> > ?4 - Interactive Play-Forward Recovery for an Entire System
> > 
> >     Play-Forward File Recovery from a backup file must be interactive,
> >     because not all log files that we need are necessarily in the 
> >     archive directory. It may be possible that not all the archive files
> >     we need can even fit on disk at one time.
> > 
> >     The system needs to be able to prompt the system administrator to feed
> >     it more log files.
> > 
> >     TODO: More here
> 
> Yes, we can have someone working on the GUI once the command-line
> interface is defined.

Yes, and the system must not allow any concurrent activity during
recovery either. So it looks like a standalone backend operation.

> 
> > ?5 - Individual file consistent recovery
> > 
> >   ?5.1 - Problem:
> > 
> >     If a file detects corruption, and we restore it from backup, how do 
> >     we know what archived files we need for recovery?
> > 
> >     Should file corruption (partial write, bad disk block, etc.) outside 
> >     the system catalog cause us to abort the system, or should we just 
> >     take the relation or database off-line?
> 
> Offline is often best so they can get in there and recover if needed. 
> We usually allow them in with a special flag or utility like
> pg_resetxlog.
> 
> 
> >     Given a backup file, how do we determine the point in the log 
> >     where we should start recovery for the file? What is the highest LSN
> >     we can use that will fully recover the file?
> 
> That is tricky.  We have discussed it and your backup has to deal with
> some pretty strange things that can happen while 'tar' is traversing the
> directory.

Even if we shutdown before we copy the file, we don't want a file that
hasn't been written to in 5 weeks before it was backed up to require
five weeks of old log files to recover. So we need to track that
information somehow, because right now if we scanned the blocks in the
file looking for at the page LSN's, we greatest LSN we would see might
be much older than where it would be safe to recover from. That is the
biggest problem, I think.

> 
> 
> >   ?5.2 - Proposal: 
> >     
> >     Put a file header on each file, and update that header to the last
> >     checkpoint LSN at least once every 'file_lsn_time_slack' minutes, or
> >     at least once every dbsize/'file_lsn_log_slack' megabytes of log
> >     written, where dbsize is the estimated size of the database. Have
> >     these values be settable from the config file. These updates would be
> >     distributed throughout the hour, or interspersed between regular
> >     amounts of log generation.
> > 
> >     If we have a database backup program or command, it can update the
> >     header on the file before backup to the greatest value it can assure
> >     to be safe.
> 
> Not sure.
> 
> >   ?5.3 - Status:
> > 
> >     Waiting for input from pgsql-hackers.
> > 
> >     Questions:
> > 
> >         There are alternate methods than using a file header to get a
> >         known-good LSN lower bound for the starting point to recover a backup
> >         file. Is this the best way?
> 
> Not sure.
> 
> I am sure others will chime in with more information.
> 
> -- 
>   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
> 
-- 
J. R. Nield
jrnield@usol.com






05 Jul 2002 06:01:40 -0400
"J. R. Nield" <jrnield@usol.com>
Re: Issues Outstanding for Point In Time Recovery (PITR)
J. R. Nield wrote:
> On Fri, 2002-07-05 at 01:42, Bruce Momjian wrote:
> > 
> > We have needed
> > point-in-time recovery for a long time, 
> 
> Most thanks should go to vadim (and whoever else worked on this), since
> his WAL code already does most of the work. The key thing is auditing
> the backend to look for every case where we assume some action is not
> visible until after commit, and therefore don't log its effects. Those
> are the main cases that must be changed.

Yep.  Glad you can focus on that.

> > ---------------------------------------------------------------------------
> > 
> > J. R. Nield wrote:
> > > Hello:
> > > 
> > > I've got the logging system to the point where I can take a shutdown
> > > consistent copy of a system, and play forward through multiple
> > > checkpoints. It seems to handle CREATE TABLE/DROP TABLE/TRUNCATE
> 
> But notably not for the btree indexes! It looked like they were working,
> because the files were there, and all indexes created before the backup
> would work under insert/delete (including sys catalog indexes). This is
> because btree insert/delete is logged, just not during build. So I
> missed that one case.
> 
> You will end-up with up-to-date table data though, so it is something.
> 
> Adding logging support to btbuild is the next step, and I don't think it
> should be too hard. I am working this now.

Great.

> It is also a major advantage that most everything in the system gets
> stored in the catalog tables, and so is logged already.
> 
> 
> > Uh, we do log pre-page writes to WAL to recover from partial page
> > writes to disk.  Is there something more we need here?
> > 
> > As for bad block detection, we have thought about adding a CRC to each
> > page header, or at least making it optional.  WAL already has a CRC.
> >
> 
> Yes this should be last to do, because it is not necessary for PITR,
> only for performance (the option not to write pre-images without fear of
> data loss). 

Yep.

> > Yes, there are a few places where we actually create a file, and if the
> > server crashes, the file remains out there forever.  We need to track that
> > better.  
> 
> OK, there is a bigger problem then just tracking the file though. We
> sometimes do stuff to that file that we don't log. We assume that if we
> commit, the file must be OK and will not need replay because the
> transaction would not have committed if the file was not in a commitable
> state. If we abort, the system never sees the file, so in a sense we
> undo everything we did to the file. It is a kind of poor-man's rollback
> for certain operations, like btbuild, create table, etc. But it means
> that we cannot recover the file from the log, even after a commit.

Yep.

> > 
> > > 
> > >     ?1.1.1 - CREATE DATABASE is also unlogged
> > > 
> > >       This will cause the same replay problems as above.
> > 
> > Yep.  Again, seems a master cleanup on startup is needed.
> 
> The cleanup is not the problem, only a nuisance. Creating the files
> during replay is the problem. I must recreate CREATE DATABASE from the
> log exactly as it was done originally. I think just logging the
> parameters to the command function should be sufficient, but I need to
> think more about it.

OK, makes sense.  Nice when you can bundle a complex action into the
logging of one command and its parameters.

> > 
> > >     b) If TRUNCATE TABLE fails, the system must PANIC. Otherwise, the table
> > >     may be used in a future command, and a replay-recovered database may
> > >     end-up with different data than the original.
> > 
> > We number based on oids.  You mean oid wraparound could cause the file
> > to be used again?
> 
> That's not what I meant. Let's say I issue 'TRUNCATE TABLE foo'. Then,
> right before smgrtruncate is called, I do an XLogInsert saying "Redo a
> TRUNCATE TABLE on foo to nblocks if we crash". Then smgrtruncate fails
> and we do an elog(ERROR)
> 
> Now the user decides that since TRUNCATE TABLE didn't work, he might as
> well use the table, so he inserts some records into it, generating log
> entries.
> 
> When I replay this log sequence later, what happens if the TRUNCATE
> succeeds instead of failing?

You mean the user is now accessing a partially truncated table? That's
just too weird.  I don't see how the WAL would know how far truncation
had gone.  I see why you would need the panic and it seems acceptable.

> I admit that there are other ways of handling it than to PANIC if the
> truncate fails. All the ones I can come up with seem to amount to some
> kind of ad-hoc UNDO log.

Yea, truncate failure seems so rare/impossible to happen, we can do a
panic and see if it ever happens to anyone.  I bet it will not.  Those
are usually cases of an OS crash, so it is the same as a panic.

> > >     WAL must be flushed before truncate as well.
> > > 
> > >     WAL does not need to be flushed before create, if we don't mind 
> > >     leaking files sometimes.
> > 
> > Cleanup?
> 
> Yes, we could garbage-collect leaked files. XLogFlush is not that
> expensive though, so I don't have an opinion on this yet.

Right now, if we do CREATE TABLE, and the backend crashes, I think it
leaves a nonreferenced file around.  Not something you need to worry
about for replication, I guess.  We can address it later.

> > >     c) Redo code should treat writes to non-existent files as an error.
> > >     Changes affect heap & nbtree AM's. [Check others]
> > 
> > Yep, once you log create/drop, if something doesn't match, it is an
> > error, while before, we could ignore it.
> > 
> > >     d) rtree [and GiST? WTF is GiST? ] is not logged. A replay recovery of
> > >       a database should mark all the rtree indices as corrupt.
> > >       [ actually we should do that now, are we? ]
> > 
> > Known problem.  Not sure what is being done.  TODO has:
> > 
> > 	* Add WAL index reliability improvement to non-btree indexes
> > 
> > so it is a known problem, and we aren't doing anything about it.  What
> > more can I say?  ;-)
> 
> Once the other stuff works reliably, I will turn to rtree logging, which
> I have looked at somewhat, although I could really use a copy of the
> paper it is supposed to be based on. I have not figured out GiST enough
> to work on it yet.

There has been talk of retiring rtree and using the GIST version of
rtree.  I thought it had some advantages/disadvantages. I don't remember
for sure.

> > >     Database will do infinite shutdown consistent system recovery from the
> > >     online logs, if you manually munge the control file to set state ==
> > >     DB_IN_PRODUCTION instead of DB_SHUTDOWNED.
> > 
> > Wow, how did you get so far?
> 
> Because it was almost there to start with :-)
> 
> Besides, it sounded better before I realized there was still a remaining
> problem with btree logging to fix. 

Our major problem is that we have a very few people who like to work
at this level in the code.  Glad you are around.

> > >      In the case of LSNLast, we check to see if pd_lsn == the lsn in the
> > >      last 64 bits of the page. If not, we assume the page is corrupt from
> > >      a partial write (although it could be something else).
> > 
> > LSN?
> 
> Log Sequence Number (XLogRecPtr)

Yep, I remembered later.

> > >      IMPORTANT ASSUMPTION:
> > >         The OS/disk device will never write both the first part and
> > >         last part of a block without writing the middle as well.
> > >         This might be wrong in some cases, but at least it's fast.
> > > 
> > >     ?2.2.4 - GUC Variables
> > > 
> > >      The user should be able to configure what method is used:
> > > 
> > >        block_checking_write_method  = [ checksum | torn_page_flag | none ]
> > > 
> > >           Which method should be used for blocks we write?
> > 
> > Do we want torn page flag?  Seems like a pain to get that on every 512
> > byte section of the 8k page.
> 
> Ok, this section (2.2) was badly written and hard to understand. What I
> am proposing is that we put a copy of the log sequence number, which is
> at the head of the page, into the 8 byte field that we are creating at
> the end of the page, in place of the CRC. The log sequence number
> increases every time the page is written (it already does this). I have
> called this method 'LSNLast' internally, and the user would call it the
> 'torn_page_flag' method.
> 
> So when we read the page, we compare the Log Sequence Number at the
> beginning and end of the page, and if they are different we assume a
> torn page.

Excellent.

> This version is weaker than the MS one we were talking about, because it
> is not on every 512 byte section of the page, only the beginning and the
> end. I'm simply looking for a fast alternative to CRC64, that doesn't
> require massive reorganization of the page layout code.

Great idea, and cheap.

> > >     The system needs to be able to prompt the system administrator to feed
> > >     it more log files.
> > > 
> > >     TODO: More here
> > 
> > Yes, we can have someone working on the GUI once the command-line
> > interface is defined.
> 
> Yes, and the system must not allow any concurrent activity during
> recovery either. So it looks like a standalone backend operation.

OK, we can address this capability.

> > That is tricky.  We have discussed it and your backup has to deal with
> > some pretty strange things that can happen while 'tar' is traversing the
> > directory.

OK, first, are you thinking of having the nightly backup operate at the
file system level, or accessing the pages through the PostgreSQL shared
buffers?

> Even if we shutdown before we copy the file, we don't want a file that

Oh, so you are thinking of some kind of tar while the db is shutdown,
and using that as the backup?

> hasn't been written to in 5 weeks before it was backed up to require
> five weeks of old log files to recover. So we need to track that
> information somehow, because right now if we scanned the blocks in the
> file looking for at the page LSN's, we greatest LSN we would see might
> be much older than where it would be safe to recover from. That is the
> biggest problem, I think.

You are saying, "How do we know what WAL records go with that backup
snapshot of the file?"  OK, lets assume we are shutdown.  You can grab
the WAL log info from pg_control using contrib/pg_controldata and that
tells you what WAL logs to roll forward when you need to PIT recover
that backup later.  If you store that info in the first file you backup,
you can have that WAL pointer available for later recovery in case you
are restoring from that backup.  Is that the issue?

What seems more complicated is doing the backup while the database is
active, and this may be a requirement for a final PITR solution.  Some
think we can grab the WAL pointer at 'tar' start and replay that on the
backup even if the file changes during backup.

-- 
  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, 5 Jul 2002 13:20:37 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Issues Outstanding for Point In Time Recovery (PITR)
Bruce Momjian wrote:
> You are saying, "How do we know what WAL records go with that backup
> snapshot of the file?"  OK, lets assume we are shutdown.  You can grab
> the WAL log info from pg_control using contrib/pg_controldata and that
> tells you what WAL logs to roll forward when you need to PIT recover
> that backup later.  If you store that info in the first file you backup,
> you can have that WAL pointer available for later recovery in case you
> are restoring from that backup.  Is that the issue?
> 
> What seems more complicated is doing the backup while the database is
> active, and this may be a requirement for a final PITR solution.  Some
> think we can grab the WAL pointer at 'tar' start and replay that on the
> backup even if the file changes during backup.

OK, I think I understand live backups now using tar and PITR.  Someone
explained this to me months ago but now I understand it.

First, a key issue is that PostgreSQL doesn't fiddle with individual
items on disk.  It reads an 8k block, modifies it, (writes it to WAL if
it hasn't been written to that WAL segment before), and writes it to
disk.  That is key.  (Are there cases where don't do this, like
pg_controldata?)

OK, so you do a tar backup of a file.  While you are doing the tar,
certain 8k blocks are being modified in the file.  There is no way to
know what blocks are modified as you are doing the tar, and in fact you
could read partial page writes during the tar.

One solution would be to read the file using the PostgreSQL page buffer,
but even then, getting a stable snapshot of the file would be difficult.
Now, we could lock the table and prevent writes while it is being backed
up, but there is a better way.

We already have pre-change page images in WAL.  When we do the backup,
any page that was modified while we were backing up is in the WAL.  On
restore, we can recover whatever tar saw of the file, knowing that the
WAL page images will recover any page changes made during the tar.

Now, you mentioned we may not want pre-change page images in WAL
because, with PITR, we can more easily recover from the WAL rather than
having this performance hit for many page writes.

What I suggest is a way for the backup tar to turn on pre-change page
images while the tar is happening, and turn it off after the tar is
done.

We already have this TODO item:

	* Turn off after-change writes if fsync is disabled (?)

No sense in doing after-change WAL writes without fsync.  We could
extend this so those after-changes writes could be turned on an off,
allowing fill tar backups and PITR recovery.  In fact, for people with
reliable hardware, we should already be giving them the option of
turning off pre-change writes.  We don't have a way of detecting partial
page writes, but then again, we can't detect failures with fsync off
anyway so it seems to be the same vulnerability.  I guess that's why we
were going to wrap the effect into the same variable, but for PITR, can
see wanting fsync always on and the ability to turn pre-change writes on
and off.

-- 
  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, 5 Jul 2002 22:08:54 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Issues Outstanding for Point In Time Recovery (PITR)
J.R.,

Nice first draft and a good read. Was going to comment 
in-line but thought this method would be easier to follow. 
The comments/suggestions below assume that PIT recovery is
being performed at the cluster level with a data backup 
image created by a tar-like utility. 

As noted, one of the main problems is knowing where to begin
in the log.  This can be handled by having backup processing 
update the control file with the first lsn and log file 
required.  At the time of the backup, this information is or 
can be made available.  The control file can be the last file
added to the tar and can contain information spanning the entire
backup process.

For data consistency, since the backup is being performed on 
an active cluster, we have to make sure to mark the end of the 
backup.  On restore, to make the cluster consistent, you have 
to force the user to perform forward recovery passed the point
of the backup completion marker in the (archived) log.  This 
can be handled using a backup end log record.  The backup end
log record would have to contain an identifier unique to this 
backup.  If a user requests to stop PIT recovery before this 
log record is encountered, consistency is not guaranteed. 
PIT should either disallow the action or warn of possible / 
impending doom.

The necessary logging for rtee (and others) insertions/deletions
can be added to the base code.  Not much of a worry but I would
expect to encounter other missing log items during testing.

The idea of using the last lsn on the page to detect a partial
write is used by other dbms systems.  You already have that 
information available so there is no overhead in computing it. 
Nothing wrong with CRC though.

As for the DB_SHUTDOWNED state, this could be handled by having
the backup processing update the control file field to 
DB_PIT_REQUIRED (or some such identifier).  After a restore,
users would be blocked from connecting to the cluster's databases 
until a forward recovery passed the backup end log record has
completed successfully.  

At the end of normal crash recovery, the user has to go digging
to identify in-flight transactions still in the system and abort
them manually.  It would be nice if PIT recovery automatically
aborted all in-flight transactions at the end. 

As PostgreSQL heads towards forward recovery functionality, it
may be wise to add headers to the log files.  As the logs from
any cluster are identically named, the header would allow unique
identification of the file and contents (cluster name, unique 
log id, id of the prior log file for chaining purposes, lsn 
ranges, etc). Most helpful.

Just a few notes from the administrative side. PIT recovery
should probably offer the user the following actions:

. forward recover to end of logs [and stop]
  Process log files located in the current directory until you
  read through the last one.  Allow the user the option to stop
  or not, just in case the logs are archived.  Send back the
  timestamp of the last encountered commit log record and the
  series of log files scanned.   

. forward recover to PIT [and stop]
  Similar to that described above but use the commit timestamps
  to gauge PIT progress.  
  
. forward recover query
  Send back the log series covered and the last commit timestamp
  encountered. 

. forward recover stop
  Stop the current forward recovery session. Undo all in-flight
  transactions and bring the databases down in a consistent
  state.  No other external user actions should be required.

Looking forward to reading draft 2.

Cheers,
Patrick
--
Patrick Macdonald 
Red Hat Canada 

"J. R. Nield" wrote:
> 
> Hello:
> 
> I've got the logging system to the point where I can take a shutdown
> consistent copy of a system, and play forward through multiple
> checkpoints. It seems to handle CREATE TABLE/DROP TABLE/TRUNCATE
> properly, and things are moving forward well. Recovery to an arbitrary
> point-in-time should be just as easy, but will need some administrative
> interface for it.
> 
> At this point, some input would be useful on how I should handle things.
> 
> The most important questions that need answering are in sections 2 & 5,
> since they impact the most other parts of the system. They will also
> require good documentation for sysadmins.
> 
> Issues Outstanding for Point In Time Recovery (PITR)
> 
>     $Date: 2002/07/04 14:23:37 $
> 
>     $Revision: 1.4 $
> 
>     J.R. Nield
> 
>     (Enc: ISO 8859-15 Latin-9)
> 
> ï¿½0 - Introduction
> 
>     This file is where I'm keeping track of all the issues I run into while
>     trying to get PITR to work properly. Hopefully it will evolve into a
>     description of how PITR actually works once it is implemented.
> 
>     I will also try to add feedback as it comes in.
> 
>     The big items so-far are:
>         ï¿½1 - Logging Relation file creation, truncation, and removal
>             This is mostly done. Can do infinte play-forward from
>             online logs.
>         ï¿½2 - Partial-Write and Bad Block detection
>             Need input before starting. Migration issues.
>         ï¿½3 - Detecting Shutdown Consistent System Recovery
>             Mostly done.
>         ï¿½4 - Interactive Play-Forward Recovery for an Entire System
>             Need input before starting.
>         ï¿½5 - Individual file consistent recovery
>             Need input. Semi-Major changes required.
> 
> ï¿½1 - Logging Relation file creation, truncation, and removal
> 
>   ï¿½1.1 - Problem:
> 
>     Without file creation in the log, we can't replay committed
>     transactions that create relations.
> 
>     The current code assumes that any transaction reaching commit has already
>     ensured it's files exist, and that those files will never be removed. This
>     is true now, but not for log-replay from an old backup database system.
> 
>     The current XLOG code silently ignores block-write requests for
>     non-existent files, and assumes that the transaction generating those
>     requests must have aborted.
> 
>     Right now a crash during TRUNCATE TABLE will leave the table in an
>     inconsistent state (partially truncated). This would not work when doing
>     replay from before the last checkpoint.
> 
>     ï¿½1.1.1 - CREATE DATABASE is also unlogged
> 
>       This will cause the same replay problems as above.
> 
>   ï¿½1.2 - Proposal:
> 
>     a) Augment the SMGR code to log relation file operations, and to handle
>     redo requests properly. This is simple in the case of create. Drop must be
>     logged only IN the commit record. For truncate see (b).
> 
>     The 'struct f_smgr' needs new operations 'smgr_recreate', 'smgr_reunlink',
>     and 'smgr_retruncate'. smgr_recreate should accept a RelFileNode instead
>     of a Relation.
> 
>     Transactions that abort through system failure (ie.  unlogged aborts)
>     will simply continue to leak files.
> 
>     b) If TRUNCATE TABLE fails, the system must PANIC. Otherwise, the table
>     may be used in a future command, and a replay-recovered database may
>     end-up with different data than the original.
> 
>     WAL must be flushed before truncate as well.
> 
>     WAL does not need to be flushed before create, if we don't mind
>     leaking files sometimes.
> 
>     c) Redo code should treat writes to non-existent files as an error.
>     Changes affect heap & nbtree AM's. [Check others]
> 
>     d) rtree [and GiST? WTF is GiST? ] is not logged. A replay recovery of
>       a database should mark all the rtree indices as corrupt.
>       [ actually we should do that now, are we? ]
> 
>     e) CREATE DATABASE must be logged properly, not use system(cp...)
> 
>   ï¿½1.3 - Status:
> 
>     All logged SMGR operations are now in a START_CRIT_SECTION()/
>     END_CRIT_SECTION() pair enclosing the XLogInsert() and the underlying fs
>     operations.
> 
>     Code has been added to smgr and xact modules to log:
>         create (no XLogFlush)
>         truncate (XLogFlush)
>         pending deletes on commit record
>         files to delete on abort record
> 
>     Code added to md.c to support redo ops
> 
>     Code added to smgr for RMGR redo/desc callbacks
> 
>     Code added to xact RMGR callbacks for redo/desc
> 
>     Database will do infinite shutdown consistent system recovery from the
>     online logs, if you manually munge the control file to set state ==
>     DB_IN_PRODUCTION instead of DB_SHUTDOWNED.
> 
>     Still need to do:
>         Item (c), recovery cleanup in all AM's
>         Item (d), logging in other index AM's
>         Item (e), CREATE DATABASE stuff
> 
> ï¿½2 - Partial-Write and Bad Block detection
> 
>   ï¿½2.1 - Problem:
> 
>     In order to protect against partial writes without logging pages
>     twice, we need to detect partial pages in system files and report them
>     to the system administrator. We also might want to be able to detect
>     damaged pages from other causes, like memory corruption, OS errors,
>     etc. or in the case where the disk doesn't report bad blocks, but
>     returns bad data.
> 
>     We should also decide what should happen when a file is marked as
>     containing corrupt pages, and requires log-archive recovery from a
>     backup.
> 
>   ï¿½2.2 - Proposal:
> 
>     Add a 1 byte 'pd_flags' field to PageHeaderData, with the following
>     flag definitions:
> 
>         PD_BLOCK_CHECKING           (1)
>         PD_BC_METHOD_BIT            (1<<1)
> 
>         PageHasBlockChecking(page)     ((page)->pd_flags & PD_BLOCK_CHECKING)
>         PageBCMethodIsCRC64(page)      ((page)->pd_flags & PD_BC_METHOD_BIT)
>         PageBCMethodIsLSNLast(page)    (!PageBCMethodIsCRC64(page))
> 
>     The last 64 bits of a page are reserved for use by the block checking
>     code.
> 
>     [ Is it worth the trouble to allow the last 8 bytes of a
>       page to contain data when block checking is turned off for a Page?
> 
>       This proposal does not allow that. ]
> 
>     If the block checking method is CRC64, then that field will contain
>     the CRC64 of the block computed at write time.
> 
>     If the block checking method is LSNLast, then the field contains a
>     duplicate of the pd_lsn field.
> 
>     ï¿½2.2.1 - Changes to Page handling routines
> 
>      All the page handling routines need to understand that
>      pd_special == (pd_special - (specialSize + 8))
> 
>      Change header comment in bufpage.h to reflect this.
> 
>     ï¿½2.2.2 - When Reading a Page
> 
>      Block corruption is detected on read in the obvious way with CRC64.
> 
>      In the case of LSNLast, we check to see if pd_lsn == the lsn in the
>      last 64 bits of the page. If not, we assume the page is corrupt from
>      a partial write (although it could be something else).
> 
>      IMPORTANT ASSUMPTION:
>         The OS/disk device will never write both the first part and
>         last part of a block without writing the middle as well.
>         This might be wrong in some cases, but at least it's fast.
> 
>     ï¿½2.2.4 - GUC Variables
> 
>      The user should be able to configure what method is used:
> 
>        block_checking_write_method  = [ checksum | torn_page_flag | none ]
> 
>           Which method should be used for blocks we write?
> 
>        check_blocks_on_read      = [ true | false ]
> 
>           When true, verify that the blocks we read are not corrupt, using
>           whatever method is in the block header.
> 
>           When false, ignore the block checking information.
> 
>   ï¿½2.3 - Status:
> 
>     Waiting for input from pgsql-hackers.
> 
>     Questions:
> 
>         Should we allow the user to have more detailed control over
>         which parts of a database use block checking?
> 
>         For example: use 'checksum' on all system catalogs in all databases,
>         'torn_page_flag' on the non-catalog parts of the production database,
>         and 'none' on everything else?
> 
> ï¿½3 - Detecting Shutdown Consistent System Recovery
> 
>     ï¿½3.1 - Problem:
> 
>      How to notice that we need to do log-replay for a system backup, when the
>      restored control file points to a shutdown checkpoint record that is
>      before the most recent checkpoint record in the log, and may point into
>      an archived file.
> 
>     ï¿½3.2 - Proposal:
> 
>      At startup, after reading the ControlFile, scan the log directory to
>      get the list of active log files, and find the lowest logId and
>      logSeg of the files. Ensure that the files cover a contiguous range
>      of LSN's.
> 
>      There are three cases:
> 
>       1) ControlFile points to the last valid checkpoint (either
>          checkPoint or prevCheckPoint, but one of them is the greatest
>          valid checkpoint record in the log stream).
> 
>       2) ControlFile points to a valid checkpoint record in an active
>          log file, but there are more valid checkpoint records beyond
>          it.
> 
>       3) ControlFile points to a checkpoint record that should be in the
>          archive logs, and is presumably valid.
> 
>      Case 1 is what we handle now.
> 
>      Cases 2 and 3 would result from restoring an entire system from
>      backup in preparation to do a play-forward recovery.
> 
>      We need to:
> 
>         Detect cases 2 and 3.
> 
>         Alert the administrator and abort startup.
>         [Question: Is this always the desired behavior?  We can
>          handle case 2 without intervention. ]
> 
>         Let the administrator start a standalone backend, and
>         perform a play-forward recovery for the system.
> 
>     ï¿½3.3 - Status:
> 
>        In progress.
> 
> ï¿½4 - Interactive Play-Forward Recovery for an Entire System
> 
>     Play-Forward File Recovery from a backup file must be interactive,
>     because not all log files that we need are necessarily in the
>     archive directory. It may be possible that not all the archive files
>     we need can even fit on disk at one time.
> 
>     The system needs to be able to prompt the system administrator to feed
>     it more log files.
> 
>     TODO: More here
> 
> ï¿½5 - Individual file consistent recovery
> 
>   ï¿½5.1 - Problem:
> 
>     If a file detects corruption, and we restore it from backup, how do
>     we know what archived files we need for recovery?
> 
>     Should file corruption (partial write, bad disk block, etc.) outside
>     the system catalog cause us to abort the system, or should we just
>     take the relation or database off-line?
> 
>     Given a backup file, how do we determine the point in the log
>     where we should start recovery for the file? What is the highest LSN
>     we can use that will fully recover the file?
> 
>   ï¿½5.2 - Proposal:
> 
>     Put a file header on each file, and update that header to the last
>     checkpoint LSN at least once every 'file_lsn_time_slack' minutes, or
>     at least once every dbsize/'file_lsn_log_slack' megabytes of log
>     written, where dbsize is the estimated size of the database. Have
>     these values be settable from the config file. These updates would be
>     distributed throughout the hour, or interspersed between regular
>     amounts of log generation.
> 
>     If we have a database backup program or command, it can update the
>     header on the file before backup to the greatest value it can assure
>     to be safe.
> 
>   ï¿½5.3 - Status:
> 
>     Waiting for input from pgsql-hackers.
> 
>     Questions:
> 
>         There are alternate methods than using a file header to get a
>         known-good LSN lower bound for the starting point to recover a backup
>         file. Is this the best way?
> 
> A) The Definitions
> 
>     This stuff is obtuse, but I need it here to keep track of what I'm
>     saying. Someday I should use it consistently in the rest of this
>     document.
> 
>     "system" or "database system":
> 
>         A collection of postgres "databases" in one $PGDATA directory,
>         managed by one postmaster instance at a time (and having one WAL
>         log, etc.)
> 
>         All the files composing such a system, as a group.
> 
>     "up to date" or "now" or "current" or "current LSN":
> 
>         The most recent durable LSN for the system.
> 
>     "block consistent copy":
> 
>         When referring to a file:
> 
>         A copy of a file, which may be written to during the process of
>         copying, but where each BLCKSZ size block is copied atomically.
> 
>         When referring to multiple files (in the same system):
> 
>         A copy of all the files, such that each is independently a "block
>         consistent copy"
> 
>     "file consistent copy":
> 
>         When referring to a file:
> 
>         A copy of a file that is not written to between the start and end
>         of the copy operation.
> 
>         When referring to multiple files (in the same system):
> 
>         A copy of all the files, such that each is independently a "file
>         consistent copy"
> 
>     "system consistent copy":
> 
>         When referring to a file:
> 
>         A copy of a file, where the entire system of which it is a member
>         is not written to during the copy.
> 
>         When referring to multiple files (in the same system):
> 
>         A copy of all the files, where the entire system of which they are
>         members was not written to between the start and end of the
>         copying of all the files, as a group.
> 
>     "shutdown consistent copy":
> 
>         When referring to a file:
> 
>         A copy of a file, where the entire system of which it is a member
>         had been cleanly shutdown before the start of and for the duration
>         of the copy.
> 
>         When referring to multiple files (in the same system):
> 
>         A copy of all the files, where the entire system of which they are
>         members had been cleanly shutdown before the start of and for the
>         duration of the copying of all the files, as a group.
> 
>     "consistent copy":
> 
>         A block, file, system, or shutdown consistent copy.
> 
>     "known-good LSN lower bound"
>     or "LSN lower bound"
>     or "LSN-LB":
> 
>         When referring to a group of blocks, a file, or a group of files:
> 
>         An LSN known to be old enough that no log entries before it are needed
>         to bring the blocks or files up-to-date.
> 
>     "known-good LSN greatest lower bound"
>     or "LSN greatest lower bound"
>     or "LSN-GLB":
> 
>         When referring to a group of blocks, a file, or a group of files:
> 
>         The greatest possible LSN that is a known-good LSN lower bound for
>         the group.
> 
>     "backup file":
> 
>         A consistent copy of a data file used by the system, for which
>         we have a known-good LSN lower bound.
> 
>     "optimal backup file":
> 
>         A backup file, for which we have the known-good LSN greatest lower
>         bound.
> 
>     "backup system":
> 
> 
>     "Play-Forward File Recovery" or "PFFR":
> 
>         The process of bringing an individual backup file up to date.
> 
> 
> --
> J. R. Nield
> jrnield@usol.com
> 
> ---------------------------(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



Sat, 06 Jul 2002 23:44:58 -0400
Patrick Macdonald <patrickm@redhat.com>
Re: Issues Outstanding for Point In Time Recovery (PITR)
Patrick Macdonald wrote:
> The idea of using the last lsn on the page to detect a partial
> write is used by other dbms systems.  You already have that 
> information available so there is no overhead in computing it. 
> Nothing wrong with CRC though.

Agreed.  Just thought I would point out that is not guaranteed.  Suppose
the 8k block is spread over 16 512 sectors in two cylinders.  The OS or
SCSI tagged queuing could wrote the second part of the page (sectors
9-16) before the first group (1-8).  If it writes 9-16, then writes 1-8
but fails in the middle of 1-8, the LSN will match at the front and back
of the page, but the page will be partially written.


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



Sun, 7 Jul 2002 19:43:01 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Issues Outstanding for Point In Time Recovery (PITR)

J.R., just checking to see how PITR recovery is going.  Do you need any
assistance or have any questions for us?

Also, do you have any idea how close you are to having something
completed?  Are you aware we are closing development of 7.3 at the end
of August and start beta September 1?  Is there any way we can help you?

---------------------------------------------------------------------------

J. R. Nield wrote:
> On Fri, 2002-07-05 at 01:42, Bruce Momjian wrote:
> > 
> > We have needed
> > point-in-time recovery for a long time, 
> 
> Most thanks should go to vadim (and whoever else worked on this), since
> his WAL code already does most of the work. The key thing is auditing
> the backend to look for every case where we assume some action is not
> visible until after commit, and therefore don't log its effects. Those
> are the main cases that must be changed.

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

Tue, 16 Jul 2002 15:36:17 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Issues Outstanding for Point In Time Recovery (PITR)
On Tue, 2002-07-16 at 15:36, Bruce Momjian wrote:
> 
> J.R., just checking to see how PITR recovery is going.  Do you need any
> assistance or have any questions for us?
> 
> Also, do you have any idea how close you are to having something
> completed?  Are you aware we are closing development of 7.3 at the end
> of August and start beta September 1?  Is there any way we can help you?
> 

It should be ready to go into CVS by the end of the month. 

That will include: logging all operations except for rtree and GiST,
archival of logfiles (with new postgresql.conf params), headers on the
logfiles to verify the system that created them, standalone backend
recovery to a point-in-time, and a rudimentary hot backup capability.

I could use some advice on the proper way to add tests to configure.in,
given that the autoconf output is in CVS. Would you ever want a patch to
include the generated 'configure' file?

Related to that, the other place I need advice is on adding Ted Tso's
LGPL'd UUID library (stolen from e2fsprogs) to the source. Are we
allowed to use this? There is a free OSF/DCE spec for UUID's, so I can
re-implement the library if required.

We also haven't discussed commands for backup/restore, but I will use
what I think is appropriate and we can change the grammar if needed. The
initial hot-backup capability will require the database to be in
read-only mode and use tar for backup, and I will add the ability to
allow writes later.

Does this sound like a reasonable timeframe/feature-set to make the 7.3
release?

-- 
J. R. Nield
jrnield@usol.com




17 Jul 2002 00:24:10 -0400
"J. R. Nield" <jrnield@usol.com>
Re: Issues Outstanding for Point In Time Recovery (PITR)
"J. R. Nield" <jrnield@usol.com> writes:
> Related to that, the other place I need advice is on adding Ted Tso's
> LGPL'd UUID library (stolen from e2fsprogs) to the source. Are we
> allowed to use this?

Uh, why exactly is UUID essential for this?  (The correct answer is
"it's not", IMHO.)

> We also haven't discussed commands for backup/restore, but I will use
> what I think is appropriate and we can change the grammar if needed. The
> initial hot-backup capability will require the database to be in
> read-only mode and use tar for backup, and I will add the ability to
> allow writes later.

There is no read-only mode, and I for one will resist adding one.

			regards, tom lane

Wed, 17 Jul 2002 01:15:44 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Issues Outstanding for Point In Time Recovery (PITR) 
J. R. Nield wrote:
> On Tue, 2002-07-16 at 15:36, Bruce Momjian wrote:
> > 
> > J.R., just checking to see how PITR recovery is going.  Do you need any
> > assistance or have any questions for us?
> > 
> > Also, do you have any idea how close you are to having something
> > completed?  Are you aware we are closing development of 7.3 at the end
> > of August and start beta September 1?  Is there any way we can help you?
> > 
> 
> It should be ready to go into CVS by the end of the month. 
> 
> That will include: logging all operations except for rtree and GiST,
> archival of logfiles (with new postgresql.conf params), headers on the
> logfiles to verify the system that created them, standalone backend
> recovery to a point-in-time, and a rudimentary hot backup capability.

Sounds great.  That gives us another month to iron out any remaining
issues.  This will be a great 7.3 feature!


> I could use some advice on the proper way to add tests to configure.in,
> given that the autoconf output is in CVS. Would you ever want a patch to
> include the generated 'configure' file?

We only patch configure.in.  If you post to hackers, they can give you
assistance and I will try to help however I can.  I can so some
configure.in stuff for you myself.

> Related to that, the other place I need advice is on adding Ted Tso's
> LGPL'd UUID library (stolen from e2fsprogs) to the source. Are we
> allowed to use this? There is a free OSF/DCE spec for UUID's, so I can
> re-implement the library if required.

We talked about this on the replication mailing list.  We decided that
hostname, properly hashed to an integer, was the proper way to get this
value.  Also, there should be a postgresql.conf variable so you can
override the hostname-generated value if you wish.  I think that is
sufficient.

> We also haven't discussed commands for backup/restore, but I will use
> what I think is appropriate and we can change the grammar if needed. The
> initial hot-backup capability will require the database to be in
> read-only mode and use tar for backup, and I will add the ability to
> allow writes later.

Yea, I saw Tom balked at that. I think we have enough manpower and time
that we can get hot backup in normal read/write mode working before 7.3
beta so I would just code it assuming the system is live and we can deal
with making it hot-capable once it is in CVS.  It doesn't have to work
100% until beta time.

> Does this sound like a reasonable timeframe/feature-set to make the 7.3
> release?

Sounds great.  This is another killer 7.3 feature, and we really need
this for greater enterprise acceptance of PostgreSQL.

-- 
  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, 17 Jul 2002 01:25:53 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Issues Outstanding for Point In Time Recovery (PITR)

We also have implemented a roll forward recovery mechanism. We modified a
7.2.1 version of Postgres.
The mechanism is designed to provide a means of recoverying from the loss or
corruption of media.  It provides for duplicating wal_files so that if a
wal_file is lost roll forward recovery can recover the database using the
duplicated wal_files.  Hooks were also added so that the roll forward
recovery mechanism can be used to implement a hot standby database.
Along with the roll forward recovery mechanism we have also implemented an
online database backup utility which is synchronized with the recovery log
so that the backup can be a starting point for a roll forward recovery
session.

Roll forward recovery is enabled for a database cluster by specifying one of
two postmaster configuration parameters.

wal_file_reuse = false  This parameter tells the wal system to not reuse wal
files.  This option is intended for sites wishing to implement a hot standby
database where wal files will be periodically copied to another machine
where they will be rolled forward into the standby database.

wal_file_duplicate = <directory_path>  This parameter tells the system to
mirror the files in the pg_xlog directory to the specified directory.  This
allows for the recovery of a database where a wal file has been damaged or
lost.  It also allows for a variant of a hot standby database where the
duplicate directory is the pg_xlog directory of the standby database.

Since both of these options cause wal files to accumulate indefinately the
dba needs a means of purging wal files when they are no longer needed.  So
an sql command, "ALTER SYSTEM PURGE WAL_FILES <wal_file_name>", has also
been implemented.  This command deletes all wal files up to and including
the specified <wal_file_name> as long as those wal files are not needed to
recover the database in the event of a system crash.  To find out the status
of the wal files a function has been implemented to return a wal file name.
The function is:

Wal_file( <request_tye>)
Request_type := [ ï¿½currentï¿½ | ï¿½lastï¿½ | ï¿½checkpointï¿½ | ï¿½oldestï¿½]

Wal_file ('current') returns the name of the log file currently being
written to.
Wal_file('last') returns the name of the last log file filled.
Wal_file('checkpoint') returns the name of the file containing the current
redo position.  The current redo position is the position in the recovery
log where crash recovery would start if the system were to crash now.  All
logs prior to this one will not be needed to recover the database cluster
and could be safely removed.
Wal_file('oldest') returns the oldest xlog file found in the pg_xlog
directory.


To actually perform a roll forward you use the postmaster configuration
parameter "roll_forward=yes".  This parameter tells the startup process to
perform crash recovery even though the state of the database as found in the
pg_control file indicates a normal shutdown.  This is necessary since the
starting point of roll forward session could be the restore of a database
cluster that was shutdown in order to back it up.  Furthermore this
parameter tells the startup process not to write out a checkpoint record at
the end of the roll forward session. This allows for the database cluster to
receive subsequent wal files and to have those rolled forward as well.  When
starting the postmaster with the roll_forward=yes option, it shuts down the
database as soon as the startup process completes.  So the idea is to
restore a backup, copy all of your saved/duplicated wal files into the
pg_xlog directory of the restored database and start the postmaster with the
roll_forward option.

For point in time recovery there is also a roll_forward_until = <time> which
rolls forward through the wal files until the first transaction commit note
that is greater than or equal to the specified time.

The pg_copy utility performs an on line copy of a database cluster.  Its
syntax is:
pg_copy <backup_directory> [-h host] [-p port] ...
This makes a copy of the database where backup_directory is what you would
set PGDATA to in order start a postmaster against the backup copy.  The
database can be being updated while the copy occurs.  If you start a
postmaster against this copy it will appear to the startup process as a
database that crashed at the instant the pg_copy operation completed.
Futhermore the pg_copy utility automatically removes any wal files not
needed to recover the database from either pg_xlog directory or the
wal_file_duplicate directory.

So a DBA to protect the database from media loss just needs to set the
wal_file_duplicate paramater and periodically pg_copy the database.


The BIG THING we have not done is address the issue that add/drop tables and
indexes do not propagate through the roll forward recovery mechanism
properly.



-regards
Richard Tucker






Wed, 17 Jul 2002 17:45:53 -0400
Richard Tucker <richt@multera.com>
Re: Issues Outstanding for Point In Time Recovery (PITR)
Richard Tucker wrote:
> 
> We also have implemented a roll forward recovery mechanism. We modified a
> 7.2.1 version of Postgres.
> The mechanism is designed to provide a means of recoverying from the loss or
> corruption of media.  It provides for duplicating wal_files so that if a
> wal_file is lost roll forward recovery can recover the database using the
> duplicated wal_files.  Hooks were also added so that the roll forward
> recovery mechanism can be used to implement a hot standby database.
> Along with the roll forward recovery mechanism we have also implemented an
> online database backup utility which is synchronized with the recovery log
> so that the backup can be a starting point for a roll forward recovery
> session.

In researching who has done this work, I found that
http://www.multera.com/ is Progress Software.  I assume this
"Distributed but Connected" is partly based on PostgreSQL.

-- 
  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, 17 Jul 2002 18:17:25 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Issues Outstanding for Point In Time Recovery (PITR)
Bruce Momjian wrote:
> Richard Tucker wrote:
> > 
> > We also have implemented a roll forward recovery mechanism. We modified a
> > 7.2.1 version of Postgres.
> > The mechanism is designed to provide a means of recoverying from the loss or
> > corruption of media.  It provides for duplicating wal_files so that if a
> > wal_file is lost roll forward recovery can recover the database using the
> > duplicated wal_files.  Hooks were also added so that the roll forward
> > recovery mechanism can be used to implement a hot standby database.
> > Along with the roll forward recovery mechanism we have also implemented an
> > online database backup utility which is synchronized with the recovery log
> > so that the backup can be a starting point for a roll forward recovery
> > session.
> 
> In researching who has done this work, I found that
> http://www.multera.com/ is Progress Software.  I assume this
> "Distributed but Connected" is partly based on PostgreSQL.

Oh, I see it now, PostgreSQL is right there:

    * OuterEdge
      A distributed site technology suite that allows remote users to
maintain the integrity, not the congestion and price, of centralized
data. The OuterEdge includes the affordable UltraSQL^(TM) database
server powered by PostgreSQL, the third most popular database, and a

       ^^^^^^^^^^^^^^^^^^^^^
Secure Application/web server that supports the industry's most popular
Internet languages. Together with the Replication Engine software, the
OuterEdge gives remote users seamless access to important data without
relying on bandwidth.

Wonder why we are "the third most popular database".  I think that's
good?

-- 
  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, 17 Jul 2002 18:19:55 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Issues Outstanding for Point In Time Recovery (PITR)
> server powered by PostgreSQL, the third most popular database, and a
> 
>        ^^^^^^^^^^^^^^^^^^^^^
 
> Wonder why we are "the third most popular database".  I think that's
> good?

You'll notice they didn't qualify where.  On this list, it's probably
#1.  Within Progress software perhaps we're third most popular (whatever
two are typically used in the InnerEdge are 1 and 2).


17 Jul 2002 18:26:43 -0400
Rod Taylor <rbt@zort.ca>
Re: Issues Outstanding for Point In Time Recovery (PITR)
Rod Taylor wrote:
> > server powered by PostgreSQL, the third most popular database, and a
> > 
> >        ^^^^^^^^^^^^^^^^^^^^^
>  
> > Wonder why we are "the third most popular database".  I think that's
> > good?
> 
> You'll notice they didn't qualify where.  On this list, it's probably
> #1.  Within Progress software perhaps we're third most popular (whatever
> two are typically used in the InnerEdge are 1 and 2).

Yea, using that logic, #1 would be the Progress internal db system, #2
would be MySQL (though that seems doubtful at this point with Nusphere),
and PostgreSQL.

Actually, PostgreSQL is #1 in my home.

-- 
  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, 17 Jul 2002 18:28:55 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Issues Outstanding for Point In Time Recovery (PITR)
On July 17, 2002 05:45 pm, Richard Tucker wrote:
> We also have implemented a roll forward recovery mechanism. We modified a
> 7.2.1 version of Postgres.
> ...

Excellent!  I can't wait.  When will it be in current?

> The BIG THING we have not done is address the issue that add/drop tables
> and indexes do not propagate through the roll forward recovery mechanism
> properly.

I can live with that.  Schemas shouldn't change so often that you can't just 
dup any changes to the backup(s).

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

Wed, 17 Jul 2002 18:43:39 -0400
"D'Arcy J.M. Cain" <darcy@druid.net>
Re: Issues Outstanding for Point In Time Recovery (PITR)
I don't know how our marketing came up third most popular but I think the
order is, Oracle, MySQL, and PostgreSQL or maybe Oracle, MSSQL and
PostgreSQL.  I'm sure there is some criterion by which PostgreSQL is tenth
and by some other its number one.
Of course, my posting was about Point In Time Recovery and not multera
marketing spin.
-regards
richt

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Wednesday, July 17, 2002 6:29 PM
To: Rod Taylor
Cc: PostgreSQL-development; richt@multera.com; J. R. Nield
Subject: Re: [HACKERS] Issues Outstanding for Point In Time Recovery
(PITR)


Rod Taylor wrote:
> > server powered by PostgreSQL, the third most popular database, and a
> >
> >        ^^^^^^^^^^^^^^^^^^^^^
>
> > Wonder why we are "the third most popular database".  I think that's
> > good?
>
> You'll notice they didn't qualify where.  On this list, it's probably
> #1.  Within Progress software perhaps we're third most popular (whatever
> two are typically used in the InnerEdge are 1 and 2).

Yea, using that logic, #1 would be the Progress internal db system, #2
would be MySQL (though that seems doubtful at this point with Nusphere),
and PostgreSQL.

Actually, PostgreSQL is #1 in my home.

--
  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, 17 Jul 2002 18:55:55 -0400
Richard Tucker <richt@multera.com>
Re: Issues Outstanding for Point In Time Recovery (PITR)
On Wed, 2002-07-17 at 01:25, Bruce Momjian wrote:
> 
> We only patch configure.in.  If you post to hackers, they can give you
> assistance and I will try to help however I can.  I can so some
> configure.in stuff for you myself.

Thanks for the offer. The only thing I was changing it for was to test
whether and how to get a ethernet MAC address using ioctl, so libuuid
could use it if available. That is dropped now.

> 
> > Related to that, the other place I need advice is on adding Ted Tso's
> > LGPL'd UUID library (stolen from e2fsprogs) to the source. Are we
> > allowed to use this? There is a free OSF/DCE spec for UUID's, so I can
> > re-implement the library if required.
> 
> We talked about this on the replication mailing list.  We decided that
> hostname, properly hashed to an integer, was the proper way to get this
> value.  Also, there should be a postgresql.conf variable so you can
> override the hostname-generated value if you wish.  I think that is
> sufficient.

I will do something like this, but reserve 16 bytes for it just in case
we change our minds. It needs to be different among systems on the same
machine, so there needs to be a time value and a pseudo-random part as
well. Also, 'hostname' will likely be the same on many machines
(localhost.localdomain or similar).

The only reason I bothered with UUID's before is because they have a
standard setup to make the possibility of collision extremely small, and
I figured replication will end up using it someday.

> 
> > We also haven't discussed commands for backup/restore, but I will use
> > what I think is appropriate and we can change the grammar if needed. The
> > initial hot-backup capability will require the database to be in
> > read-only mode and use tar for backup, and I will add the ability to
> > allow writes later.
> 
> Yea, I saw Tom balked at that. I think we have enough manpower and time
> that we can get hot backup in normal read/write mode working before 7.3
> beta so I would just code it assuming the system is live and we can deal
> with making it hot-capable once it is in CVS.  It doesn't have to work
> 100% until beta time.

Hot backup read/write requires that we force an advance in the logfile
segment after the backup. We need to save all the logs between backup
start and completion. Otherwise the files will be useless as a
standalone system if the current logs somehow get destroyed (fire in the
machine room, etc.).

The way I would do this is:

  create a checkpoint
  do the block-by-block walk of the files using the bufmgr
  create a second checkpoint
  force the log to advance past the end of the current segment
  save the log segments containing records between the
       first & second checkpont with the backup

Then if you restore the backup, you can recover to the point of the
second checkpoint, even if the logs since then are all gone.

Right now the log segment size is fixed, so this means that we'd waste
8MB of log space on average to do a backup. Also, the way XLOG reads
records right now, we have to write placeholder records into the empty
space, because that's how it finds the end of the log stream. So I need
to change XLOG to handle "skip records", and then to truncate the file
when it gets archived, so we don't have to save up to 16MB of zeros.

Also, if archiving is turned off, then we can't recycle or delete any
logs for the duration of the backup, and we have to save them.

So I'll finish the XLOG support for this, and then think about the
correct way to walk through all the files.
 
-- 
J. R. Nield
jrnield@usol.com




17 Jul 2002 19:01:39 -0400
"J. R. Nield" <jrnield@usol.com>
Re: Issues Outstanding for Point In Time Recovery (PITR)
J. R. Nield wrote:
> I will do something like this, but reserve 16 bytes for it just in case
> we change our minds. It needs to be different among systems on the same
> machine, so there needs to be a time value and a pseudo-random part as
> well. Also, 'hostname' will likely be the same on many machines
> (localhost.localdomain or similar).
> 
> The only reason I bothered with UUID's before is because they have a
> standard setup to make the possibility of collision extremely small, and
> I figured replication will end up using it someday.

Sure.  Problem is, we support so many platforms that any trickery is a
problem.  If they can change it in postgresql.conf, that should be
sufficient.

> Hot backup read/write requires that we force an advance in the logfile
> segment after the backup. We need to save all the logs between backup
> start and completion. Otherwise the files will be useless as a
> standalone system if the current logs somehow get destroyed (fire in the
> machine room, etc.).
> 
> The way I would do this is:
> 
>   create a checkpoint
>   do the block-by-block walk of the files using the bufmgr
>   create a second checkpoint
>   force the log to advance past the end of the current segment
>   save the log segments containing records between the
>        first & second checkpont with the backup

Sounds good.

> Then if you restore the backup, you can recover to the point of the
> second checkpoint, even if the logs since then are all gone.

Good, you put the logs that happened during the backup inside the same
backup, make it consistent.  Makes sense.

> Right now the log segment size is fixed, so this means that we'd waste
> 8MB of log space on average to do a backup. Also, the way XLOG reads
> records right now, we have to write placeholder records into the empty
> space, because that's how it finds the end of the log stream. So I need
> to change XLOG to handle "skip records", and then to truncate the file
> when it gets archived, so we don't have to save up to 16MB of zeros.
> 
> Also, if archiving is turned off, then we can't recycle or delete any
> logs for the duration of the backup, and we have to save them.
> 
> So I'll finish the XLOG support for this, and then think about the
> correct way to walk through all the files.

Sounds like a good plan.

-- 
  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, 17 Jul 2002 20:27:09 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Issues Outstanding for Point In Time Recovery (PITR)