涉及表：
history_uint
连接信息
PGPASSWORD=123 /usr/local/pg12/bin/psql -d zabbix -U zabbixadmin -p 5435 -h localhost
备份表
这里的备份只是应急用大概率用不到
-- 创建备份表 CREATE TABLE history_uint_20230816 ( itemid bigint not null, clock integer not null default 0, value numeric(20,0) not null default 0, ns integer not null default 0 ); -- 插入数据 insert into history_uint_20230816 select * from history_uint; -- 对比数据 select count(*) from history_uint; select count(*) from history_uint_20230816;
获取时间最久的值
按时间排序获取最久的时间的数据值，这里确定的是分区开始的时间
2023-05-18 12:14:16 zabbix=> select * from history_uint order by clock limit 1; itemid | clock | value | ns --------+------------+-------+----------- 57376 | 1684383256 | 0 | 575701329
获取数据量
获取数据量
select count(*) from history_uint; zabbix=> select count(*) from history; count ----------- 110263979 (1 row) 3亿1千万
停止zabbix
systemctl stop zabbix-server
修改表名为老表
-- history_uint ALTER TABLE history_uint RENAME TO history_uint_old;
创建父表
-- 创建父表 CREATE TABLE history_uint ( itemid bigint not null, clock integer not null default 0, value numeric(20,0) not null default 0, ns integer not null default 0 )PARTITION BY RANGE (clock); -- 删除表 -- drop table history_uint;
创建分区表
使用函数计算并拼接出创建分区的SQL语句
DO $$ DECLARE -- 开始时间 start_date timestamp := '2023-05-14'::timestamp; -- 结束时间 end_date timestamp := '2023-12-31'::timestamp; current_datea timestamp; table_names text; BEGIN FOR current_datea IN SELECT generate_series(start_date, end_date, '1 week'::interval) AS week_start LOOP -- 将每周的开始时间定义为 每周1的00:00:00 current_datea := date_trunc('week', current_datea) - interval '8 hours'; -- 定义表名并拼接为年份和周数 table_names := 'history_uint_week_' || EXTRACT(YEAR FROM current_datea) || EXTRACT(WEEK FROM current_datea); -- 生成创建分区表的SQL RAISE NOTICE '%', format('CREATE TABLE %I PARTITION OF history_uint FOR VALUES FROM (%s) TO (%s);', table_names, EXTRACT(EPOCH FROM current_datea), EXTRACT(EPOCH FROM current_datea) + 604800); END LOOP; END $$;
SQL详情
使用上面的循环得来的SQL语句
CREATE TABLE history_uint_week_202318 PARTITION OF history_uint FOR VALUES FROM (1683475200) TO (1684080000); CREATE TABLE history_uint_week_202319 PARTITION OF history_uint FOR VALUES FROM (1684080000) TO (1684684800); CREATE TABLE history_uint_week_202320 PARTITION OF history_uint FOR VALUES FROM (1684684800) TO (1685289600); CREATE TABLE history_uint_week_202321 PARTITION OF history_uint FOR VALUES FROM (1685289600) TO (1685894400); CREATE TABLE history_uint_week_202322 PARTITION OF history_uint FOR VALUES FROM (1685894400) TO (1686499200); CREATE TABLE history_uint_week_202323 PARTITION OF history_uint FOR VALUES FROM (1686499200) TO (1687104000); CREATE TABLE history_uint_week_202324 PARTITION OF history_uint FOR VALUES FROM (1687104000) TO (1687708800); CREATE TABLE history_uint_week_202325 PARTITION OF history_uint FOR VALUES FROM (1687708800) TO (1688313600); CREATE TABLE history_uint_week_202326 PARTITION OF history_uint FOR VALUES FROM (1688313600) TO (1688918400); CREATE TABLE history_uint_week_202327 PARTITION OF history_uint FOR VALUES FROM (1688918400) TO (1689523200); CREATE TABLE history_uint_week_202328 PARTITION OF history_uint FOR VALUES FROM (1689523200) TO (1690128000); CREATE TABLE history_uint_week_202329 PARTITION OF history_uint FOR VALUES FROM (1690128000) TO (1690732800); CREATE TABLE history_uint_week_202330 PARTITION OF history_uint FOR VALUES FROM (1690732800) TO (1691337600); CREATE TABLE history_uint_week_202331 PARTITION OF history_uint FOR VALUES FROM (1691337600) TO (1691942400); CREATE TABLE history_uint_week_202332 PARTITION OF history_uint FOR VALUES FROM (1691942400) TO (1692547200); CREATE TABLE history_uint_week_202333 PARTITION OF history_uint FOR VALUES FROM (1692547200) TO (1693152000); CREATE TABLE history_uint_week_202334 PARTITION OF history_uint FOR VALUES FROM (1693152000) TO (1693756800); CREATE TABLE history_uint_week_202335 PARTITION OF history_uint FOR VALUES FROM (1693756800) TO (1694361600); CREATE TABLE history_uint_week_202336 PARTITION OF history_uint FOR VALUES FROM (1694361600) TO (1694966400); CREATE TABLE history_uint_week_202337 PARTITION OF history_uint FOR VALUES FROM (1694966400) TO (1695571200); CREATE TABLE history_uint_week_202338 PARTITION OF history_uint FOR VALUES FROM (1695571200) TO (1696176000); CREATE TABLE history_uint_week_202339 PARTITION OF history_uint FOR VALUES FROM (1696176000) TO (1696780800); CREATE TABLE history_uint_week_202340 PARTITION OF history_uint FOR VALUES FROM (1696780800) TO (1697385600); CREATE TABLE history_uint_week_202341 PARTITION OF history_uint FOR VALUES FROM (1697385600) TO (1697990400); CREATE TABLE history_uint_week_202342 PARTITION OF history_uint FOR VALUES FROM (1697990400) TO (1698595200); CREATE TABLE history_uint_week_202343 PARTITION OF history_uint FOR VALUES FROM (1698595200) TO (1699200000); CREATE TABLE history_uint_week_202344 PARTITION OF history_uint FOR VALUES FROM (1699200000) TO (1699804800); CREATE TABLE history_uint_week_202345 PARTITION OF history_uint FOR VALUES FROM (1699804800) TO (1700409600); CREATE TABLE history_uint_week_202346 PARTITION OF history_uint FOR VALUES FROM (1700409600) TO (1701014400); CREATE TABLE history_uint_week_202347 PARTITION OF history_uint FOR VALUES FROM (1701014400) TO (1701619200); CREATE TABLE history_uint_week_202348 PARTITION OF history_uint FOR VALUES FROM (1701619200) TO (1702224000); CREATE TABLE history_uint_week_202349 PARTITION OF history_uint FOR VALUES FROM (1702224000) TO (1702828800); CREATE TABLE history_uint_week_202350 PARTITION OF history_uint FOR VALUES FROM (1702828800) TO (1703433600); CREATE TABLE history_uint_week_202351 PARTITION OF history_uint FOR VALUES FROM (1703433600) TO (1704038400);
导入数据
insert into history_uint select * from history_uint_old;
-- 按月导入 -- 小于等于1687017600 insert into history_uint select * from history_uint_old where clock<=1687017600; -- 大于1687017600 and 小于1689609600 insert into history_uint select * from history_uint_old where clock>1687017600 and clock<1689609600; -- 大于1689609600 insert into history_uint select * from history_uint_old where clock>=1689609600;
对比数据
zabbix=> select count(*) from history_uint; count ----------- 307234801 (1 row) zabbix=> select count(*) from history_uint_old; count ----------- 307234801 (1 row)
创建索引
pg中索引名全局不能重复
-- history CREATE INDEX "idx_history_uint_new_itemid_clock1" on "history_uint" USING btree(itemid, clock);
启动zabbix
systemctl start zabbix-server
定时创建分区表
定时脚本创建分区表每周一，添加到定时任务即可 !
vim history_uint_create_partiton_table.sh #! /bin/bash TableName='history_uint' # 获取下周年份 NextYear=$(date -d "next week" +%Y) # 获取下周时间 NextWeek=$(date -d "next week" +%V) NextWeekDate=$(date -d "next week" +%Y-%m-%d) # 下下周的时间 NextNextWeek=$(date -d "+2 week" +%V) NextNextWeekDate=$(date -d "+2 weeks" +%Y-%m-%d) # 本周时间戳 WeekTimestamp=$(date -d "${WeekDate}" +%s) # 下周时间戳 NextWeekTimestamp=$(date -d "${NextWeekDate}" +%s) # 下下周时间戳 NextNextWeekTimestamp=$(date -d "${NextNextWeekDate}" +%s) # 创建分区表 #PGPASSWORD=123 /usr/local/pg12/bin/psql -d zabbix -U zabbixadmin -p 5435 -h localhost -c "CREATE TABLE ${TableName}_week_${NextYear}${NextWeek} PARTITION OF ${TableName} FOR VALUES FROM (${NextWeekTimestamp}) TO (${NextNextWeekTimestamp});" >>/data/pg_data/5435/scripts/${TableName}.log 2>&1 # 显示创建分区表SQL echo "CREATE TABLE ${TableName}_week_${NextYear}${NextWeek} PARTITION OF ${TableName} FOR VALUES FROM (${NextWeekTimestamp}) TO (${NextNextWeekTimestamp});"
添加到定时任务
# crontab -e 0 0 * * 1 /bin/sh /data/pg_data/5435/scripts/history_create_partiton_table.sh 0 0 * * 1 /bin/sh /data/pg_data/5435/scripts/history_uint_create_partiton_table.sh
