PG Partition Manager 分区管理器

卜瀚漠

2023-12-01

PG Partition Manager 分区管理器

简介

Pg partman 是创建和管理基于时间和基于序列的表分区集的扩展

下载

git clone https://github.com/pgpartman/pg_partman.git

编译安装

chown -R postgres.postgres pg_partman-master
su - postgres
[postgres@mysql ~]$ cd /tools/pg_partman-master
[postgres@mysql pg_partman-master]$ make NO_BGW=1 install
cat sql/types/types.sql sql/tables/tables.sql sql/functions/apply_cluster.sql sql/functions/apply_constraints.sql sql/functions/apply_foreign_keys.sql sql/functions/apply_privileges.sql sql/functions/apply_publications.sql sql/functions/autovacuum_off.sql sql/functions/autovacuum_reset.sql sql/functions/check_control_type.sql sql/functions/check_default.sql sql/functions/check_name_length.sql sql/functions/check_subpart_sameconfig.sql sql/functions/check_subpartition_limits.sql sql/functions/create_function_id.sql sql/functions/create_function_time.sql sql/functions/create_parent.sql sql/functions/create_partition_id.sql sql/functions/create_partition_time.sql sql/functions/create_sub_parent.sql sql/functions/create_trigger.sql sql/functions/drop_constraints.sql sql/functions/drop_partition_column.sql sql/functions/drop_partition_id.sql sql/functions/drop_partition_time.sql sql/functions/dump_partition_table_definition.sql sql/functions/inherit_template_properties.sql sql/functions/partition_data_id.sql sql/functions/partition_data_time.sql sql/functions/partition_gap_fill.sql sql/functions/reapply_privileges.sql sql/functions/run_maintenance.sql sql/functions/show_partition_info.sql sql/functions/show_partition_name.sql sql/functions/show_partitions.sql sql/functions/stop_sub_partition.sql sql/functions/undo_partition.sql sql/procedures/partition_data_proc.sql sql/procedures/reapply_constraints_proc.sql sql/procedures/run_maintenance_proc.sql sql/procedures/undo_partition_proc.sql > sql/pg_partman--4.4.0.sql
/bin/mkdir -p '/home/postgres/pg13/share/extension'
/bin/mkdir -p '/home/postgres/pg13/share/extension'
/bin/mkdir -p '/home/postgres/pg13/share/doc/extension'
/bin/mkdir -p '/home/postgres/pg13/bin'
/usr/bin/install -c -m 644 .//pg_partman.control '/home/postgres/pg13/share/extension/'
/usr/bin/install -c -m 644 .//updates/pg_partman--0.1.0--0.1.1.sql .//updates/pg_partman--0.1.1--0.1.2.sql .//updates/pg_partman--0.1.2--0.2.0.sql .//updates/pg_partman--0.2.0--0.3.0.sql .//updates/pg_partman--0.3.0--0.3.1.sql .//updates/pg_partman--0.3.1--0.3.2.sql .//updates/pg_partman--0.3.2--0.4.0.sql .//updates/pg_partman--0.4.0--0.4.1.sql .//updates/pg_partman--0.4.1--0.4.2.sql .//updates/pg_partman--0.4.2--1.0.0.sql .//updates/pg_partman--1.0.0--1.1.0.sql .//updates/pg_partman--1.1.0--1.2.0.sql .//updates/pg_partman--1.2.0--1.3.0.sql .//updates/pg_partman--1.3.0--1.4.0.sql .//updates/pg_partman--1.4.0--1.4.1.sql .//updates/pg_partman--1.4.1--1.4.2.sql .//updates/pg_partman--1.4.2--1.4.3.sql .//updates/pg_partman--1.4.3--1.4.4.sql .//updates/pg_partman--1.4.4--1.4.5.sql .//updates/pg_partman--1.4.5--1.5.0.sql .//updates/pg_partman--1.5.0--1.5.1.sql .//updates/pg_partman--1.5.1--1.6.0.sql .//updates/pg_partman--1.6.0--1.6.1.sql .//updates/pg_partman--1.6.1--1.7.0.sql .//updates/pg_partman--1.7.0--1.7.1.sql .//updates/pg_partman--1.7.1--1.7.2.sql .//updates/pg_partman--1.7.2--1.8.0.sql .//updates/pg_partman--1.8.0--1.8.1.sql .//updates/pg_partman--1.8.1--1.8.2.sql .//updates/pg_partman--1.8.2--1.8.3.sql .//updates/pg_partman--1.8.3--1.8.4.sql .//updates/pg_partman--1.8.4--1.8.5.sql .//updates/pg_partman--1.8.5--1.8.6.sql .//updates/pg_partman--1.8.6--1.8.7.sql .//updates/pg_partman--1.8.7--1.8.8.sql .//updates/pg_partman--1.8.7--2.0.0.sql .//updates/pg_partman--1.8.8--2.0.0.sql .//updates/pg_partman--2.0.0--2.1.0.sql .//updates/pg_partman--2.1.0--2.2.0.sql .//updates/pg_partman--2.2.0--2.2.1.sql .//updates/pg_partman--2.2.1--2.2.2.sql .//updates/pg_partman--2.2.2--2.2.3.sql .//updates/pg_partman--2.2.3--2.3.0.sql .//updates/pg_partman--2.3.0--2.3.1.sql .//updates/pg_partman--2.3.1--2.3.2.sql .//updates/pg_partman--2.3.2--2.3.3.sql .//updates/pg_partman--2.3.3--2.3.4.sql .//updates/pg_partman--2.3.4--2.4.0.sql .//updates/pg_partman--2.4.0--2.4.1.sql .//updates/pg_partman--2.4.1--2.5.0.sql .//updates/pg_partman--2.5.0--2.5.1.sql .//updates/pg_partman--2.5.1--2.6.0.sql .//updates/pg_partman--2.6.0--2.6.1.sql .//updates/pg_partman--2.6.1--2.6.2.sql .//updates/pg_partman--2.6.2--2.6.3.sql .//updates/pg_partman--2.6.3--2.6.4.sql .//updates/pg_partman--2.6.4--3.0.0.sql .//updates/pg_partman--3.0.0--3.0.1.sql .//updates/pg_partman--3.0.1--3.0.2.sql .//updates/pg_partman--3.0.2--3.1.0.sql .//updates/pg_partman--3.1.0--3.1.1.sql .//updates/pg_partman--3.1.1--3.1.2.sql .//updates/pg_partman--3.1.2--3.1.3.sql .//updates/pg_partman--3.1.3--3.2.0.sql .//updates/pg_partman--3.2.0--3.2.1.sql .//updates/pg_partman--3.2.1--4.0.0.sql .//updates/pg_partman--4.0.0--4.1.0.sql .//updates/pg_partman--4.1.0--4.2.0.sql .//updates/pg_partman--4.2.0--4.2.1.sql .//updates/pg_partman--4.2.1--4.2.2.sql .//updates/pg_partman--4.2.2--4.3.0.sql .//updates/pg_partman--4.3.0--4.3.1.sql .//updates/pg_partman--4.3.1--4.4.0.sql .//sql/pg_partman--4.4.0.sql  '/home/postgres/pg13/share/extension/'
/usr/bin/install -c -m 644 .//doc/migrate_to_native.md .//doc/migration_to_partman.md .//doc/pg_partman_howto.md .//doc/pg_partman.md '/home/postgres/pg13/share/doc/extension/'
/usr/bin/install -c -m 755 .//bin/common/*.py '/home/postgres/pg13/bin/'
[postgres@mysql pg_partman-master]$

查看生成的文件

[postgres@mysql pg_partman-master]$ ll ./bin/common/*py
-rwxr-xr-x 1 postgres postgres 4.3K May 12 02:25 ./bin/common/check_unique_constraint.py
-rwxr-xr-x 1 postgres postgres 5.7K May 12 02:25 ./bin/common/dump_partition.py
-rwxr-xr-x 1 postgres postgres  17K May 12 02:25 ./bin/common/reapply_indexes.py
-rwxr-xr-x 1 postgres postgres  12K May 12 02:25 ./bin/common/vacuum_maintenance.py
[postgres@mysql pg_partman-master]$ ll /home/postgres/pg13/share/extension/pg_par*
-rw-r--r-- 1 postgres postgres 2.8K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--0.1.0--0.1.1.sql
-rw-r--r-- 1 postgres postgres  36K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--0.1.1--0.1.2.sql
-rw-r--r-- 1 postgres postgres  21K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--0.1.2--0.2.0.sql
-rw-r--r-- 1 postgres postgres  12K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--0.2.0--0.3.0.sql
-rw-r--r-- 1 postgres postgres  20K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--0.3.0--0.3.1.sql
-rw-r--r-- 1 postgres postgres 9.2K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--0.3.1--0.3.2.sql
-rw-r--r-- 1 postgres postgres  25K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--0.3.2--0.4.0.sql
-rw-r--r-- 1 postgres postgres  20K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--0.4.0--0.4.1.sql
-rw-r--r-- 1 postgres postgres  28K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--0.4.1--0.4.2.sql
-rw-r--r-- 1 postgres postgres  64K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--0.4.2--1.0.0.sql
-rw-r--r-- 1 postgres postgres  28K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.0.0--1.1.0.sql
-rw-r--r-- 1 postgres postgres  49K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.1.0--1.2.0.sql
-rw-r--r-- 1 postgres postgres  35K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.2.0--1.3.0.sql
-rw-r--r-- 1 postgres postgres  88K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.3.0--1.4.0.sql
-rw-r--r-- 1 postgres postgres  15K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.4.0--1.4.1.sql
-rw-r--r-- 1 postgres postgres 8.2K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.4.1--1.4.2.sql
-rw-r--r-- 1 postgres postgres  624 Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.4.2--1.4.3.sql
-rw-r--r-- 1 postgres postgres 8.4K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.4.3--1.4.4.sql
-rw-r--r-- 1 postgres postgres  598 Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.4.4--1.4.5.sql
-rw-r--r-- 1 postgres postgres  42K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.4.5--1.5.0.sql
-rw-r--r-- 1 postgres postgres 8.8K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.5.0--1.5.1.sql
-rw-r--r-- 1 postgres postgres 129K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.5.1--1.6.0.sql
-rw-r--r-- 1 postgres postgres  43K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.6.0--1.6.1.sql
-rw-r--r-- 1 postgres postgres  76K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.6.1--1.7.0.sql
-rw-r--r-- 1 postgres postgres  53K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.7.0--1.7.1.sql
-rw-r--r-- 1 postgres postgres  35K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.7.1--1.7.2.sql
-rw-r--r-- 1 postgres postgres 161K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.7.2--1.8.0.sql
-rw-r--r-- 1 postgres postgres  36K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.8.0--1.8.1.sql
-rw-r--r-- 1 postgres postgres  82K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.8.1--1.8.2.sql
-rw-r--r-- 1 postgres postgres  48K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.8.2--1.8.3.sql
-rw-r--r-- 1 postgres postgres 7.4K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.8.3--1.8.4.sql
-rw-r--r-- 1 postgres postgres  43K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.8.4--1.8.5.sql
-rw-r--r-- 1 postgres postgres  16K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.8.5--1.8.6.sql
-rw-r--r-- 1 postgres postgres 2.1K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.8.6--1.8.7.sql
-rw-r--r-- 1 postgres postgres  20K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.8.7--1.8.8.sql
-rw-r--r-- 1 postgres postgres 193K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.8.7--2.0.0.sql
-rw-r--r-- 1 postgres postgres 186K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--1.8.8--2.0.0.sql
-rw-r--r-- 1 postgres postgres 205K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--2.0.0--2.1.0.sql
-rw-r--r-- 1 postgres postgres 152K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--2.1.0--2.2.0.sql
-rw-r--r-- 1 postgres postgres  21K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--2.2.0--2.2.1.sql
-rw-r--r-- 1 postgres postgres  398 Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--2.2.1--2.2.2.sql
-rw-r--r-- 1 postgres postgres  83K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--2.2.2--2.2.3.sql
-rw-r--r-- 1 postgres postgres 219K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--2.2.3--2.3.0.sql
-rw-r--r-- 1 postgres postgres  440 Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--2.3.0--2.3.1.sql
-rw-r--r-- 1 postgres postgres 129K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--2.3.1--2.3.2.sql
-rw-r--r-- 1 postgres postgres 221K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--2.3.2--2.3.3.sql
-rw-r--r-- 1 postgres postgres 213K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--2.3.3--2.3.4.sql
-rw-r--r-- 1 postgres postgres 107K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--2.3.4--2.4.0.sql
-rw-r--r-- 1 postgres postgres 6.3K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--2.4.0--2.4.1.sql
-rw-r--r-- 1 postgres postgres  65K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--2.4.1--2.5.0.sql
-rw-r--r-- 1 postgres postgres  30K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--2.5.0--2.5.1.sql
-rw-r--r-- 1 postgres postgres  89K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--2.5.1--2.6.0.sql
-rw-r--r-- 1 postgres postgres  85K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--2.6.0--2.6.1.sql
-rw-r--r-- 1 postgres postgres  72K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--2.6.1--2.6.2.sql
-rw-r--r-- 1 postgres postgres 109K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--2.6.2--2.6.3.sql
-rw-r--r-- 1 postgres postgres 2.7K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--2.6.3--2.6.4.sql
-rw-r--r-- 1 postgres postgres 283K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--2.6.4--3.0.0.sql
-rw-r--r-- 1 postgres postgres 1.8K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--3.0.0--3.0.1.sql
-rw-r--r-- 1 postgres postgres  81K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--3.0.1--3.0.2.sql
-rw-r--r-- 1 postgres postgres 146K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--3.0.2--3.1.0.sql
-rw-r--r-- 1 postgres postgres  39K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--3.1.0--3.1.1.sql
-rw-r--r-- 1 postgres postgres  833 Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--3.1.1--3.1.2.sql
-rw-r--r-- 1 postgres postgres  39K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--3.1.2--3.1.3.sql
-rw-r--r-- 1 postgres postgres  73K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--3.1.3--3.2.0.sql
-rw-r--r-- 1 postgres postgres 6.1K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--3.2.0--3.2.1.sql
-rw-r--r-- 1 postgres postgres 254K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--3.2.1--4.0.0.sql
-rw-r--r-- 1 postgres postgres 133K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--4.0.0--4.1.0.sql
-rw-r--r-- 1 postgres postgres 118K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--4.1.0--4.2.0.sql
-rw-r--r-- 1 postgres postgres  14K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--4.2.0--4.2.1.sql
-rw-r--r-- 1 postgres postgres 9.0K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--4.2.1--4.2.2.sql
-rw-r--r-- 1 postgres postgres 150K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--4.2.2--4.3.0.sql
-rw-r--r-- 1 postgres postgres  93K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--4.3.0--4.3.1.sql
-rw-r--r-- 1 postgres postgres 128K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--4.3.1--4.4.0.sql
-rw-r--r-- 1 postgres postgres 323K Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman--4.4.0.sql
-rw-r--r-- 1 postgres postgres  111 Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman.control


-rw-r--r-- 1 postgres postgres  111 Jun  7 16:05 /home/postgres/pg13/share/extension/pg_partman.control
[postgres@mysql pg_partman-master]$ ll /home/postgres/pg13/share/doc/extension/pg_par*
-rw-r--r-- 1 postgres postgres  52K Jun  7 16:05 /home/postgres/pg13/share/doc/extension/pg_partman_howto.md
-rw-r--r-- 1 postgres postgres 104K Jun  7 16:05 /home/postgres/pg13/share/doc/extension/pg_partman.md

使用

-- 查看插件是否存在
postgres=# select * from pg_available_extensions where name like 'pg_part%';
    name    | default_version | installed_version |                       comment                        
------------+-----------------+-------------------+------------------------------------------------------
 pg_partman | 4.4.0           | 4.4.0             | Extension to manage partitioned tables by time or ID
(1 row)


-- 创建扩展
create extension if not EXISTS pg_partman;

-- 创建测试表
drop table  if exits test
create table test(id int primary key , name varchar, create_time TIMESTAMP without time zone not null default clock_timestamp() );

-- 注意，要想使用pg_partman查看，需要注意父表分表字段必须有非空约束
 SELECT create_parent('public.test', 'create_time', 'partman', 'daily'，'{id}',20);
 
-- 查看分表，发现默认是创建9张表
 postgres=# \d+ test
                                                     Table "public.test"
   Column    |            Type             | Collation | Nullable |      Default      | Storage  | Stats target | Description 
-------------+-----------------------------+-----------+----------+-------------------+----------+--------------+-------------
 id          | integer                     |           | not null |                   | plain    |              | 
 name        | character varying           |           |          |                   | extended |              | 
 create_time | timestamp without time zone |           | not null | clock_timestamp() | plain    |              | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
Triggers:
    test_part_trig BEFORE INSERT ON test FOR EACH ROW EXECUTE FUNCTION test_part_trig_func()
Child tables: test_p2020_06_03,
              test_p2020_06_04,
              test_p2020_06_05,
              test_p2020_06_06,
              test_p2020_06_07,
              test_p2020_06_08,
              test_p2020_06_09,
              test_p2020_06_10,
              test_p2020_06_11
Access method: heap

研究函数 create_parent

create_parent(p_parent_table text, p_control text, p_type text, p_interval text, p_constraint_cols text[] DEFAULT NULL, p_premake int DEFAULT 4, p_automatic_maintenance text DEFAULT 'on', p_start_partition text DEFAULT NULL, p_inherit_fk boolean DEFAULT true, p_epoch text DEFAULT 'none', p_upsert text DEFAULT '', p_publications text[] DEFAULT NULL, p_trigger_return_null boolean DEFAULT true, p_template_table text DEFAULT NULL, p_jobmon boolean DEFAULT true, p_debug boolean DEFAULT false) RETURNS boolean

作用：

创建指定父表的若干子表集合,使用此函数的前提是父表已经存在；

说明：

请将所有默认值、索引、约束、特权和所有权应用于父表，这样它们将传播到子表
对于本地分区，必须已经声明父表，并且传递给该函数的配置选项必须与该定义匹配
此函数运行期间，对父表执行 ACCESS EXCLUSIVE 锁。运行这个函数时没有移动任何数据，因此锁定应该很简短

参数解释：

p_parent_table ：需要创建分表的父表名称，需要带有模式限制(形如public.test)
p_control : 分区将基于的列。必须是基于时间或整数的列
p_type: 用于设置将要使用的分区类型, 下列值之一
- native: 本地分区
  - 使用 PostgreSQL 10 + 中内置的本地分区方法
  - 对于 PG11 + ，强烈建议使用本机分区而不是基于触发器的分区。 Pg10仍然缺乏本地分区的重要特性
- partman:基于触发器的分区
  - 使用 pg partman 的分区方法创建一个基于触发器的分区集
  - 是基于时间还是基于序列取决于控件列的数据类型以及是否设置了 p_epoch 标志
  - 最有效管理的分区的数量是由 part_config表中的优化触发器配置值决定的(默认值为4表示前4个分区和后4个分区的数据处理效果最好)
  - 如果给定的值对应的字表并不存在，则会将该行记录插入到父表中保存
  - 子表的创建和触发器函数通过run_maintenance函数保持最新
p_interval : 每个分区的时间间隔或整数范围间隔,无论分区类型如何，值都必须以文本形式给出
- 当使用 pg partman 的基于触发器的划分时，给出其中一个明确的值，将比使用任意时间间隔提供更好的性能。
- 对于本机分区，任何间隔值都是有效的，并且具有相同的性能，总是优于基于触发器的分区
- 可取值如下：
  - yearly : 年度的
  - quarterly : 季度的
  - monthly : 月度的
  - weekly : 每周
  - daily ：每日
  - hourly ：每小时
  - half-hour：每半小时
  - quarter-hour：每15分钟
  - ：除了上面的值以外，对于 PostgreSQL 间隔类型有效的任何其他间隔。请注意，如果不使用本机分区，将会严重影响性能。不要输入强制转换参数值，只保留文本形式
  - ：对于基于 ID 的分区，为每个分区设置 ID 的整数值范围。以文本格式输入此整数(“100”而不是100)。必须大于或等于10

实例

基于 partman (触发器方式) 创建分表

* drop table if exists test;
  create table test(id int primary key , name varchar, create_time timestamp  without time zone  not null default clock_timestamp());
  postgres=# select create_parent('public.test', 'create_time', 'partman', 'daily');
   create_parent 

---------------

 t
(1 row)

postgres=# 
postgres=# \d+ test
                                                     Table "public.test"
   Column    |            Type             | Collation | Nullable |      Default      | Storage  | Stats target | Description 
-------------+-----------------------------+-----------+----------+-------------------+----------+--------------+-------------
 id          | integer                     |           | not null |                   | plain    |              | 
 name        | character varying           |           |          |                   | extended |              | 
 create_time | timestamp without time zone |           | not null | clock_timestamp() | plain    |              | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
Triggers:
    test_part_trig BEFORE INSERT ON test FOR EACH ROW EXECUTE FUNCTION test_part_trig_func()
Child tables: test_p2020_06_03,
              test_p2020_06_04,
              test_p2020_06_05,
              test_p2020_06_06,
              test_p2020_06_07,
              test_p2020_06_08,
              test_p2020_06_09,
              test_p2020_06_10,
              test_p2020_06_11
Access method: heap

基于 native 创建分表

  * drop table if exists test2;
    create table test2(
    id int,
    name varchar, 
    create_time timestamp  without time zone  not null default clock_timestamp(),
    constraint pk_test2 primary key(id, create_time)
    ) partition by range(create_time);
  
  postgres=# select create_parent('public.test2', 'create_time', 'native', 'daily');
  
   create_parent 
  ---------------
  
   t
  (1 row)
  
  postgres=# \d+ test2
                                                 Partitioned table "public.test2"
     Column    |            Type             | Collation | Nullable |      Default      | Storage  | Stats target | Description 
  -------------+-----------------------------+-----------+----------+-------------------+----------+--------------+-------------
   id          | integer                     |           | not null |                   | plain    |              | 
   name        | character varying           |           |          |                   | extended |              | 
   create_time | timestamp without time zone |           | not null | clock_timestamp() | plain    |              | 
  Partition key: RANGE (create_time)
  Indexes:
      "pk_test2" PRIMARY KEY, btree (id, create_time)
  Partitions: test2_p2020_06_03 FOR VALUES FROM ('2020-06-03 00:00:00') TO ('2020-06-04 00:00:00'),
              test2_p2020_06_04 FOR VALUES FROM ('2020-06-04 00:00:00') TO ('2020-06-05 00:00:00'),
              test2_p2020_06_05 FOR VALUES FROM ('2020-06-05 00:00:00') TO ('2020-06-06 00:00:00'),
              test2_p2020_06_06 FOR VALUES FROM ('2020-06-06 00:00:00') TO ('2020-06-07 00:00:00'),
              test2_p2020_06_07 FOR VALUES FROM ('2020-06-07 00:00:00') TO ('2020-06-08 00:00:00'),
              test2_p2020_06_08 FOR VALUES FROM ('2020-06-08 00:00:00') TO ('2020-06-09 00:00:00'),
              test2_p2020_06_09 FOR VALUES FROM ('2020-06-09 00:00:00') TO ('2020-06-10 00:00:00'),
              test2_p2020_06_10 FOR VALUES FROM ('2020-06-10 00:00:00') TO ('2020-06-11 00:00:00'),
              test2_p2020_06_11 FOR VALUES FROM ('2020-06-11 00:00:00') TO ('2020-06-12 00:00:00'),
              test2_default DEFAULT
  
  postgres=#

再来一个实例

  drop table if exists test3;
  delete from part_config where parent_table='public.test3';
  create table test3(
  	id int,
  	name varchar, 
  	create_time timestamp  without time zone  not null default clock_timestamp(),
  	constraint pk_test3 primary key(id, create_time)
  ) partition by range(create_time);
  
  select create_parent('public.test3' , 'create_time', 'native', 'daily',  NULL,20,  'on',(now()+interval '2 days')::text);
  
  结果如下所示：
  postgres=# \d+ test3
                                                 Partitioned table "public.test3"
     Column    |            Type             | Collation | Nullable |      Default      | Storage  | Stats target | Description 
  -------------+-----------------------------+-----------+----------+-------------------+----------+--------------+-------------
   id          | integer                     |           | not null |                   | plain    |              | 
   name        | character varying           |           |          |                   | extended |              | 
   create_time | timestamp without time zone |           | not null | clock_timestamp() | plain    |              | 
  Partition key: RANGE (create_time)
  Indexes:
      "pk_test3" PRIMARY KEY, btree (id, create_time)
  Partitions: test3_p2020_06_09 FOR VALUES FROM ('2020-06-09 00:00:00') TO ('2020-06-10 00:00:00'),
              test3_p2020_06_10 FOR VALUES FROM ('2020-06-10 00:00:00') TO ('2020-06-11 00:00:00'),
              test3_p2020_06_11 FOR VALUES FROM ('2020-06-11 00:00:00') TO ('2020-06-12 00:00:00'),
              test3_p2020_06_12 FOR VALUES FROM ('2020-06-12 00:00:00') TO ('2020-06-13 00:00:00'),
              test3_p2020_06_13 FOR VALUES FROM ('2020-06-13 00:00:00') TO ('2020-06-14 00:00:00'),
              test3_p2020_06_14 FOR VALUES FROM ('2020-06-14 00:00:00') TO ('2020-06-15 00:00:00'),
              test3_p2020_06_15 FOR VALUES FROM ('2020-06-15 00:00:00') TO ('2020-06-16 00:00:00'),
              test3_p2020_06_16 FOR VALUES FROM ('2020-06-16 00:00:00') TO ('2020-06-17 00:00:00'),
              test3_p2020_06_17 FOR VALUES FROM ('2020-06-17 00:00:00') TO ('2020-06-18 00:00:00'),
              test3_p2020_06_18 FOR VALUES FROM ('2020-06-18 00:00:00') TO ('2020-06-19 00:00:00'),
              test3_p2020_06_19 FOR VALUES FROM ('2020-06-19 00:00:00') TO ('2020-06-20 00:00:00'),
              test3_p2020_06_20 FOR VALUES FROM ('2020-06-20 00:00:00') TO ('2020-06-21 00:00:00'),
              test3_p2020_06_21 FOR VALUES FROM ('2020-06-21 00:00:00') TO ('2020-06-22 00:00:00'),
              test3_p2020_06_22 FOR VALUES FROM ('2020-06-22 00:00:00') TO ('2020-06-23 00:00:00'),
              test3_p2020_06_23 FOR VALUES FROM ('2020-06-23 00:00:00') TO ('2020-06-24 00:00:00'),
              test3_p2020_06_24 FOR VALUES FROM ('2020-06-24 00:00:00') TO ('2020-06-25 00:00:00'),
              test3_p2020_06_25 FOR VALUES FROM ('2020-06-25 00:00:00') TO ('2020-06-26 00:00:00'),
              test3_p2020_06_26 FOR VALUES FROM ('2020-06-26 00:00:00') TO ('2020-06-27 00:00:00'),
              test3_p2020_06_27 FOR VALUES FROM ('2020-06-27 00:00:00') TO ('2020-06-28 00:00:00'),
              test3_default DEFAULT

PG Partition Manager 分区管理器

PG Partition Manager 分区管理器

简介

下载

编译安装

查看生成的文件

使用

研究函数 create_parent

相关阅读

相关文章

相关问答

相关文档