huor
发布于

WARNING: database "xxx" must be vacuumed within yyy transactions

1)报错信息:

WARNING:  database "xxx" must be vacuumed within yyy transactions HINT:  To avoid a database shutdown, execute a full-database VACUUM in "xxx"

2)处理步骤如下:
Step 1: 备份 master data directory
Step 2: hawq stop cluster -a -M immediate
Step 3: 清理 pg_temp_xxx

cat /dev/null > execute_drop_on_all.sh | psql -d template1 -Atc "select datname from pg_database where datname != 'template0'" | while read a; do echo "Checking database ${a}"; psql -Atc "select 'drop schema if exists ' || nspname || ' cascade;' from (select nspname from pg_namespace where nspname like 'pg_temp%' union select nspname from gp_dist_random('pg_namespace') where nspname like 'pg_temp%' except select 'pg_temp_' || sess_id::varchar from pg_stat_activity) as foo" ${a} > drop_temp_schema_$a.ddl ; echo "psql -f drop_temp_schema_$a.ddl -d ${a}" >> execute_drop_on_all.sh ; done

chmod +x execute_drop_on_all.sh
nohup bash execute_drop_on_all.sh >execute_drop_on_all.log 2>&1 &

Step 4: 确定数据库 age

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

Step 5: 普通数据库清理

vacuumdb -F databasename

Step 6. template0 清理

set allow_system_table_mods='dml';
update pg_database set datallowconn='t' where datname='template0';
\c template0
vacuum freeze;
\c postgres
set allow_system_table_mods='dml';
update pg_database set datallowconn='f' where datname='template0';

3)查找 age 比较大的数据库或者表的方法

select datname, datfrozenxid, age(datfrozenxid) from pg_database;
select relname, relfrozenxid, age(relfrozenxid) from pg_class where relkind in ('r', 't', 'o', 'b') and relstorage not in ('x', 'f', 'v') order by age(relfrozenxid) desc;
评论
    test