From: Darron Harrison <darron@realtyserver.com>
To: Rui DeSousa <rui@crazybean.net>
Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Vacuum Issues
Date: Thu, 26 Mar 2020 13:18:36 -0700
Message-ID: <171187f1f11.e7c537f6208044.4379759561125142996@realtyserver.com> (raw)
In-Reply-To: <EB0BC3D5-EE49-4863-963F-932A5D148A2B@crazybean.net>
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>
I am not seeing anything suspicious on the master or slaves.
master:
pid | usename | client_addr | client_port | state | state_time | xact_time
-------+------------------+---------------+-------------+--------+------------------+------------------
17627 | xposure | 64.40.104.231 | 13231 | active | 00:00:02.11561 | 00:00:02.116436
31197 | vancouver_island | 64.40.104.242 | 8174 | active | 00:00:00.067581 | 00:00:00.068289
17616 | xposure | 64.40.104.231 | 13216 | active | 00:00:00.015216 | 00:00:00.015392
17631 | xposure | 64.40.104.231 | 13230 | active | 00:00:00.007903 | 00:00:00.010017
4589 | postgres | | -1 | active | -00:00:00.000002 | 00:00:00
31578 | south_okanagan | 64.40.104.230 | 32926 | active | -00:00:00.00138 | -00:00:00.001307
Slave:
pid | usename | client_addr | client_port | state | state_time | xact_time
-------+------------------+---------------+-------------+--------+------------------+-----------------
187058 | postgres | | -1 | active | -00:00:00.000004 | 00:00:00
Slave:
pid | usename | client_addr | client_port | state | state_time | xact_time
-------+------------------+---------------+-------------+--------+------------------+-----------------
19411 | vancouver_island | 64.40.104.242 | 30274 | active | 00:00:00.08489 | 00:00:00.085521
18180 | pgsql | | -1 | active | -00:00:00.000003 | 00:00:00
Slave:
pid | usename | client_addr | client_port | state | state_time | xact_time
-------+----------+-------------+-------------+--------+------------------+-----------
29524 | postgres | | -1 | active | -00:00:00.000004 | 00:00:00
Darron
---- On Thu, 26 Mar 2020 12:22:57 -0700 Rui DeSousa <rui@crazybean.net> wrote ----
On Mar 26, 2020, at 3:01 PM, Darron Harrison <mailto: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’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 “idle” sessions — i.e. “idle in tran” 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 != 'idle'
order by xact_time desc
;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: darron@realtyserver.com, rui@crazybean.net
Subject: Re: Vacuum Issues
In-Reply-To: <171187f1f11.e7c537f6208044.4379759561125142996@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>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox