基于 pg_partman + pg_partman_bgw 的自动化分区管理

-- 创建 partman schema（推荐将 pg_partman 对象放在独立 schema）
CREATE SCHEMA IF NOT EXISTS partman;

-- 安装 pg_partman 扩展
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;

-- 验证安装
SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_partman';

  extname   | extversion
------------+------------
 pg_partman | 4.7.4

-- 创建原生分区表（PARTITION BY RANGE）
CREATE TABLE public.orders (
    id          BIGSERIAL,
    order_date  TIMESTAMPTZ NOT NULL DEFAULT now(),
    customer_id INTEGER NOT NULL,
    amount      NUMERIC(12,2) NOT NULL,
    status      VARCHAR(20) DEFAULT 'pending',
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);

-- 创建业务索引
CREATE INDEX idx_orders_customer ON public.orders(customer_id);
CREATE INDEX idx_orders_status ON public.orders(status);

-- 使用 create_parent 注册分区管理
-- pg_partman v4.7.4 签名：create_parent(p_parent_table, p_control, p_type, p_interval, ...)
SELECT partman.create_parent(
    p_parent_table := 'public.orders',
    p_control      := 'order_date',
    p_type         := 'native',       -- 使用 PostgreSQL 原生分区
    p_interval     := '1 month',      -- 按月分区
    p_premake      := 4               -- 预创建未来4个分区
);

-- 查看已创建的分区
SELECT child.relname AS partition_name,
       pg_get_expr(child.relpartbound, child.oid) AS partition_bound
FROM pg_inherits i
JOIN pg_class parent ON i.inhparent = parent.oid
JOIN pg_class child ON i.inhrelid = child.oid
JOIN pg_namespace n ON parent.relnamespace = n.oid
WHERE parent.relname = 'orders' AND n.nspname = 'public'
ORDER BY child.relname;

       partition_name        |                          partition_bound
-----------------------------+-------------------------------------------------------------------
 orders_default              | DEFAULT
 orders_p2025_12             | FOR VALUES FROM ('2025-12-01') TO ('2026-01-01')
 orders_p2026_01             | FOR VALUES FROM ('2026-01-01') TO ('2026-02-01')
 orders_p2026_02             | FOR VALUES FROM ('2026-02-01') TO ('2026-03-01')
 orders_p2026_03             | FOR VALUES FROM ('2026-03-01') TO ('2026-04-01')
 orders_p2026_04             | FOR VALUES FROM ('2026-04-01') TO ('2026-05-01')

SELECT * FROM partman.show_partitions('public.orders');

-- 创建日志分区表
CREATE TABLE public.app_logs (
    id        BIGSERIAL,
    log_time  TIMESTAMPTZ NOT NULL DEFAULT now(),
    log_level VARCHAR(10) NOT NULL DEFAULT 'INFO',
    message   TEXT,
    PRIMARY KEY (id, log_time)
) PARTITION BY RANGE (log_time);

-- 注册 pg_partman 按天分区
SELECT partman.create_parent(
    p_parent_table := 'public.app_logs',
    p_control      := 'log_time',
    p_type         := 'native',
    p_interval     := '1 day',
    p_premake      := 7          -- 预创建未来7天的分区
);

-- 设置保留策略：只保留最近 3 个月的数据
UPDATE partman.part_config
SET
    retention            = '3 months',
    retention_keep_table = true,     -- 过期分区解绑但保留表（可后续手动删除）
    retention_keep_index = true      -- 保留过期分区的索引
WHERE parent_table = 'public.orders';

-- 对指定表执行维护
SELECT partman.run_maintenance('public.orders');

-- 对所有受管理的表执行维护
SELECT partman.run_maintenance();

-- 查看所有受管理表的配置
SELECT parent_table, control, partition_interval, premake,
       automatic_maintenance, retention, infinite_time_partitions
FROM partman.part_config;

pg_partman_bgw.dbname   = 'mydb'          -- 目标数据库
pg_partman_bgw.role     = 'myuser'        -- 不能为 postgres！
pg_partman_bgw.interval = 3600            -- 每小时执行一次维护

-- 检查 shared_preload_libraries 是否包含 pg_partman_bgw
SHOW shared_preload_libraries;

-- 检查 BGW 参数
SELECT name, setting, source
FROM pg_settings
WHERE name LIKE 'pg_partman_bgw%'
ORDER BY name;

-- 检查后台进程
SELECT pid, backend_type, state, query
FROM pg_stat_activity
WHERE backend_type LIKE '%partman%';

CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;

CREATE TABLE public.my_table (
    id         BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    data       JSONB,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

SELECT partman.create_parent(
    p_parent_table := 'public.my_table',
    p_control      := 'created_at',
    p_type         := 'native',
    p_interval     := '1 month',
    p_premake      := 4
);

UPDATE partman.part_config SET
    retention = '6 months',
    retention_keep_table = true
WHERE parent_table = 'public.my_table';

-- 查看分区
SELECT * FROM partman.show_partitions('public.my_table');

-- 查看配置
SELECT * FROM partman.part_config WHERE parent_table = 'public.my_table';

-- 手动测试维护
SELECT partman.run_maintenance('public.my_table');

-- 正确写法（包含 p_type 参数）
SELECT partman.create_parent(
    p_parent_table := 'public.my_table',
    p_control      := 'created_at',
    p_type         := 'native',        -- 不能省略！
    p_interval     := '1 month'
);

-- 错误写法（缺少 p_type）会导致函数签名不匹配

SELECT pid, backend_type, state
FROM pg_stat_activity
WHERE backend_type LIKE '%partman%';