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 ) id 1jHY5p-0006t9-4Y for pgsql-admin@arkaria.postgresql.org; Thu, 26 Mar 2020 19:23:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1jHY5n-00056M-U5 for pgsql-admin@arkaria.postgresql.org; Thu, 26 Mar 2020 19:23:03 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1jHY5n-00056F-L3 for pgsql-admin@lists.postgresql.org; Thu, 26 Mar 2020 19:23:03 +0000 Received: from impout002aa.msg.chrl.nc.charter.net ([47.43.20.26] helo=impout002.msg.chrl.nc.charter.net) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jHY5l-0002HZ-5m for pgsql-admin@lists.postgresql.org; Thu, 26 Mar 2020 19:23:03 +0000 Received: from cerberus.crazybean.net ([24.151.69.73]) by cmsmtp with ESMTP id HY5ijsbXDh15uHY5ijIUGC; Thu, 26 Mar 2020 19:22:59 +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=_6EuNyhftJQdrMHhr3UA:9 a=QEXdDO2ut3YA:10 a=ADJBxRqJC7zR3NZD6EsA:9 a=iy1NVfWlbwdOvGdZ:21 a=_W_S_7VecoQA:10 a=K48GPUIhKHzPTnLCTHz5:22 Received: from [IPv6:2001:470:e117:4:154:e85e:7412:3762] ( [2001:470:e117:4:154:e85e:7412:3762]) by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id 7860f993 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO); Thu, 26 Mar 2020 15:22:58 -0400 (EDT) From: Rui DeSousa Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_7B0639F3-5DBD-4F38-AE98-F4A871FFD49E" Mime-Version: 1.0 (Mac OS X Mail 13.0 \(3608.60.0.2.5\)) Subject: Re: Vacuum Issues Date: Thu, 26 Mar 2020 15:22:57 -0400 In-Reply-To: <1711838a1a0.b8776dce198656.4553889266632620330@realtyserver.com> Cc: pgsql-admin To: Darron Harrison References: <171180a0730.f301716f192269.5929166075824764633@realtyserver.com> <8612E7A0-95FE-45DD-92BF-99FF68757BCF@crazybean.net> <1711838a1a0.b8776dce198656.4553889266632620330@realtyserver.com> X-Mailer: Apple Mail (2.3608.60.0.2.5) X-CMAE-Envelope: MS4wfFchDHMZHR35ao3YtEZpSZGJeA28YOQpQcc7ZF8VlWFlwyxGiU24EaTCQMm7wSRnhEyfXDzkJNC+/TIWN0L83AkxfvArwqtaaT0FuonDG9i+v5z1VPhq VBYh7BF8EwvdW0HC+fdhbF3k+EeB6acJHkGbgFj8AZuBcvR6zuqPY2O/q8XMCL+h+wxthS0cUodXRKVOvfsSNDsMNdRDY9Idr7rTsZIXGfKl3gQ3cPiAvMty List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_7B0639F3-5DBD-4F38-AE98-F4A871FFD49E Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Mar 26, 2020, at 3:01 PM, Darron Harrison = wrote: >=20 > Unable to run the first query and third query. I believe it's a = version issue, as we are running 9.2.4. >=20 Looks like backend_xmin was added in 9.4; shoot Replication slots was added in 9.4, too; no slots to worry about. Since you don=E2=80=99t have access to backend_xmin; you have check the = master and all the replicas as you have feedback enabled. =20 I would look at all non =E2=80=9Cidle=E2=80=9D sessions =E2=80=94 i.e. = =E2=80=9Cidle in tran=E2=80=9D could be holding on to an old xmin.=20 select pid , usename , client_addr , client_port , state , age(now(), state_change) as state_time , age(now(), xact_start) as xact_time from pg_stat_activity where state !=3D 'idle' order by xact_time desc ;= --Apple-Mail=_7B0639F3-5DBD-4F38-AE98-F4A871FFD49E Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On Mar 26, 2020, at 3:01 PM, Darron Harrison <darron@realtyserver.com> wrote:

Unable= to run the first query and third query. I believe it's a version issue, = as we are running 9.2.4.

Looks like backend_xmin was added in 9.4; = shoot

Replication slots was added in 9.4, too; no slots to worry = about.

Since you don=E2=80=99= t have access to backend_xmin; you have check the master and all = the replicas as you have feedback enabled.

I would look at all non =E2=80=9Cidle=E2=80=9D = sessions =E2=80=94 i.e. =E2=80=9Cidle in tran=E2=80=9D could be = holding on to an old xmin.

select = pid

, usename

, = client_addr

, client_port

, = state

, age(now(), state_change) as = state_time

, age(now(), xact_start) as = xact_time

from pg_stat_activity

where state !=3D = 'idle'

order by xact_time = desc

;

= --Apple-Mail=_7B0639F3-5DBD-4F38-AE98-F4A871FFD49E--