Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id ACB14D84657
	for <pgsql-admin@postgresql.org>; Fri,  8 Jun 2012 16:31:41 -0300 (ADT)
Received: from mail-yx0-f174.google.com ([209.85.213.174])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ichbinrene@gmail.com>) id 1Sd4uH-0001sD-Ig
	for pgsql-admin@postgresql.org; Fri, 08 Jun 2012 19:31:40 +0000
Received: by yenm10 with SMTP id m10so1539503yen.19
	for <pgsql-admin@postgresql.org>; Fri, 08 Jun 2012 12:31:24 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=message-id:date:from:user-agent:mime-version:to:subject:references
	:in-reply-to:content-type;
	bh=t42dP46z8KlN1umtTuYk5UnN6ACXt8l9KOapv7n70yM=;
	b=0s71zlABKJosKUjJG7zTaiRHKh2nxLPbdbEs07TFEJ64C8Dq8hD4iWlziufH19DzWI
	YQTJJki6ON0D291juKL/W2n5loaYbkvsQVFXDlxrmYIgwwxsbq2xKhYlbYJaWPTPSWdX
	GzV+4+HcxJKpIUEjZQXO7ZaeNdLdpkxZlK5d2Ny5wNrKLSsGRYDyzzVzHtLPbWsDc0H9
	5o7CHmQCufK6GNau1E0YqAV5Lca6pcppgTTrXU3Inpr304xoTlQ4IrwKISiMdn/WinyJ
	5p+5Ww5dBv987ImRnEqCeOQR9eAVNQ3KnXDpMzxa6txOge7P0K4rvtvuQV6MuVXmqUPP
	k7MA==
Received: by 10.236.78.39 with SMTP id f27mr8826782yhe.121.1339183884667;
	Fri, 08 Jun 2012 12:31:24 -0700 (PDT)
Received: from [10.0.0.117] (na-201-156-19-99.static.avantel.net.mx.
	[201.156.19.99])
	by mx.google.com with ESMTPS id f61sm24319492yhg.14.2012.06.08.12.31.23
	(version=SSLv3 cipher=OTHER); Fri, 08 Jun 2012 12:31:24 -0700 (PDT)
Message-ID: <4FD252D8.2010103@gmail.com>
Date: Fri, 08 Jun 2012 14:30:32 -0500
From: =?ISO-8859-1?Q?Ren=E9_Romero_Benavides?= <ichbinrene@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:12.0) Gecko/20120421 Thunderbird/12.0
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: Question about PITR backup
References: <1339152214.5566.YahooMailClassic@web15601.mail.cnb.yahoo.com>
In-Reply-To: <1339152214.5566.YahooMailClassic@web15601.mail.cnb.yahoo.com>
Content-Type: multipart/alternative;
	boundary="------------000203030504010903060908"
X-Pg-Spam-Score: -2.7 (--)
X-Archive-Number: 201206/41
X-Sequence-Number: 39632

This is a multi-part message in MIME format.
--------------000203030504010903060908
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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 <http://pglearn.blogspot.mx/>
*Twitter*.You might consider to follow *@sqlhotfix*

--------------000203030504010903060908
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    Am 08.06.2012 05:43, schrieb sgm:
    <blockquote
      cite="mid:1339152214.5566.YahooMailClassic@web15601.mail.cnb.yahoo.com"
      type="cite">
      <table border="0" cellpadding="0" cellspacing="0">
        <tbody>
          <tr>
            <td style="font: inherit;" valign="top">Dear admin,
              <div><br>
              </div>
              <div>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?&nbsp;</div>
              <div><br>
              </div>
              <div>How can we recover the database to 14:00 pm? We are
                missing the newest pg_xlog.</div>
              <div><br>
              </div>
              <div>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? &nbsp; Any suggestions?</div>
            </td>
          </tr>
        </tbody>
      </table>
    </blockquote>
    <br>
    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. <br>
    So,provided all the WAL files generated by the server after the last
    checkpoint are available, it will always be able to&nbsp; bring the data
    files to a consistent state. Those WAL files are always kept in the
    pg_xlog directory.<br>
    <br>
    Though I'm also in favor of&nbsp; leveraging a streaming replication
    configuration as it's more reliable and the window for data loss is
    very small.<br>
    <br>
    <div class="moz-signature">-- <br>
      <a href="http://pglearn.blogspot.mx/">
        pglearn.blogspot.mx:postgresql recipes </a>
      <br>
      <b>Twitter</b>.You might consider to follow <b>@sqlhotfix</b></div>
  </body>
</html>

--------------000203030504010903060908--