PostgreSQL 查找需要收集的vacuum 表信息




  • 前言

    通常,在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

    Speak Your Mind

    *