使用pg_partman管理PostgreSQL分区
pg_partman分区数据插入流程
为了更好帮助阅读者使用pg_partman，下面先介绍分区管理流程
-
插入数据:
当插入的数据超过当前最新分区的范围时，这些数据会暂时被插入到默认分区，这是因为此时还没有适合该数据的分区存在
-
运行维护函数:
执行
SELECT partman.run_maintenance();函数。这个函数会检查所有由 pg_partman 管理的分区表，并根据需要创建新的分区 -
分区扩展:
如果
run_maintenance函数检测到需要创建新的分区（例如，有数据落在默认分区，或根据预设规则即将到达需要创建新分区的时间点），它会自动创建这些新分区 -
数据迁移:
新分区创建后，原先落在默认分区的数据（如果有的话）通常需要手动迁移到正确的分区。pg_partman 提供了一些工具来帮助执行这种数据迁移，但这可能需要额外的步骤。
-
后续插入:
一旦新分区创建完成，后续插入的数据（如果落在新分区的时间范围内）将直接被插入到相应的新分区中
数据添加到了默认分区会影响性能，请及时迁移
开启插件
阿里云和AWS的RDS数据库，都可以使用以下SQL进行插件安装，连接上数据库执行下面的指令，注意连接数据库需要使用高权限账号，否则会提示权限不足
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;
使用pg_partman进行分区
先建立一个测试表
pg_partman对分区表支持两种模式，分别是native和partman，native是原生的由pgsql提供的，性能会更好，partman是由插件提供，性能没原生的好，但是功能多
native模式–建表
DROP TABLE IF EXISTS "log"."t_log";
CREATE TABLE "log"."t_log" (
"id" int8 NOT NULL,
"mac" varchar(255) COLLATE "pg_catalog"."default",
"create_time" timestamp(6) NOT NULL
) PARTITION BY RANGE ("create_time");
COMMENT ON COLUMN "log"."t_log"."mac" IS '设备mac';
COMMENT ON COLUMN "log"."t_log"."create_time" IS '创建时间';
COMMENT ON TABLE "log"."t_log" IS '设备连接日志';
--
ALTER TABLE "log"."t_log" ADD CONSTRAINT "t_device_connect_pkey" PRIMARY KEY ("id", "create_time");
partman模式–建表
DROP TABLE IF EXISTS "log"."t_log";
CREATE TABLE "log"."t_log" (
"id" int8 NOT NULL,
"mac" varchar(255) COLLATE "pg_catalog"."default",
"create_time" timestamp(6) NOT NULL
);
COMMENT ON COLUMN "log"."t_log"."mac" IS '设备mac';
COMMENT ON COLUMN "log"."t_log"."create_time" IS '创建时间';
COMMENT ON TABLE "log"."t_log" IS '设备连接日志';
--
ALTER TABLE "log"."t_log" ADD CONSTRAINT "t_device_connect_pkey" PRIMARY KEY ("id");
按照create_time分表
SELECT partman.create_parent(p_parent_table => 'log.t_log',
p_control => 'create_time',
p_type => 'native',
p_interval=> 'daily',
p_premake => 10);
p_parent_table– 父分区表。此表必须已存在并完全限定（包括架构在内p_control– 分区所依据的列。数据类型必须是整数或基于时间的p_type– 该类型是'native'或者'partman'p_interval– 每个分区的时间间隔或整数范围。示例值包括yearly、monthly、weekly、daily、hourly、minutely等p_premake– 为支持新插入而提前创建的分区数量
开启分区扩展功能
可以理解为半自动，插件帮助我们可以更方便的去扩展分区，使用指令partman.run_maintenance()会对分表进行检查，满足条件就进行扩展，但是做不到自动扩展，需要配合插件pg_corn定时调度来实现自动扩展
native的扩展规则：根据当前时间进行扩展
partman的扩展规则：根据当前时间和默认子表中的数据分区键值进行扩展
-- 更新part_config配置
UPDATE partman.part_config
SET infinite_time_partitions = TRUE,
retention = '3 months',
retention_keep_table = TRUE
WHERE
parent_table = 'log.t_log';
infinite_time_partitions = true, – 将表配置为能够在没有任何限制的情况下自动创建新分区。
retention = '3 months', – 将表配置为最长保留三个月。
retention_keep_table=true– t-过期表仍然包含它们原有的数据，只是不会有新数据插入 f-删除过期的表
定时任务调度
1.首先找到PostgreSQL数据库实例关联的参数，向shared_preload_libraries参数值添加 pg_cron，重新启动
2.创建定时任务调度，SQL如下
-- 安装pg_cron,一般是在postgres数据库创建，执行下面的指令都需要在postgres下执行
CREATE EXTENSION pg_cron;
-- 添加定时任务
-- 每小时调度一次
SELECT cron.schedule('0 * * * *', $$CALL partman.run_maintenance_proc()$$);
-- 每分钟调度一次
SELECT cron.schedule('* * * * *', $$CALL partman.run_maintenance_proc()$$);
-- 更新调度目标数据库
UPDATE cron.job SET database = 'target_data_name' WHERE jobid = 1; -- jobid是任务id
验证分表
native模式不能直接看到子表，需要运行下面的查询SQL，可以查询到创建出来的分表
SELECT
nmsp_parent.nspname AS parent_schema,
parent.relname AS parent_table,
nmsp_child.nspname AS child_schema,
child.relname AS child_table,
pg_size_pretty ( pg_relation_size ( child.oid ) ) AS child_table_size,
stat.n_live_tup AS ROW_COUNT
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
JOIN pg_stat_user_tables stat ON child.relname = stat.relname
AND nmsp_child.nspname = stat.schemaname
WHERE
parent.relname = 't_log';-- 可以修改表名
分表的断开和连接
断开10月31号的子表，尽量在访问量较小的时间段进行操作
-- 查询是否有事务正在访问需要断开的分区表
SELECT * FROM pg_stat_activity WHERE state = 'active';
BEGIN;
-- 对父表施加独占锁
LOCK TABLE log.t_log IN ACCESS EXCLUSIVE MODE;
-- 执行断开操作
ALTER TABLE log.t_log DETACH PARTITION log.t_log_p2023_10_31;
COMMIT;
连接10月31号的子表，('2023-10-31') TO ('2023-11-1')是子表之前的约束，区间是左开右闭
ALTER TABLE log.t_log ATTACH PARTITION log.t_log_p2023_10_31 FOR VALUES FROM ('2023-10-31') TO ('2023-11-1');
手动扩展分区
执行如下SQL
SELECT partman.create_partition_time(
'log.t_log'::text, -- 父表的名称,记得带上模式
ARRAY['2023-11-03'::timestamp, '2023-11-05'::timestamp], -- 起始和结束时间戳的数组,范围是开区间
true::boolean, -- 是否在创建分区后对其进行分析
NULL::text -- （可选）预先创建的未来分区的数量
);
分区分析：数据库会收集该新分区的统计信息，以便更好地优化查询，开启可以提供性能
创建的未来分区的数量：一般设置为NULL
默认分区数据迁移
1. 确定数据
首先，需要明确哪些数据被错误地插入到默认分区中。这可以通过查询默认分区来完成。
ELECT * FROM your_table_default_partition;
2. 创建临时表
为了安全地处理数据，可以创建一个临时表来存储需要迁移的数据。
CREATE TEMP TABLE temp_data AS SELECT * FROM your_table_default_partition WHERE <condition>;
确保 <condition> 能正确筛选出需要迁移的数据。
3. 从默认分区删除数据
在数据迁移到临时表后，从默认分区删除这些数据。
DELETE FROM your_table_default_partition WHERE <condition>;
使用与第2步中相同的条件。
4. 将数据插入正确的分区
现在，可以将数据从临时表迁移到正确的分区
- 对于native模式，使用父表进行添加
- 对于partman模式，需要找到对应子表进行添加
-- native模式
INSERT INTO your_parent_table SELECT * FROM temp_data;
-- partman模式
INSERT INTO your_specific_partition_table SELECT * FROM temp_data;
5. 清理
迁移完成后，别忘了清理临时表。
DROP TABLE temp_data;
parman模式t_log表的迁移示例如下，为了方便实践，表已调整为按小时进行划分
SELECT * FROM log.t_log;
CREATE TEMP TABLE temp_data AS SELECT * FROM log.t_log WHERE create_time >= '2023-11-2 08:00:00' AND create_time < '2023-11-2 09:00:00' ;
DELETE FROM log.t_log WHERE create_time >= '2023-11-2 08:00:00' AND create_time < '2023-11-2 09:00:00';
INSERT INTO log.t_log_p2023_11_02_0800 SELECT * FROM temp_data;
DROP TABLE temp_data;
删除分区表
删除子表
DO $$
DECLARE
r RECORD;
BEGIN
FOR r IN (SELECT inhrelid::regclass::text AS child_table
FROM pg_inherits
WHERE inhparent = 'log.t_log'::regclass)
LOOP
EXECUTE 'DROP TABLE IF EXISTS ' || r.child_table || ' CASCADE';
END LOOP;
END $$;
删除父表
DROP TABLE IF EXISTS "log"."t_log";
删除分区配置
分区配置记录在partman模式下的part_config表中，可以执行以下SQL进行删除
DELETE FROM partman.part_config WHERE parent_table ='log.t_log'
评论