解决PG索引膨胀（reindex）

﹏ヽ暗。殇╰゛Y 2023-06-30 05:25 38阅读 0赞

\#!/bin/bash  
\#当表的数据小于20万则对表做 reindex table xxx操作  
\#否则先删除索引（唯一约束索引，及主键除则 reindx index xxx），然后再创建该索引  
\#zhaowenzhong 2019-04-02

date=\`date +"%Y%m%d%H%M"\`  
v\_constant=200000  
v\_hour=0  
v\_param=$\#

if \[ $v\_param -ge 3 \];then  
        echo "Invalid argument!! E.g sh pg-reindex-all.sh vlnx191001.xxx.cn OR sh pg-reindex-all.sh vlnx191001.xxx.cn  \\" db1,db2,db3,...\\""  
        exit 1  
fi

pghosts=$1

if \[ -n "$pghosts" \];then

hostname=$\{pghosts%%.\*\}  
        filename=$hostname'-reindex-'$date

for pghostname in $pghosts  
        do  
                if \[ $v\_param -eq 1 \];then  
                        dblist=$(psql  -h $pghostname -U zhaowz -d postgres -c "select datname from pg\_database where datname not in ('postgres','template0','template1','repmgr')" -A -t)  
                elif \[ $v\_param -eq 2 \];then  
                        dblist=$(psql  -h $pghostname -U zhaowz -d postgres -c "select datname from pg\_database where datname=any('\{$2\}')" -A -t)  
                fi

if \[ -z "$dblist" \];then  
                        echo "Invalid dbname OR Invalid hostname !!!" >> /home/zhaowz/logs/$filename.log  
                        exit 1  
                fi

for db in $dblist  
                do  
                        v\_hour=$(date "+%k")  
                        if \[\[ $\{v\_hour\} -ge 1 && $\{v\_hour\} -lt 7 \]\]; then

echo "$db : \`date +"%Y%m%d%H%M%S"\` Begin ">>/home/zhaowz/logs/$filename.log  
                                tables=$(psql  -h $pghostname -U zhaowz -d $db -c "select tablename from pg\_tables where schemaname='public'" -A -t)

for table in $tables  
                        do

v\_count=0  
                                v\_count=$(psql  -h $pghostname -U zhaowz -d $db -c "select reltuples from v\_get\_eachtable\_size where relname='$table'" -A -t)

v\_hour=$(date "+%k")

if \[\[ $\{v\_hour\} -ge 1 && $\{v\_hour\} -lt 7 \]\]; then

if \[ $v\_count -gt $v\_constant \]; then  
                                               \#过滤出非 主键索引和唯一索引  
                                                indexes=$(psql -h $pghostname -U zhaowz -d $db -c "select indexname from v\_get\_indexes\_stats where relname='$table' and ((\\"Unique\\" !='Y' and \\"Primary\\" !='Y') or (\\"Unique\\" !='Y' and \\"Primary\\" ='N'))" -A -t)

for idx\_line in $indexes  
                                                do

v\_create\_index=''  
                                                        v\_create\_index=$(psql -h $pghostname -U zhaowz -d $db -c "select 'create index  if not exists '|| indexname || ' on '|| relname || ' USING '|| pg\_get\_indexdef || ';' from v\_get\_indexes\_stats where relname='$table' and indexname='$idx\_line'" -A -t)

\#由于程序使用的是长连接模式，长连接模式会产生很多  idle in trans 状态，该状态会导致 reindex index 阻塞。故在reindex时 将该状态的连接 pg\_terminate\_backend   
                                                        v\_idel\_in\_tran=''  
                                                        v\_idel\_in\_tran=$(psql -h $pghostname -U zhaowz -d $db -c "select 'pg\_terminate\_backend('||pid||');' from pg\_stat\_activity where state='idle in transaction'" -A -t)

if \[ -n "$v\_idel\_in\_tran" \]; then  
                                                                for v\_tran in $v\_idel\_in\_tran  
                                                                do  
                                                                        psql -h $pghostname -U zhaowz -d postgres -c "select $v\_tran"  
                                                                done  
                                                        fi

echo "$db.$table.$idx\_line :\`date +"%Y%m%d%H%M%S"\` Begin drop  ">>/home/zhaowz/logs/$filename.log  
                                                        psql -h $pghostname -U zhaowz -d $db -c "drop index $idx\_line ;"  
                                                        echo "$db.$table.$idx\_line :\`date +"%Y%m%d%H%M%S"\` has finished drop ">>/home/zhaowz/logs/$filename.log

echo "$db.$table.$idx\_line :\`date +"%Y%m%d%H%M%S"\` Begin create  ">>/home/zhaowz/logs/$filename.log  
                                                        psql -h $pghostname -U zhaowz -d $db -c " $v\_create\_index">>/home/zhaowz/logs/$filename.log  
                                                        echo "$db.$table.$idx\_line :\`date +"%Y%m%d%H%M%S"\` has finished create ">>/home/zhaowz/logs/$filename.log  
                                                        echo $v\_create\_index >> /home/zhaowz/logs/$filename.log

done  
                                                \#过滤出 主键索引和唯一索引      
                                                indexes\_pkey\_uq=$(psql -h $pghostname -U zhaowz -d $db -c "select indexname from v\_get\_indexes\_stats where relname='$table' and ((\\"Unique\\" ='Y' and \\"Primary\\" ='Y') or (\\"Unique\\" ='Y' and \\"Primary\\" ='N'))" -A -t)

for idx\_pkey\_uq in $indexes\_pkey\_uq  
                                                do

echo "$db.$table.$indexes\_pkey\_uq :\`date +"%Y%m%d%H%M%S"\` Begin reindex  " >> /home/zhaowz/logs/$filename.log  
                                                        psql -h $pghostname -U zhaowz -d $db -c "reindex index $indexes\_pkey\_uq ;"  
                                                        echo "$db.$table.$indexes\_pkey\_uq :\`date +"%Y%m%d%H%M%S"\` has finished reindex " >> /home/zhaowz/logs/$filename.log  
                                                done

elif \[\[ $v\_count -gt 0 && $v\_count -lt $v\_constant \]\]; then  
                                                v\_hour=$(date "+%k")  
                                                if \[\[ $\{v\_hour\} -ge 1 && $\{v\_hour\} -lt 7 \]\]; then

echo "$db.$table ：\`date +"%Y%m%d%H%M%S"\` Begin reindex ">>/home/zhaowz/logs/$filename.log  
                                                        psql  -h $pghostname -U zhaowz -d $db -c "reindex table $table"  
                                                        echo "$db.$table ：\`date +"%Y%m%d%H%M%S"\` has finished reindex ">>/home/zhaowz/logs/$filename.log  
                                                fi  
                                        fi

echo "$db.$table ：\`date +"%Y%m%d%H%M%S"\` has Begin vacuum analyze  ">>/home/zhaowz/logs/$filename.log  
                                psql  -h $pghostname -U zhaowz -d $db -c "vacuum analyze $table"  
                                echo "$db.$table ：\`date +"%Y%m%d%H%M%S"\` has finished vacuum analyze  ">>/home/zhaowz/logs/$filename.log

fi  
                        done

fi  
                done  
        done  
fi