postgreSQL 创建分区实践

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 ( C.relpartbound, i.inhrelid, TRUE )
FROM
pg_class C,
pg_inherits i
WHERE
C.oid = i.inhrelid
AND i.inhparent = ( 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脚本新增最新分区，从而解决自动创建分区时获取最新分区的分区键值不是最大的问题。