Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42725=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1XEMC4-0003PN-MU
 for pgsql-admin@arkaria.postgresql.org; Mon, 04 Aug 2014 17:37:08 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42725=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1XEMC4-0004T4-14
 for pgsql-admin@arkaria.postgresql.org; Mon, 04 Aug 2014 17:37:08 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <jmathis@redzonesoftware.com>)
 id 1XEMC3-0004Sy-04
 for pgsql-admin@postgresql.org; Mon, 04 Aug 2014 17:37:07 +0000
Received: from mail-ie0-f178.google.com ([209.85.223.178])
 by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <jmathis@redzonesoftware.com>)
 id 1XEMBy-0005Gy-Vo
 for pgsql-admin@postgresql.org; Mon, 04 Aug 2014 17:37:05 +0000
Received: by mail-ie0-f178.google.com with SMTP id rd18so10551803iec.9
 for <pgsql-admin@postgresql.org>; Mon, 04 Aug 2014 10:37:01 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
 d=1e100.net; s=20130820;
 h=x-gm-message-state:date:from:to:cc:message-id:in-reply-to
 :references:subject:mime-version:content-type;
 bh=xFOtYIXIA79nH0Q8DlaDGOyl5hvbajvZRLQL/jJR49M=;
 b=WimNu7l966poRvrKh9hQjdRd9XhssvXnPsoVotMJgJMJU01inn24PK/ETyMA8mRAXW
 uvPxIogjOZP+VFqFsjB8eFalp0vZfRDCSy0OvpuuBIjV00Y79fuua3zBt7J0E0SPICsm
 KsJ5UYzDLedYlb0DuzBOddm+cNWuP8nTGg/5YR+mQus1rYeUbWMmf9AG4UUq2EUfUE/g
 HhFf9v84f3FBiZW+quZkLAbIBWPecRGATzWhnN6Ou8L80wb5rZhbpdmHWev7xWekCt3z
 4+7PAgec+/IDdTgObDgoc8KwRDskZmPs3R2MyUzJ3xPXA7bMOW931wCp9AkAF8cbqBpa
 OERw==
X-Gm-Message-State: 
 ALoCoQmkgZpEp+sX5frvjQWUn1oB7CWKiL/dydiNAWNajA3L1ZQ06prD+4aC2DW6kchlu/A0IxbhgA4Ui34pQld9towYTiUJGn+nGmVjzYXh5xBUidWFTgI=
X-Received: by 10.42.83.131 with SMTP id h3mr34376727icl.77.1407173820926;
 Mon, 04 Aug 2014 10:37:00 -0700 (PDT)
Received: from palos ([50.76.140.65])
 by mx.google.com with ESMTPSA id ki9sm15254402igb.3.2014.08.04.10.36.59
 for <multiple recipients>
 (version=TLSv1.2 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Mon, 04 Aug 2014 10:37:00 -0700 (PDT)
Date: Mon, 4 Aug 2014 11:36:57 -0600
From: Jason Mathis <jmathis@redzonesoftware.com>
To: Scott Whitney <scott@journyx.com>, Techie <techchavez@gmail.com>,
 Scott Whitney <swhitney@journyx.com>
Cc: pgsql-admin@postgresql.org
Message-ID: <etPan.53dfc4b9.66334873.137@palos>
In-Reply-To: 
 <674692279.745123.1407172516368.JavaMail.zimbra@mail.int.journyx.com>
References: 
 <CAEUA182F3=NOqjVayHMVdcxUnx3XeyQHAaohOZ9LdMqBSvWOcA@mail.gmail.com>
 <674692279.745123.1407172516368.JavaMail.zimbra@mail.int.journyx.com>
Subject: Re: Help with PITR WAL Restore and configuration.
X-Mailer: Airmail (247)
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="53dfc4b9_74b0dc51_137"
X-Pg-Spam-Score: -2.6 (--)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

--53dfc4b9_74b0dc51_137
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline


You may want to look at the =E2=80=9C-x=E2=80=9D option for the older backu=
ps.

"Includes the required transaction log files (WAL files) in the backup. Thi=
s will include all transaction logs generated during the backup. If this op=
tion is specified, it is possible to start a postmaster directly in the ext=
racted directory without the need to consult the log archive, thus making t=
his a completely standalone backup."

http://www.postgresql.org/docs/9.2/static/app-pgbasebackup.html

Otherwise you need those wal segments to perform a restore.=C2=A0

-jason


On August 4, 2014 at 11:18:40 AM, Scott Whitney (scott@journyx.com) wrote:

Let's say that you began this in January. According to your retention plan,=
 you will have:

Base Backups:
--------------
Jan 31, 2014
Feb 28, 2014
Mar 31, 2014
Apr 30, 2014
May 31, 2014
Jun 30, 2014
July 31, 2014

WAL Logs:
Daily WAL logs since Jun 5, 2014

If my understanding is correct, none of your base backups prior to Jun 5, 2=
014 will be useful for recovering any PITR other than the specific time _of=
_ the base backup. You cannot replay later WALs on the March backup, for ex=
ample, as you are missing all of the WAL segments between Mar 31, 2014 and =
Jun 5, 2014.

So, if I am reading this correctly, the only points which you could recover=
 are:

Jan 31, 2014
Feb 28, 2014
Mar 31, 2014
Apr 30, 2014
May 31, 2014
Jun 30, 2014 + any point in time to your latest WAL backups.

There would be a gap between May 31st and June 5th which would not allow PI=
TR.

Hi All,

We are moving to a new backup solution where we will backup direct to the b=
ackup server using a Daily Base backup with pg_start_backup and WAL Log bac=
kups every 4 hours.
In regards to retention we are planning on this.

WAL Logs
Daily 4 to 6 times: 60 days retention.

Base Backup
Daily: Mon-Thursday 7 Day Retention
Weekly: Friday 31 Day Retention
Monthly: Last Day of month: 365 Day Retention

So what I am looking for is some clarification on the Base Backups that are=
 6 months old for example.
Since my WAL Log backups have a 2 month retention I can do PITR from any Ba=
se Backup + WAL Logs any day from 2 months back.

However let's say for example I want to restore the Base Backup from 6 mont=
hs ago, I only have the Base Backups.. Will I be able to recover to that po=
int in time using that Base backup? I have read the PITR documentation howe=
ver I still struggle to understand this part of it.

Thanks
Jimmy


--=20
=20

This transmission contains confidential and privileged information intended=
=20
solely for the party identified above. If you receive this message in=20
error, you must not use it or convey it to others. Please destroy it=20
immediately and contact the sender at (303) 386-3955 or by return e-mail to=
=20
the sender.

--53dfc4b9_74b0dc51_137
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

<html><head><style>body{font-family:Helvetica,Arial;font-size:13px}</style>=
</head><body style=3D"word-wrap: break-word; -webkit-nbsp-mode: space; -web=
kit-line-break: after-white-space;"><div id=3D"bloop_customfont" style=3D"f=
ont-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: =
0px; line-height: auto;"><br></div> <div id=3D"bloop_sign_14071736465861859=
84" class=3D"bloop_sign"><div style=3D"font-family:helvetica,arial;font-siz=
e:13px">You may want to look at the =E2=80=9C-x=E2=80=9D option for the old=
er backups.</div><div style=3D"font-family:helvetica,arial;font-size:13px">=
<br></div><div style=3D"font-family:helvetica,arial;font-size:13px">"<span =
style=3D"font-family: verdana, sans-serif; font-size: 12px; line-height: 18=
.239999771118164px;">Includes the required transaction log files (WAL files=
) in the backup. This will include all transaction logs generated during th=
e backup. If this option is specified, it is possible to start a postmaster=
 directly in the extracted directory without the need to consult the log ar=
chive, thus making this a completely standalone backup."</span></div><div s=
tyle=3D"font-family:helvetica,arial;font-size:13px"><br></div><div style=3D=
"font-family:helvetica,arial;font-size:13px"><a href=3D"http://www.postgres=
ql.org/docs/9.2/static/app-pgbasebackup.html">http://www.postgresql.org/doc=
s/9.2/static/app-pgbasebackup.html</a></div><div style=3D"font-family:helve=
tica,arial;font-size:13px"><br></div><div style=3D"font-family:helvetica,ar=
ial;font-size:13px">Otherwise you need those wal segments to perform a rest=
ore.&nbsp;</div><div style=3D"font-family:helvetica,arial;font-size:13px"><=
br></div><div style=3D"font-family:helvetica,arial;font-size:13px">-jason</=
div><div style=3D"font-family:helvetica,arial;font-size:13px"><br></div><di=
v style=3D"font-family:helvetica,arial;font-size:13px"><br></div></div><p s=
tyle=3D"color:#000;">On August 4, 2014 at 11:18:40 AM, Scott Whitney (<a hr=
ef=3D"mailto:scott@journyx.com">scott@journyx.com</a>) wrote:</p> <blockquo=
te type=3D"cite" class=3D"clean_bq"><span><div><div></div><div>



<title></title>


<div style=3D"font-family: times new roman, new york, times, serif; font-si=
ze: 12pt; color: #000000">
<div>Let's say that you began this in January. According to your
retention plan, you will have:</div>
<div><br></div>
<div>Base Backups:</div>
<div>--------------</div>
<div>Jan 31, 2014</div>
<div>Feb 28, 2014</div>
<div>Mar 31, 2014</div>
<div>Apr 30, 2014</div>
<div>May 31, 2014</div>
<div>Jun 30, 2014</div>
<div>July 31, 2014</div>
<div><br></div>
<div>WAL Logs:</div>
<div>Daily WAL logs since Jun 5, 2014</div>
<div><br></div>
<div>If my understanding is correct, none of your base backups
prior to Jun 5, 2014 will be useful for recovering any PITR other
than the specific time _of_ the base backup. You cannot replay
later WALs on the March backup, for example, as you are missing all
of the WAL segments between Mar 31, 2014 and Jun 5, 2014.</div>
<div><br></div>
<div>So, if I am reading this correctly, the only points which you
could recover are:</div>
<div><br></div>
<div>
<div>Jan 31, 2014</div>
<div>Feb 28, 2014</div>
<div>Mar 31, 2014</div>
<div>Apr 30, 2014</div>
<div>May 31, 2014</div>
<div>Jun 30, 2014 + any point in time to your latest WAL
backups.</div>
<div><br></div>
<div>There would be a gap between May 31st and June 5th which would
not allow PITR.</div>
</div>
<div><br></div>
<hr id=3D"zwchr">
<blockquote style=3D"border-left:2px solid #1010FF;margin-left:5px;padding-=
left:5px;color:#000;font-weight:normal;font-style:normal;text-decoration:no=
ne;font-family:Helvetica,Arial,sans-serif;font-size:12pt;">
<div dir=3D"ltr">
<div>
<div>
<div>Hi All,<br>
<div><br></div>
</div>
We are moving to a new backup solution where we will backup direct
to the backup server using a Daily Base backup with pg_start_backup
and WAL Log backups every 4 hours.<br></div>
In regards to retention we are planning on this.<br>
<div><br></div>
WAL Logs<br>
Daily 4 to 6 times: 60 days retention.<br></div>
<br>
Base Backup<br>
<div>Daily: Mon-Thursday 7 Day Retention<br></div>
<div>Weekly: Friday 31 Day Retention<br></div>
<div>Monthly: Last Day of month: 365 Day Retention<br>
<div><br></div>
</div>
<div>So what I am looking for is some clarification on the Base
Backups that are 6 months old for example.<br></div>
<div>Since my WAL Log backups have a 2 month retention I can do
PITR from any Base Backup + WAL Logs any day from 2 months
back.<br>
<br></div>
<div>However let's say for example I want to restore the Base
Backup from 6 months ago, I only have the Base Backups.. Will I be
able to recover to that point in time using that Base backup? I
have read the PITR documentation however I still struggle to
understand this part of it.<br>
<br></div>
<div>Thanks<br></div>
<div>Jimmy<br></div>
</div>
</blockquote>
<div><br></div>
</div>


</div></div></span></blockquote></body></html>
<br>


<p><font face=3D"Arial" size=3D"2">This transmission contains confidential =
and privileged
information intended solely for the party identified above. If you receive =
this message in error, you must not use
it or convey it to others. Please destroy it immediately and contact the se=
nder at
(303) 386-3955 or by return e-mail to the sender.</font></p>


--53dfc4b9_74b0dc51_137--