删除原表
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
zabbix5.0之postgresql表分区操作详情(存储过程、定时任务)
最新推荐文章于 2024-04-28 10:02:38 发布