PostgreSQL数据库的年轮值

模拟数据库事务号用尽，可参考 https://www.modb.pro/db/31736

数据库的年轮值

age(datfrozenxid) 的值越高，说明这个数据库长时间未进行freeze回收事务操作。

select datname,datfrozenxid,age(datfrozenxid) 
from pg_database 
order by age(datfrozenxid) desc;

--
datfrozenxid：
在此之前的所有事务ID在数据库中已经被替换为一个永久的（“冻结的”) 事务ID，
它是此数据库中所有表的pg_class.relfrozenxid值的最小值。

数据库下表的年轮值

age(relfrozenxid) 的值越高，说明这个表长时间未进行freeze回收事务操作。

select (u.schemaname||'.'||u.relname) as tablename,relfrozenxid,age(relfrozenxid) 
from pg_class c,pg_stat_user_tables u 
where c.oid=u.relid 
order by age(relfrozenxid) desc;

--
relfrozenxid：
在此之前的所有事务ID在表中已经被替换为一个永久的（“冻结的”) 事务ID

数据库对象的年轮值

--所有对象的年轮值
select nspname,relname,relkind,relfrozenxid,age(relfrozenxid) 
from pg_class,pg_namespace  
where pg_namespace.oid=relnamespace and relfrozenxid !=0 and nspname not in('pg_catalog','information_schema') order by 5 desc;

--展示剩余age
select current_database(),rolname,nspname,relkind,relname,age(relfrozenxid),2^31-age(relfrozenxid) age_remain 
from pg_authid t1 
join pg_class t2 on t1.oid=t2.relowner 
join pg_namespace t3 on t2.relnamespace=t3.oid 
where t2.relkind in ('t','r','p') 
order by age(relfrozenxid) desc limit 6;

--直接生成vacuum 语句
select ('vacuum freeze '||((case when b.inhparent is not null then b.inhparent else a.oid end)::regclass)::varchar||';') table_name,  max(age) age 
from (select c.oid,greatest(age(c.relfrozenxid),age(t.relfrozenxid)) age 
      FROM pg_class c 
      LEFT JOIN pg_class t ON c.reltoastrelid = t.oid 
      WHERE  c.relkind IN ('r', 'm','p') and c.oid not in (select inhparent from pg_inherits) and  c.reltuples!=0) a 
left join pg_inherits b on a.oid=b.inhrelid  
group by (case when b.inhparent is not null then b.inhparent else a.oid end)
order by max(age) desc limit 10;

表中某一行数据的年轮值

select xmin,age(xmin),* from table_name order by age(xmin) desc;

freeze 失败的报错

uncommitted xmin %u from before xid cutoff %u needs to be frozen

这个报错可以通过手工执行vacuum freeze复现，原因可能是数据损坏，也可能是有数据库bug

处理
方法1:建议用最新的数据库版本，
方法2:将有问题的数据库pg_dump出来重建新的数据库，并将原数据库删除
方法3:找到有问题的数据，并将数据重写
方法4:用德哥的方法跳过 https://www.modb.pro/db/91545