1. 问题背景

在 PostgreSQL 中，业务表 public.obs_data 是一张按照 obs_time 字段进行 RANGE 月度分区 的分区表。

父表结构如下：

CREATE TABLE public.obs_data (
  id int8 NOT NULL,
  st_id varchar(50) NOT NULL,
  obs_time timestamp(6) NOT NULL,
  el_code varchar(50) NOT NULL,
  el_mode_code varchar(50),
  value numeric(12,2),
  value_t text,
  qc_flag varchar(4),
  ingest_time timestamp(6) NOT NULL DEFAULT now()
)
PARTITION BY RANGE (obs_time);

已有分区示例：

ALTER TABLE public.obs_data
ATTACH PARTITION public.obs_data_202601
FOR VALUES FROM ('2026-01-01 00:00:00')
TO ('2026-02-01 00:00:00');

ALTER TABLE public.obs_data
ATTACH PARTITION public.obs_data_202602
FOR VALUES FROM ('2026-02-01 00:00:00')
TO ('2026-03-01 00:00:00');

ALTER TABLE public.obs_data
ATTACH PARTITION public.obs_data_default
DEFAULT;

其中 obs_data_default 是默认分区，用于接收没有匹配到具体月份分区的数据。

---

2. 遇到的问题

在创建 2026 年 4 月分区时，数据库报错：

ERROR: 创建分区失败：public.obs_data_default 中可能已经存在 [2026-04-01 00:00:00 至 2026-05-01 00:00:00) 范围内的数据，请先迁移 default 分区中的对应数据
CONTEXT: PL/pgSQL function create_obs_data_next_month_partition(text) line 74 at RAISE

这个错误的根本原因是：

在 2026 年 4 月分区尚未提前创建时，4 月份的数据已经写入了 obs_data_default 默认分区。

PostgreSQL 在新增分区时，会检查 default 分区中是否已经存在属于新分区范围的数据。如果存在，就会阻止创建或挂载该分区，避免同一条数据在逻辑上同时可能归属两个分区。

---

3. RANGE 分区的边界说明

PostgreSQL 的 RANGE 分区采用 左闭右开区间。

例如：

FOR VALUES FROM ('2026-04-01 00:00:00')
TO ('2026-05-01 00:00:00')

实际表示：

obs_time >= '2026-04-01 00:00:00'
AND
obs_time <  '2026-05-01 00:00:00'

因此：

• 2026-04-30 23:59:59 属于 4 月分区；
• 2026-05-01 00:00:00 不属于 4 月分区；
• 2026-05-01 00:00:00 属于 5 月分区。

所以相邻月份分区可以连续定义为：

CREATE TABLE public.obs_data_202604
PARTITION OF public.obs_data
FOR VALUES FROM ('2026-04-01 00:00:00')
TO ('2026-05-01 00:00:00');

CREATE TABLE public.obs_data_202605
PARTITION OF public.obs_data
FOR VALUES FROM ('2026-05-01 00:00:00')
TO ('2026-06-01 00:00:00');

这两个分区不会发生范围冲突。

---

4. 为什么不能直接 INSERT + DELETE

如果 default 分区里已经有大量历史数据，例如 2600 多万条，那么不建议直接使用如下方式处理：

INSERT INTO public.obs_data_202604
SELECT *
FROM public.obs_data_default
WHERE obs_time >= timestamp '2026-04-01 00:00:00'
  AND obs_time <  timestamp '2026-05-01 00:00:00';

DELETE FROM public.obs_data_default
WHERE obs_time >= timestamp '2026-04-01 00:00:00'
  AND obs_time <  timestamp '2026-05-01 00:00:00';

原因包括：

1. DELETE 大批量数据非常慢；
2. 会产生大量 WAL 日志；
3. 会导致 obs_data_default 表严重膨胀；
4. 大事务失败后回滚成本极高；
5. 容易长时间持有锁，影响线上写入。

更稳妥的方式是：

先快速摘除旧 default 分区，创建正式月份分区和新的空 default 分区，再离线迁移旧 default 中的数据。

---

5. 推荐处理思路

假设当前 2026 年 4 月和 5 月的数据都已经误落入 obs_data_default，推荐处理流程如下：

1. 锁定父表，避免切换分区结构时继续写入；
2. 将旧 obs_data_default 从父表中 DETACH；
3. 把旧 default 改名为备份表；
4. 创建 2026 年 4 月分区；
5. 创建 2026 年 5 月分区；
6. 创建新的空 default 分区；
7. 提交事务，使业务尽快恢复写入；
8. 再从旧 default 备份表中迁移 4 月、5 月数据到正式分区。

---

6. 第一步：重建 4 月、5 月分区结构

以下 SQL 可以直接执行。

注意：这一步会短暂锁住父表，建议在业务低峰期执行。

BEGIN;

-- 锁住父表，避免切换分区结构时有新数据写入
LOCK TABLE public.obs_data IN ACCESS EXCLUSIVE MODE;

-- 1. 摘除当前 default 分区
ALTER TABLE public.obs_data
DETACH PARTITION public.obs_data_default;

-- 2. 将原 default 分区改名为备份表
ALTER TABLE public.obs_data_default
RENAME TO obs_data_default_bak_202604_202605;

COMMENT ON TABLE public.obs_data_default_bak_202604_202605 IS
'原 obs_data_default 备份表，包含误落入 default 分区的历史数据，主要用于迁移 2026年4月、5月数据';

-- 3. 创建 2026 年 4 月分区
CREATE TABLE public.obs_data_202604
PARTITION OF public.obs_data
FOR VALUES FROM ('2026-04-01 00:00:00')
TO ('2026-05-01 00:00:00');

ALTER TABLE public.obs_data_202604 OWNER TO hf_user;

COMMENT ON TABLE public.obs_data_202604 IS
'基础数据表月度分区，范围 [2026-04-01 00:00:00, 2026-05-01 00:00:00)';

-- 4. 创建 2026 年 5 月分区
CREATE TABLE public.obs_data_202605
PARTITION OF public.obs_data
FOR VALUES FROM ('2026-05-01 00:00:00')
TO ('2026-06-01 00:00:00');

ALTER TABLE public.obs_data_202605 OWNER TO hf_user;

COMMENT ON TABLE public.obs_data_202605 IS
'基础数据表月度分区，范围 [2026-05-01 00:00:00, 2026-06-01 00:00:00)';

-- 5. 重新创建新的空 default 分区
CREATE TABLE public.obs_data_default
PARTITION OF public.obs_data
DEFAULT;

ALTER TABLE public.obs_data_default OWNER TO hf_user;

COMMENT ON TABLE public.obs_data_default IS
'基础数据表默认分区，用于接收未提前创建分区的数据';

COMMIT;

执行完成后：

• 新的 4 月数据会进入 public.obs_data_202604；
• 新的 5 月数据会进入 public.obs_data_202605；
• 未匹配到月份分区的数据会进入新的 public.obs_data_default；
• 原来的历史数据保留在 public.obs_data_default_bak_202604_202605 中。

---

7. 第二步：迁移旧 default 中的 4 月、5 月数据

7.1 迁移 2026 年 4 月数据

INSERT INTO public.obs_data_202604
SELECT *
FROM public.obs_data_default_bak_202604_202605
WHERE obs_time >= timestamp '2026-04-01 00:00:00'
  AND obs_time <  timestamp '2026-05-01 00:00:00';

7.2 迁移 2026 年 5 月数据

INSERT INTO public.obs_data_202605
SELECT *
FROM public.obs_data_default_bak_202604_202605
WHERE obs_time >= timestamp '2026-05-01 00:00:00'
  AND obs_time <  timestamp '2026-06-01 00:00:00';

这一步数据量较大时可能执行时间较长，但不会再影响分区结构切换，也避免了大规模 DELETE 带来的表膨胀问题。

---

8. 第三步：迁移后数据核对

8.1 核对 4 月分区数据量

SELECT count(*) AS cnt_202604
FROM public.obs_data_202604
WHERE obs_time >= timestamp '2026-04-01 00:00:00'
  AND obs_time <  timestamp '2026-05-01 00:00:00';

8.2 核对 5 月分区数据量

SELECT count(*) AS cnt_202605
FROM public.obs_data_202605
WHERE obs_time >= timestamp '2026-05-01 00:00:00'
  AND obs_time <  timestamp '2026-06-01 00:00:00';

8.3 查看备份表中还有哪些月份的数据

SELECT 
    to_char(date_trunc('month', obs_time), 'YYYY-MM') AS month_time,
    count(*) AS cnt
FROM public.obs_data_default_bak_202604_202605
GROUP BY date_trunc('month', obs_time)
ORDER BY month_time;

这个查询非常重要。因为旧 default 分区中可能不止包含 4 月、5 月数据，还可能包含 3 月、6 月或其他未提前创建分区的历史数据。

---

9. 备份表如何处理

如果确认 public.obs_data_default_bak_202604_202605 中只有 4 月、5 月数据，并且这两个月的数据已经迁移完成，可以删除备份表：

DROP TABLE public.obs_data_default_bak_202604_202605;

如果备份表中还有其他月份数据，不要直接删除，需要继续为对应月份创建正式分区，然后再迁移。

---

10. 自动创建下月分区的函数

为了避免后续数据再次进入 default 分区，可以创建一个函数，用于自动创建传入日期所在月份的下一个月分区。

例如传入：

SELECT public.create_obs_data_next_month_partition('2026-05-15 00:00:00');

会创建：

public.obs_data_202606

分区范围为：

[2026-06-01 00:00:00, 2026-07-01 00:00:00)

函数如下：

CREATE OR REPLACE FUNCTION public.create_obs_data_next_month_partition(p_date_text text)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
    v_base_time      timestamp;
    v_start_time     timestamp;
    v_end_time       timestamp;
    v_partition_name text;
    v_exists         boolean;
BEGIN
    IF p_date_text IS NULL OR btrim(p_date_text) = '' THEN
        RAISE EXCEPTION '日期参数不能为空，格式示例：2026-02-15 00:00:00';
    END IF;

    BEGIN
        v_base_time := to_timestamp(p_date_text, 'YYYY-MM-DD HH24:MI:SS')::timestamp;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE EXCEPTION '日期格式错误：%，正确格式如：2026-02-15 00:00:00', p_date_text;
    END;

    -- 创建传入日期所在月份的下一个月分区
    v_start_time := (date_trunc('month', v_base_time) + interval '1 month')::timestamp;
    v_end_time   := (v_start_time + interval '1 month')::timestamp;

    -- 分区表名，例如 obs_data_202603
    v_partition_name := 'obs_data_' || to_char(v_start_time, 'YYYYMM');

    -- 判断分区表是否已经存在
    SELECT EXISTS (
        SELECT 1
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE n.nspname = 'public'
          AND c.relname = v_partition_name
    )
    INTO v_exists;

    IF v_exists THEN
        RETURN format(
            '分区已存在：public.%s，范围 [%s, %s)',
            v_partition_name,
            to_char(v_start_time, 'YYYY-MM-DD HH24:MI:SS'),
            to_char(v_end_time, 'YYYY-MM-DD HH24:MI:SS')
        );
    END IF;

    BEGIN
        EXECUTE format(
            'CREATE TABLE public.%I PARTITION OF public.obs_data FOR VALUES FROM (%L) TO (%L)',
            v_partition_name,
            v_start_time,
            v_end_time
        );

        EXECUTE format(
            'ALTER TABLE public.%I OWNER TO hf_user',
            v_partition_name
        );

        EXECUTE format(
            'COMMENT ON TABLE public.%I IS %L',
            v_partition_name,
            format(
                '基础数据表月度分区，范围 [%s, %s)',
                to_char(v_start_time, 'YYYY-MM-DD HH24:MI:SS'),
                to_char(v_end_time, 'YYYY-MM-DD HH24:MI:SS')
            )
        );

    EXCEPTION
        WHEN duplicate_table THEN
            RETURN format('分区已存在：public.%s', v_partition_name);

        WHEN check_violation THEN
            RAISE EXCEPTION
                '创建分区失败：public.obs_data_default 中可能已经存在 [% 至 %) 范围内的数据，请先迁移 default 分区中的对应数据',
                to_char(v_start_time, 'YYYY-MM-DD HH24:MI:SS'),
                to_char(v_end_time, 'YYYY-MM-DD HH24:MI:SS');
    END;

    RETURN format(
        '创建成功：public.%s，范围 [%s, %s)',
        v_partition_name,
        to_char(v_start_time, 'YYYY-MM-DD HH24:MI:SS'),
        to_char(v_end_time, 'YYYY-MM-DD HH24:MI:SS')
    );
END;
$$;

---

11. 后续预防方案

建议每个月提前创建下一个月的分区，避免新月份数据进入 default 分区。

例如当前是 2026 年 5 月，可以提前创建 2026 年 6 月分区：

SELECT public.create_obs_data_next_month_partition('2026-05-15 00:00:00');

也可以将该函数放入定时任务中，例如每月 25 日自动创建下月分区。

整体原则是：

不要等新月份数据已经进入 default 分区后再补建分区。

否则再次创建对应月份分区时，仍然会遇到 default 分区中存在冲突数据的问题。

---

12. 总结

对于 PostgreSQL 按月 RANGE 分区表，如果 default 分区中已经存在待创建月份的数据，不能直接挂载新的月份分区。

在数据量较小时，可以考虑 INSERT + DELETE 的方式迁移数据；但当 default 分区中已有千万级数据时，更推荐使用以下方式：

DETACH 旧 default
RENAME 为备份表
CREATE 正式月份分区
CREATE 新 default 分区
再离线 INSERT 迁移历史数据

这种方式可以最大程度降低锁表时间，避免大规模 DELETE，减少表膨胀和 WAL 压力，也更适合线上环境中的历史分区修复场景。