greenplum自动分区脚本说明
greenplum自动分区脚本说明
目录
前言：
- greenplum版本：greenplum 6
- 为啥greenplum不删除历史分区：默认情况下greenplum不像mysql5.7版本一样有硬件限制只能创建1024个分区，greenplum默认分区限制很大，可以不用考虑分区数上限，若考虑到性能问题，可以手动删除历史分区，详情请查看文档：各数据库的分区数限制和说明。
分区脚本
以下是一个增强版的 Greenplum 自动分区管理函数，支持按天、按月、按年三种粒度，动态创建未来指定数量的分区。它基于经典分区语法（SPLIT DEFAULT PARTITION），并包含了参数验证、分区存在性检查和异常处理。
CREATE OR REPLACE FUNCTION auto_split_partitions(
p_table text, -- 表名（可带 schema，如 public.test_p）
p_granularity text DEFAULT 'month', -- 分区粒度：day / month / year
p_future int DEFAULT 3 -- 需要提前创建的未来分区数量（包含当前周期）
)
RETURNS text AS $$
DECLARE
v_schema text; -- 模式名
v_tabname text; -- 表名（不含模式）
v_granularity text; -- 规范化后的粒度
v_current_base date; -- 当前周期基准日期（对齐到粒度起点）
v_start_date date; -- 分区起始日期（包含）
v_end_date date; -- 分区结束日期（不包含）
v_part_name text; -- 生成的分区名
v_exists int; -- 分区是否存在标志（1存在，NULL不存在）
v_sql text; -- 动态执行的 ALTER 语句
v_result text := ''; -- 执行结果汇总
v_count int := 0; -- 成功创建的分区数量
v_i int; -- 循环计数器
BEGIN
-- 1. 解析表名
IF position('.' in p_table) > 0 THEN
v_schema := split_part(p_table, '.', 1);
v_tabname := split_part(p_table, '.', 2);
ELSE
v_schema := 'public';
v_tabname := p_table;
END IF;
-- 2. 规范化粒度参数，并验证合法性
v_granularity := lower(p_granularity);
IF v_granularity NOT IN ('day', 'month', 'year') THEN
RAISE EXCEPTION 'Invalid granularity "%". Allowed values: day, month, year', p_granularity;
END IF;
-- 3. 循环创建未来分区
FOR v_i IN 0..(p_future - 1) LOOP
-- 计算当前基准日期（根据粒度对齐到周期起点）
CASE v_granularity
WHEN 'day' THEN
v_current_base := date_trunc('day', current_date + (v_i || ' days')::interval)::date;
v_start_date := v_current_base;
v_end_date := v_current_base + interval '1 day';
v_part_name := 'p_' || to_char(v_start_date, 'YYYY_MM_DD');
WHEN 'month' THEN
v_current_base := date_trunc('month', current_date + (v_i || ' months')::interval)::date;
v_start_date := v_current_base;
v_end_date := v_current_base + interval '1 month';
v_part_name := 'p_' || to_char(v_start_date, 'YYYY_MM');
WHEN 'year' THEN
v_current_base := date_trunc('year', current_date + (v_i || ' years')::interval)::date;
v_start_date := v_current_base;
v_end_date := v_current_base + interval '1 year';
v_part_name := 'p_' || to_char(v_start_date, 'YYYY');
END CASE;
-- 4. 检查分区是否已存在（通过 pg_partitions 视图）
EXECUTE format('
SELECT 1 FROM pg_partitions
WHERE schemaname = %L
AND tablename = %L
AND partitiontablename = %L',
v_schema, v_tabname, v_part_name
) INTO v_exists;
-- 5. 如果不存在，则从默认分区拆分
IF v_exists IS NULL THEN
BEGIN
v_sql := format('
ALTER TABLE %I.%I
SPLIT DEFAULT PARTITION
START (%L::date) INCLUSIVE
END (%L::date) EXCLUSIVE
INTO (PARTITION %I, DEFAULT PARTITION)',
v_schema, v_tabname,
v_start_date::text, v_end_date::text,
v_part_name
);
-- 打印即将执行的语句
RAISE NOTICE 'Executing: %', v_sql;
EXECUTE v_sql;
v_count := v_count + 1;
v_result := v_result || format('Created %s [%s to %s]; ', v_part_name, v_start_date, v_end_date);
EXCEPTION WHEN OTHERS THEN
RAISE WARNING 'Failed to create %: %', v_part_name, SQLERRM;
v_result := v_result || format('Failed to create %s: %s; ', v_part_name, SQLERRM);
END;
END IF;
END LOOP;
-- 6. 返回汇总信息
RETURN format('Created %s new partition(s). Details: %s', v_count, v_result);
END;
$$ LANGUAGE plpgsql;
分区表结构
CREATE TABLE public.amb_data_doc_lines_partion_test (
account_code varchar(255) NULL,
compo_id varchar(60) NULL,
compo_line_id varchar(60) NULL,
created_at timestamp(0) NULL,
currency_id varchar(60) NULL,
doc_id varchar(200) NOT NULL,
ent_id varchar(60) NULL,
fr_biz_type_id varchar(255) NULL,
fr_docno varchar(128) NULL,
fr_doc_date date NULL
)
WITH (
appendonly=true,
orientation=column,
compresstype=zstd,
compresslevel=5
)
DISTRIBUTED BY (fr_doc_date)
PARTITION BY RANGE(fr_doc_date)
(
PARTITION p_2022_01 START ('2022-01-01'::date) END ('2022-02-01'::date) ,
PARTITION p_2022_02 START ('2022-02-01'::date) END ('2022-03-01'::date) ,
PARTITION p_2022_03 START ('2022-03-01'::date) END ('2022-04-01'::date) ,
DEFAULT PARTITION default_p
);
或者：
-- public.dwm_amb_sfchzb_aoco definition
-- Drop table
-- DROP TABLE public.dwm_amb_sfchzb_aoco;
CREATE TABLE public.dwm_amb_sfchzb_aoco (
djrq varchar(20) NULL,
ckbm varchar(50) NULL,
ckmc varchar(255) NULL,
is_xc varchar(10) NULL
)
WITH (
appendonly=true,
orientation=column,
compresstype=zstd,
compresslevel=5
)
DISTRIBUTED BY (djrq)
PARTITION BY RANGE(djrq)
(
PARTITION p_2021_01 START ('2021-01-01'::character varying(20)) END ('2021-02-01'::character varying(20)),
PARTITION p_2021_02 START ('2021-02-01'::character varying(20)) END ('2021-03-01'::character varying(20)),
PARTITION p_2028_09 START ('2028-09-01'::character varying(20)) END ('2028-10-01'::character varying(20)),
PARTITION p_2028_10 START ('2028-10-01'::character varying(20)) END ('2028-11-01'::character varying(20)),
PARTITION p_2028_11 START ('2028-11-01'::character varying(20)) END ('2028-12-01'::character varying(20)),
PARTITION p_2028_12 START ('2028-12-01'::character varying(20)) END ('2029-01-01'::character varying(20)),
DEFAULT PARTITION default_p
);
查看表的分区
SELECT SCHEMANAME,partitionname,partitiontablename, partitiontype, partitionrangestart, partitionrangeend
FROM pg_partitions
WHERE tablename = 'dwd_amb_mxb_auto_pation' AND schemaname = 'public';
SELECT *
FROM pg_partitions
WHERE tablename = 'dwd_amb_mxb_auto_pation' AND schemaname = 'public';
使用示例
按月分区（未来6个月，默认粒度）
-- 按月分区，新增当前月份以及向后推12个月的分区
-- 比如现在3月，那么会尝试添加p_2026_03 - p_2027_03 ，如果3月份的分区已经存在，就会跳过，也就是添加3月份的分区报错，其他月份正常添加
SELECT auto_split_partitions('public.amb_data_doc_lines_partion_test','month', 12);
按天分区（未来7天）
-- 按天分区，新增当前日期以及向后推7天的分区
-- 同上，当前日期往后推7天
SELECT auto_split_partitions('public.amb_data_doc_lines_partion_test', 'day', 7);
按年分区（未来2年）
-- 按年分区，新增当前年以及向后推2年的分区
-- 比如现在是2026年，那么会尝试执行新增p_2026和p_2027年的分区，如果2026已经有其他按月或者按天的分区，则会返回添加失败的信息，因为当前已经有分区包含了2026年的数据，所以只会添加2027年的分区
SELECT auto_split_partitions('public.amb_data_doc_lines_partion_test', 'year', 2);
linux环境自动调用脚本
vim auto_partition_multi_tables.sh
#!/bin/bash
# =============================================================================
# 脚本名称: auto_partition_multi_tables.sh
# 功能描述: 为多个Greenplum表自动创建分区（调用 auto_split_partitions 函数）
# 适用环境: 需安装 psql 客户端，且数据库连接已配置（通过 .pgpass 或环境变量）
# 使用方法:
# 1. 直接执行: ./auto_partition_multi_tables.sh
# 2. 定时任务: 添加到 crontab 每日运行
# =============================================================================
# ------------------------------ 配置区域 ------------------------------------
# 数据库连接参数（可通过环境变量覆盖）
PGHOST="${PGHOST:-127.0.0.1}" # 数据库主机
PGPORT="${PGPORT:-5432}" # 端口
PGDATABASE="datawarehouse" # 数据库名
PGUSER="${PGUSER:-gpadmin}" # 用户名
# 密码建议使用 ~/.pgpass 文件设置，避免明文
# 日志文件路径
LOG_FILE="/tmp/gp_auto_partition.log"
# 定义要处理的表及其参数
# 格式: "schema.table:granularity:future_count"
# granularity: day / month / year
# future_count: 需要提前创建的分区数量（包含当前周期），不填默认3
TABLE_CONFIGS=(
"public.dwd_amb_mxb:month:6"
"public.dws_amb_app_srcbsyb_all:month:6"
"public.dwd_bc_sfchzb_inc_day:month:6"
"public.dwm_amb_sfchzb:month:6"
"public.dwd_amb_sfchzb_kc:month:6"
)
# 也可以从外部文件读取（取消下面注释，并准备一个文件，每行格式同上）
# CONFIG_FILE="/path/to/table_list.conf"
# if [ -f "$CONFIG_FILE" ]; then
# mapfile -t TABLE_CONFIGS < "$CONFIG_FILE"
# fi
# ----------------------------------------------------------------------------
# 检查 psql 是否可用
if ! command -v psql &> /dev/null; then
echo "$(date '+%Y-%m-%d %H:%M:%S') - ERROR: psql command not found" >> "$LOG_FILE"
exit 1
fi
# 记录开始时间
echo "$(date '+%Y-%m-%d %H:%M:%S') - ========== 开始分区自动创建任务 ==========" >> "$LOG_FILE"
# 遍历每个表配置
for config in "${TABLE_CONFIGS[@]}"; do
# 忽略空行和注释行（以#开头）
[[ -z "$config" || "$config" =~ ^#.*$ ]] && continue
# 解析配置项
IFS=':' read -r table granularity future <<< "$config"
# 若 future 为空，则设为默认值3
future=${future:-3}
echo "$(date '+%Y-%m-%d %H:%M:%S') - 处理表: $table (粒度=$granularity, 未来分区数=$future)" >> "$LOG_FILE"
# 构建SQL调用存储过程
SQL="SELECT auto_split_partitions('$table', '$granularity', $future);"
# 执行SQL，捕获输出和错误
# 注意：RAISE NOTICE 信息会输出到 stderr，这里合并输出到变量
result=$(psql -h "$PGHOST" -p "$PGPORT" -d "$PGDATABASE" -U "$PGUSER" -t -c "$SQL" 2>&1)
exit_code=$?
if [ $exit_code -eq 0 ]; then
echo "$(date '+%Y-%m-%d %H:%M:%S') - 成功: $result" >> "$LOG_FILE"
else
echo "$(date '+%Y-%m-%d %H:%M:%S') - 失败: $result" >> "$LOG_FILE"
fi
done
echo "$(date '+%Y-%m-%d %H:%M:%S') - ========== 分区自动创建任务结束 ==========" >> "$LOG_FILE"
exit 0
加入定时任务，每天凌晨2点执行：
##自动分区
0 2 * * * /home/gpadmin/gj_monitor/auto_partition_multi_tables.sh
重要说明
1.前提条件
- 目标表必须是已存在的分区表，且包含 默认分区（DEFAULT PARTITION），否则 SPLIT DEFAULT PARTITION 会失败。
- 分区列必须为 date 类型（或可隐式转换为 date 的类型），函数中统一使用 ::date 转换。
2.分区命名规则
- 天：p_YYYY_MM_DD（例如 p_2026_03_19）
- 月：p_YYYY_MM（例如 p_2026_03）
- 年：p_YYYY（例如 p_2026）
边界处理
- 采用左闭右开原则（INCLUSIVE 起始，EXCLUSIVE 结束），与 Greenplum 默认行为一致，确保数据不会落入错误分区。
幂等性
- 每次执行前会检查分区名是否存在，因此即使每天定时运行，也不会重复创建已存在的分区。
异常处理
- 单个分区的创建失败不会中断整个循环，错误信息会记录在返回结果中。
性能考虑
- 对于大量分区（例如按天创建未来数百天），建议适当控制 p_future 值，或分批执行，避免单次操作时间过长。
定时任务配置（cron）
在 Greenplum 主节点添加 crontab，每天凌晨执行一次，自动创建未来所需分区：
# 每天凌晨2点执行，为 test_p 创建未来3个月的分区（默认）
0 2 * * * psql -d yourdb -c "SELECT auto_split_partitions('public.test_p');" >> /var/log/gp_auto_partition.log 2>&1
# 若需按天分区（例如未来7天）
0 2 * * * psql -d yourdb -c "SELECT auto_split_partitions('public.test_p', 'day', 7);" >> /var/log/gp_auto_partition.log 2>&1
该函数已全面兼容 Greenplum 6 的经典分区语法，可按需灵活调整分区粒度，实现自动化维护。
浙公网安备 33010602011771号