Recently, I had an issue with one of my database which got XID wraparound issue. So, I have started with single-user
mode and ran vacuum
. Everything was fine. But, when I checked datfrozenxid
its ~1.5billon where as my txid_current
is ~70billon. I feel like, its huge different and something is wrong.
The below query, I have used to see the datfrozenxid
from pg_database
select datname, datfrozenxid, age(datfrozenxid) from pg_database order by datname;
I have read a XID wraparound scenario from http://www.rummandba.com/2014/02/understanding-xid-wrap-around.html
It make sense to me as per the Example given in above link. But, I have calculated xidWrapLimit
, xidStopLimit
, xidWarnLimit
, xidVacLimit
with my statistics.
xidWrapLimit = oldest_datfrozenxid + (MaxTransactionId >> 1);
xidStopLimit = xidWrapLimit - 1000000;
xidWarnLimit = xidStopLimit - 10000000;
(I haven't given all the values here since I am looking only for xidWarnLimit)
So, the xidWarnLimit
is 3.747 billon where my current txid_current
>70 billion. And strange thing is txid_current
is greater than xidWrapLimit
which is 3.758 billon.
How to find out, when it will raise the Warning about wraparound warn limit.
(using Pg9.5)