From: Jerry Sievers <gsievers19@comcast.net>
To: Darron Harrison <darron@realtyserver.com>
Cc: "Rui DeSousa" <rui@crazybean.net>,
  "pgsql-admin" <pgsql-admin@lists.postgresql.org>
Subject: Re: Vacuum Issues
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>
	<8D9390C5-E192-4232-B18D-BDF9F658DFEA@crazybean.net>
	<1711929172a.10513d01b224459.1323447661095709500@realtyserver.com>
Date: Thu, 26 Mar 2020 19:04:49 -0500
In-Reply-To: 
 <1711929172a.10513d01b224459.1323447661095709500@realtyserver.com>
	(Darron Harrison's message of "Thu, 26 Mar 2020 16:24:15 -0700")
Message-ID: <87a742y866.fsf@jsievers.enova.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.1 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

Darron Harrison <darron@realtyserver.com> writes:

> I am currently seeing three WAL sender processes on the master, and
> no stale replication connections on the slaves.=20
>
> To clarify, are you saying there should be two WAL sender processes
> for each slave for a total of six?
>

Silly question...

Have you verified that autovac is actually running?  launcher still
alive, not malconfig'd as to do nothing... etc?

Silly question #2;  supposing autovac is trying to do as intended, have
you inspected the logs to insure that it's not erroring out on one or
more tables, catalogs, indexes... etc?

I've reade most of this thread and not seeing any indication if the
problem is confined to just one object or several.

FWIW


> Darron=20=20
>
>
> ---- On Thu, 26 Mar 2020 15:26:01 -0700 Rui DeSousa
> <rui@crazybean.net> wrote ----
>
>
>=20=20=20=20
>=20=20=20=20
>=20=20=20=20
>         On Mar 26, 2020, at 5:40 PM, Darron Harrison <
>         darron@realtyserver.com> wrote:
>=20=20=20=20=20=20=20=20
>         There is no current lag on the replicas. Replication does
>         traverse a firewall, but we have made no changes recently.
>=20=20=20=20=20=20=20=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=20=20=20=20=20=20=20
>         One side effect of whatever is happening, is that nightly
>         backups are taking twice as long as normal.
>=20=20=20=20
>=20=20=20=20
>     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 repli=
cs
>     that should not exist.=20
>=20=20=20=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.
>=20=20=20=20
>     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=99t recall the name of them.
>=20=20=20=20
>=20=20=20=20
>=20=20=20=20
>
>
>
>
>

--=20
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net