Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.92)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1jHax0-0002wm-58
	for pgsql-admin@arkaria.postgresql.org; Thu, 26 Mar 2020 22:26:10 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1jHawy-0001Oo-DD
	for pgsql-admin@arkaria.postgresql.org; Thu, 26 Mar 2020 22:26:08 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <rui@crazybean.net>)
	id 1jHawx-0001OY-Vv
	for pgsql-admin@lists.postgresql.org; Thu, 26 Mar 2020 22:26:08 +0000
Received: from impout002aa.msg.chrl.nc.charter.net ([47.43.20.26]
 helo=impout002.msg.chrl.nc.charter.net)
	by makus.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <rui@crazybean.net>)
	id 1jHawu-000731-1j
	for pgsql-admin@lists.postgresql.org; Thu, 26 Mar 2020 22:26:06 +0000
Received: from cerberus.crazybean.net ([24.151.69.73])
	by cmsmtp with ESMTP
	id HawsjtWRvh15uHawsjIuKR; Thu, 26 Mar 2020 22:26:03 +0000
Authentication-Results: crazybean.net; none
X-Authority-Analysis: v=2.3 cv=etJrMbhX c=1 sm=1 tr=0
 a=YXTuGtgWN9YX+aIhe8tYuA==:117 a=YXTuGtgWN9YX+aIhe8tYuA==:17
 a=jpOVt7BSZ2e4Z31A5e1TngXxSK0=:19 a=xqWC_Br6kY4A:10 a=SS2py6AdgQ4A:10
 a=0jFAOcL7AAAA:8 a=s_o6jwPlYdBnZrq7LcAA:9 a=a3CfjclJpBH4PFye:21
 a=SHBq-lYX-IRY-Y7T:21 a=QEXdDO2ut3YA:10 a=kgNHDavT5etJH-AvzCEA:9
 a=-y26KxOP0e_2T2sH:21 a=n8xWL8SO072AI6AY:21 a=t84xDH0TyeytR_dj:21
 a=_W_S_7VecoQA:10 a=K48GPUIhKHzPTnLCTHz5:22
Received: from [IPv6:2001:470:e117:4:154:e85e:7412:3762] (<unknown>
 [2001:470:e117:4:154:e85e:7412:3762])
	by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id f3a813ce
 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO);
	Thu, 26 Mar 2020 18:26:02 -0400 (EDT)
From: Rui DeSousa <rui@crazybean.net>
Message-Id: <8D9390C5-E192-4232-B18D-BDF9F658DFEA@crazybean.net>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_03F4922E-FB49-4082-9C1E-97DEACA3B1CB"
Mime-Version: 1.0 (Mac OS X Mail 13.0 \(3608.60.0.2.5\))
Subject: Re: Vacuum Issues
Date: Thu, 26 Mar 2020 18:26:01 -0400
In-Reply-To: <17118ca7beb.bd8e3204216663.1608462906593065114@realtyserver.com>
Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
To: Darron Harrison <darron@realtyserver.com>
References: <171180a0730.f301716f192269.5929166075824764633@realtyserver.com>
 <8612E7A0-95FE-45DD-92BF-99FF68757BCF@crazybean.net>
 <1711838a1a0.b8776dce198656.4553889266632620330@realtyserver.com>
 <EB0BC3D5-EE49-4863-963F-932A5D148A2B@crazybean.net>
 <171187f1f11.e7c537f6208044.4379759561125142996@realtyserver.com>
 <30BC197F-1AC7-438E-BF72-AB29B5194C69@crazybean.net>
 <17118ca7beb.bd8e3204216663.1608462906593065114@realtyserver.com>
X-Mailer: Apple Mail (2.3608.60.0.2.5)
X-CMAE-Envelope: 
 MS4wfJmdRBatnl+rH5deDS5tdDKDnKYjGwNDxc2yTx/lxeAkD2lwjqRq3NT17FTI2JlIn0GgPEoArAHWam4sfAGONgdvqTAk3/1U5LgJc16BqLEyOHwOJLUq
 ijT5HQZSksCNQ/aI89eKiJocWd+p5JTB5FwdV96F8zcuJhkgzcK612K1LRBWrMU9jsFhYC3TYsUglvR6WwWiekyIfJVpd674Y/6bP7ew3wj8wgJ5c5uDBBpv
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Precedence: bulk


--Apple-Mail=_03F4922E-FB49-4082-9C1E-97DEACA3B1CB
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8



> On Mar 26, 2020, at 5:40 PM, Darron Harrison <darron@realtyserver.com> =
wrote:
>=20
> There is no current lag on the replicas. Replication does traverse a =
firewall, but we have made no changes recently.
>=20
> I will say that one of the hot standbys was only recently attached, =
and it seems like the issues started when we began sending some longer =
running queries it's way. We have since placed those queries back on the =
master, but the vacuum issues remain.
>=20
> One side effect of whatever is happening, is that nightly backups are =
taking twice as long as normal.

It could sill be a bad/sale replication session.  If the firewall drops =
the replication stream and does not send a reset packets (bad pratice) =
then the replication session might still be lingering on the Postgres =
server and holding on to a very old xmin.  Do you know if you have =
TCP/IP Keepalive enabled? I don=E2=80=99t think in 9.2 replication =
sessions are listed in pg_stat_activity; thus, you=E2=80=99ll have to =
look for TCP/IP connections to the replics that should not exist.=20

Check all the upstream servers for stale TCP/IP replication connections; =
using netstat.  I would also look at the system=E2=80=99s process list =
for walsender processes to see if there more more than there should be; =
i.e. two of them for single replica.

If you do find one; the best way to terminate it is to drop the TCP/IP =
connection.  i.e. In FreeBSD it would be the command =E2=80=9Ctcpdrop=E2=80=
=9D; for Linux there are few utilities that do same -- I just don=E2=80=99=
t recall the name of them.




--Apple-Mail=_03F4922E-FB49-4082-9C1E-97DEACA3B1CB
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><head><meta http-equiv=3D"Content-Type" content=3D"text/html; =
charset=3Dutf-8"></head><body style=3D"word-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;" class=3D""><br =
class=3D""><div><br class=3D""><blockquote type=3D"cite" class=3D""><div =
class=3D"">On Mar 26, 2020, at 5:40 PM, Darron Harrison &lt;<a =
href=3D"mailto:darron@realtyserver.com" =
class=3D"">darron@realtyserver.com</a>&gt; wrote:</div><br =
class=3D"Apple-interchange-newline"><div class=3D""><meta =
content=3D"text/html;charset=3DUTF-8" http-equiv=3D"Content-Type" =
class=3D""><div class=3D""><div style=3D"font-family: Verdana, Arial, =
Helvetica, sans-serif; font-size: 10pt;" class=3D""><div class=3D"">There =
is no current lag on the replicas. Replication does traverse a firewall, =
but we have made no changes recently.<br class=3D""></div><div =
class=3D""><br class=3D""></div><div class=3D"">I will say that one of =
the hot standbys was only recently attached, and it seems like the =
issues started when we began sending some longer running queries it's =
way. We have since placed those queries back on the master, but the =
vacuum issues remain.<br class=3D""></div><div class=3D""><br =
class=3D""></div><div class=3D"">One side effect of whatever is =
happening, is that nightly backups are taking twice as long as =
normal.<br class=3D""></div></div></div></div></blockquote><br =
class=3D""></div><div>It could sill be a bad/sale replication session. =
&nbsp;If the firewall drops the replication stream and does not send a =
reset packets (bad pratice) then the replication session might still be =
lingering on the Postgres server and holding on to a very old xmin. =
&nbsp;Do you know if you have TCP/IP Keepalive enabled? I don=E2=80=99t =
think in 9.2 replication sessions are listed in pg_stat_activity; thus, =
you=E2=80=99ll have to look for TCP/IP connections to the replics that =
should not exist.&nbsp;</div><div><br class=3D""></div><div>Check all =
the upstream servers for stale TCP/IP replication connections; using =
netstat. &nbsp;I would also look at the system=E2=80=99s process list =
for walsender processes to see if there more more than there should be; =
i.e. two of them for single replica.</div><div><br =
class=3D""></div><div>If you do find one; the best way to terminate it =
is to drop the TCP/IP connection. &nbsp;i.e. In FreeBSD it would be the =
command =E2=80=9Ctcpdrop=E2=80=9D; for Linux there are few utilities =
that do same -- I just don=E2=80=99t recall the name of =
them.</div><div><br class=3D""></div><div><br class=3D""></div><div><br =
class=3D""></div></body></html>=

--Apple-Mail=_03F4922E-FB49-4082-9C1E-97DEACA3B1CB--