Re: Vacuum Issues

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(at)crazybean(dot)net> wrote ----

On Mar 26, 2020, at 3:01 PM, Darron Harrison <mailto:darron(at)realtyserver(dot)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

;