postgresql大表分批次删除

[root@localhost ~]# cat  /root/delete_big_table.sh 
#!/bin/bash
#$1对应表名，$2对应主键列，$3对应一次删除多少行


i=`psql -h 127.0.0.1 -U postgres -d tenant_1011046 -c "select ceil(count(1)/${3}::float) from ${1} where  platcreatetime < '2023-04-30 23:59:59'"`
n=`echo ${i} |awk -F ' ' '{print $3}'`
c=1
#echo ${n}
#echo `echo ${i} |awk -F ' ' '{print "一共需要执行",$3,"次"}'`
while [ $c -le $n ]
do
        echo `date`
        echo '一共需要执行'${n}'次,当前执行第'${c}'次'
        echo '一共需要执行'${n}'次,当前执行第'${c}'次' > /home/jindu.txt
        returns=`psql -h 127.0.0.1 -U postgres -d tenant_1011046 -c "delete from ${1} where ${2} =any (select ${2} from ${1} where  platcreatetime < '2023-04-30 23:59:59' limit ${3} );"`
        echo `date`
        c=$[ $c+1 ]
done

[root@localhost ~]# sh /root/delete_big_table.sh gt_interface_logs id 4000000
2023年 07月 04日 星期二 22:44:07 CST
一共需要执行8次,当前执行第1次

 

vacuumn full table_name