Re: Question about PITR backup

René Romero Benavides <ichbinrene@xxxxxxxxx> · Fri, 08 Jun 2012 14:30:32 -0500



    Am 08.06.2012 05:43, schrieb sgm:
    
      
            Dear admin,
              

              I have a question about PITR backup in a single
                server, the method is make a base backup, and backup the
                WAL archive log(eg, every day at 11:30 pm). But if the
                OS' harddisk is broken(eg,14:00 pm),the system can't
                start, we have to recover the database on a another
                server using the base backup and the WAL backup,so in
                this case suppose that we only can restore the database
                to yesterday's 11:30 pm state, am I right? 
              

              How can we recover the database to 14:00 pm? We are
                missing the newest pg_xlog.
              

              I want to backup the pg_xlog folder every minute by
                crontab but my concern is that the data inconsistent,
                because the lastest log in pg_xlog is being updated all
                the time, am I right?   Any suggestions?
            
          
    Yes, the wal segments are updated constantly. You'll have a
    consistent state in your data files up to the time when the last
    checkpoint happened. Should a crash occur the server will replay the
    log segments in the WAL files starting from the one that was marked
    during the last checkpoint recorded. 

    So,provided all the WAL files generated by the server after the last
    checkpoint are available, it will always be able to  bring the data
    files to a consistent state. Those WAL files are always kept in the
    pg_xlog directory.

    
    Though I'm also in favor of  leveraging a streaming replication
    configuration as it's more reliable and the window for data loss is
    very small.

    
    -- 

      
        pglearn.blogspot.mx:postgresql recipes 
      

      Twitter.You might consider to follow @sqlhotfix