Finding out the aging tables for vacuuming to prevent data loss ontransaction id wraparound - Mailing list pgsql-admin

Hi,

We are running PostgreSQL 9.4.14 and recently we received an alert for transaction id crossing the 1 billion mark. Since we can hit the transaction id wraparound issue for aging tables,
we investigated by running the following queries:

mpsdb=> select * from txid_current();
 txid_current
--------------
   1100826671
(1 row)

mpsdb=> SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC LIMIT 20;
  datname  |    age
-----------+------------
 mpsdb     | 1087909867
 rdsadmin  | 1000562517
 postgres  |  950697143
 template0 |  900709601
 template1 |  900697643
(5 rows)

mpsdb=> SELECT c.relname as table_name, c.relkind as type, age(c.relfrozenxid) as age, c.relfrozenxid FROM pg_class AS c WHERE age(c.relfrozenxid) <> 2147483647 ORDER BY 3 DESC LIMIT 20;
    table_name     | type |    age     | relfrozenxid
-------------------+------+------------+--------------
 pg_toast_22985917 | t    | 1042877986 |     57941433
 pg_toast_22986301 | t    | 1042877877 |     57941542
 pg_toast_23823161 | t    | 1041791836 |     59027583
 pg_toast_23823242 | t    | 1041791776 |     59027643
 pg_toast_23904594 | t    | 1041658236 |     59161183
 pg_toast_23904629 | t    | 1041658221 |     59161198
 pg_toast_23904640 | t    | 1041658208 |     59161211
 pg_toast_23904648 | t    | 1041658200 |     59161219
 pg_toast_23904658 | t    | 1041658191 |     59161228
 pg_toast_23904666 | t    | 1041658183 |     59161236
 pg_toast_23904674 | t    | 1041658175 |     59161244
 pg_toast_23904682 | t    | 1041658169 |     59161250
 pg_toast_23904690 | t    | 1041658161 |     59161258
 pg_toast_23904698 | t    | 1041658156 |     59161263
 pg_toast_23904706 | t    | 1041658149 |     59161270
 pg_toast_23904714 | t    | 1041658129 |     59161290
 pg_toast_23904722 | t    | 1041658096 |     59161323
 pg_toast_23904730 | t    | 1041658090 |     59161329
 pg_toast_23904738 | t    | 1041658085 |     59161334
 pg_toast_23904746 | t    | 1041658077 |     59161342
(20 rows)


mpsdb=> SELECT datname, usename, pid, waiting, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) like '%VACUUM%' ORDER BY xact_start;
datname | usename  |  pid  | waiting |  xact_runtime   |                                 query
---------+----------+-------+---------+-----------------+------------------------------------------------------------------------
 mpsdb   | rdsadmin | 13833 | f       | 00:00:14.161262 | autovacuum: VACUUM pg_toast.pg_toast_242649675 (to prevent wraparound)
 mpsdb   | rdsadmin | 14565 | f       | 00:00:11.735571 | autovacuum: VACUUM pg_toast.pg_toast_242649757 (to prevent wraparound)
 mpsdb   | rdsadmin | 15315 | f       | 00:00:09.303762 | autovacuum: VACUUM pg_toast.pg_toast_242649819 (to prevent wraparound)
 mpsdb   | rdsadmin | 14935 | f       | 00:00:06.893078 | autovacuum: VACUUM pg_toast.pg_toast_243226657 (to prevent wraparound)
 mpsdb   | rdsadmin | 15851 | f       | 00:00:04.322474 | autovacuum: VACUUM pg_toast.pg_toast_243227582 (to prevent wraparound)
 mpsdb   | rdsadmin | 15615 | f       | 00:00:01.768495 | autovacuum: VACUUM pg_toast.pg_toast_243226332 (to prevent wraparound)

It is clear that we need to vacuum the most aged pg_toast tables which are not yet picked up by the autovacuum process.

The question which is bothering us though is:
As per the queries we ran the most aged transaction and table is 1042877986 (pg_toast_22985917). And as per PostgreSQL pg_database documentation the most aged table/transaction in pg_class determines age of the database (via the datfrozenxid value). If that is the case we would have expected the age of mpsdb database to be 1042877986 (or in this range) but what we see as age is 1087909867.

Does this mean there are other aging tables we are not aware of? If yes, how do we get to those tables since we have already queried pg_class for the most aged table?

Request any help/guidance we can get regarding this.

Thanks and Regards,
Subu