磐维数据库自动添加/删除 分区脚本
脚本功能
磐维数据库自动按天添加/删除 分区
脚本使用示例
一、自动按天添加分区
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;
/
