关于数据库: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/OpenSource…