pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

;

view thread (16+ messages)  latest in thread

Message-ID: <171187f1f11.e7c537f6208044.4379759561125142996@realtyserver.com>
Permalink:  ../171187f1f11.e7c537f6208044.4379759561125142996@realtyserver.com/
Also on:    postgresql.org/message-id/171187f1f11.e7c537f6208044.4379759561125142996@realtyserver.com

 · 

reply

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