PostgreSQL维护年龄的处理
1、错误信息
WARNING: database “postgres” must be vacuumed within 3330803 transactions
最常见的方法是通过此消息,警告您正在进行事务处理:
WARNING: database “postgres” must be vacuumed within XXX transactions. HINT: To avoid a database shutdown, execute a database-wide VACUUM in that database. You might also need to commit or roll back old prepared transactions.
或者更糟糕的是,以下消息表明您已经完成了 transaction wraparound,并且PostgreSQL正在尝试防止数据丢失:
ERROR: database is not accepting commands to avoid wraparound data loss in database “template0” HINT: Stop the postmaster and use a standalone backend to vacuum that database. You might also need to commit or roll back old prepared transactions.
2、处理方法:
在适当的数据库上执行建议的VACUUM; 这里我们使用默认数据库postgres
,因此用适当的数据库名称替换该名称。
vacuumdb postgres
如果您收到上面提到的错误,并且数据库不再接受 命令
以下是在单用户模式下运行真空的步骤; 请注意,--single
必须是命令行上的第一个命令:
–查询库xid
SELECT datname, age(datfrozenxid) FROM pg_database;
或者:select max(age(datfrozenxid)) from pg_database;
— 来查询每个表的xid使用程度
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;
这查询按照最老的XID排序,查看大于1G而且是排名前20的表。
SELECT relname, age(relfrozenxid) as xid_age, pg_size_pretty(pg_table_size(oid)) as table_size FROM pg_class WHERE relkind = 'r' and pg_table_size(oid) > 1073741824 ORDER BY age(relfrozenxid) DESC LIMIT 20;
建议使用vacuum freeze [ table_name ] 来对指定的表进行xid 冻结清理。
执行vacuum freeze pg_database 。其他table 根据需要freeze