1 基础知识
重点: 如果您的数据库运行了很久,并且从来没有打开过autovacuum,那么请在打开autovacuum之前全库手动运行vacuum analyze(可能要非常久的时间)
完全禁用autovacuum,请不要这样做，除非你真的知道你在做什么,并且需要定期清理脚本.否则当问题发生时你将不得不处理花费大量的时间处理,甚至可能需要停库、停机
1.1 dead tuples
tuple:元组,也就是一行数据
首先,简要解释什么是"死元组"和"膨胀". 当您在PostgreSQL中执行DELETE时,行不会立即从数据文件中删除.而是仅通过在页头中设置xmax字段将其标记为已删除.同样对于UPDATE,它可能在PostgreSQL中被视为DELETE+INSERT. 这是PostgreSQL MVCC背后的基本思想之一,因为它允许更大并发,在不同的进程之间最小的锁定.这个MVCC实现的缺点是留下了已删除的元组,即使在所有可能看到这些版本的事务完成之后也是如此.
如果没有清理,那些"死元组"(对于任何事务实际上是不可见的)将永远留在数据文件中.
对于DELETE和UPDATE比较多的的表,死元组可能占据很多磁盘空间.
同时,死元组也将从索引中引用,进一步增加了浪费的磁盘空间量.
这就是我们在PostgreSQL中称之为“膨胀”的东西，同时因为查询也会变慢。
[root@iZ2zeijrvu2s38mfzvzrc3Z ~]# cat /home/pg_freeze.sh #!/bin/bash # # cript: pg_freeze.sh # Date:2020-04-09 # # ###################################################################################################################### # init message ###################################################################################################################### # list databases # 日期 #backup_date=`date "+%Y-%m-%d"` # 备份所有业务库 数据库用户根据实际用户进行替换； for x in $(psql -U postgres -h 127.0.0.1 -c "select datname from pg_database where datname not in (‘template0‘,‘template1‘); " -A -t) do ##避免错误可直接写死路径 ` psql -U postgres -h 127.0.0.1 -d ${x} -c " VACUUM FREEZE;"` echo "freeze ${x} success!!" done [root@iZ2zeijrvu2s38mfzvzrc3Z ~]#
6. pg_repack
[root@iZ2zeijrvu2s38mfzvzrc3Z ~]# cat /home/pg_repack.sh #!/bin/bash # # Script: pg_repack.sh # Date:2020-07-21 # # ###################################################################################################################### # init message ###################################################################################################################### pg_repack_log=/home/pg_repack_log # 备份日期 backup_date=`date "+%Y-%m-%d"` log_file=pg_repack_${backup_date}.txt echo `date` >${pg_repack_log}/${log_file} # 核心表清单； for x in $(psql -U postgres -h 127.0.0.1 -d tenant_1008446 -c "select relname from pg_class where relname in ( ‘kx_kq_store‘, ‘kx_kq_storeinandout‘, ‘sfa_t_tbascostexec‘, ‘ka_kq_channelcustomers‘, ‘dms_t_contract‘, ‘sfa_t_tbasstorephoto‘, ‘tn_channelscore‘, ‘sfa_t_tbasstoreperformance‘, ‘kx_gzq_content‘, ‘kx_order‘, ‘kx_visit_customerstatus‘, ‘kx_visit_actual‘ ); " -A -t) do ##避免错误可直接写死路径 ##echo `date` >> ${pg_repack_log}/${log_file} exec=` /usr/pgsql-10/bin/pg_repack -Upostgres -h127.0.0.1 -d tenant_test -t ${x}` echo "pg_repack ${x} success!!" >> ${pg_repack_log}/${log_file} echo `date` >> ${pg_repack_log}/${log_file} done [root@iZ2zeijrvu2s38mfzvzrc3Z ~]#