PostgreSQL如何查找需要收集的vacuum表信息-互联网品牌圈oem365

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

　项目招商找A5 快速获取精准代理名单

这篇文章主要介绍了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$DECLAREv_tablename text;v_dead_cond bigint;v_sql text;cur_tablename REFCURSOR;v_vacuum_record text;BEGINv_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.iniCT_FILE=${TOPDIR}/sql/CREATE_VACCUM_TABLE_RECORD.sqlCT_FUNCTION=${TOPDIR}/sql/CHECK_NEEDS_VACUUM_TABLE_FUNCTION.sqlsource $CONFIGCONNINFO="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_statuscreate_tablecreate_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 10002postgres=# SELECT * FROM tab_vacuum_record ; sqltext -----------------------VACUUM FULL tab_test;(1 row)

该脚本也可以自己根据需要进行修改，详细见github

补充：PostgreSQL中 Vacuum 略谈

VACUUM doc

路由清理

PostgreSQL 需要定期维护清理，一般都是由守护进程自动清理的，我们只是需要参数调优，也可以执行脚本定时去清理回收。

Vacuumming Basics

PG不得不对每张表进行 Vacuum 命令，原因如下：

1、为了回收和再利用通过更新或者删除行所占用的磁盘空间

2、为了更新被PG查询计划所使用的数据分析

3、为了更新只读索引扫描的可见的集合

4、避免由于事务ID或者混合事务ID丢失历史数据

由于这些原因，在进行频繁的 VACUUM 操作时进行规定：