Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42976=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1XeRQQ-0005m3-IX
 for pgsql-admin@arkaria.postgresql.org; Wed, 15 Oct 2014 16:27:46 +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+M42976=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1XeRQP-0001fZ-P3
 for pgsql-admin@arkaria.postgresql.org; Wed, 15 Oct 2014 16:27:45 +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 <jayknowsunix@gmail.com>)
 id 1XeRQO-0001fT-Mw
 for pgsql-admin@postgresql.org; Wed, 15 Oct 2014 16:27:45 +0000
Received: from mail-qa0-x22b.google.com ([2607:f8b0:400d:c00::22b])
 by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <jayknowsunix@gmail.com>)
 id 1XeRQL-0003Ko-HJ
 for pgsql-admin@postgresql.org; Wed, 15 Oct 2014 16:27:42 +0000
Received: by mail-qa0-f43.google.com with SMTP id s7so1089837qap.16
 for <pgsql-admin@postgresql.org>; Wed, 15 Oct 2014 09:27:40 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=references:mime-version:in-reply-to:content-type
 :content-transfer-encoding:message-id:cc:from:subject:date:to;
 bh=I58C6b5GcPciZQ0SpWZKU4Vg6dgd//DZUItNY1ww0Dg=;
 b=S/Hj8zmqVgCkeqz+KDYnWJrqdtZ0rDuj0m9abyFzhJjDo5mw8rxMYToRaJ2opsFCsg
 MTxR4fAD1Mi8dEe2giroJH2jsp4hqkXa9fKZuJptjqojqflJuAfGjsnfnwEs/hHkgcVo
 39cgyBCGQmGK4w4QEE+VlnhhroTMuJxw8fwBcwvse7je2nKxKV2QPch/G+f+ih4v7dvJ
 w3bFplWfrE/XCUbGVZrszJQ/DZMZBCrwOGaQYohqpBHr50P+gDGjAZkeuDYJUerGtXXk
 Chx+JnPp8BOPBDd6UHB0Pb+sNRy6PIEMXtp+1BDhjAUYncNbaXQWqz0y2d9NGhcRwWdQ
 j6Pw==
X-Received: by 10.140.86.73 with SMTP id o67mr20318325qgd.67.1413390455809;
 Wed, 15 Oct 2014 09:27:35 -0700 (PDT)
Received: from [192.168.254.3] (h77.89.102.166.static.ip.windstream.net.
 [166.102.89.77])
 by mx.google.com with ESMTPSA id 18sm5454242qgl.5.2014.10.15.09.27.34
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Wed, 15 Oct 2014 09:27:35 -0700 (PDT)
References: <3A8E2AE3D7AFB44F89EA82A873FBE4E6557E2CCA@DC-MBOX1.advisory.com>
Mime-Version: 1.0 (1.0)
In-Reply-To: <3A8E2AE3D7AFB44F89EA82A873FBE4E6557E2CCA@DC-MBOX1.advisory.com>
Content-Type: multipart/alternative;
 boundary=Apple-Mail-215D4E0F-36B0-49CE-9D82-240207E53BE7
Content-Transfer-Encoding: 7bit
Message-Id: <18EBD8DA-B2BE-4962-AC44-3FA7BC273803@gmail.com>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
X-Mailer: iPad Mail (11D257)
From: jayknowsunix@gmail.com
Subject: Re: WAL Archive Cleanup on Master/Standby
Date: Wed, 15 Oct 2014 12:27:35 -0400
To: "Doiron, Daniel" <DoironD@advisory.com>
X-Pg-Spam-Score: -2.0 (--)
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


--Apple-Mail-215D4E0F-36B0-49CE-9D82-240207E53BE7
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable

Daniel,=20

I use a custom script that I developed as we needed a reliable copy to a pai=
r a standby servers. That script knows which WAL segment has been successful=
ly copied to which server, and when it knows that both copies were successfu=
l, it deletes the segment from the master. However, if one of the copies fai=
led, the WAL segment is left on the primary where it is tried again the next=
 time it gets called. So far, it seems fairly reliable and consistent.
--
Jay

Sent from my iPad

> On Oct 15, 2014, at 12:02 PM, "Doiron, Daniel" <DoironD@advisory.com> wrot=
e:
>=20
> I=E2=80=99m curious how others are cleaning up their WAL archive files and=
 other miscellany created by the process.
> =20
> Here=E2=80=99s my setup:
> =20
> Server A: archive_command executes a script that rsync=E2=80=99s the archi=
ve file to a local folder and a matching folder on the standby server (does n=
ot mirror the folder, just pushes the file twice)
> =20
> Server B: recovery.conf archive_cleanup_command uses pg_archivecleanup to c=
lean the standby archive folder and sends log output to a cleanup.log file
> =20
> What I=E2=80=99m left with:
> -          WAL archive files on the Master server that only get cleaned up=
 if I fail over and recover using a new pg_basebackup from Server B to Serve=
r A
> -          .history and .backup files on the standby server
> -          Entries in the cleanup.log file
> =20
> Right now, I=E2=80=99m thinking my cleanup will involve (every 6 months):
> -          Failing over my cluster (kidding)
> -          Truncating the cleanup.log file
> -          ?? with .history and .backup files
> =20
> What are you doing?

--Apple-Mail-215D4E0F-36B0-49CE-9D82-240207E53BE7
Content-Type: text/html;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto"><div>Daniel,&nbsp;</div><div><br></div><div=
>I use a custom script that I developed as we needed a reliable copy to a pa=
ir a standby servers. That script knows which WAL segment has been successfu=
lly copied to which server, and when it knows that both copies were successf=
ul, it deletes the segment from the master. However, if one of the copies fa=
iled, the WAL segment is left on the primary where it is tried again the nex=
t time it gets called. So far, it seems fairly reliable and consistent.</div=
><div>--</div><div>Jay<br><br>Sent from my iPad</div><div><br>On Oct 15, 201=
4, at 12:02 PM, "Doiron, Daniel" &lt;<a href=3D"mailto:DoironD@advisory.com"=
>DoironD@advisory.com</a>&gt; wrote:<br><br></div><blockquote type=3D"cite">=
<div>

<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii">=

<meta name=3D"Generator" content=3D"Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:Wingdings;
	panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
	{font-family:Wingdings;
	panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
	{mso-style-priority:34;
	margin-top:0in;
	margin-right:0in;
	margin-bottom:0in;
	margin-left:.5in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri","sans-serif";}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
/* List Definitions */
@list l0
	{mso-list-id:1329209554;
	mso-list-type:hybrid;
	mso-list-template-ids:1825092204 1748770224 67698691 67698693 67698=
689 67698691 67698693 67698689 67698691 67698693;}
@list l0:level1
	{mso-level-start-at:192;
	mso-level-number-format:bullet;
	mso-level-text:-;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-.25in;
	font-family:"Calibri","sans-serif";
	mso-fareast-font-family:Calibri;
	mso-bidi-font-family:"Times New Roman";}
@list l0:level2
	{mso-level-number-format:bullet;
	mso-level-text:o;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-.25in;
	font-family:"Courier New";}
@list l0:level3
	{mso-level-number-format:bullet;
	mso-level-text:\F0A7;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-.25in;
	font-family:Wingdings;}
@list l0:level4
	{mso-level-number-format:bullet;
	mso-level-text:\F0B7;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-.25in;
	font-family:Symbol;}
@list l0:level5
	{mso-level-number-format:bullet;
	mso-level-text:o;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-.25in;
	font-family:"Courier New";}
@list l0:level6
	{mso-level-number-format:bullet;
	mso-level-text:\F0A7;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-.25in;
	font-family:Wingdings;}
@list l0:level7
	{mso-level-number-format:bullet;
	mso-level-text:\F0B7;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-.25in;
	font-family:Symbol;}
@list l0:level8
	{mso-level-number-format:bullet;
	mso-level-text:o;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-.25in;
	font-family:"Courier New";}
@list l0:level9
	{mso-level-number-format:bullet;
	mso-level-text:\F0A7;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-.25in;
	font-family:Wingdings;}
ol
	{margin-bottom:0in;}
ul
	{margin-bottom:0in;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->


<div class=3D"WordSection1">
<p class=3D"MsoNormal">I=E2=80=99m curious how others are cleaning up their W=
AL archive files and other miscellany created by the process.<o:p></o:p></p>=

<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Here=E2=80=99s my setup:<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Server A: archive_command executes a script that rsyn=
c=E2=80=99s the archive file to a local folder and a matching folder on the s=
tandby server (does not mirror the folder, just pushes the file twice)<o:p><=
/o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Server B: recovery.conf archive_cleanup_command uses p=
g_archivecleanup to clean the standby archive folder and sends log output to=
 a cleanup.log file<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">What I=E2=80=99m left with:<o:p></o:p></p>
<p class=3D"MsoListParagraph" style=3D"text-indent:-.25in;mso-list:l0 level1=
 lfo1"><!--[if !supportLists]--><span style=3D"mso-list:Ignore">-<span style=
=3D"font:7.0pt &quot;Times New Roman&quot;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;
</span></span><!--[endif]-->WAL archive files on the Master server that only=
 get cleaned up if I fail over and recover using a new pg_basebackup from Se=
rver B to Server A<o:p></o:p></p>
<p class=3D"MsoListParagraph" style=3D"text-indent:-.25in;mso-list:l0 level1=
 lfo1"><!--[if !supportLists]--><span style=3D"mso-list:Ignore">-<span style=
=3D"font:7.0pt &quot;Times New Roman&quot;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;
</span></span><!--[endif]-->.history and .backup files on the standby server=
<o:p></o:p></p>
<p class=3D"MsoListParagraph" style=3D"text-indent:-.25in;mso-list:l0 level1=
 lfo1"><!--[if !supportLists]--><span style=3D"mso-list:Ignore">-<span style=
=3D"font:7.0pt &quot;Times New Roman&quot;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;
</span></span><!--[endif]-->Entries in the cleanup.log file<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Right now, I=E2=80=99m thinking my cleanup will invol=
ve (every 6 months):<o:p></o:p></p>
<p class=3D"MsoListParagraph" style=3D"text-indent:-.25in;mso-list:l0 level1=
 lfo1"><!--[if !supportLists]--><span style=3D"mso-list:Ignore">-<span style=
=3D"font:7.0pt &quot;Times New Roman&quot;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;
</span></span><!--[endif]-->Failing over my cluster (kidding)<o:p></o:p></p>=

<p class=3D"MsoListParagraph" style=3D"text-indent:-.25in;mso-list:l0 level1=
 lfo1"><!--[if !supportLists]--><span style=3D"mso-list:Ignore">-<span style=
=3D"font:7.0pt &quot;Times New Roman&quot;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;
</span></span><!--[endif]-->Truncating the cleanup.log file<o:p></o:p></p>
<p class=3D"MsoListParagraph" style=3D"text-indent:-.25in;mso-list:l0 level1=
 lfo1"><!--[if !supportLists]--><span style=3D"mso-list:Ignore">-<span style=
=3D"font:7.0pt &quot;Times New Roman&quot;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;
</span></span><!--[endif]-->?? with .history and .backup files<o:p></o:p></p=
>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">What are you doing?<o:p></o:p></p>
</div>


</div></blockquote></body></html>=

--Apple-Mail-215D4E0F-36B0-49CE-9D82-240207E53BE7--