Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50056=pgsql-performance-ar@postgresql.org>)
 id 1Um7Wk-0005hE-KT
 for pgsql-performance@arkaria.postgresql.org; Mon, 10 Jun 2013 19:13:14 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.72) (envelope-from
 <pgsql-performance-owner+M50056=pgsql-performance-ar@postgresql.org>)
 id 1Um7Wj-00084V-Dh
 for pgsql-performance@arkaria.postgresql.org; Mon, 10 Jun 2013 19:13:13 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.72)
 (envelope-from <nielskristian@autouncle.com>) id 1Um7W5-0007WH-Re
 for pgsql-performance@postgresql.org; Mon, 10 Jun 2013 19:12:34 +0000
Received: from mail-bk0-x235.google.com ([2a00:1450:4008:c01::235])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <nielskristian@autouncle.com>) id 1Um7W2-0006l1-Iz
 for pgsql-performance@postgresql.org; Mon, 10 Jun 2013 19:12:33 +0000
Received: by mail-bk0-f53.google.com with SMTP id e11so2923359bkh.40
 for <pgsql-performance@postgresql.org>; Mon, 10 Jun 2013 12:12:29 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
 d=google.com; s=20120113;
 h=content-type:mime-version:subject:from:in-reply-to:date:cc
 :message-id:references:to:x-mailer:x-gm-message-state;
 bh=v7IzJEy9t45Fi/k65gSi/TU/fHaVTEqaXPVIJwwCS8k=;
 b=MaZhrwC6Y01AUj0zWoU/KsB+KCYFKMpF/Oq744WTTjL7JQa0aNaWOTaptOL0K6PNvf
 0JbMgOy/Sc0/Z1dya4faSVt7tfFJtBAw56TByF1nF8zMz4IHcvhniGkctHmnUwRuWM9d
 bupM2yx+vK6WkZNa2Wy38LCJFcTNO1WcwNo1IAchwXJBy2+eYoJb0l11+rbqalnC2bWu
 +dL+vno+2Q2Yje85iAB8bq2zNbGDQCDk5mOVtrM1bjhhPYXBcXeqWH7xXMQCZfnGakpA
 QWm4tPBjILbaN8fP1+IZZIWcM7zR7kzeBY31VfrhObmMG7b4/k7ZgzC8IbCgAz0pr0vN
 JzeQ==
X-Received: by 10.204.231.197 with SMTP id jr5mr1655745bkb.18.1370891549057;
 Mon, 10 Jun 2013 12:12:29 -0700 (PDT)
Received: from [192.168.1.46] (0905ds8-ar.0.fullrate.dk. [90.185.25.212])
 by mx.google.com with ESMTPSA id b12sm4422881bkz.0.2013.06.10.12.12.27
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Mon, 10 Jun 2013 12:12:28 -0700 (PDT)
Content-Type: multipart/alternative;
 boundary="Apple-Mail=_F49C74B6-ECDD-4091-881F-7266F7442FBC"
Mime-Version: 1.0 (Mac OS X Mail 6.3 \(1503\))
Subject: Re: URGENT issue: pg-xlog growing on master!
From: =?iso-8859-1?Q?Niels_Kristian_Schj=F8dt?= <nielskristian@autouncle.com>
In-Reply-To: 
 <CAMkU=1yrOnRWA+ETgyo3hv7wHW=JDrvrZq++pktrytcgt=-ScA@mail.gmail.com>
Date: Mon, 10 Jun 2013 21:12:25 +0200
Cc: "pgsql-performance@postgresql.org list" <pgsql-performance@postgresql.org>
Message-Id: <4BA47952-3CFC-4B02-9B70-8D3FFD84DA61@autouncle.com>
References: <BCE969C3-612D-4622-8E7D-B3241D23EEA4@autouncle.com>
 <CAGrpgQ9mc+jwqj_ZK4MCFmx7RaSifdPDsCRaw0qT=bG0RTLMVg@mail.gmail.com>
 <4DB42015-B9C6-4019-9A4D-68E356C8C947@autouncle.com>
 <CAMkU=1zf9jbmY7D_oof59qbecx=DJr_gGg2MOW_9QQQ7GvQO2g@mail.gmail.com>
 <44F3F6DC-A0BB-407A-AA4D-3879AE5DE221@autouncle.com>
 <CAMkU=1yrOnRWA+ETgyo3hv7wHW=JDrvrZq++pktrytcgt=-ScA@mail.gmail.com>
To: Jeff Janes <jeff.janes@gmail.com>
X-Mailer: Apple Mail (2.1503)
X-Gm-Message-State: 
 ALoCoQkPrBgeu96N0X6D6kZegeY2g9fd0B/hZvfTZSOybWio59/coHWVBSf7YyMNzqA5Db+nN0rN
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


--Apple-Mail=_F49C74B6-ECDD-4091-881F-7266F7442FBC
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=iso-8859-1

Solved it - thanks!

Den 10/06/2013 kl. 20.24 skrev Jeff Janes <jeff.janes@gmail.com>:

> On Mon, Jun 10, 2013 at 11:02 AM, Niels Kristian Schj=F8dt =
<nielskristian@autouncle.com> wrote:
> Okay, cool
>=20
> You mean that I should do the following right?:
>=20
> 1. Stop slave server
>=20
>=20
> At this point, you don't have a slave server.  Not a usable one, =
anyway.  If you used to have a hot-standby server, it is now simply a =
historical reporting server.  If you have no need/use for such a =
reporting server, then yes you should stop it, to avoid confusion.
>=20
> =20
> 2. set archive_command =3D 'true' in postgresql.conf on the master =
server
> 3. restart master server
>=20
> You can simply do a reload rather than a full restart.
> =20
> 4. run psql -c "SELECT pg_start_backup('label', true)" on master
>=20
> No, you shouldn't do that yet without first having correctly =
functioning archiving back in place.  After setting archive_command=3Dtrue=
 and reloading the server, you have to wait a while for the "bad" WAL =
files to get pseudo-archived and cleared from the system.  Once that has =
happened, you can then return archive_command to its previous setting, =
and again reload/restart the server.  Only at that point should you =
begin taking the new backup.  In other words, steps 7 and 8 have to be =
moved up to before step 4.
> =20
> 5. run rsync -av --exclude postmaster.pid --exclude pg_xlog =
/var/lib/postgresql/9.2/main/ =
postgres@192.168.0.2:/var/lib/postgresql/9.2/main/" on master server
> 6. run psql -c "SELECT pg_stop_backup();" on master server
> 7. change archive_command back on master
> 8. restart master
> 9. start slave
>=20
> Just to confirm the approach :-)
>=20
>=20
> Cheers,
>=20
> Jeff
>=20
>=20


--Apple-Mail=_F49C74B6-ECDD-4091-881F-7266F7442FBC
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=iso-8859-1

<html><head><meta http-equiv=3D"Content-Type" content=3D"text/html =
charset=3Diso-8859-1"></head><body style=3D"word-wrap: break-word; =
-webkit-nbsp-mode: space; -webkit-line-break: after-white-space; =
">Solved it - thanks!<div><br><div><div>Den 10/06/2013 kl. 20.24 skrev =
Jeff Janes &lt;<a =
href=3D"mailto:jeff.janes@gmail.com">jeff.janes@gmail.com</a>&gt;:</div><b=
r class=3D"Apple-interchange-newline"><blockquote type=3D"cite"><div =
dir=3D"ltr">On Mon, Jun 10, 2013 at 11:02 AM, Niels Kristian Schj=F8dt =
<span dir=3D"ltr">&lt;<a href=3D"mailto:nielskristian@autouncle.com" =
target=3D"_blank">nielskristian@autouncle.com</a>&gt;</span> =
wrote:<br><div class=3D"gmail_extra">
<div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" =
style=3D"margin:0 0 0 .8ex;border-left:1px #ccc =
solid;padding-left:1ex"><div style=3D"word-wrap:break-word">Okay, =
cool<div><br></div><div>You mean that I should do the following =
right?:</div>
<div><br></div><div>1. Stop slave =
server</div></div></blockquote><div><br></div><div><br></div><div =
style=3D"">At this point, you don't have a slave server. &nbsp;Not a =
usable one, anyway. &nbsp;If you used to have a hot-standby server, it =
is now simply a historical reporting server. &nbsp;If you have no =
need/use for such a reporting server, then yes you should stop it, to =
avoid confusion.</div>
<div style=3D""><br></div><div style=3D"">&nbsp;</div><blockquote =
class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc =
solid;padding-left:1ex"><div style=3D"word-wrap:break-word"><div>2. =
set&nbsp;archive_command =3D 'true' in postgresql.conf on the master =
server</div>
<div>3. restart master =
server</div></div></blockquote><div><br></div><div style=3D"">You can =
simply do a reload rather than a full =
restart.</div><div>&nbsp;</div><blockquote class=3D"gmail_quote" =
style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div style=3D"word-wrap:break-word">4. run <span =
style=3D"background-color:rgb(249,249,249);line-height:1.1em">psql -c =
"SELECT pg_start_backup('label', true)" on =
master</span></div></blockquote><div>
<br></div><div style=3D"">No, you shouldn't do that yet without first =
having correctly functioning archiving back in place. &nbsp;After =
setting archive_command=3Dtrue and reloading the server, you have to =
wait a while for the "bad" WAL files to get pseudo-archived and cleared =
from the system. &nbsp;Once that has happened, you can then return =
archive_command to its previous setting, and again reload/restart the =
server. &nbsp;Only at that point should you begin taking the new backup. =
&nbsp;In other words, steps 7 and 8 have to be moved up to before step =
4.</div>
<div style=3D"">&nbsp;</div><blockquote class=3D"gmail_quote" =
style=3D"margin:0 0 0 .8ex;border-left:1px #ccc =
solid;padding-left:1ex"><div style=3D"word-wrap:break-word"><div><span =
style=3D"background-color:rgb(249,249,249);line-height:1.1em">5. =
run&nbsp;</span>rsync -av --exclude postmaster.pid --exclude pg_xlog =
/var/lib/postgresql/9.2/main/ <a href=3D"mailto:postgres@192.168.0.2" =
target=3D"_blank">postgres@192.168.0.2</a>:/var/lib/postgresql/9.2/main/"&=
nbsp;on master server</div>
<div>6. run psql -c "SELECT pg_stop_backup();" on master =
server</div><div>7. change&nbsp;archive_command back on =
master</div><div>8. restart master</div><div>9. start =
slave</div><div><br></div><div>Just to confirm the approach :-)</div>
</div></blockquote><div><br></div><div><br></div><div =
style=3D"">Cheers,</div><div style=3D""><br></div><div =
style=3D"">Jeff</div><div style=3D""><br></div><div =
style=3D""><br></div></div></div></div>
</blockquote></div><br></div></body></html>=

--Apple-Mail=_F49C74B6-ECDD-4091-881F-7266F7442FBC--