Re: How does the WAL work? Need to recovery the database to a specific point of time

Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx> · Tue, 22 Apr 2014 09:00:37 -0700



    On 04/22/2014 08:40 AM, Wang, Mary Y
      wrote:

    
        Hi All,
         
        I’d like to recovery the database to a
          specific point of time. I see some WAL files in the pg_xlog
          directories.
        I read some blogs, and some have indicated
          that a recovery.conf needs to be updated.  I do not see a
          recovery.conf file in the $PG_DATA directory ( I see
          pg_hba.conf and postgresql.conf). 
          
         
        1.     
             Do I need to create a
          recovery.conf file?  
        2.     
             What specific settings should I
          set?  
        3.     
             How do I ensure that postgres
          will read the recovery.conf file? 
          
        4.     
             What is the start/stop postgres
          command to use when working with a recovery.conf file?
         
        I am running on Postgres 8.4.11 and RHEL
          6.4.
         
        
    Point-in-time recovery is not something that can typically be
    performed arbitrarily "after the fact" - you need to have
    established the necessary backup and archiving in advance.

    
    This capability is essentially a useful side-effect of the process
    through which PostgreSQL recovers from an inconsistent state
    following a power-failure or other disruptive event by replaying the
    appropriate WAL files.

    
    Point-in-time recovery assumes you have both a *file-system* backup
    (not a pg_dump) of the PostgreSQL data files that was completed
    prior to the point in time to which you wish to recover *and* all
    the write-ahead log (WAL) files created from when the file-system
    was backed-up and the point to which you wish to recover. This is
    typically set up through a combination of scheduled file-system
    backups and archiving of WAL files.

    
    WAL files are not stored indefinitely but rather are rotated and
    optionally archived based on your configuration settings.

    
    If you have the base file-system backup and all the required WAL
    files, have a look at
    http://www.postgresql.org/docs/8.4/static/continuous-archiving.html

    
    Cheers,

    Steve