前言
通常，在PostgreSQL中，由于经常需要对表进行 UPDATE 和 DELETE，因此表会产生碎片空间。在 PostgreSQL中，使用VACUUM 仅仅对需要执行 VACUUM 表将已删除的空间标识为未使用，以便以后重用这些空间，但是不能立即将占用的空间返还给操作系统，因此需要使用 VACUUM FULL，才可以释放空间，并立即将空间返还给操作系统。
实现脚本
记录收集表创建
CREATE TABLE IF NOT EXISTS tab_vacuum_record (sqltext text);
收集需要VACUUM 表函数
CREATE OR REPLACE FUNCTION f_vacuum_tables()
RETURNS void AS
$FUNCTION$
DECLARE
v_tablename text;
v_dead_cond bigint;
v_sql text;
cur_tablename REFCURSOR;
v_vacuum_record text;
BEGIN
v_vacuum_record := ‘tab_vacuum_record‘;
OPEN cur_tablename FOR SELECT tablename FROM pg_tables WHERE tablename !~ ‘^pg|^sql‘;
LOOP
FETCH cur_tablename INTO v_tablename;
SELECT n_dead_tup INTO v_dead_cond FROM pg_stat_user_tables WHERE relname = v_tablename;
IF v_dead_cond > 0 THEN
v_sql := ‘INSERT INTO ‘ || v_vacuum_record || ‘ VALUES(‘ || chr(39) ||‘VACUUM FULL ‘ || v_tablename ||‘;‘|| chr(39) ||‘)‘;
EXECUTE v_sql;
END IF;
EXIT WHEN NOT FOUND;
END LOOP;
CLOSE cur_tablename;
END;
$FUNCTION$
LANGUAGE PLPGSQL;
SHELL脚本
#!/bin/bash
#获取环境变量
CURRDIR=$(cd "$(dirname $0)";pwd)
TOPDIR=$(cd $CURRDIR/..;pwd)
CONFIG=$TOPDIR/conf/host.ini
CT_FILE=${TOPDIR}/sql/CREATE_VACCUM_TABLE_RECORD.sql
CT_FUNCTION=${TOPDIR}/sql/CHECK_NEEDS_VACUUM_TABLE_FUNCTION.sql
source $CONFIG
CONNINFO="psql -U $USER -d $DBNAME -h $HOSTADDR -p $PORT"
function check_status()
{
echo "检查数据库服务器状态是否正常 !"
stat=`$CONNINFO -Aqt -c ‘SELECT 1‘`
if [ "${stat}" == "1" ];then
echo "服务器连接正常"
else
echo "服务器连接异常,退出"
exit -1;
fi
}
function create_table()
{
echo "创建收集需要vacuum的表"
$CONNINFO -f $CT_FILE
}
function create_function()
{
echo "创建收集需要 vacuum 表的函数"
$CONNINFO -f $CT_FUNCTION
}
check_status
create_table
create_function
执行方式
postgres=# SELECT * FROM f_vacuum_tables();
f_vacuum_tables
-----------------
(1 row)
--创建测试表
postgres=# CREATE TABLE tab_test(id int);
--插入数据
postgres=# INSERT INTO tab_test SELECT id FROM generate_series(1,100000) as id;
INSERT 0 100000
--删除数据
postgres=# DELETE FROM tab_Test WHERE id <= 10000;
DELETE 10002
postgres=# SELECT * FROM tab_vacuum_record ;
sqltext
-----------------------
VACUUM FULL tab_test;
(1 row)
该脚本也可以自己根据需要进行修改，详细见github
https://github.com/OpenSourceLinux/VACUUM-FOR-PostgreSQL