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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/70000068/viewspace-2776789/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL定時任務與儲存過程例項MySql儲存過程
- 利用排程任務定時刪除分割槽
- Greenplum儲存過程使用分割槽表將進行全表掃描儲存過程
- postgresql分割槽表SQL
- Sql儲存過程分頁--臨時表儲存SQL儲存過程
- PostgreSQL獲取建表語句儲存過程SQL儲存過程
- Java定時任務排程詳解Java
- mysql-定時呼叫儲存過程MySql儲存過程
- MySQL儲存過程詳解 mysql 儲存過程MySql儲存過程
- SQL Server表分割槽操作詳解SQLServer
- 定時任務操作
- 當前EVA4400儲存分割槽情況
- PostgreSQL/LightDB 分割槽表之分割槽裁剪SQL
- 在ORACLE裡用儲存過程定期分割表(轉)Oracle儲存過程
- INTERVAL分割槽表鎖分割槽操作
- java操作儲存過程Java儲存過程
- SQL Server表分割槽刪除詳情DSCCSQLServer
- PostgreSQL:傳統分割槽表SQL
- PostgreSQL:內建分割槽表SQL
- MySQL儲存過程詳解 mysql 儲存過程linkMySql儲存過程
- LightDB/PostgreSQL 相容Oracle儲存過程SQLOracle儲存過程
- MySql資料分割槽操作之新增分割槽操作MySql
- Oracle:建立JOB定時執行儲存過程Oracle儲存過程
- [MSSQL]sql 儲存過程定時執行方法SQL儲存過程
- PostgreSQL分割槽表更新思路SQL
- postgresql分割槽表實現方式SQL
- MySQL的nnodb引擎表資料分割槽儲存MySql
- PostgreSQL/LightDB分割槽表之常見問題SQL
- MySQL之儲存過程MySql儲存過程
- mongo 儲存過程詳解Go儲存過程
- mysql儲存過程詳解MySql儲存過程
- 儲存過程的詳解儲存過程
- 把自編儲存過程設定為系統儲存過程儲存過程
- EMC儲存重灌系統丟失分割槽的資料恢復過程資料恢復
- Oracle 定時任務詳解Oracle
- 定時任務crontab 詳解
- Spark操作Hive分割槽表SparkHive
- 分割槽表attach detach操作