postgresql年龄xid维护防止数据库只读

select * from
(SELECT c.oid::regclass as table_name, greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind IN ('r', 'm') 
order by age desc) tmp
where age>=1002644113

建议使用vacuum freeze [ table_name ] 来对指定的表进行xid 冻结清理。

参考链接：
https://blog.csdn.net/weixin_39803207/article/details/111882812

SELECT datname,max(age(datfrozenxid)) FROM pg_database
GROUP BY datname
//查询可能其他数据库age比较大

update pg_database set datallowconn = TRUE where datname = 'template0';
/opt/pgsql/bin/vacuumdb --freeze template0
update pg_database set datallowconn = FALSE where datname = 'template0';

/opt/pgsql/bin/vacuumdb --freeze template1
/opt/pgsql/bin/vacuumdb --freeze postgres

//查询各数据库xid最大值
SELECT datname,max(age(datfrozenxid)) FROM pg_database
GROUP BY datname

//查询autovacuum正在执行的sql
SELECT
procpid,
start,
now() - start AS lap,
current_query
FROM
(SELECT
backendid,
pg_stat_get_backend_pid(S.backendid) AS procpid,
pg_stat_get_backend_activity_start(S.backendid) AS start,
pg_stat_get_backend_activity(S.backendid) AS current_query
FROM
(SELECT pg_stat_get_backend_idset() AS backendid) AS S
) AS S
WHERE
current_query <> '<IDLE>'
and current_query like '%vacuum%'
ORDER BY
start DESC;

//批量冻结脚本

#!/bin/bash
for i in `/opt/pgsql/bin/psql auditdb auditor -c "SELECT c.oid::regclass as table_name, greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind IN ('r', 'm') order by age desc limit 30" | grep 't_'`
do
  echo $i | grep 't' > /dev/null
  if [ $? -eq 0 ]; then
    echo $i
    fsql="vacuum freeze $i"
    /opt/pgsql/bin/psql auditdb auditor -c "$fsql"
  fi
done