Postgres数据库主动化分区

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

1. -- Table: managerdb.par_info
3. -- DROP TABLE IF EXISTS managerdb.par_info;
5. CREATE TABLE IF NOT EXISTS managerdb.par_info
6. (
7.     table_schema character varying(255) COLLATE pg_catalog."default" NOT NULL,
8.     table_name character varying(255) COLLATE pg_catalog."default" NOT NULL,
9.     par_column character varying(255) COLLATE pg_catalog."default",
10.     keep_data_days bigint,
11.     step_length bigint,
12.     create_next_intervals bigint,
13.     update_time timestamp with time zone NOT NULL DEFAULT now(),
14.     min_partition_name character varying(300) COLLATE pg_catalog."default",
15.     max_partition_name character varying(300) COLLATE pg_catalog."default",
16.     CONSTRAINT par_info_pkey PRIMARY KEY (table_schema, table_name)
17. )
19. TABLESPACE pg_default;
21. ALTER TABLE IF EXISTS managerdb.par_info
22.     OWNER to postgres;
24. COMMENT ON COLUMN managerdb.par_info.table_schema
25.     IS '模式名';
27. COMMENT ON COLUMN managerdb.par_info.table_name
28.     IS '表名';
30. COMMENT ON COLUMN managerdb.par_info.par_column
31.     IS '分区字段名';
33. COMMENT ON COLUMN managerdb.par_info.keep_data_days
34.     IS '分区保留时长';
36. COMMENT ON COLUMN managerdb.par_info.step_length
37.     IS '步长';
39. COMMENT ON COLUMN managerdb.par_info.create_next_intervals
40.     IS '预分区时间';
42. COMMENT ON COLUMN managerdb.par_info.update_time
43.     IS '更新时间';
45. COMMENT ON COLUMN managerdb.par_info.min_partition_name
46.     IS '最小分区';
48. COMMENT ON COLUMN managerdb.par_info.max_partition_name
49.     IS '最大分区';
50. -- Index: par_info_table_name_idx
52. -- DROP INDEX IF EXISTS managerdb.par_info_table_name_idx;
54. CREATE INDEX IF NOT EXISTS par_info_table_name_idx
55.     ON managerdb.par_info USING btree
56.     (table_name COLLATE pg_catalog."default" ASC NULLS LAST)
57.     TABLESPACE pg_default;

复制代码

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

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

复制代码

二.创建需要分区的表

1. CREATE TABLE IF NOT EXISTS public.t_01
2. (
3.     id integer NOT NULL,
4.     name character varying(100) COLLATE pg_catalog."default",
5.     ctime timestamp without time zone NOT NULL,
6.     CONSTRAINT t_01_pkey PRIMARY KEY (ctime, id)
7. );
9. CREATE TABLE IF NOT EXISTS public.t_02
10. (
11.     id integer NOT NULL,
12.     name character varying(100) COLLATE pg_catalog."default",
13.     ctime timestamp without time zone NOT NULL,
14.     CONSTRAINT t_02_pkey PRIMARY KEY (ctime, id)
15. );
17. CREATE TABLE IF NOT EXISTS public.t_03
18. (
19.     id integer NOT NULL,
20.     name character varying(100) COLLATE pg_catalog."default",
21.     cint bigint NOT NULL,
22.     CONSTRAINT t_03_pkey PRIMARY KEY (ctime, id)
23. );
25. CREATE TABLE IF NOT EXISTS public.t_04
26. (
27.     id integer NOT NULL,
28.     name character varying(100) COLLATE pg_catalog."default",
29.     cint bigint NOT NULL,
30.     CONSTRAINT t_04_pkey PRIMARY KEY (ctime, id)
31. );

复制代码

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

查询表索引函数

1. -- FUNCTION: managerdb.get_index_ddl(character varying, character varying, character varying)
3. -- DROP FUNCTION IF EXISTS managerdb.get_index_ddl(character varying, character varying, character varying);
5. CREATE OR REPLACE FUNCTION managerdb.get_index_ddl(
6.         namespace character varying,
7.         tablename character varying,
8.         ctype character varying)
9.     RETURNS character varying
10.     LANGUAGE 'plpgsql'
11.     COST 100
12.     VOLATILE PARALLEL UNSAFE
13. AS $BODY$
15. declare
16. tt oid ;
17. aname character varying default '';
19. begin
20.        tt := oid from pg_class where relname= tablename and relnamespace =(select oid from pg_namespace  where nspname=namespace) ;
21.        aname:=  array_to_string(
22.                 array(
23.                        select a.attname  from pg_attribute  a
24.                                 where a.attrelid=tt and  a.attnum   in (               
25.                                 select unnest(conkey) from pg_constraint c where contype=ctype
26.                                 and conrelid=tt  and array_to_string(conkey,',') is not null  
27.                         )
28.                 ),',');
29.        
30.         return aname;
31. end
32. $BODY$;
34. ALTER FUNCTION managerdb.get_index_ddl(character varying, character varying, character varying)
35.     OWNER TO postgres;

复制代码

查询创建表索引

1. -- FUNCTION: managerdb.get_table_ddl(character varying, character varying)
3. -- DROP FUNCTION IF EXISTS managerdb.get_table_ddl(character varying, character varying);
5. CREATE OR REPLACE FUNCTION managerdb.get_table_ddl(
6.         namespace character varying,
7.         tablename character varying)
8.     RETURNS character varying
9.     LANGUAGE 'plpgsql'
10.     COST 100
11.     VOLATILE PARALLEL UNSAFE
12. AS $BODY$
13. declare
14. tableScript character varying default '';
15. idx_text character varying default '';
16. table_flag oid;
17. begin
18. -- columns
19. tableScript:=tableScript || ' CREATE TABLE '|| namespace || '.' ||tablename|| ' ( '|| chr(13)||chr(10) || array_to_string(
20.   array(
21. select '   ' || concat_ws('  ',fieldName, fieldType, fieldLen, indexType, isNullStr,attdefault, fieldComment ) as column_line
22. from (
24. select fieldName,case when attdefault like 'default nextval%' then null else fieldType end fieldType,
25. fieldLen,indexType,
26. case when attdefault like 'default nextval%' then null else isNullStr end isNullStr,
27. case when attdefault like 'default nextval%' then 'serial' else attdefault end attdefault,
28. fieldComment
29. from (
30. select a.attname as fieldName,
31. pg_catalog.format_type(a.atttypid, a.atttypmod) as fieldType,
32. null as fieldLen,
33. null as indexType,
34. (case when a.attnotnull=true then 'not null' else 'null' end) as isNullStr,
35. (SELECT 'default '||substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128) FROM pg_catalog.pg_attrdef d
36.             WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef
37. ) as attdefault,
38. null as fieldComment
39. FROM pg_catalog.pg_attribute a
40.     JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
41.     JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
42.     LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
43.     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)
44.     AND a.attnum > 0 AND NOT a.attisdropped
45.     ORDER BY a.attnum
46. ) t
47. ) as string_columns
48. ),','||chr(13)||chr(10));
51. -- 约束
52. idx_text:=array_to_string(
53. array(
54.         select concat('   CONSTRAINT ',conname ,c ,u,p,f)   from (
55.                 select conname,
56.                 case when contype='c' then  ' CHECK('|| ( select managerdb.get_index_ddl(namespace,tablename,'c') ) ||')' end  as c  ,
57.                 case when contype='u' then  ' UNIQUE('|| ( select managerdb.get_index_ddl(namespace,tablename,'u') ) ||')' end as u ,
58.                 case when contype='p' then ' PRIMARY KEY ('|| ( select managerdb.get_index_ddl(namespace,tablename,'p') ) ||')' end  as p  ,
59.                 case when contype='f' then ' FOREIGN KEY('|| ( select managerdb.get_index_ddl(namespace,tablename,'u') ) ||') REFERENCES '||
60.                 (select p.relname from pg_class p where p.oid=c.confrelid )  || '('|| ( select managerdb.get_index_ddl(namespace,tablename,'u') ) ||')' end as  f
61.                 from pg_constraint c
62.                 where contype in('u','c','f','p') and conrelid=(
63.                         select oid  from pg_class  where relname=tablename and relnamespace =(
64.                         select oid from pg_namespace where nspname = namespace
65.                         )
66.                  )
67.         ) as t  
68. ),','||chr(13)||chr(10));
70. if length(idx_text) > 0  then
71. tableScript:= tableScript||','||chr(13)||chr(10)||idx_text||') --PARTITION' ||chr(13)||chr(10) ||';';
72. else
73. tableScript:= tableScript||' ) --PARTITION' ||chr(13)||chr(10) ||';';
74. end if;
76. --- 获取非约束索引 column
77. -- CREATE UNIQUE INDEX pg_language_oid_index ON pg_language USING btree (oid); -- table pg_language
78. tableScript:= tableScript || chr(13)||chr(10) || chr(13)||chr(10) || array_to_string(
79.         array(
80.                 select 'CREATE INDEX ' || indexrelname || ' ON ' ||namespace || '.' || tablename || ' USING btree '|| '(' || attname || ');' from (
81.                  SELECT
82.                     i.relname AS indexrelname ,  x.indkey,
83.                     
84.                     ( select array_to_string (
85.                         array(
86.                                 select a.attname from pg_attribute a where attrelid=c.oid and a.attnum in ( select unnest(x.indkey) )
88.                              )
89.                      ,',' ) )as attname
90.                     
91.                    FROM pg_class c
92.                    JOIN pg_index x ON c.oid = x.indrelid
93.                    JOIN pg_class i ON i.oid = x.indexrelid
94.                    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
95.                    WHERE  c.relname=tablename and i.relname not in
96.                           ( select constraint_name from information_schema.key_column_usage  where  table_name=tablename  )
97.                 )as t
98. ) ,','|| chr(13)||chr(10));
99.                        
101. -- COMMENT COMMENT ON COLUMN sys_activity.id IS '主键';
102. tableScript:= tableScript || chr(13)||chr(10) || chr(13)||chr(10) || array_to_string(
103. array(
104. SELECT 'COMMENT ON COLUMN ' || namespace || '.' || tablename || '.' || a.attname ||' IS  '|| ''''|| d.description ||''''||';'
105. FROM pg_class c
106. JOIN pg_description d ON c.oid=d.objoid
107. JOIN pg_attribute a ON c.oid = a.attrelid
108. WHERE c.relname=tablename
109. AND a.attnum = d.objsubid), chr(13)||chr(10)) ;
111. -- COMMENT COMMENT ON table sys_activity  IS '表名';
112. tableScript:= tableScript || chr(13)||chr(10) || chr(13)||chr(10) || array_to_string(
113. array(
114. SELECT 'COMMENT ON table ' || namespace || '.' || tablename ||' IS  '|| ''''|| d.description ||''''||';'
115. FROM pg_class c
116. inner join pg_description d on c.oid=d.objoid
117. 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)
118. ), chr(13)||chr(10)) ;
121. return format(tableScript);
123. end
124. $BODY$;
126. ALTER FUNCTION managerdb.get_table_ddl(character varying, character varying)
127.     OWNER TO postgres;
128.        
129.        

复制代码

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

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

1. -- PROCEDURE: managerdb.partition_verify(character varying, character varying, character varying, bigint, bigint, bigint)
3. -- DROP PROCEDURE IF EXISTS managerdb.partition_verify(character varying, character varying, character varying, bigint, bigint, bigint);
5. CREATE OR REPLACE PROCEDURE managerdb.partition_verify(
6.         IN p_schema_name character varying,
7.         IN p_table_name character varying,
8.         IN p_par_column character varying,
9.         IN p_keep_data_days bigint,
10.         IN p_step_length bigint,
11.         IN p_create_next_intervals bigint)
12. LANGUAGE 'plpgsql'
13. AS $BODY$
14. DECLARE
15.    error_message text;
16.    i int :=1;
17.    is_par bigint;
18.    l_itera bigint :=p_keep_data_days+p_create_next_intervals;
19.    par_name varchar(100);
20.    par_table_name varchar(100);
21.    start_data varchar(100);
22.    end_data  varchar(100);
23.    sql_create varchar(4000);
24.    sql_text varchar(4000);
25.    sql_insert varchar(4000);
26.    l_partition_type varchar(100);
27.    max_par varchar(100);
28.    min_par varchar(100);
29. begin
30. select count(1) into is_par
31. from pg_partitioned_table a
32. inner join pg_class c on a.partrelid =c.oid
33. inner join pg_namespace n on c.relnamespace=n.oid
34. where c.relname=p_table_name and n.nspname=p_schema_name;
36.     -- 检查分区字段类型
37.         SELECT data_type INTO l_partition_type
38.         FROM information_schema.columns
39.         WHERE table_schema = p_schema_name
40.         AND table_name = p_table_name
41.         AND column_name = p_par_column;
43. raise notice '分区字段类型为:%',l_partition_type;
45. if is_par=1 then
46.   raise notice '该表是分区表';
47. else
48. par_table_name:=p_table_name||'_par';
49. raise notice '%.%该表不是分区表！！！',p_schema_name,p_table_name;
50. select replace(managerdb.get_table_ddl(p_schema_name,p_table_name),'--PARTITION','PARTITION BY RANGE ('||p_par_column||')') into sql_create;
51. select replace(sql_create,p_table_name,par_table_name) into sql_create;
52. raise notice '创建中间分区表%.%！！！',p_schema_name,par_table_name;
54. raise notice 'table_create_sql: %', sql_create;
55. execute sql_create;
57. for i in 1..l_itera loop
58. par_name = p_schema_name||'.'||p_table_name||'_'||to_char(current_date+(interval '1 day' *(p_create_next_intervals-i)*p_step_length),'yyyymmdd');
59.   if l_partition_type = 'timestamp without time zone' then
60.    start_data = ''''||to_char(current_date+(interval '1 day' *(p_create_next_intervals-i)*p_step_length),'yyyy-mm-dd'||' 00:00:00')||'''';
61.    end_data = ''''||to_char(current_date+(interval '1 day' *(p_create_next_intervals+1-i)*p_step_length),'yyyy-mm-dd'||' 00:00:00')||'''';
62.   else -- if l_partition_type='integer' or l_partition_type='bigint' then
63.    start_data = (date_part('epoch',current_date+(interval '1 day' *(p_create_next_intervals-i)*p_step_length)))::int8;
64.    end_data = (date_part('epoch',current_date+(interval '1 day' *(p_create_next_intervals+1-i)*p_step_length)))::int8;
65.   end if;
66.   sql_text='CREATE TABLE '||par_name||' PARTITION OF '||p_schema_name||'.'||par_table_name||' FOR VALUES FROM ('||start_data||') TO ('||end_data||');';
67.   raise notice 'partition_create_sql: %', sql_text;
68.   execute sql_text;
69. end loop;
70. end if;
72. if l_partition_type = 'timestamp without time zone' then
73.    max_par:=''''||to_char(current_date+(interval '1 day' *(p_create_next_intervals)*p_step_length),'yyyy-mm-dd'||' 00:00:00')||'''';
74.    min_par:=''''||to_char(current_date-(interval '1 day' *(p_keep_data_days)*p_step_length),'yyyy-mm-dd'||' 00:00:00')||'''';
75. else
76.    max_par = (date_part('epoch',current_date+(interval '1 day' *(p_create_next_intervals)*p_step_length)))::int8;
77.    min_par = (date_part('epoch',current_date-(interval '1 day' *(p_keep_data_days)*p_step_length)))::int8;
78. end if;
80. sql_insert:='insert into '||p_schema_name||'.'||par_table_name||chr(13)||chr(10)||'select * from '||p_schema_name||'.'||p_table_name
81.            ||' where '||p_par_column||'>='||min_par||' and '||p_par_column||'<'||max_par||';';
83. raise notice '将历史数据导入中间分区表sql:%',sql_insert;
84. execute sql_insert;
86. sql_insert:='alter table '||p_schema_name||'.'||p_table_name||' rename to '||p_table_name||'_bak;';
87. raise notice '将原表重命名:%',sql_insert;
88. execute sql_insert;
90. sql_insert:='alter table '||p_schema_name||'.'||par_table_name||' rename to '||p_table_name||';';
91. raise notice '将中间分区表重命名:%',sql_insert;
92. execute sql_insert;
94. EXCEPTION
95.         WHEN OTHERS THEN
96.                 GET STACKED DIAGNOSTICS error_message = MESSAGE_TEXT;
97.                 RAISE NOTICE '捕获到异常信息*********************************ERROR************************************';
98.                 RAISE NOTICE 'ERROR:%',error_message;
99.                 RAISE NOTICE '捕获到异常信息*********************************ERROR************************************';
101. end;
102. $BODY$;
103. ALTER PROCEDURE managerdb.partition_verify(character varying, character varying, character varying, bigint, bigint, bigint)
104.     OWNER TO postgres;

复制代码

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

1. -- PROCEDURE: managerdb.partition_maintenance(character varying, character varying, character varying, bigint, bigint, bigint)
3. -- DROP PROCEDURE IF EXISTS managerdb.partition_maintenance(character varying, character varying, character varying, bigint, bigint, bigint);
5. CREATE OR REPLACE PROCEDURE managerdb.partition_maintenance(
6.         IN p_schema_name character varying,
7.         IN p_table_name character varying,
8.         IN p_par_column character varying,
9.         IN p_keep_data_days bigint,
10.         IN p_step_length bigint,
11.         IN p_create_next_intervals bigint)
12. LANGUAGE 'plpgsql'
13. AS $BODY$
14. DECLARE
15.    error_message text;
16.    i int :=1;
17.    is_par bigint;
18.    par_cn bigint;
19.    l_itera bigint :=p_keep_data_days+p_create_next_intervals;
20.    par_name varchar(100);
21.    start_data varchar(100);
22.    end_data  varchar(100);
23.    sql_text varchar(4000);
24.    sql_del varchar(4000);
25.    sql_update varchar(4000);
26.    l_partition_type varchar(100);
27.    max_par varchar(100);
28.    min_par varchar(100);
29.    his_par varchar(100);
30.    csr_par cursor for select p.relname
31.     from pg_class c
32.     inner join pg_inherits i on c.oid = i.inhparent
33.     inner join pg_class p on p.oid=i.inhrelid
34.     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)
35.     order by p.relname;
37. begin
39. select count(1) into is_par
40. from pg_partitioned_table a
41. inner join pg_class c on a.partrelid =c.oid
42. inner join pg_namespace n on c.relnamespace=n.oid
43. where c.relname=p_table_name and n.nspname=p_schema_name;
45. if is_par=1 then
46.   raise notice '该表是分区表。';
47. else
48. raise notice '该表为普通表，需要进行分区表重建。';
49. call managerdb.partition_verify(p_schema_name,p_table_name,p_par_column,p_keep_data_days,p_step_length,p_create_next_intervals);
50. end if;
52. -- 检查分区字段类型
53. SELECT data_type INTO l_partition_type
54. FROM information_schema.columns
55. WHERE table_schema = p_schema_name
56. AND table_name = p_table_name
57. AND column_name = p_par_column;
59. raise notice '该表分区字段类型为:%',l_partition_type;
61. /* 查看分区表的分区情况
62. select pg_get_expr(p.relpartbound, p.oid),p.relname
63.         from pg_class c
64.         inner join pg_inherits i on c.oid = i.inhparent
65.         inner join pg_class p on p.oid=i.inhrelid
66.         where 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')
67.         order by pg_get_expr(p.relpartbound, p.oid)
68. */
70. select max(p.relname) INTO max_par
71. from pg_class c
72. inner join pg_inherits i on c.oid = i.inhparent
73. inner join pg_class p on p.oid=i.inhrelid
74. 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);
76. select min(p.relname) INTO min_par
77. from pg_class c
78. inner join pg_inherits i on c.oid = i.inhparent
79. inner join pg_class p on p.oid=i.inhrelid
80. 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);
82. select count(1) INTO par_cn
83. from pg_class c
84. inner join pg_inherits i on c.oid = i.inhparent
85. inner join pg_class p on p.oid=i.inhrelid
86. 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);
88. raise notice '该表的最大分区:%，最小分区:%，分区总数:%。',max_par,min_par,par_cn;
90. -- 增加分区
91. for i in 1..l_itera loop
92. par_name = p_table_name||'_'||to_char(current_date+(interval '1 day' *(p_create_next_intervals-i)*p_step_length),'yyyymmdd');
93.   -- raise notice '分区%',par_name;
94.   if par_name>max_par then
95.     if l_partition_type = 'timestamp without time zone' then
96.      start_data = ''''||to_char(current_date+(interval '1 day' *(p_create_next_intervals-i)*p_step_length),'yyyy-mm-dd'||' 00:00:00')||'''';
97.      end_data = ''''||to_char(current_date+(interval '1 day' *(p_create_next_intervals+1-i)*p_step_length),'yyyy-mm-dd'||' 00:00:00')||'''';
98.     else
99.      start_data = (date_part('epoch',current_date+(interval '1 day' *(p_create_next_intervals-i)*p_step_length)))::int8;
100.      end_data = (date_part('epoch',current_date+(interval '1 day' *(p_create_next_intervals+1-i)*p_step_length)))::int8;
101.     end if;
102.     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||');';
103.     raise notice 'partition_create_sql: %', sql_text;
104.     execute sql_text;
105. else
106.    raise notice '该表分区增加完成。。。';
107.    exit;
108. end if;
109. end loop;
111. -- 删除分区
113. --应该最小分区和最大分区
114. if l_partition_type = 'timestamp without time zone' then
115.    max_par:=''''||to_char(current_date+(interval '1 day' *(p_create_next_intervals)*p_step_length),'yyyy-mm-dd'||' 00:00:00')||'''';
116.    min_par:=''''||to_char(current_date-(interval '1 day' *(p_keep_data_days)*p_step_length),'yyyy-mm-dd'||' 00:00:00')||'''';
117. else
118.    max_par = (date_part('epoch',current_date+(interval '1 day' *(p_create_next_intervals)*p_step_length)))::int8;
119.    min_par = (date_part('epoch',current_date-(interval '1 day' *(p_keep_data_days)*p_step_length)))::int8;
120. end if;
122. raise notice '该表应该最大分区:%，最小分区:%。',max_par,min_par;
124. --应该最小分区名称
125. par_name = p_table_name||'_'||to_char(current_date-(interval '1 day' *(p_keep_data_days)*p_step_length),'yyyymmdd');
126. raise notice '应该最小分区名称%',par_name;
128. open csr_par;
129.     loop
130.        fetch csr_par into his_par;
131.            exit when not found;
132.        -- raise notice '历史分区。。。%',his_par;
133.            if his_par<par_name then
134.              sql_del='drop table '||p_schema_name||'.'||his_par||';';
135.                  raise notice '删除分区: %', sql_del;
136.                  execute sql_del;
137.            else
138.              raise notice '最小分区已删除到: %', p_schema_name||'.'||his_par;
139.              exit;
140.            end if;
141.     end loop;
142. close csr_par;
144. -- 更新自动化分区配置文件
145. select max(p.relname) INTO max_par
146. from pg_class c
147. inner join pg_inherits i on c.oid = i.inhparent
148. inner join pg_class p on p.oid=i.inhrelid
149. 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);
151. select min(p.relname) INTO min_par
152. from pg_class c
153. inner join pg_inherits i on c.oid = i.inhparent
154. inner join pg_class p on p.oid=i.inhrelid
155. 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);
157. sql_update='update managerdb.par_info set update_time='''||to_char(now(),'yyyy-mm-dd hh24:mi:ss')||''',min_partition_name='''||
158.            min_par||''',max_partition_name='''||max_par||''' where table_schema='''||p_schema_name||''' and table_name='''||p_table_name||''';';
159. raise notice '更新自动化分区配置文件sql:%',sql_update;
160. execute sql_update;
162. raise notice '---------------------------------------------------------------------------------';
163. raise notice '----------------------分区表：%.%,自动化分区完成！！！------------------------',p_schema_name,p_table_name;
164. raise notice '---------------------------------------------------------------------------------';
167. EXCEPTION
168.         WHEN OTHERS THEN
169.                 GET STACKED DIAGNOSTICS error_message = MESSAGE_TEXT;
170.         RAISE NOTICE '捕获到异常信息*********************************ERROR************************************';
171.                 RAISE NOTICE 'ERROR:%',error_message;
172.                 RAISE NOTICE '捕获到异常信息*********************************ERROR************************************';
173.                        
174. end;
175. $BODY$;
176. ALTER PROCEDURE managerdb.partition_maintenance(character varying, character varying, character varying, bigint, bigint, bigint)
177.     OWNER TO postgres;

复制代码

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

1. -- PROCEDURE: managerdb.partition_maintenance_all()
3. -- DROP PROCEDURE IF EXISTS managerdb.partition_maintenance_all();
5. CREATE OR REPLACE PROCEDURE managerdb.partition_maintenance_all(
6.         )
7. LANGUAGE 'plpgsql'
8. AS $BODY$
9. DECLARE
10.    error_message text;
11.    p_schema_name VARCHAR(255);
12.    p_table_name VARCHAR(255);
13.    p_par_column VARCHAR(255);
14.    p_keep_data_days INT8;
15.    p_step_length INT8;
16.    p_create_next_intervals INT8;
17.    csr cursor for SELECT table_schema,table_name,par_column,keep_data_days,step_length,create_next_intervals from managerdb.par_info;
19. begin
21.    raise notice '自动化分区日志开始（%）。。。',to_char(now(),'yyyy-mm-dd hh24:mi:ss');
22.         open csr;
23.     loop
24.        fetch csr into p_schema_name,p_table_name,p_par_column,p_keep_data_days,p_step_length,p_create_next_intervals;
25.            exit when not found;
26.            raise notice '---------------------------------------------------------------------------------';
27.        raise notice '----------------------分区表：%.%,自动化分区开始！！！------------------------',p_schema_name,p_table_name;
28.        raise notice '---------------------------------------------------------------------------------';
29.        raise notice '分区表：%.%,保留时长%天,时间间隔%天,预分区时间%天。',p_schema_name,p_table_name,p_keep_data_days,p_step_length,p_create_next_intervals;
30.            call managerdb.partition_maintenance(p_schema_name,p_table_name,p_par_column,p_keep_data_days,p_step_length,p_create_next_intervals);
32.     end loop;
33.         close csr;
34.         raise notice '...自动化分区执行完成！！！';
36.         EXCEPTION
37.             WHEN OTHERS THEN
38.                     GET STACKED DIAGNOSTICS error_message = MESSAGE_TEXT;
39.                         RAISE NOTICE '捕获到异常信息*********************************ERROR************************************';
40.                         RAISE NOTICE 'ERROR:%',error_message;
41.                         RAISE NOTICE '捕获到异常信息*********************************ERROR************************************';
42. end;
43. $BODY$;
44. ALTER PROCEDURE managerdb.partition_maintenance_all()
45.     OWNER TO postgres;
47. COMMENT ON PROCEDURE managerdb.partition_maintenance_all()
48.     IS '自动化分区调度入口';

复制代码

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

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

复制代码

1. crontab -e
3. 0 2 * * * sh /home/postgres//scripts/crontab_job_auto_partition.sh

复制代码

六.试运行效果查看

执行日志

1. [postgres@db scripts]$ cat partition_his.log
2. NOTICE:  自动化分区日志开始（2024-12-02 17:30:39）。。。
3. NOTICE:  ---------------------------------------------------------------------------------
4. NOTICE:  ----------------------分区表：public.t_01,自动化分区开始！！！------------------------
5. NOTICE:  ---------------------------------------------------------------------------------
6. NOTICE:  分区表：public.t_01,保留时长7天,时间间隔1天,预分区时间15天。
7. NOTICE:  该表是分区表。
8. NOTICE:  该表分区字段类型为:timestamp without time zone
9. NOTICE:  该表的最大分区:t_01_20241221，最小分区:t_01_20241127，分区总数:25。
10. NOTICE:  该表分区增加完成。。。
11. NOTICE:  该表应该最大分区:'2024-12-17 00:00:00'，最小分区:'2024-11-25 00:00:00'。
12. NOTICE:  应该最小分区名称t_01_20241125
13. NOTICE:  最小分区已删除到: public.t_01_20241127
14. 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';
15. NOTICE:  ---------------------------------------------------------------------------------
16. NOTICE:  ----------------------分区表：public.t_01,自动化分区完成！！！------------------------
17. NOTICE:  ---------------------------------------------------------------------------------
18. NOTICE:  ---------------------------------------------------------------------------------
19. NOTICE:  ----------------------分区表：public.t_02,自动化分区开始！！！------------------------
20. NOTICE:  ---------------------------------------------------------------------------------
21. NOTICE:  分区表：public.t_02,保留时长7天,时间间隔1天,预分区时间15天。
22. NOTICE:  该表是分区表。
23. NOTICE:  该表分区字段类型为:timestamp without time zone
24. NOTICE:  该表的最大分区:t_02_20241216，最小分区:t_02_20241125，分区总数:22。
25. NOTICE:  该表分区增加完成。。。
26. NOTICE:  该表应该最大分区:'2024-12-17 00:00:00'，最小分区:'2024-11-25 00:00:00'。
27. NOTICE:  应该最小分区名称t_02_20241125
28. NOTICE:  最小分区已删除到: public.t_02_20241125
29. 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';
30. NOTICE:  ---------------------------------------------------------------------------------
31. NOTICE:  ----------------------分区表：public.t_02,自动化分区完成！！！------------------------
32. NOTICE:  ---------------------------------------------------------------------------------
33. NOTICE:  ---------------------------------------------------------------------------------
34. NOTICE:  ----------------------分区表：public.t_03,自动化分区开始！！！------------------------
35. NOTICE:  ---------------------------------------------------------------------------------
36. NOTICE:  分区表：public.t_03,保留时长7天,时间间隔1天,预分区时间15天。
37. NOTICE:  该表是分区表。
38. NOTICE:  该表分区字段类型为:bigint
39. NOTICE:  该表的最大分区:t_03_20241216，最小分区:t_03_20241125，分区总数:22。
40. NOTICE:  该表分区增加完成。。。
41. NOTICE:  该表应该最大分区:1734393600，最小分区:1732492800。
42. NOTICE:  应该最小分区名称t_03_20241125
43. NOTICE:  最小分区已删除到: public.t_03_20241125
44. 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';
45. NOTICE:  ---------------------------------------------------------------------------------
46. NOTICE:  ----------------------分区表：public.t_03,自动化分区完成！！！------------------------
47. NOTICE:  ---------------------------------------------------------------------------------
48. NOTICE:  ---------------------------------------------------------------------------------
49. NOTICE:  ----------------------分区表：public.t_04,自动化分区开始！！！------------------------
50. NOTICE:  ---------------------------------------------------------------------------------
51. NOTICE:  分区表：public.t_04,保留时长7天,时间间隔1天,预分区时间15天。
52. NOTICE:  该表是分区表。
53. NOTICE:  该表分区字段类型为:bigint
54. NOTICE:  该表的最大分区:t_04_20241216，最小分区:t_04_20241125，分区总数:22。
55. NOTICE:  该表分区增加完成。。。
56. NOTICE:  该表应该最大分区:1734393600，最小分区:1732492800。
57. NOTICE:  应该最小分区名称t_04_20241125
58. NOTICE:  最小分区已删除到: public.t_04_20241125
59. 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';
60. NOTICE:  ---------------------------------------------------------------------------------
61. NOTICE:  ----------------------分区表：public.t_04,自动化分区完成！！！------------------------
62. NOTICE:  ---------------------------------------------------------------------------------
63. NOTICE:  ...自动化分区执行完成！！！
64. CALL

复制代码

普通表主动化转换分区表乐成

定期增加删除分区乐成

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。