Postgres数据库自动化分区

一.创建自动化分区配置表并插入数据

-- Table: managerdb.par_info-- DROP TABLE IF EXISTS managerdb.par_info;CREATE TABLE IF NOT EXISTS managerdb.par_info
(table_schema character varying(255) COLLATE pg_catalog."default" NOT NULL,table_name character varying(255) COLLATE pg_catalog."default" NOT NULL,par_column character varying(255) COLLATE pg_catalog."default",keep_data_days bigint,step_length bigint,create_next_intervals bigint,update_time timestamp with time zone NOT NULL DEFAULT now(),min_partition_name character varying(300) COLLATE pg_catalog."default",max_partition_name character varying(300) COLLATE pg_catalog."default",CONSTRAINT par_info_pkey PRIMARY KEY (table_schema, table_name)
)TABLESPACE pg_default;ALTER TABLE IF EXISTS managerdb.par_infoOWNER to postgres;COMMENT ON COLUMN managerdb.par_info.table_schemaIS '模式名';COMMENT ON COLUMN managerdb.par_info.table_nameIS '表名';COMMENT ON COLUMN managerdb.par_info.par_columnIS '分区字段名';COMMENT ON COLUMN managerdb.par_info.keep_data_daysIS '分区保留时长';COMMENT ON COLUMN managerdb.par_info.step_lengthIS '步长';COMMENT ON COLUMN managerdb.par_info.create_next_intervalsIS '预分区时间';COMMENT ON COLUMN managerdb.par_info.update_timeIS '更新时间';COMMENT ON COLUMN managerdb.par_info.min_partition_nameIS '最小分区';COMMENT ON COLUMN managerdb.par_info.max_partition_nameIS '最大分区';
-- Index: par_info_table_name_idx-- DROP INDEX IF EXISTS managerdb.par_info_table_name_idx;CREATE INDEX IF NOT EXISTS par_info_table_name_idxON managerdb.par_info USING btree(table_name COLLATE pg_catalog."default" ASC NULLS LAST)TABLESPACE pg_default;

插入四张测试表，根据范围分区

INSERT INTO managerdb.par_info (table_schema,table_name,par_column,keep_data_days,
step_length,create_next_intervals,update_time,min_partition_name,max_partition_name) VALUES('public','t_01','ctime',7,1,15,'2024-12-02 17:30:39+08',null,null),('public','t_02','ctime',7,1,15,'2024-12-02 17:30:39+08',null,null),('public','t_03','cint',7,1,15,'2024-12-02 17:30:39+08',null,null),('public','t_04','cint',7,1,15,'2024-12-02 17:30:39+08',null,null);

二.创建需要分区的表

CREATE TABLE IF NOT EXISTS public.t_01
(id integer NOT NULL,name character varying(100) COLLATE pg_catalog."default",ctime timestamp without time zone NOT NULL,CONSTRAINT t_01_pkey PRIMARY KEY (ctime, id)
);CREATE TABLE IF NOT EXISTS public.t_02
(id integer NOT NULL,name character varying(100) COLLATE pg_catalog."default",ctime timestamp without time zone NOT NULL,CONSTRAINT t_02_pkey PRIMARY KEY (ctime, id)
);CREATE TABLE IF NOT EXISTS public.t_03
(id integer NOT NULL,name character varying(100) COLLATE pg_catalog."default",cint bigint NOT NULL,CONSTRAINT t_03_pkey PRIMARY KEY (ctime, id)
);CREATE TABLE IF NOT EXISTS public.t_04
(id integer NOT NULL,name character varying(100) COLLATE pg_catalog."default",cint bigint NOT NULL,CONSTRAINT t_04_pkey PRIMARY KEY (ctime, id)
);

三.创建数据库函数，查询表结构，用于普通表转分区表

查询表索引函数

-- FUNCTION: managerdb.get_index_ddl(character varying, character varying, character varying)-- DROP FUNCTION IF EXISTS managerdb.get_index_ddl(character varying, character varying, character varying);CREATE OR REPLACE FUNCTION managerdb.get_index_ddl(namespace character varying,tablename character varying,ctype character varying)RETURNS character varyingLANGUAGE 'plpgsql'COST 100VOLATILE PARALLEL UNSAFE
AS $BODY$declare
tt oid ;
aname character varying default '';begintt := oid from pg_class where relname= tablename and relnamespace =(select oid from pg_namespace  where nspname=namespace) ;aname:=  array_to_string(array(select a.attname  from pg_attribute  a where a.attrelid=tt and  a.attnum   in (		select unnest(conkey) from pg_constraint c where contype=ctype and conrelid=tt  and array_to_string(conkey,',') is not null  ) ),',');return aname;
end
$BODY$;ALTER FUNCTION managerdb.get_index_ddl(character varying, character varying, character varying)OWNER TO postgres;

查询创建表索引

-- FUNCTION: managerdb.get_table_ddl(character varying, character varying)-- DROP FUNCTION IF EXISTS managerdb.get_table_ddl(character varying, character varying);CREATE OR REPLACE FUNCTION managerdb.get_table_ddl(namespace character varying,tablename character varying)RETURNS character varyingLANGUAGE 'plpgsql'COST 100VOLATILE PARALLEL UNSAFE
AS $BODY$
declare 
tableScript character varying default '';
idx_text character varying default '';
table_flag oid;
begin
-- columns
tableScript:=tableScript || ' CREATE TABLE '|| namespace || '.' ||tablename|| ' ( '|| chr(13)||chr(10) || array_to_string(array(
select '   ' || concat_ws('  ',fieldName, fieldType, fieldLen, indexType, isNullStr,attdefault, fieldComment ) as column_line
from (select fieldName,case when attdefault like 'default nextval%' then null else fieldType end fieldType,
fieldLen,indexType,
case when attdefault like 'default nextval%' then null else isNullStr end isNullStr,
case when attdefault like 'default nextval%' then 'serial' else attdefault end attdefault,
fieldComment 
from (
select a.attname as fieldName,
pg_catalog.format_type(a.atttypid, a.atttypmod) as fieldType,
null as fieldLen,
null as indexType,
(case when a.attnotnull=true then 'not null' else 'null' end) as isNullStr,
(SELECT 'default '||substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128) FROM pg_catalog.pg_attrdef dWHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef
) as attdefault,
null as fieldComment
FROM pg_catalog.pg_attribute aJOIN pg_catalog.pg_class c ON a.attrelid = c.oidJOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oidLEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)WHERE a.attrelid = (select c.oid from pg_class c,pg_namespace n where c.relnamespace=n.oid and n.nspname =namespace and relname =tablename)AND a.attnum > 0 AND NOT a.attisdroppedORDER BY a.attnum
) t
) as string_columns
),','||chr(13)||chr(10));-- 约束
idx_text:=array_to_string(
array(select concat('   CONSTRAINT ',conname ,c ,u,p,f)   from (select conname,case when contype='c' then  ' CHECK('|| ( select managerdb.get_index_ddl(namespace,tablename,'c') ) ||')' end  as c  ,case when contype='u' then  ' UNIQUE('|| ( select managerdb.get_index_ddl(namespace,tablename,'u') ) ||')' end as u ,case when contype='p' then ' PRIMARY KEY ('|| ( select managerdb.get_index_ddl(namespace,tablename,'p') ) ||')' end  as p  ,case when contype='f' then ' FOREIGN KEY('|| ( select managerdb.get_index_ddl(namespace,tablename,'u') ) ||') REFERENCES '|| (select p.relname from pg_class p where p.oid=c.confrelid )  || '('|| ( select managerdb.get_index_ddl(namespace,tablename,'u') ) ||')' end as  ffrom pg_constraint cwhere contype in('u','c','f','p') and conrelid=( select oid  from pg_class  where relname=tablename and relnamespace =(select oid from pg_namespace where nspname = namespace))) as t  
),','||chr(13)||chr(10));if length(idx_text) > 0  then tableScript:= tableScript||','||chr(13)||chr(10)||idx_text||') --PARTITION' ||chr(13)||chr(10) ||';';elsetableScript:= tableScript||' ) --PARTITION' ||chr(13)||chr(10) ||';';end if;--- 获取非约束索引 column
-- CREATE UNIQUE INDEX pg_language_oid_index ON pg_language USING btree (oid); -- table pg_language
tableScript:= tableScript || chr(13)||chr(10) || chr(13)||chr(10) || array_to_string(array(select 'CREATE INDEX ' || indexrelname || ' ON ' ||namespace || '.' || tablename || ' USING btree '|| '(' || attname || ');' from (SELECT i.relname AS indexrelname ,  x.indkey, ( select array_to_string (array( select a.attname from pg_attribute a where attrelid=c.oid and a.attnum in ( select unnest(x.indkey) )) ,',' ) )as attnameFROM pg_class cJOIN pg_index x ON c.oid = x.indrelidJOIN pg_class i ON i.oid = x.indexrelidLEFT JOIN pg_namespace n ON n.oid = c.relnamespaceWHERE  c.relname=tablename and i.relname not in( select constraint_name from information_schema.key_column_usage  where  table_name=tablename  ))as t
) ,','|| chr(13)||chr(10));-- COMMENT COMMENT ON COLUMN sys_activity.id IS '主键';
tableScript:= tableScript || chr(13)||chr(10) || chr(13)||chr(10) || array_to_string(
array(
SELECT 'COMMENT ON COLUMN ' || namespace || '.' || tablename || '.' || a.attname ||' IS  '|| ''''|| d.description ||''''||';'
FROM pg_class c
JOIN pg_description d ON c.oid=d.objoid
JOIN pg_attribute a ON c.oid = a.attrelid
WHERE c.relname=tablename
AND a.attnum = d.objsubid), chr(13)||chr(10)) ;-- COMMENT COMMENT ON table sys_activity  IS '表名';
tableScript:= tableScript || chr(13)||chr(10) || chr(13)||chr(10) || array_to_string(
array(
SELECT 'COMMENT ON table ' || namespace || '.' || tablename ||' IS  '|| ''''|| d.description ||''''||';'
FROM pg_class c
inner join pg_description d on c.oid=d.objoid
where d.objsubid=0 and c.oid=(select c.oid from pg_class c,pg_namespace n where c.relnamespace=n.oid and n.nspname =namespace and relname =tablename)
), chr(13)||chr(10)) ;return format(tableScript);end
$BODY$;ALTER FUNCTION managerdb.get_table_ddl(character varying, character varying)OWNER TO postgres;

四.创建存储过程自动分区

创建普通表转分区表存储过程

-- PROCEDURE: managerdb.partition_verify(character varying, character varying, character varying, bigint, bigint, bigint)-- DROP PROCEDURE IF EXISTS managerdb.partition_verify(character varying, character varying, character varying, bigint, bigint, bigint);CREATE OR REPLACE PROCEDURE managerdb.partition_verify(IN p_schema_name character varying,IN p_table_name character varying,IN p_par_column character varying,IN p_keep_data_days bigint,IN p_step_length bigint,IN p_create_next_intervals bigint)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE error_message text;i int :=1;is_par bigint;l_itera bigint :=p_keep_data_days+p_create_next_intervals;par_name varchar(100);par_table_name varchar(100);start_data varchar(100);end_data  varchar(100);sql_create varchar(4000);sql_text varchar(4000);sql_insert varchar(4000);l_partition_type varchar(100);max_par varchar(100);min_par varchar(100);
begin
select count(1) into is_par
from pg_partitioned_table a
inner join pg_class c on a.partrelid =c.oid
inner join pg_namespace n on c.relnamespace=n.oid
where c.relname=p_table_name and n.nspname=p_schema_name;-- 检查分区字段类型SELECT data_type INTO l_partition_typeFROM information_schema.columns WHERE table_schema = p_schema_nameAND table_name = p_table_nameAND column_name = p_par_column;raise notice '分区字段类型为:%',l_partition_type;if is_par=1 thenraise notice '该表是分区表';
elsepar_table_name:=p_table_name||'_par';raise notice '%.%该表不是分区表！！！',p_schema_name,p_table_name;select replace(managerdb.get_table_ddl(p_schema_name,p_table_name),'--PARTITION','PARTITION BY RANGE ('||p_par_column||')') into sql_create;select replace(sql_create,p_table_name,par_table_name) into sql_create;raise notice '创建中间分区表%.%！！！',p_schema_name,par_table_name;raise notice 'table_create_sql: %', sql_create;execute sql_create;for i in 1..l_itera looppar_name = p_schema_name||'.'||p_table_name||'_'||to_char(current_date+(interval '1 day' *(p_create_next_intervals-i)*p_step_length),'yyyymmdd');if l_partition_type = 'timestamp without time zone' thenstart_data = ''''||to_char(current_date+(interval '1 day' *(p_create_next_intervals-i)*p_step_length),'yyyy-mm-dd'||' 00:00:00')||'''';end_data = ''''||to_char(current_date+(interval '1 day' *(p_create_next_intervals+1-i)*p_step_length),'yyyy-mm-dd'||' 00:00:00')||'''';else -- if l_partition_type='integer' or l_partition_type='bigint' thenstart_data = (date_part('epoch',current_date+(interval '1 day' *(p_create_next_intervals-i)*p_step_length)))::int8;end_data = (date_part('epoch',current_date+(interval '1 day' *(p_create_next_intervals+1-i)*p_step_length)))::int8;end if;sql_text='CREATE TABLE '||par_name||' PARTITION OF '||p_schema_name||'.'||par_table_name||' FOR VALUES FROM ('||start_data||') TO ('||end_data||');';raise notice 'partition_create_sql: %', sql_text;execute sql_text;end loop;
end if;if l_partition_type = 'timestamp without time zone' thenmax_par:=''''||to_char(current_date+(interval '1 day' *(p_create_next_intervals)*p_step_length),'yyyy-mm-dd'||' 00:00:00')||'''';min_par:=''''||to_char(current_date-(interval '1 day' *(p_keep_data_days)*p_step_length),'yyyy-mm-dd'||' 00:00:00')||'''';
elsemax_par = (date_part('epoch',current_date+(interval '1 day' *(p_create_next_intervals)*p_step_length)))::int8;min_par = (date_part('epoch',current_date-(interval '1 day' *(p_keep_data_days)*p_step_length)))::int8;
end if;sql_insert:='insert into '||p_schema_name||'.'||par_table_name||chr(13)||chr(10)||'select * from '||p_schema_name||'.'||p_table_name||' where '||p_par_column||'>='||min_par||' and '||p_par_column||'<'||max_par||';';raise notice '将历史数据导入中间分区表sql:%',sql_insert;
execute sql_insert;sql_insert:='alter table '||p_schema_name||'.'||p_table_name||' rename to '||p_table_name||'_bak;';
raise notice '将原表重命名:%',sql_insert;
execute sql_insert;sql_insert:='alter table '||p_schema_name||'.'||par_table_name||' rename to '||p_table_name||';';
raise notice '将中间分区表重命名:%',sql_insert;
execute sql_insert;EXCEPTIONWHEN OTHERS THENGET STACKED DIAGNOSTICS error_message = MESSAGE_TEXT;RAISE NOTICE '捕获到异常信息*********************************ERROR************************************';RAISE NOTICE 'ERROR:%',error_message;RAISE NOTICE '捕获到异常信息*********************************ERROR************************************';end;
$BODY$;
ALTER PROCEDURE managerdb.partition_verify(character varying, character varying, character varying, bigint, bigint, bigint)OWNER TO postgres;

创建分区表定时增减分区存储过程

-- PROCEDURE: managerdb.partition_maintenance(character varying, character varying, character varying, bigint, bigint, bigint)-- DROP PROCEDURE IF EXISTS managerdb.partition_maintenance(character varying, character varying, character varying, bigint, bigint, bigint);CREATE OR REPLACE PROCEDURE managerdb.partition_maintenance(IN p_schema_name character varying,IN p_table_name character varying,IN p_par_column character varying,IN p_keep_data_days bigint,IN p_step_length bigint,IN p_create_next_intervals bigint)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE error_message text;i int :=1;is_par bigint;par_cn bigint;l_itera bigint :=p_keep_data_days+p_create_next_intervals;par_name varchar(100);start_data varchar(100);end_data  varchar(100);sql_text varchar(4000);sql_del varchar(4000);sql_update varchar(4000);l_partition_type varchar(100);max_par varchar(100);min_par varchar(100);his_par varchar(100);csr_par cursor for select p.relnamefrom pg_class c inner join pg_inherits i on c.oid = i.inhparentinner join pg_class p on p.oid=i.inhrelidwhere c.oid =(select c.oid from pg_class c,pg_namespace n where c.relnamespace=n.oid and n.nspname=p_schema_name and relname=p_table_name)order by p.relname;beginselect count(1) into is_par
from pg_partitioned_table a
inner join pg_class c on a.partrelid =c.oid
inner join pg_namespace n on c.relnamespace=n.oid
where c.relname=p_table_name and n.nspname=p_schema_name;if is_par=1 thenraise notice '该表是分区表。';
elseraise notice '该表为普通表，需要进行分区表重建。';call managerdb.partition_verify(p_schema_name,p_table_name,p_par_column,p_keep_data_days,p_step_length,p_create_next_intervals);
end if;-- 检查分区字段类型
SELECT data_type INTO l_partition_type
FROM information_schema.columns 
WHERE table_schema = p_schema_name
AND table_name = p_table_name
AND column_name = p_par_column;raise notice '该表分区字段类型为:%',l_partition_type;/* 查看分区表的分区情况
select pg_get_expr(p.relpartbound, p.oid),p.relnamefrom pg_class c inner join pg_inherits i on c.oid = i.inhparentinner join pg_class p on p.oid=i.inhrelidwhere c.oid =(select c.oid from pg_class c,pg_namespace n where c.relnamespace=n.oid and n.nspname ='public' and relname ='t_01')order by pg_get_expr(p.relpartbound, p.oid)
*/select max(p.relname) INTO max_par
from pg_class c 
inner join pg_inherits i on c.oid = i.inhparent
inner join pg_class p on p.oid=i.inhrelid
where c.oid =(select c.oid from pg_class c,pg_namespace n where c.relnamespace=n.oid and n.nspname=p_schema_name and relname=p_table_name);select min(p.relname) INTO min_par
from pg_class c 
inner join pg_inherits i on c.oid = i.inhparent
inner join pg_class p on p.oid=i.inhrelid
where c.oid =(select c.oid from pg_class c,pg_namespace n where c.relnamespace=n.oid and n.nspname=p_schema_name and relname=p_table_name);select count(1) INTO par_cn
from pg_class c 
inner join pg_inherits i on c.oid = i.inhparent
inner join pg_class p on p.oid=i.inhrelid
where c.oid =(select c.oid from pg_class c,pg_namespace n where c.relnamespace=n.oid and n.nspname=p_schema_name and relname=p_table_name);raise notice '该表的最大分区:%，最小分区:%，分区总数:%。',max_par,min_par,par_cn;-- 增加分区for i in 1..l_itera looppar_name = p_table_name||'_'||to_char(current_date+(interval '1 day' *(p_create_next_intervals-i)*p_step_length),'yyyymmdd');-- raise notice '分区%',par_name;if par_name>max_par then if l_partition_type = 'timestamp without time zone' thenstart_data = ''''||to_char(current_date+(interval '1 day' *(p_create_next_intervals-i)*p_step_length),'yyyy-mm-dd'||' 00:00:00')||'''';end_data = ''''||to_char(current_date+(interval '1 day' *(p_create_next_intervals+1-i)*p_step_length),'yyyy-mm-dd'||' 00:00:00')||'''';elsestart_data = (date_part('epoch',current_date+(interval '1 day' *(p_create_next_intervals-i)*p_step_length)))::int8;end_data = (date_part('epoch',current_date+(interval '1 day' *(p_create_next_intervals+1-i)*p_step_length)))::int8;end if;sql_text='CREATE TABLE '||p_schema_name||'.'||par_name||' PARTITION OF '||p_schema_name||'.'||p_table_name||' FOR VALUES FROM ('||start_data||') TO ('||end_data||');';raise notice 'partition_create_sql: %', sql_text;execute sql_text;else raise notice '该表分区增加完成。。。';exit;end if;end loop;-- 删除分区--应该最小分区和最大分区
if l_partition_type = 'timestamp without time zone' thenmax_par:=''''||to_char(current_date+(interval '1 day' *(p_create_next_intervals)*p_step_length),'yyyy-mm-dd'||' 00:00:00')||'''';min_par:=''''||to_char(current_date-(interval '1 day' *(p_keep_data_days)*p_step_length),'yyyy-mm-dd'||' 00:00:00')||'''';
elsemax_par = (date_part('epoch',current_date+(interval '1 day' *(p_create_next_intervals)*p_step_length)))::int8;min_par = (date_part('epoch',current_date-(interval '1 day' *(p_keep_data_days)*p_step_length)))::int8;
end if;raise notice '该表应该最大分区:%，最小分区:%。',max_par,min_par;--应该最小分区名称
par_name = p_table_name||'_'||to_char(current_date-(interval '1 day' *(p_keep_data_days)*p_step_length),'yyyymmdd');
raise notice '应该最小分区名称%',par_name;open csr_par;loopfetch csr_par into his_par;exit when not found;-- raise notice '历史分区。。。%',his_par;if his_par<par_name then sql_del='drop table '||p_schema_name||'.'||his_par||';';raise notice '删除分区: %', sql_del;execute sql_del;else raise notice '最小分区已删除到: %', p_schema_name||'.'||his_par;exit;end if; end loop;
close csr_par;-- 更新自动化分区配置文件
select max(p.relname) INTO max_par
from pg_class c 
inner join pg_inherits i on c.oid = i.inhparent
inner join pg_class p on p.oid=i.inhrelid
where c.oid =(select c.oid from pg_class c,pg_namespace n where c.relnamespace=n.oid and n.nspname=p_schema_name and relname=p_table_name);select min(p.relname) INTO min_par
from pg_class c 
inner join pg_inherits i on c.oid = i.inhparent
inner join pg_class p on p.oid=i.inhrelid
where c.oid =(select c.oid from pg_class c,pg_namespace n where c.relnamespace=n.oid and n.nspname=p_schema_name and relname=p_table_name);sql_update='update managerdb.par_info set update_time='''||to_char(now(),'yyyy-mm-dd hh24:mi:ss')||''',min_partition_name='''||min_par||''',max_partition_name='''||max_par||''' where table_schema='''||p_schema_name||''' and table_name='''||p_table_name||''';';
raise notice '更新自动化分区配置文件sql:%',sql_update;
execute sql_update;raise notice '---------------------------------------------------------------------------------';
raise notice '----------------------分区表：%.%,自动化分区完成！！！------------------------',p_schema_name,p_table_name;
raise notice '---------------------------------------------------------------------------------';EXCEPTIONWHEN OTHERS THENGET STACKED DIAGNOSTICS error_message = MESSAGE_TEXT;RAISE NOTICE '捕获到异常信息*********************************ERROR************************************';RAISE NOTICE 'ERROR:%',error_message;RAISE NOTICE '捕获到异常信息*********************************ERROR************************************';end;
$BODY$;
ALTER PROCEDURE managerdb.partition_maintenance(character varying, character varying, character varying, bigint, bigint, bigint)OWNER TO postgres;

创建全局自动化分区存储过程

-- PROCEDURE: managerdb.partition_maintenance_all()-- DROP PROCEDURE IF EXISTS managerdb.partition_maintenance_all();CREATE OR REPLACE PROCEDURE managerdb.partition_maintenance_all()
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE error_message text;p_schema_name VARCHAR(255);p_table_name VARCHAR(255);p_par_column VARCHAR(255);p_keep_data_days INT8;p_step_length INT8;p_create_next_intervals INT8;csr cursor for SELECT table_schema,table_name,par_column,keep_data_days,step_length,create_next_intervals from managerdb.par_info;beginraise notice '自动化分区日志开始（%）。。。',to_char(now(),'yyyy-mm-dd hh24:mi:ss');open csr;loopfetch csr into p_schema_name,p_table_name,p_par_column,p_keep_data_days,p_step_length,p_create_next_intervals;exit when not found;raise notice '---------------------------------------------------------------------------------';raise notice '----------------------分区表：%.%,自动化分区开始！！！------------------------',p_schema_name,p_table_name;raise notice '---------------------------------------------------------------------------------';raise notice '分区表：%.%,保留时长%天,时间间隔%天,预分区时间%天。',p_schema_name,p_table_name,p_keep_data_days,p_step_length,p_create_next_intervals;call managerdb.partition_maintenance(p_schema_name,p_table_name,p_par_column,p_keep_data_days,p_step_length,p_create_next_intervals);end loop;close csr;raise notice '...自动化分区执行完成！！！';EXCEPTIONWHEN OTHERS THENGET STACKED DIAGNOSTICS error_message = MESSAGE_TEXT;RAISE NOTICE '捕获到异常信息*********************************ERROR************************************';RAISE NOTICE 'ERROR:%',error_message;RAISE NOTICE '捕获到异常信息*********************************ERROR************************************';
end; 
$BODY$;
ALTER PROCEDURE managerdb.partition_maintenance_all()OWNER TO postgres;COMMENT ON PROCEDURE managerdb.partition_maintenance_all()IS '自动化分区调度入口';

五.创建定时作业，每天定时执行

vim /home/postgres/script/crontab_job_auto_partition.sh/home/postgres/bin/psql -p 15432 -d postgres -c "call managerdb.partition_maintenance_all()" > /home/postgres/scripts/partition_his.log 2>&1

crontab -e0 2 * * * sh /home/postgres//scripts/crontab_job_auto_partition.sh

六.试运行结果查看

执行日志

[postgres@db scripts]$ cat partition_his.log 
NOTICE:  自动化分区日志开始（2024-12-02 17:30:39）。。。
NOTICE:  ---------------------------------------------------------------------------------
NOTICE:  ----------------------分区表：public.t_01,自动化分区开始！！！------------------------
NOTICE:  ---------------------------------------------------------------------------------
NOTICE:  分区表：public.t_01,保留时长7天,时间间隔1天,预分区时间15天。
NOTICE:  该表是分区表。
NOTICE:  该表分区字段类型为:timestamp without time zone
NOTICE:  该表的最大分区:t_01_20241221，最小分区:t_01_20241127，分区总数:25。
NOTICE:  该表分区增加完成。。。
NOTICE:  该表应该最大分区:'2024-12-17 00:00:00'，最小分区:'2024-11-25 00:00:00'。
NOTICE:  应该最小分区名称t_01_20241125
NOTICE:  最小分区已删除到: public.t_01_20241127
NOTICE:  更新自动化分区配置文件sql:update managerdb.par_info set update_time='2024-12-02 17:30:39',min_partition_name='t_01_20241127',max_partition_name='t_01_20241221' where table_schema='public' and table_name='t_01';
NOTICE:  ---------------------------------------------------------------------------------
NOTICE:  ----------------------分区表：public.t_01,自动化分区完成！！！------------------------
NOTICE:  ---------------------------------------------------------------------------------
NOTICE:  ---------------------------------------------------------------------------------
NOTICE:  ----------------------分区表：public.t_02,自动化分区开始！！！------------------------
NOTICE:  ---------------------------------------------------------------------------------
NOTICE:  分区表：public.t_02,保留时长7天,时间间隔1天,预分区时间15天。
NOTICE:  该表是分区表。
NOTICE:  该表分区字段类型为:timestamp without time zone
NOTICE:  该表的最大分区:t_02_20241216，最小分区:t_02_20241125，分区总数:22。
NOTICE:  该表分区增加完成。。。
NOTICE:  该表应该最大分区:'2024-12-17 00:00:00'，最小分区:'2024-11-25 00:00:00'。
NOTICE:  应该最小分区名称t_02_20241125
NOTICE:  最小分区已删除到: public.t_02_20241125
NOTICE:  更新自动化分区配置文件sql:update managerdb.par_info set update_time='2024-12-02 17:30:39',min_partition_name='t_02_20241125',max_partition_name='t_02_20241216' where table_schema='public' and table_name='t_02';
NOTICE:  ---------------------------------------------------------------------------------
NOTICE:  ----------------------分区表：public.t_02,自动化分区完成！！！------------------------
NOTICE:  ---------------------------------------------------------------------------------
NOTICE:  ---------------------------------------------------------------------------------
NOTICE:  ----------------------分区表：public.t_03,自动化分区开始！！！------------------------
NOTICE:  ---------------------------------------------------------------------------------
NOTICE:  分区表：public.t_03,保留时长7天,时间间隔1天,预分区时间15天。
NOTICE:  该表是分区表。
NOTICE:  该表分区字段类型为:bigint
NOTICE:  该表的最大分区:t_03_20241216，最小分区:t_03_20241125，分区总数:22。
NOTICE:  该表分区增加完成。。。
NOTICE:  该表应该最大分区:1734393600，最小分区:1732492800。
NOTICE:  应该最小分区名称t_03_20241125
NOTICE:  最小分区已删除到: public.t_03_20241125
NOTICE:  更新自动化分区配置文件sql:update managerdb.par_info set update_time='2024-12-02 17:30:39',min_partition_name='t_03_20241125',max_partition_name='t_03_20241216' where table_schema='public' and table_name='t_03';
NOTICE:  ---------------------------------------------------------------------------------
NOTICE:  ----------------------分区表：public.t_03,自动化分区完成！！！------------------------
NOTICE:  ---------------------------------------------------------------------------------
NOTICE:  ---------------------------------------------------------------------------------
NOTICE:  ----------------------分区表：public.t_04,自动化分区开始！！！------------------------
NOTICE:  ---------------------------------------------------------------------------------
NOTICE:  分区表：public.t_04,保留时长7天,时间间隔1天,预分区时间15天。
NOTICE:  该表是分区表。
NOTICE:  该表分区字段类型为:bigint
NOTICE:  该表的最大分区:t_04_20241216，最小分区:t_04_20241125，分区总数:22。
NOTICE:  该表分区增加完成。。。
NOTICE:  该表应该最大分区:1734393600，最小分区:1732492800。
NOTICE:  应该最小分区名称t_04_20241125
NOTICE:  最小分区已删除到: public.t_04_20241125
NOTICE:  更新自动化分区配置文件sql:update managerdb.par_info set update_time='2024-12-02 17:30:39',min_partition_name='t_04_20241125',max_partition_name='t_04_20241216' where table_schema='public' and table_name='t_04';
NOTICE:  ---------------------------------------------------------------------------------
NOTICE:  ----------------------分区表：public.t_04,自动化分区完成！！！------------------------
NOTICE:  ---------------------------------------------------------------------------------
NOTICE:  ...自动化分区执行完成！！！
CALL

普通表自动化转换分区表成功

定期增加删除分区成功