PG Partition Manager 分区管理器
简介
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()
研究函数 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,
- 基于 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
