磐维数据库自动添加/删除 分区脚本

目录

• 脚本功能
• 脚本使用示例
  • 一、自动按天添加分区
  • 二、自动按天删除分区

脚本功能

磐维数据库自动按天添加/删除 分区

脚本使用示例

一、自动按天添加分区

1、shell脚本的内容panwei_add_partition.sh

#!/bin/bash
. ~/.bash_profile

for db in "nlkf" "nlkf1" "nlkf2" "nlkf3" "nlkf4" "nlkf5" "nlkf6" "nlkf7" "nlkf8" "nlkf9" "nlkf10" "nlkf11"  
do
echo "db:"$db `date +"%Y%m%d %H:%M:%S"`
gsql -d $db -p 17700 -U dbmt -W Bcv_1308 << EOF
call ap_panwei_add_partition();

EOF
done

2、 创建自动添加分区的存储过程

CREATE OR REPLACE PROCEDURE public.ap_panwei_add_partition() 
AS  DECLARE                                                  
begin
for cc in (select generate_series(to_char(date_trunc('month',current_date) + interval'1 month','yyyymmdd'),
                             to_char(date_trunc('month',current_date) + interval'2 month - 1 day','yyyymmdd'), '1') as day ) loop
                             
execute immediate 'alter table table_1_0 add partition PART_'||cc.day||' values less than (to_date(('''||cc.day||'''::timestamp + ''1 day''),''YYYY-MM-DD HH24:MI:SS''))';
execute immediate 'alter table table_1_1 add partition PART_'||cc.day||' values less than (to_date(('''||cc.day||'''::timestamp + ''1 day''),''YYYY-MM-DD HH24:MI:SS''))';
execute immediate 'alter table table_2_0 add partition PART_'||cc.day||' values less than (to_date(('''||cc.day||'''::timestamp + ''1 day''),''YYYY-MM-DD HH24:MI:SS''))';
execute immediate 'alter table table_2_1 add partition PART_'||cc.day||' values less than (to_date(('''||cc.day||'''::timestamp + ''1 day''),''YYYY-MM-DD HH24:MI:SS''))';

execute immediate 'alter table table_3_0 add partition PART_'||cc.day||' values less than (to_date(('''||cc.day||'''::timestamp + ''1 day''),''YYYY-MM-DD HH24:MI:SS''))';
execute immediate 'alter table table_3_1 add partition PART_'||cc.day||' values less than (to_date(('''||cc.day||'''::timestamp + ''1 day''),''YYYY-MM-DD HH24:MI:SS''))';
execute immediate 'alter table table_4_0 add partition PART_'||cc.day||' values less than (to_date(('''||cc.day||'''::timestamp + ''1 day''),''YYYY-MM-DD HH24:MI:SS''))';
execute immediate 'alter table table_4_1 add partition PART_'||cc.day||' values less than (to_date(('''||cc.day||'''::timestamp + ''1 day''),''YYYY-MM-DD HH24:MI:SS''))';

execute immediate 'alter table table_5_0 add partition PART_'||cc.day||' values less than (to_date(('''||cc.day||'''::timestamp + ''1 day''),''YYYY-MM-DD HH24:MI:SS''))';
execute immediate 'alter table table_5_1 add partition PART_'||cc.day||' values less than (to_date(('''||cc.day||'''::timestamp + ''1 day''),''YYYY-MM-DD HH24:MI:SS''))';

end loop;
end

二、自动按天删除分区

1、shell脚本的内容panwei_drop_partition.sh

#!/bin/bash
. ~/.bash_profile

for db in "nlkf" "nlkf1" "nlkf2" "nlkf3" "nlkf4" "nlkf5" "nlkf6" "nlkf7" "nlkf8" "nlkf9" "nlkf10" "nlkf11" 
do
echo "db:"$db `date +"%Y%m%d %H:%M:%S"`
gsql -d $db -p 17700 -U dbmt-W Bcv_1308 << EOF
call ap_panwei_drop_partition();

EOF
done

2、 创建自动删除分区的存储过程

CREATE OR REPLACE PROCEDURE public.ap_panwei_drop_partition()                                                                                          
 AS  DECLARE                                                                                                                                                
 begin                                                                                                                                                      
 for cc in (select a.relname                                                                                                                                
 from pg_partition a join pg_class b on (a.parentid = b.oid)                                                                                                
 where a.parttype = 'p' and b.relname = 'aopen_api_cdrrsp_0' and substr(a.relname,6) < to_char(date_trunc('day',current_date) - interval'60 day','yyyymmdd')
 order by 1) loop                                                                                                                                           
                                                                                                                                                            
 execute immediate 'alter table AOPEN_API_CDRREQ_0 drop partition '||cc.relname;                                                                            
 execute immediate 'alter table AOPEN_API_CDRREQ_1 drop partition '||cc.relname;                                                                            
 execute immediate 'alter table AOPEN_API_CDRRSP_0 drop partition '||cc.relname;                                                                            
 execute immediate 'alter table AOPEN_API_CDRRSP_1 drop partition '||cc.relname;                                                                            
                                                                                                                                                            
 execute immediate 'alter table AOPEN_API_CDRREQ_NEW_0 drop partition '||cc.relname;                                                                        
 execute immediate 'alter table AOPEN_API_CDRREQ_NEW_1 drop partition '||cc.relname;                                                                        
 execute immediate 'alter table AOPEN_API_CDRRSP_NEW_0 drop partition '||cc.relname;                                                                        
 execute immediate 'alter table AOPEN_API_CDRRSP_NEW_1 drop partition '||cc.relname;                                                                        
                                                                                                                                                            
 execute immediate 'alter table AOPEN_API_CDRTHIRDLOG_0 drop partition '||cc.relname;                                                                       
 execute immediate 'alter table AOPEN_API_CDRTHIRDLOG_1 drop partition '||cc.relname;                                                                       
                                                                                                                                                            
 end loop;                                                                                                                                                  
 end;                                                                                                                                                       
 /