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_info
    OWNER to postgres;

COMMENT ON COLUMN managerdb.par_info.table_schema
    IS '模式名';

COMMENT ON COLUMN managerdb.par_info.table_name
    IS '表名';

COMMENT ON COLUMN managerdb.par_info.par_column
    IS '分区字段名';

COMMENT ON COLUMN managerdb.par_info.keep_data_days
    IS '分区保留时长';

COMMENT ON COLUMN managerdb.par_info.step_length
    IS '步长';

COMMENT ON COLUMN managerdb.par_info.create_next_intervals
    IS '预分区时间';

COMMENT ON COLUMN managerdb.par_info.update_time
    IS '更新时间';

COMMENT ON COLUMN managerdb.par_info.min_partition_name
    IS '最小分区';

COMMENT ON COLUMN managerdb.par_info.max_partition_name
    IS '最大分区';
-- 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_idx
    ON 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 varying
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
 
declare
tt oid ;
aname character varying default '';
 
begin
       tt := 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 varying
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 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 1