Date: Mon, 4 Aug 2014 12:15:16 -0500 (CDT)
From: Scott Whitney <scott@journyx.com>
Reply-To: Scott Whitney <swhitney@journyx.com>
To: Techie <techchavez@gmail.com>
Cc: pgsql-admin@postgresql.org
Message-ID: 
 <674692279.745123.1407172516368.JavaMail.zimbra@mail.int.journyx.com>
In-Reply-To: 
 <CAEUA182F3=NOqjVayHMVdcxUnx3XeyQHAaohOZ9LdMqBSvWOcA@mail.gmail.com>
References: 
 <CAEUA182F3=NOqjVayHMVdcxUnx3XeyQHAaohOZ9LdMqBSvWOcA@mail.gmail.com>
Subject: Re: Help with PITR WAL Restore and configuration.
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="----=_Part_745122_1149104250.1407172516367"
Thread-Topic: Help with PITR WAL Restore and configuration.
Thread-Index: S4fsV7dl1ToFp6h/eXEbdp7CZkX17A==
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

------=_Part_745122_1149104250.1407172516367
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

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

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

----- Original Message -----

> Hi All,

> 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.
> 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 Base
> Backup + WAL Logs any day from 2 months back.

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

> Thanks
> Jimmy

------=_Part_745122_1149104250.1407172516367
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><body><div style=3D"font-family: times new roman, new york, times, se=
rif; font-size: 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><b=
r></div><div>If my understanding is correct, none of your base backups prio=
r to Jun 5, 2014 will be useful for recovering any PITR other than the spec=
ific time _of_ the base backup. You cannot replay later WALs on the March b=
ackup, 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></di=
v><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 poin=
t 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></d=
iv><div><br></div><hr id=3D"zwchr"><blockquote style=3D"border-left:2px sol=
id #1010FF;margin-left:5px;padding-left:5px;color:#000;font-weight:normal;f=
ont-style:normal;text-decoration:none;font-family:Helvetica,Arial,sans-seri=
f;font-size:12pt;"><div dir=3D"ltr"><div><div><div>Hi All,<br><div><br></di=
v></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>WA=
L 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 3=
1 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 Backu=
ps that are 6 months old for example.<br></div><div>Since my WAL Log backup=
s have a 2 month retention I can do PITR from any Base Backup + WAL Logs an=
y day from 2 months back.<br>
<br></div><div>However let's say for example I want to restore the Base Bac=
kup from 6 months ago, I only have the Base Backups.. Will I be able to rec=
over to that point in time using that Base backup? I have read the PITR doc=
umentation 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></body></html>
------=_Part_745122_1149104250.1407172516367--