zabbix5.0之postgresql表分区操作详情(存储过程、定时任务）

删除原表
psql -U zabbix -d zabbix  //登录数据库
drop table history;
drop table history_str;
drop table history_log;
drop table history_text;
drop table history_uint;
drop table trends;
drop table trends_uint;

创建独立的表空间
CREATE TABLESPACE zabbix_tbs
  OWNER zabbix
  LOCATION '/app/postgresql-12.3/data';
 
ALTER TABLESPACE zabbix_tbs
  OWNER TO zabbix;

重建删除的表
--history 表
CREATE TABLE public.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)
TABLESPACE zabbix_tbs;
ALTER TABLE public.history
    OWNER to zabbix;
CREATE INDEX history_1
    ON public.history USING btree
    (itemid ASC NULLS LAST, clock ASC NULLS LAST)
    TABLESPACE zabbix_tbs;
-- Partitions SQL
CREATE TABLE public.history_default PARTITION OF public.history
    DEFAULT;
 
--history_str 表
CREATE TABLE public.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)
TABLESPACE zabbix_tbs;
ALTER TABLE public.history_str
    OWNER to zabbix;
 
CREATE INDEX history_str_1
    ON public.history_str USING btree
    (itemid ASC NULLS LAST, clock ASC NULLS LAST)
    TABLESPACE zabbix_tbs;
 
-- Partitions SQL
CREATE TABLE public.history_str_default PARTITION OF public.history_str
    DEFAULT;
 
--history_log 表
CREATE TABLE public.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)
 
TABLESPACE zabbix_tbs;
ALTER TABLE public.history_log
    OWNER to zabbix;
CREATE INDEX history_log_1
    ON public.history_log USING btree
    (itemid ASC NULLS LAST, clock ASC NULLS LAST)
    TABLESPACE zabbix_tbs;
 
-- Partitions SQL
CREATE TABLE public.history_log_default PARTITION OF public.history_log
    DEFAULT;
 
--history_text 表
CREATE TABLE public.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)
 
TABLESPACE zabbix_tbs;
 
ALTER TABLE public.history_text
    OWNER to zabbix;
CREATE INDEX history_text_1
    ON public.history_text USING btree
    (itemid ASC NULLS LAST, clock ASC NULLS LAST)
    TABLESPACE zabbix_tbs;
 
-- Partitions SQL
CREATE TABLE public.history_text_default PARTITION OF public.history_text
    DEFAULT;
 
 
--history_uint 表
CREATE TABLE public.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)
 
TABLESPACE zabbix_tbs;
ALTER TABLE public.history_uint
    OWNER to zabbix;
CREATE INDEX history_uint_1
    ON public.history_uint USING btree
    (itemid ASC NULLS LAST, clock ASC NULLS LAST)
    TABLESPACE zabbix_tbs;
 
-- Partitions SQL
CREATE TABLE public.history_uint_default PARTITION OF public.history_uint
    DEFAULT;
 
 
--trends 表
CREATE TABLE public.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)
 
TABLESPACE zabbix_tbs;
ALTER TABLE public.trends
    OWNER to zabbix;
 
-- Partitions SQL
CREATE TABLE public.trends_default PARTITION OF public.trends
    DEFAULT;
 
--trends_uint 表
CREATE TABLE public.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)
 
TABLESPACE zabbix_tbs;
ALTER TABLE public.trends_uint
    OWNER to zabbix;
 
-- Partitions SQL
CREATE TABLE public.trends_uint_default PARTITION OF public.trends_uint
    DEFAULT;


创建存储过程
psql -U zabbix -d zabbix

--创建存储过程partition_create
 
CREATE OR REPLACE PROCEDURE public.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 public.' || 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 public.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 public.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$;
 
 
--创建存储过程partition_maintenance_all
 
CREATE OR REPLACE PROCEDURE public.partition_maintenance_all(
    )
LANGUAGE 'plpgsql'
AS $BODY$    begin
                CALL partition_maintenance('history', 90);
    CALL partition_maintenance('history_log', 90);
    CALL partition_maintenance('history_str', 90);
    CALL partition_maintenance('history_text', 90);
    CALL partition_maintenance('history_uint', 90);
    CALL partition_maintenance('trends', 90);
    CALL partition_maintenance('trends_uint', 90);
    end;
$BODY$;

首次执行过程
call partition_maintenance_all()；


添加定时任务
su - postgres
vi /home/postgres/.procedure
 
#!/bin/bash
source ~/.bash_profile
psql "user=zabbix password=zabbix123 host=localhost port=5432" -c "call partition_maintenance_all()";
 
保存并退出编辑

添加定时任务
crontab -e  //编辑定时任务，并加入如下内容，保存退出（添加多条，防止执行失败未创建分区）
 
30 10 * * * /bin/sh /home/postgres/.procedure
30 21 * * * /bin/sh /home/postgres/.procedure
30 23 * * * /bin/sh /home/postgres/.procedure