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