文章目录
- postgreSQL 分区的创建
- 创建分区SQL
- 创建分区索引
- 分区规则
- 创建分区函数
- 函数分析
- （1）通过sql获取最新分区键值关系：
- （2）解析FOR VALUES FROM ('32000000') TO ('34500000')
- （3）查询表中的最大分区键
- （4）判断是否超过阈值
- (5) 创建分区、创建分区索引
- 问题背景
- 数据同步问题分析
- 数据同步问题解决方案
- 造成这次生产事故的原因
- 分区创建失败问题分析
- 问题1: 为什么自动创建的新分区的已经存在的？
- 问题2：为什么函数会生成已经创建过的分区SQL去创建分区？
- 问题3：为什么最新的分区是FOR VALUES FROM ('9500000') TO ('12000000') 而不是 FOR VALUES FROM ('29500000') TO ('32000000')
- 问题4：分区的创建为什么是乱序的？
- 解决问题方案：
postgreSQL 分区的创建
创建分区SQL
CREATE TABLE “np_name”.“t_name_2000000” PARTITION OF “np_name”.“t_name” FOR VALUES FROM (2000000) TO (4500000);
创建分区索引
CREATE UNIQUE INDEX “PK_t_name_2000000” ON “np_name”.“t_name_2000000” USING btree (id);
分区规则
[外链图片转存中…(img-3y0bTWNT-1677228780410)]
re：
FOR VALUES FROM (‘1’) TO (‘2000000’)
FOR VALUES FROM(‘2000000’) TO (‘4500000’)
创建分区函数
CREATE OR REPLACE FUNCTION "cs"."checkapartition"("np_name" varchar, "t_name" varchar, "p_attr" varchar, "critical_percent" numeric, "new_section_span_long" int8)
-- np_name 模式名称；t_name 表名称；p_attr 属性名称；critical_percent 触发创建分区比例系数；new_section_span_long 分区行数
RETURNS "pg_catalog"."varchar" AS $BODY$
DECLARE
partition_expression VARCHAR; -- 分区关系 re：FOR VALUES FROM('2500000') TO ('5000000')
lastest_section_p_min_key bigint; -- 原来最新分区 的最小分区键值
lastest_section_p_max_key bigint; -- 原来最新分区 的最大分区键值
critical_point_p_key bigint; -- 触发分区阀值
actual_max_key bigint; -- actual_max_key 当前分区键值最大值
new_section_table_name VARCHAR; -- 新建分区表名字
BEGIN
-- 查询最新分区设置中的最小界限或者最大界限 表达式 默认后面设置的分区表的oid比前面的小::通用
select pg_get_expr(c.relpartbound, i.inhrelid, true) into partition_expression
from pg_class c , pg_inherits i
where c.oid = i.inhrelid and i.inhparent =
(select oid from pg_class where relname = t_name AND relnamespace =
(select oid from pg_namespace where nspname = np_name))
order by oid desc
limit 1;
-- 解析最小界限 解析最大界限 ::通用
EXECUTE format('SELECT CAST(%L AS bigint);',(regexp_match(SUBSTRING(partition_expression from 'FROM \((.*)\) TO'), '[0-9]+'))[1]) INTO lastest_section_p_min_key ;
EXECUTE format('SELECT CAST(%L AS bigint);',(regexp_match(SUBSTRING(partition_expression from 'TO \((.*)\)'), '[0-9]+'))[1]) INTO lastest_section_p_max_key ;
-- 查询表中的最大分区键,添加分区条件::通用
EXECUTE format('SELECT MAX(%I) from %I.%I where %I >= $1;', p_attr, np_name,t_name, p_attr) into actual_max_key USING lastest_section_p_min_key ;
-- 判断是否超过阈值::通用
critical_point_p_key := lastest_section_p_min_key + (lastest_section_p_max_key - lastest_section_p_min_key) * critical_percent ;
IF actual_max_key IS NULL OR critical_point_p_key > actual_max_key THEN
RETURN actual_max_key;
ELSE
DECLARE
new_section_p_max_key bigint;
new_section_table_pk_constraint_name VARCHAR;
id_idx VARCHAR;
BEGIN
-- 计算分区最大键值::通用
new_section_p_max_key := lastest_section_p_max_key + new_section_span_Long;
-- 分区表名称::通用
new_section_table_name := concat(t_name, '_', lastest_section_p_max_key);
-- 建表::通用
EXECUTE format('CREATE TABLE %I.%I PARTITION OF %I.%I FOR VALUES FROM (%L) TO (%L);',
np_name, new_section_table_name, np_name,t_name,lastest_section_p_max_key,new_section_p_max_key);
-- 主键约束::通用
new_section_table_pk_constraint_name = concat('PK_',new_section_table_name);
EXECUTE format('alter table %I.%I add constraint %I primary key("id");',
np_name, new_section_table_name, new_section_table_pk_constraint_name);
-- 索引
id_idx := concat('id_idx_',lastest_section_p_max_key);
EXECUTE format('CREATE INDEX %I ON %I.%I USING btree (service_order_id);',
id_idx, np_name, new_section_table_name);
END;
RETURN new_section_table_name;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
函数分析
（1）通过sql获取最新分区键值关系：
[外链图片转存中…(img-1Gv7stck-1677228780411)]
获取最新的创建分区的关系：FOR VALUES FROM (‘32000000’) TO (‘34500000’)
备注：pg_class，pg_inherits，pg_namespace 通过postgreSQL中文手册查询
（2）解析FOR VALUES FROM (‘32000000’) TO (‘34500000’)
解析最小界限 解析最大界限
EXECUTE format(‘SELECT CAST(%L AS bigint);’,(regexp_match(SUBSTRING(partition_expression from ‘FROM ((.)) TO’), ‘[0-9]+’))[1]) INTO lastest_section_p_min_key ;
EXECUTE format(‘SELECT CAST(%L AS bigint);’,(regexp_match(SUBSTRING(partition_expression from 'TO ((.))’), ‘[0-9]+’))[1]) INTO lastest_section_p_max_key ;
最小界限 = 32000000 最大界限 34500000
（3）查询表中的最大分区键
EXECUTE format(‘SELECT MAX(%I) from %I.%I where %I >= $1;’, p_attr, np_name,t_name, p_attr) into actual_max_key USING lastest_section_p_min_key ;
得到数据表中最大分区键 lastest_section_p_min_key
（4）判断是否超过阈值
critical_point_p_key := lastest_section_p_min_key + (lastest_section_p_max_key - lastest_section_p_min_key) * critical_percent ;
IF actual_max_key IS NULL OR critical_point_p_key > actual_max_key THEN
RETURN actual_max_key;
ELSE …
(5) 创建分区、创建分区索引
问题背景
00:16:00 收到生产环境分区任务失败预警[外链图片转存中…(img-XiTNIRze-1677228780412)]
08:46:00 收到数据同步失败
[外链图片转存中…(img-s5yjeITu-1677228780412)]
数据同步问题分析
（1） 通过日志排查****069 同步失败原因：不在数据库分区键值范围内
[外链图片转存中…(img-sweNFRYs-1677228780413)]
数据同步问题解决方案
临时方案：脚本提交创建新的分区
[外链图片转存中…(img-iyVmN3da-1677228780413)]
造成这次生产事故的原因
自动分区创建失败，造成数据同步出错
分区创建失败问题分析
[外链图片转存中…(img-pFAe1EPF-1677228780414)]
问题1: 为什么自动创建的新分区的已经存在的？
（CREATE TABLE _12000000 PARTITION OF **** FOR VALUES FROM (‘12000000’) TO (‘14500000’);
通过查询表的分区
[外链图片转存中…(img-UPQLsJvW-1677228780414)]
数据库中确实存在FOR VALUES FROM (‘12000000’) TO (‘14500000’)这个分区
问题2：为什么函数会生成已经创建过的分区SQL去创建分区？
回归函数执行步骤查找问题：
函数执行第一步骤（1）通过sql获取最新分区键值关系：
SELECT
pg_get_expr ( , , TRUE )
FROM
pg_class C,
pg_inherits i
WHERE
=
AND = ( SELECT oid FROM pg_class WHERE relname = ‘****’ AND relnamespace = ( SELECT oid FROM pg_namespace WHERE nspname = ‘**’ ) )
ORDER BY
oid DESC
LIMIT 1;
获取最新的创建分区的关系：FOR VALUES FROM (‘9500000’) TO (‘12000000’)
问题3：为什么最新的分区是FOR VALUES FROM (‘9500000’) TO (‘12000000’) 而不是 FOR VALUES FROM (‘29500000’) TO (‘32000000’)
最新的分区键值应该是最大的才对，目前从排序来看，分区的创建时乱序的。
[外链图片转存中…(img-N7JXDzhI-1677228780415)]
问题4：分区的创建为什么是乱序的？
2022年运维做数据库迁移（金山云->腾讯云）
数据库云迁移时候把分区的创建顺序打乱了
解决问题方案：
提交SQL脚本新增最新分区，从而解决自动创建分区时获取最新分区的分区键值不是最大的问题。