zabbix5.0之PostgreSQL表分区操作

创建表结构

drop table if exists history;
drop table if exists history_str;
drop table if exists history_log;
drop table if exists history_text;
drop table if exists history_uint;
drop table if exists trends;
drop table if exists trends_uint;

-- history 表
CREATE TABLE history
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    value double precision NOT NULL DEFAULT '0'::double precision,
    ns integer NOT NULL DEFAULT 0
) PARTITION BY RANGE (clock);

CREATE INDEX history_1 ON history USING btree (itemid ASC NULLS LAST, clock ASC NULLS LAST);
CREATE TABLE history_default PARTITION OF history DEFAULT;
 
-- history_str 表
CREATE TABLE history_str
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    value character varying(255) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::character varying,
    ns integer NOT NULL DEFAULT 0
)PARTITION BY RANGE (clock);
 
CREATE INDEX history_str_1 ON history_str USING btree (itemid ASC NULLS LAST, clock ASC NULLS LAST);
CREATE TABLE history_str_default PARTITION OF history_str DEFAULT;
 
-- history_log 表
CREATE TABLE history_log
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    "timestamp" integer NOT NULL DEFAULT 0,
    source character varying(64) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::character varying,
    severity integer NOT NULL DEFAULT 0,
    value text COLLATE pg_catalog."default" NOT NULL DEFAULT ''::text,
    logeventid integer NOT NULL DEFAULT 0,
    ns integer NOT NULL DEFAULT 0
) PARTITION BY RANGE (clock);

CREATE INDEX history_log_1 ON history_log USING btree (itemid ASC NULLS LAST, clock ASC NULLS LAST);
CREATE TABLE history_log_default PARTITION OF history_log DEFAULT;
 
-- history_text 表
CREATE TABLE history_text
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    value text COLLATE pg_catalog."default" NOT NULL DEFAULT ''::text,
    ns integer NOT NULL DEFAULT 0
)PARTITION BY RANGE (clock);
 
CREATE INDEX history_text_1 ON history_text USING btree (itemid ASC NULLS LAST, clock ASC NULLS LAST);
CREATE TABLE history_text_default PARTITION OF history_text DEFAULT;

-- history_uint 表
CREATE TABLE history_uint
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    value numeric(20,0) NOT NULL DEFAULT '0'::numeric,
    ns integer NOT NULL DEFAULT 0
)PARTITION BY RANGE (clock);

CREATE INDEX history_uint_1 ON history_uint USING btree (itemid ASC NULLS LAST, clock ASC NULLS LAST);
CREATE TABLE history_uint_default PARTITION OF history_uint DEFAULT;

-- trends 表
CREATE TABLE trends
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    num integer NOT NULL DEFAULT 0,
    value_min double precision NOT NULL DEFAULT '0'::double precision,
    value_avg double precision NOT NULL DEFAULT '0'::double precision,
    value_max double precision NOT NULL DEFAULT '0'::double precision,
    CONSTRAINT trends_pkey PRIMARY KEY (itemid, clock)
)PARTITION BY RANGE (clock);

CREATE TABLE trends_default PARTITION OF trends DEFAULT;
 
-- trends_uint 表
CREATE TABLE trends_uint
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    num integer NOT NULL DEFAULT 0,
    value_min numeric(20,0) NOT NULL DEFAULT '0'::numeric,
    value_avg numeric(20,0) NOT NULL DEFAULT '0'::numeric,
    value_max numeric(20,0) NOT NULL DEFAULT '0'::numeric,
    CONSTRAINT trends_uint_pkey PRIMARY KEY (itemid, clock)
)PARTITION BY RANGE (clock);

CREATE TABLE trends_uint_default PARTITION OF trends_uint DEFAULT;

存储过程

partition_create

CREATE OR REPLACE PROCEDURE partition_create(tablename character varying,partitionname character varying,clock integer)
LANGUAGE 'plpgsql'
AS $BODY$DECLARE RETROWS INT;
DECLARE STRCLOCK VARCHAR = CLOCK;
DECLARE LESSCLOCK VARCHAR = CLOCK + 86400;
DECLARE tableCreateSQL VARCHAR;
BEGIN
        SELECT COUNT(1) INTO RETROWS
        FROM pg_inherits JOIN pg_class parent
        ON pg_inherits.inhparent = parent.oid JOIN pg_class child
        ON pg_inherits.inhrelid = child.oid JOIN pg_namespace nmsp_parent
        ON nmsp_parent.oid = parent.relnamespace JOIN pg_namespace nmsp_child
        ON nmsp_child.oid = child.relnamespace WHERE parent.relname = TABLENAME  and child.relname = PARTITIONNAME;
        IF RETROWS = 0 THEN
            tableCreateSQL := 'create table ' || PARTITIONNAME || ' PARTITION OF ' || tablename || ' FOR VALUES FROM (' || STRCLOCK || ') TO (' || LESSCLOCK || ')';
            RAISE NOTICE '开始创建表%',tableCreateSQL;
            EXECUTE(tableCreateSQL);
            RAISE NOTICE '成功创建表%',tableCreateSQL;
        END IF;
END
$BODY$;

partition_drop

CREATE OR REPLACE PROCEDURE partition_drop(tablename character varying,partitionname character varying)
LANGUAGE 'plpgsql'
AS $BODY$
        DECLARE RETROWS INT;
        DECLARE tableDropSQL VARCHAR;
BEGIN
        SELECT COUNT(1) INTO RETROWS
        FROM pg_inherits JOIN pg_class parent
        ON pg_inherits.inhparent = parent.oid JOIN pg_class child
        ON pg_inherits.inhrelid = child.oid JOIN pg_namespace nmsp_parent
        ON nmsp_parent.oid = parent.relnamespace JOIN pg_namespace nmsp_child
        ON nmsp_child.oid = child.relnamespace WHERE parent.relname = tablename  and child.relname = partitionname;
       IF RETROWS = 1 THEN
            tableDropSQL := 'drop table ' || PARTITIONNAME;
            RAISE NOTICE '开始删除表%',tableDropSQL;
            EXECUTE(tableDropSQL);
            RAISE NOTICE '成功删除表%',tableDropSQL;
        END IF;
END
$BODY$;

partition_maintenance

原逻辑：

CREATE OR REPLACE PROCEDURE partition_maintenance(table_name character varying,keep_data_days integer)
LANGUAGE 'plpgsql'
AS $BODY$
        DECLARE PARTITION_NAME VARCHAR(64);
        DECLARE NEXT_DATE VARCHAR;
        DECLARE NEXT_TIMESTAMP INT;
        DECLARE HISTORY_PARTITION_NAME VARCHAR(64);
        DECLARE RETROWS INT;
        DECLARE DATE_TIMESTAMP INT;
        DECLARE DATE_PARTITION_NAME VARCHAR(64);
BEGIN
       select to_char(now() + interval '1 d','yyyyMMdd') INTO NEXT_DATE;
       select floor(extract(epoch from to_timestamp(to_char(now() + interval  '1 d','yyyyMMdd'),'yyyyMMdd'))) INTO NEXT_TIMESTAMP;
       select TABLE_NAME||to_char(now() + interval '1 d','yyyyMMdd') INTO PARTITION_NAME;
       CALL partition_create(TABLE_NAME, PARTITION_NAME, NEXT_TIMESTAMP);
        select TABLE_NAME||to_char(now(),'yyyyMMdd') INTO DATE_PARTITION_NAME;
        SELECT COUNT(1) INTO RETROWS
        FROM pg_inherits JOIN pg_class parent
        ON pg_inherits.inhparent = parent.oid JOIN pg_class child
        ON pg_inherits.inhrelid = child.oid JOIN pg_namespace nmsp_parent
        ON nmsp_parent.oid = parent.relnamespace JOIN pg_namespace nmsp_child
        ON nmsp_child.oid = child.relnamespace WHERE parent.relname = table_name  and child.relname = DATE_PARTITION_NAME;
        IF RETROWS = 0 THEN
    select floor(extract(epoch from to_timestamp(to_char(now(),'yyyyMMdd'),'yyyyMMdd'))) INTO DATE_TIMESTAMP;
    CALL partition_create(TABLE_NAME, DATE_PARTITION_NAME, DATE_TIMESTAMP);
        END IF;
        EXECUTE 'select to_char(now() - interval ''' || KEEP_DATA_DAYS || '  d'''||','||'''yyyyMMdd'''||')'|| '' INTO HISTORY_PARTITION_NAME;
       HISTORY_PARTITION_NAME := table_name||HISTORY_PARTITION_NAME;
       CALL partition_drop(table_name,HISTORY_PARTITION_NAME);
END
$BODY$;

修改后逻辑：

CREATE OR REPLACE PROCEDURE partition_maintenance(table_name varchar,keep_data_days int,create_part_days int)
LANGUAGE 'plpgsql'
AS $BODY$
        DECLARE PARTITION_NAME VARCHAR(64);
                 NEXT_TIMESTAMP INT;
                 RETROWS INT;
                 DATE_TIMESTAMP INT;
                 DATE_PARTITION_NAME VARCHAR(64);
				 loop_cons int;
				 tmp_date TIMESTAMP;
				 keep_end_date date;
				 cur cursor for (select inhrelid::regclass::text from pg_inherits where inhparent = 'history'::regclass and inhrelid::regclass::text ~ '\d+');
BEGIN
-- 创建当日分区逻辑
        select TABLE_NAME||to_char(now(),'yyyyMMdd') INTO DATE_PARTITION_NAME;
        SELECT COUNT(1) INTO RETROWS
        FROM pg_inherits JOIN pg_class parent
        ON pg_inherits.inhparent = parent.oid JOIN pg_class child
        ON pg_inherits.inhrelid = child.oid JOIN pg_namespace nmsp_parent
        ON nmsp_parent.oid = parent.relnamespace JOIN pg_namespace nmsp_child
        ON nmsp_child.oid = child.relnamespace WHERE parent.relname = table_name  and child.relname = DATE_PARTITION_NAME;
        IF RETROWS = 0 THEN
          select floor(extract(epoch from to_timestamp(to_char(now(),'yyyyMMdd'),'yyyyMMdd'))) INTO DATE_TIMESTAMP;
          CALL partition_create(TABLE_NAME, DATE_PARTITION_NAME, DATE_TIMESTAMP);
        END IF;
-- 根据create_part_days参数，创建当前日期后create_part_days天的分区
       select floor(extract(epoch from to_timestamp(to_char(now() + interval  '1 d','yyyyMMdd'),'yyyyMMdd'))) INTO NEXT_TIMESTAMP;
	   loop_cons := NEXT_TIMESTAMP+create_part_days*86400;
	   tmp_date := now();
	   while loop_cons > NEXT_TIMESTAMP LOOP
         select TABLE_NAME||to_char(tmp_date + interval '1 d','yyyyMMdd') INTO PARTITION_NAME;
         CALL partition_create(TABLE_NAME, PARTITION_NAME, NEXT_TIMESTAMP);
		 NEXT_TIMESTAMP := NEXT_TIMESTAMP+86400;
		 tmp_date := tmp_date + interval '1 d';
	   end loop;
-- 根据keep_data_days参数，删除当前日期前keep_data_days天的分区
        EXECUTE 'select (now() - interval ''' || KEEP_DATA_DAYS || '  d'''||')::date'|| '' INTO keep_end_date;
		for recordvar in cur loop
		  if substring(recordvar::text from '\d+')::date < keep_end_date THEN
		    CALL partition_drop(table_name,table_name||to_char(substring(recordvar::text from '\d+')::date,'yyyymmdd'));
		  end if;
		end loop;
END
$BODY$;

partition_maintenance_all

CREATE OR REPLACE PROCEDURE partition_maintenance_all()
LANGUAGE 'plpgsql'
AS $BODY$
   begin
    CALL partition_maintenance('history',5,10);
    end;
$BODY$;

首次执行过程

call partition_maintenance_all();

创建测试数据

CREATE TABLE history
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    value double precision NOT NULL DEFAULT '0'::double precision,
    ns integer NOT NULL DEFAULT 0
) PARTITION BY RANGE (clock);

CREATE INDEX history_1 ON history USING btree (itemid ASC NULLS LAST, clock ASC NULLS LAST);
CREATE TABLE history_default PARTITION OF history DEFAULT;
CREATE TABLE history
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    value double precision NOT NULL DEFAULT '0'::double precision,
    ns integer NOT NULL DEFAULT 0
) PARTITION BY RANGE (clock);

CREATE INDEX history_1 ON history USING btree (itemid ASC NULLS LAST, clock ASC NULLS LAST);
CREATE TABLE history_default PARTITION OF history DEFAULT;

create table history20250521 partition of history for values from (1747756800) to (1747843200);
create table history20250522 partition of history for values from (1747843200) to (1747929600);
create table history20250523 partition of history for values from (1747929600) to (1748016000);
create table history20250524 partition of history for values from (1748016000) to (1748102400);
create table history20250525 partition of history for values from (1748102400) to (1748188800);
create table history20250526 partition of history for values from (1748188800) to (1748275200);
create table history20250527 partition of history for values from (1748275200) to (1748361600);

参考资料

https://blog.csdn.net/musen208428/article/details/119678747

posted @ 2025-05-28 21:28 kahnyao 阅读(24) 评论(0) 收藏举报

刷新页面返回顶部

knlbase

zabbix5.0之PostgreSQL表分区操作

创建表结构

存储过程

partition_create

partition_drop

partition_maintenance

partition_maintenance_all

首次执行过程

创建测试数据

参考资料