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;