安装 pg_partman

  • clone pg_partman repo

    1
    $ git clone git@github.com:pgpartman/pg_partman.git

  • create a new file in pg_partman directory

    1
    2
    $ cd pg_partman
    $ touch make.sh

    edit content as below in make.sh

    1
    2
    3
    4
    5
    6
    7
    8
    9
    #!/bin/bash
    EXTENSION=pg_partman
    VERSION=$(grep default_version $EXTENSION.control | \
              sed -e "s/default_version[[:space:]]*=[[:space:]]*'\([^']*\)'/\1/")

    cat sql/types/*.sql > "${EXTENSION}--${VERSION}.sql"
    cat sql/tables/*.sql >> "${EXTENSION}--${VERSION}.sql"
    cat sql/functions/*.sql >> "${EXTENSION}--${VERSION}.sql"
    cat sql/procedures/*.sql >> "${EXTENSION}--${VERSION}.sql"

  • after exec make.sh, you will see two new files: pg_partman.control & pg_partman–5.1.0.sql

    1
    $ ./make.sh

  • copy pg_partman.control & pg_partman–5.1.0.sql to C:\Program Files\PostgreSQL\15\share\extension

  • install pg_partman using sql:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    C:\Program Files\PostgreSQL\15\bin>psql.exe -U postgres
    用户 postgres 的口令：
    psql (15.7)
    输入 "help" 来获取帮助信息.

    postgres=# CREATE SCHEMA IF NOT EXISTS partman;
    CREATE SCHEMA
    postgres=# create extension IF NOT EXISTS pg_partman SCHEMA partman;
    CREATE EXTENSION
    postgres=# select * from pg_available_extensions where name='pg_partman';
        name    | default_version | installed_version |                       comment
    ------------+-----------------+-------------------+------------------------------------------------------
     pg_partman | 5.1.0           | 5.1.0             | Extension to manage partitioned tables by time or ID
    (1 行记录)

示例

示例一：每年1个分区

创建t1表：

1
2
3
4
5
6
CREATE TABLE public.t1 (
  id serial,
  data text,
  dt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE(dt);
CREATE INDEX ON public.t1 (dt);

创建t1_template表, 使用id 为主键：

1
2
CREATE TABLE public.t1_template ( LIKE public.t1);
ALTER TABLE public.t1_template ADD PRIMARY KEY (id);

使用pg_partman创建分区：

1
2
3
4
5
6
7
SELECT partman.create_parent(
    p_parent_table := 'public.t1'
    , p_control := 'dt'
    , p_interval := '1 year'
    , p_template_table := 'public.t1_template'
    , p_start_partition := to_char(date_trunc('year',CURRENT_TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS'));
);

查看t1的详细信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
postgres=# \d+ public.t1
                                                      分区表 "public.t1"
 栏位 |            类型             | 校对规则 |  可空的  |              预设              |   存储   | 压缩 | 统计目标 | 描述
------+-----------------------------+----------+----------+--------------------------------+----------+------+----------+------
 id   | integer                     |          | not null | nextval('t1_id_seq'::regclass) | plain    |      |          |
 data | text                        |          |          |                                | extended |      |          |
 dt   | timestamp without time zone |          | not null | CURRENT_TIMESTAMP              | plain    |      |          |
分区键值: RANGE (dt)
索引：
    "t1_dt_idx" btree (dt)
分区: t1_p20240101 FOR VALUES FROM ('2024-01-01 00:00:00') TO ('2025-01-01 00:00:00'),
      t1_p20250101 FOR VALUES FROM ('2025-01-01 00:00:00') TO ('2026-01-01 00:00:00'),
      t1_p20260101 FOR VALUES FROM ('2026-01-01 00:00:00') TO ('2027-01-01 00:00:00'),
      t1_p20270101 FOR VALUES FROM ('2027-01-01 00:00:00') TO ('2028-01-01 00:00:00'),
      t1_p20280101 FOR VALUES FROM ('2028-01-01 00:00:00') TO ('2029-01-01 00:00:00'),
      t1_default DEFAULT

分区表信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
postgres=# \d+ public.t1_p20240101
                                                 数据表 "public.t1_p20240101"
 栏位 |            类型             | 校对规则 |  可空的  |              预设              |   存储   | 压缩 | 统计目标 | 描述
------+-----------------------------+----------+----------+--------------------------------+----------+------+----------+------
 id   | integer                     |          | not null | nextval('t1_id_seq'::regclass) | plain    |      |          |
 data | text                        |          |          |                                | extended |      |          |
 dt   | timestamp without time zone |          | not null | CURRENT_TIMESTAMP              | plain    |      |          |
分区: t1 FOR VALUES FROM ('2024-01-01 00:00:00') TO ('2025-01-01 00:00:00')
分区约束: ((dt IS NOT NULL) AND (dt >= '2024-01-01 00:00:00'::timestamp without time zone) AND (dt < '2025-01-01 00:00:00'::timestamp without time zone))
索引：
    "t1_p20240101_pkey" PRIMARY KEY, btree (id)
    "t1_p20240101_dt_idx" btree (dt)
访问方法 heap

示例二：每 10 个 ID 值 1 个分区

创建t2表：

1
2
3
4
5
CREATE TABLE public.t2 (
	id serial PRIMARY KEY,
  data text,
  dt timestamp DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE(id);

使用pg_partman创建分区：

1
2
3
4
5
SELECT partman.create_parent(
    p_parent_table := 'public.t2'
    , p_control := 'id'
    , p_interval := '10'
);

t2的详细信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
postgres=# \d+ public.t2
                                                      分区表 "public.t2"
 栏位 |            类型             | 校对规则 |  可空的  |              预设              |   存储   | 压缩 | 统计目标 | 描述
------+-----------------------------+----------+----------+--------------------------------+----------+------+----------+------
 id   | integer                     |          | not null | nextval('t2_id_seq'::regclass) | plain    |      |          |
 data | text                        |          |          |                                | extended |      |          |
 dt   | timestamp without time zone |          |          | CURRENT_TIMESTAMP              | plain    |      |          |
分区键值: RANGE (id)
索引：
    "t2_pkey" PRIMARY KEY, btree (id)
分区: t2_p0 FOR VALUES FROM (0) TO (10),
      t2_p10 FOR VALUES FROM (10) TO (20),
      t2_p20 FOR VALUES FROM (20) TO (30),
      t2_p30 FOR VALUES FROM (30) TO (40),
      t2_p40 FOR VALUES FROM (40) TO (50),
      t2_default DEFAULT

分区表信息：

1
2
3
4
5
6
7
8
9
10
11
12
postgres=# \d+ t2_p10
                                                    数据表 "public.t2_p10"
 栏位 |            类型             | 校对规则 |  可空的  |              预设              |   存储   | 压缩 | 统计目标 | 描述
------+-----------------------------+----------+----------+--------------------------------+----------+------+----------+------
 id   | integer                     |          | not null | nextval('t2_id_seq'::regclass) | plain    |      |          |
 data | text                        |          |          |                                | extended |      |          |
 dt   | timestamp without time zone |          |          | CURRENT_TIMESTAMP              | plain    |      |          |
分区: t2 FOR VALUES FROM (10) TO (20)
分区约束: ((id IS NOT NULL) AND (id >= 10) AND (id < 20))
索引：
    "t2_p10_pkey" PRIMARY KEY, btree (id)
访问方法 heap

后台自动创建分区

可手动调用run_maintenance_proc自动创建：

1
CALL partman.run_maintenance_proc();

可配合pg_cron或pg_partman_bgw定时进行分区的创建和删除。

Reference