66. pg_partman
66.1. 插件pg_partman简介
66.1.1. 功能和发展
partman是pg的一个扩展，可帮助使用者更轻松地管理基于time或serialid的分区表。partman只需要几个选项，就可以设置分区。目前，触发器函数仅处理对父表的插入。仅在PG 11 本地分区中支持将值从一个分区移动到另一个分区的update。
partman从版本3.0.1开始，支持PG10中引入的本地分区的方法。在本地分区中不再需要触发器函数，但自动子表创建本地分区不处理，partman会做子表自动创建。partman版本4.0.0 增加了对 PG11 中引入本地分区功能（更简单的index/fk继承、默认分区）的更多支持。
请注意，PG本地分区尚不支持基于触发器的分区的所有功能，但读取和写入的性能都明显更好。KingbaseES中pg_partman的版本是4.4.0.
子表的创建全部由partman自身管理。对于非本地分区，插件还会维护触发器函数。具有现有数据的表，在非本地分区中，可以将其数据分区为易于管理的小批次；在本地分区中，必须首先创建新的父分区，并在创建过程完成后迁移数据。
66.1.2. 插入数据机制解释
对于非本地分区，如果尝试将数据插入到超过分区集范围的分区集中，则该数据将放入该集的父表中。与自动创建新分区以匹配该数据相比，这是首选，因为这种策略能够规避插入非分区数据的错误可能会导致产生大量不需要的子表以及由于DDL事务而产生的竞争。
check_default()函数提供对插入到父默认表中的数据的监视，如果数据是有效数据，则 partition_data_* 函数集可以轻松地为该数据分区。这比必须清理可能数百或数千个不需要的分区要容易得多，而且也比抛出错误并丢失数据更好！
对于本地分区，插入没有相关子分区的数据会导致PG 10 中出现错误。本地的默认分区仅在PG 11 +中可用。
66.1.3. 插入数据的策略
请注意，将来的子表创建基于分区集中的当前数据。这意味着，如果放入"未来"数据，则新创建的表将不基于该值。如果不存在子表，这可能会导致在这期间的数据转到父/默认值，如上所述。
建议你将预制值设置得足够高，以包含要插入的预期数据范围。对于非本地分区，请设置optimize_trigger值以有效地处理最常见的数据范围。
66.1.4. partman的设计约束和特性
66.1.4.1. 子分区属性继承
对于此扩展，子分区的大多数属性都是从父表中获取的。对于非本地的、基于触发器的分区，所有属性都来自父分区，并且将始终如此。但是，使用本地分区，某些功能无法从父级继承，具体取决于PG的版本。因此，partman使用模板代替。下表矩阵显示了如何使用本地分区pg_partman管理某些适当的继承。如果此处未列出某个属性，则假定它是通过父级管理的。
| Feature | Parent Inheritance | Template Inheritance |
|---|---|---|
| non-partition column primary key | All | |
| non-partition column unique index | All | |
| non-partition column unique index tablespace | All | |
| unlogged table state* | All | |
| non-unique indexes | 11, 12 | 10 |
| foreign keys | 11, 12 | 10 |
| tablespaces | 12 | 10, 11 |
| privileges/ownership | All | |
| constraints | All | |
| defaults | All |
默认情况下，对于非本地分区，所有权是继承的，但对于本地分区则不是。另请注意，此继承仅在创建子表时进行，并且在更改时不会自动追溯。除非你需要直接访问子表，否则不需要这样做。如果需要，你可以设置inherit_privileges选项。
如果某个属性是通过模板表管理的，则在更改该属性时，它可能不会追溯应用于所有现有的子表。它将应用于任何新创建的子项，但必须手动应用于任何现有子项。
PG10中引入的新IDENTITY功能仅在本地分区表中受支持，并且仅当数据通过父表插入而不是直接插入子表时，才支持使用此功能自动生成新序列值。
66.1.4.2. 时区
保证partman运行的操作系统的时区一致性非常重要，尤其是使用基于时间建立的分区。对pg_partman函数的调用将使用客户端在调用函数时设置的时区。这与PG客户端的一般工作方式一致。
强烈建议在 UTC时间内运行数据库系统，以解决由于夏令时更改而无法解决的问题。然后，还要确保将创建分区集和运行维护调用的客户端也设置为UTC。
66.1.4.3. 子分区
支持具有多个级别的子分区，但它在PG中的用途非常有限，并且在单个分区集（100TB，PB）中的超大数据之外，它几乎没有性能优势。如果你正在寻找提高性能的方法，请考虑在使用子分区之前调整分区间隔，它是数据组织和保留管理的主要方法。
对可以执行的子分区级别没有设置限制，但要明智，并牢记在单个继承集中管理多个表时的性能注意事项。此外，如果单个分区集中的表数变得非常大，则可能必须将max_locks_per_transactionpostgresql.conf 设置调整到默认值 64以上。否则，你可能会遇到共享内存问题，甚至是群集崩溃问题。
如果在调用run_maintenance()对所有分区集进行常规维护时遇到竞争问题，可以将part_config表中的automatic_maintenance 列设置为false。但是，你必须调用run_maintenance（parent_table），并且必须足够频繁地调用将来的分区。
如果你使用的是PG11+，则可以使用新的run_maintenance_proc（）过程来减少竞争问题，因为它在每个分区集的维护后会自动提交。本地子分区不支持逻辑复制的发布者/预订者机制。
66.1.4.4. Retention
如果不需要将数据保留在较旧的分区中，则可以使用保留机制来自动删除不需要的子分区。默认情况下，它们只是未继承/分离的，实际上不会被删除，但如果需要，可以对其进行配置。
还有一种方法可以在表不再需要位于数据库中但仍需要保留，要设置保留策略，请在part_config表的保留列中输入间隔或整数值。对于基于时间的分区，间隔值将设置仅包含早于该时间的数据的任何分区将被删除；
对于基于id 的分区，整数值将设置 id 值小于当前最大 id值减去保留值的任何分区。例如，如果当前最大 id 为 100，保留值为 30，则 id值小于 70 的任何分区都将被删除。drop函数运行始终参考的当前最大 id值。
请记住，对于子分区集，当父表删除了子表时，如果该子表又被分区，则该删除是级联，在这个继承树下面的子表都将被删除。另请注意，由pg_partman管理的分区集必须始终至少有一个子级，因此保留机制永远不会删除集中的最后一个子表。
66.1.4.5. 约束排除
约束排除是partman的一大特性。但是，大多数分区设置的问题在于，这将仅用于分区控制列。如果对分区集中的任何其他列使用WHERE条件，则将对所有子表进行扫描，除非这些列上也存在约束。
预测列的值将是什么来预先创建约束可能非常困难或不可能。pg_partman具有对分区集中的旧表应用约束的功能，该分区集可能不再对它们进行任何编辑（"old"被定义为早于optimize_constraint配置值）。
它检查给定列中的当前最小值/最大值，然后对该子表应用约束。这可以允许约束排除功能在WHERE条件下使用其他列时，潜在地消除扫描较旧的子表。这限制了编辑这些列的能力，但对于适用这些列的情况，它可能会对非常大的分区集的查询性能产生巨大影响。
因此，如果你只插入新数据，这可能非常有用，但如果在整个分区集中定期插入/更新数据，则作用有限。如果数据最终必须在那些较旧的分区中进行编辑，partman还提供了轻松创建约束的功能。
66.1.4.6. 自定义时间间隔注意事项
create_parent函数中提供的时间间隔列表经过优化，可在非本地、基于触发器的分区中非常快地起作用。除了这些值之外，还可以使用其他间隔，但非常影响性能。
对于本地分区，与pg_partman基于触发器的方法不同，对于任何给定的时间间隔，没有不同的分区方法。使用本地方法的所有可能的时间间隔都具有相同的性能特征，并且比任何基于触发器的方法都更好。
如果需要的分区间隔不同于pg_partman提供的优化间隔，强烈建议升级到PG10。支持的最小时间间隔为1秒，上限由PG支持的最小和最大时间戳值限制，此时支持的最小整数间隔为10。
首次运行 create_parent函数创建分区集时，在确定要创建的第一个分区时，小于一天的会向小时舍入。例如小于24 小时但大于 1 分钟的间隔舍入的最近小时。小于 1分钟的间隔使用舍入的最接近的分钟。
当运行 create_parent时，可能创建的分区多于预期，并且可能不会创建所有的分区。第一次运行run_maintenance将修复缺少的分区，发生这种情况是由于partman支持自定义时间间隔的特性造成的。
对于等于或大于100年的间隔，partman将使用千年的实际开始来确定分区名称和约束规则。例如，21世纪和第3个千禧年开始于2001年1月1日（不是2000年）。
66.1.4.7. 命名长度限制
PG的对象命名长度限制为63个字符。因此，如果partman尝试创建具有较长名称的对象，则在命名限制之下，将会自动截断对象名称中超出63个字符的部分。这可能会导致依赖于具有专门命名的后缀的分区名称出现明显问题。
partman会自动处理所有子表、触发器函数和触发器的此问题。它将截断现有的父表名称以适合所需的后缀。
请注意，如果表的名称很长，名称相似，则当它们是单独分区集的一部分时，可能会遇到命名冲突。使用基于串行的分区时，请注意，随着时间的推移，表名将被越来越多地截断，以适应更长的分区后缀。因此，建议保留将分区的表名尽可能短。
66.1.4.8. 唯一约束和更新
PG中的表继承不允许将父表上的主键或唯一索引/约束应用于所有子表。该约束应用于每个单独的表，但不应用于整个分区集。
例如，这意味着粗心大意的应用程序可能会导致分区集中的主键值重复。同时，check_unique_constraint.py脚本包含在pg_partman中，可以提供监视。
重要说明：自 4.6.0 版和 PG 11+起，pg_partman不再支持本地分区的更新插入。请使用PG自带的INSERT...ONCONFLICT功能。
对于非本地分区和 PG10 本地分区，INSERT ...ONCONFLICT（upsert）在分区触发器和本地分区中都受支持，但都是受限制的。主要限制是，上面提到的，触发ON CONFLICT子句的约束冲突仅发生在实际包含数据的单个子表上。
比数据重复更大的问题是ONCONFLICT DOUPDATE从句，它可能不会触发并导致数据非常不一致。对于仅插入新数据的情况，upsert可以提供显著的性能改进。
但是，如果你依赖较旧分区中的数据来导致upsert 通常会处理的约束冲突，则它可能不起作用。此外，如果生成的 UPDATE最终违反了该子表的分区约束，它将失败。
pg_partman和 PG10本地分区目前都不支持需要将行从一个子表移动到另一个子表的 UPDATES。这仅在PG11以上的版本中支持。
pg_partman的更新插入功能是可选的，默认情况下关闭，并且仅包含在没有PG支持的版本上。目前，如果你尚未实现此功能，强烈建议你升级到PG11。
66.1.4.9. 日志记录/监控
PG的Jobmon插件是可选的，允许审核和监视分区维护。如果 jobmon安装并配置正确，partman将自动使用它，无需额外设置。还可以通过使用part_config表中的 jobmon列或在初始设置期间选择create_parent（） 来为每个分区集单独打开或关闭Jobmon。
请注意，如果你尝试对使用pg_jobmon的表进行分区，则必须将create_parent（） 中的 jobmon 选项设置为false，否则它将被放入永久死锁，因为pg_jobmon将尝试写入时，就会分区。默认情况下，任何连续3次无法成功运行的函数都会导致 jobmon引发警报。
这就是为什么默认的预做值设置为4，以便及时引发警报以进行干预，而无需对 jobmon进行额外配置。当然，如果需要，你可以将 jobmon 配置为在 3次故障之前（或之后）发出警报。
如果你在生产环境中运行partman，强烈建议你安装jobmon并配置某种第三方监控（Nagios，Circonus等），以便在分区失败时发出警报。
66.1.4.10. 后台进程
在PG 9.4中，引入了创建自定义后台工作线程并在运行时动态加载它们的功能。pg_partman的BGW基本上只是一个调度程序，它为你运行run_maintenance（）函数，因此你不必使用外部调度程序（cron等）。
现在，它与直接调用run_maintenance（）没有任何不同，但将来可能会改变。如果需要直接在任何特定分区集上调用run_maintenance（），则仍需要使用外部调度程序手动执行此操作。这仅将part_config 中的automatic_maintenance设置为 true的分区集。
日志消息被输出到正常的PG日志文件，以记录BGW何时运行。如果log_min_messages设置为"DEBUG1"，则其他级别的日志记录消息被记录。
66.2. 插件pg_partman加载方式
KingbaseES数据库默认不加载pg_partman, 如果用户需要使用pg_partman的相关功能，请人为手动使用以下命令加载pg_partman插件。
示例：
create extension pg_partman;
66.3. 插件pg_partman的参数配置
无需配置任何参数
66.4. 插件pg_partman的使用方法
从 4.4.0 开始，SECURITY DEFINER 已从 pg_partman中的所有函数中删除。现在，要求超级用户使用pg_partman对于本地分区是可选的。
若要以非超级用户身份运行，运行pg_partman功能和维护的角色必须拥有它们管理的所有分区集的所有权，并有权在将包含它所管理的分区集的任何架构中创建对象。为了便于使用和权限管理，建议创建专用于分区管理的角色。
66.4.1. 函数
66.4.1.1. create函数
用于创建具有一个父表和继承子表的分区集
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_date_trunc_interval text DEFAULT NULL) RETURNS boolean
创建已存在的分区集的子分区集。
create_sub_parent(p_top_parent text, p_control text, p_type text, p_interval text, p_native_check text DEFAULT NULL, p_constraint_cols text[] DEFAULT NULL, p_premake int DEFAULT 4, p_start_partition text DEFAULT NULL, p_inherit_fk boolean DEFAULT true, p_epoch text DEFAULT 'none', p_upsert text DEFAULT '', p_trigger_return_null boolean DEFAULT true, p_jobmon boolean DEFAULT true, p_date_trunc_interval text DEFAULT NULL) RETURNS boolean 对在将父表设置为基于时间的分区集之前可能存在的数据进行分区。
partition_data_time(p_parent_table text, p_batch_count int DEFAULT 1, p_batch_interval interval DEFAULT NULL, p_lock_wait numeric DEFAULT 0, p_order text DEFAULT 'ASC', p_analyze boolean DEFAULT true, p_source_table text DEFAULT NULL, p_ignored_columns text[] DEFAULT NULL) RETURNS bigint
在将父表设置为serial id 分区集之前可能存在的分区数据。
partition_data_id(p_parent_table text, p_batch_count int DEFAULT 1, p_batch_interval bigint DEFAULT NULL, p_lock_wait numeric DEFAULT 0, p_order text DEFAULT 'ASC', p_analyze boolean DEFAULT true, p_source_table text DEFAULT NULL, p_ignored_columns text[] DEFAULT NULL) RETURNS bigint
可以在不同的提交批处理中对数据进行分区，以避免长时间运行的事务和数据竞争问题。
partition_data_proc(p_parent_table text, p_interval text DEFAULT NULL, p_batch int DEFAULT NULL, p_wait int DEFAULT 1, p_source_table text DEFAULT NULL, p_order text DEFAULT 'ASC', p_lock_wait int DEFAULT 0, p_lock_wait_tries int DEFAULT 10, p_quiet boolean DEFAULT false, p_ignored_columns text[] DEFAULT NULL)
为给定的基于时间父表创建子分区。
create_partition_time(p_parent_table text, p_partition_times timestamptz[], p_analyze boolean DEFAULT true, p_start_partition text DEFAULT NULL) RETURNS boolean
为给定的基于serial id父表创建子分区。
create_partition_id(p_parent_table text, p_partition_ids bigint[], p_analyze boolean DEFAULT true, p_start_partition text DEFAULT NULL) RETURNS boolean
为非本地基于时间的分区创建触发器函数
create_function_time(p_parent_table text, p_job_id bigint DEFAULT NULL) RETURNS void
为非本地基于serial id的分区创建触发器函数
create_function_id(p_parent_table text, p_job_id bigint DEFAULT NULL) RETURNS void
66.4.1.2. 维护函数
将此函数作为计划作业（cron等）运行，以便为配置为使用它的分区集自动创建子表
run_maintenance(p_parent_table text DEFAULT NULL, p_analyze boolean DEFAULT NULL, p_jobmon boolean DEFAULT true) RETURNS void
对于PG11以上，这是运行分区维护与直接调用 run_maintenance（）函数的首选方法
run_maintenance_proc(p_wait int DEFAULT 0, p_analyze boolean DEFAULT NULL, p_jobmon boolean DEFAULT true)
运行此函数以监视pg_partman管理的分区集的父表（非本地表）或默认表（本地PG11+）是否未向其插入行。
check_default(p_exact_count boolean DEFAULT true)
列出由pg_partman管理的给定分区集的所有子表。
show_partitions (p_parent_table text, p_order text DEFAULT 'ASC', p_include_default boolean DEFAULT false) RETURNS TABLE (partition_schemaname text, partition_tablename text)
给定由pg_partman管理的父表和适当的值，返回该值将存在于其中的子分区的名称。
show_partition_name (p_parent_table text, p_value text, OUT partition_table text, OUT suffix_timestamp timestamp, OUT suffix_id bigint, OUT table_exists boolean)
给定一个schema限定的子表名，返回该子表的相关边界值以及追加到子表名的后缀。
@extschema@.show_partition_info (p_child_table text, p_partition_interval text DEFAULT NULL, p_parent_table text DEFAULT NULL, OUT child_start_time timestamptz, OUT child_end_time timestamptz, OUT child_start_id bigint, OUT child_end_id bigint, OUT suffix text) RETURNS record
用于返回必要命令以在pg_partman中为给定的父表重新创建分区集的函数
@extschema@.dump_partitioned_table_definition(p_parent_table text, p_ignore_template_table boolean default false) RETURNS text
用于填充给定父表的子表系列中可能存在的间隔的函数
partition_gap_fill(p_parent_table text) RETURNS integer
将约束应用于为已配置的列的给定分区集中的子表
apply_constraints(p_parent_table text, p_child_table text DEFAULT NULL, p_analyze boolean DEFAULT FALSE, p_job_id bigint DEFAULT NULL) RETURNS void
删除由pg_partman为在part_config中配置的列创建的约束。
drop_constraints(p_parent_table text, p_child_table text, p_debug boolean DEFAULT false)
PG11以上，pg_partman管理的额外约束
reapply_constraints_proc(p_parent_table text, p_drop_constraints boolean DEFAULT false, p_apply_constraints boolean DEFAULT false, p_wait int DEFAULT 0, p_dryrun boolean DEFAULT false)
根据父表设置的内容，对所有子表重新应用所有权和grants。
reapply_privileges(p_parent_table text)
将分区集中的父表上存在的任何外键应用于所有子表。PG10之前使用
apply_foreign_keys(p_parent_table text, p_child_table text DEFAULT NULL, p_job_id bigint DEFAULT NULL, p_debug boolean DEFAULT false)
从part_config_sub表中删除parent_table条目
stop_sub_partition(p_parent_table text, p_jobmon boolean DEFAULT true) RETURNS boolean
66.4.1.3. 销毁函数
销毁由partman创建的函数
undo_partition(p_parent_table text, p_batch_count int DEFAULT 1, p_batch_interval text DEFAULT NULL, p_keep_table boolean DEFAULT true, p_lock_wait numeric DEFAULT 0, p_target_table text DEFAULT NULL, p_ignored_columns text[] DEFAULT NULL, p_drop_cascade boolean DEFAULT false, OUT partitions_undone int, OUT rows_undone bigint) RETURNS record
在不同的提交批处理中取消分区数据，以避免长时间运行的事务和数据竞争问题
undo_partition_proc(p_parent_table text, p_interval text DEFAULT NULL, p_batch int DEFAULT NULL, p_wait int DEFAULT 1, p_target_table text DEFAULT NULL, p_keep_table boolean DEFAULT true, p_lock_wait int DEFAULT 0, p_lock_wait_tries int DEFAULT 10, p_quiet boolean DEFAULT false, p_ignored_columns text[] DEFAULT NULL, p_drop_cascade boolean DEFAULT false)
从基于时间的分区集中删除子表
drop_partition_time(p_parent_table text, p_retention interval DEFAULT NULL, p_keep_table boolean DEFAULT NULL, p_keep_index boolean DEFAULT NULL, p_retention_schema text DEFAULT NULL) RETURNS int
从基于serial id的分区集中删除子表
drop_partition_id(p_parent_table text, p_retention bigint DEFAULT NULL, p_keep_table boolean DEFAULT NULL, p_keep_index boolean DEFAULT NULL, p_retention_schema text DEFAULT NULL) RETURNS int
根据添加列的时间（在设置分区之前或之后），将其放在父级上可能会或可能不会从所有子级中删除它。
drop_partition_column(p_parent_table text, p_column text) RETURNS void
66.4.1.4. 脚本
创建分区使用小批量的脚本。pg10以下才会提供，10以上用partition_data_proc()。
partition_data.py
撤掉本地分区中使用小批量的脚本。pg10以下才会提供，10以上用undo_partition_proc()
undo_partition.py
使用已有schema提取表中数据。
dump_partition.py
清理一个已有分区中的死数据。
vacuum_maintenance.py
在分区集中的子表上更改索引后，重新应用这些索引。
reapply_indexes.py
为表中配置的列重做给定分区集中子表part_config约束。pg10以下才会提供，10以上用reapply_constraints_proc()
reapply_constraints.py
重做整个分区集的继承外键。只在pg10以下才会提供
reapply_foreign_keys.py
检查分区集中的所有行对于给定列是否唯一。
check_unique_constraints.py
66.4.1.5. 编译
在KingbaseES编译之前需要确保contrib目录的makefile已经添加了pg_partman，可以随KingbaseES编译。
66.4.1.6. 配置
在原生pg中，在./src/backend/utils/misc/portgresql.conf中的shared_preload_libraries中可以添加pg_partman以支持它的使用。修改配置文件之后需要启动之前或者重启之后才会生效。
在KingbaseES中，无需以上操作。
66.4.1.7. 示例
66.4.1.7.1. 创建本地分区
一天一分区
用模版添加主键
CREATE SCHEMA IF NOT EXISTS partman;
CREATE TABLE partman.time_taptest_table
(col1 int,
col2 text default 'stuff',
col3 timestamptz NOT NULL DEFAULT now())
PARTITION BY RANGE (col3);
CREATE INDEX ON partman.time_taptest_table (col3);
\d+ partman.time_taptest_table
Partitioned table "partman.time_taptest_table"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------------+----------+--------------+-------------
col1 | integer | | | | plain | |
col2 | text | | | 'stuff'::text | extended | |
col3 | timestamp with time zone | | not null | now() | plain | |
Partition key: RANGE (col3)
Indexes:
"time_taptest_table_col3_idx" btree (col3)
Number of partitions: 0
CREATE TABLE partman.time_taptest_table_template (LIKE partman.time_taptest_table); ALTER TABLE partman.time_taptest_table_template ADD PRIMARY KEY (col1);
\d partman.time_taptest_table_template
Table "partman.time_taptest_table_template"
Column | Type | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------
col1 | integer | | not null |
col2 | text | | |
col3 | timestamp with time zone | | not null |
Indexes:
"time_taptest_table_template_pkey" PRIMARY KEY, btree (col1)
SELECT partman.create_parent('partman.time_taptest_table', 'col3', 'native', 'daily', p_template_table := 'partman.time_taptest_table_template');
create_parent
---------------
t
(1 row)
\d+ partman.time_taptest_table
Partitioned table "partman.time_taptest_table"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------------+----------+--------------+-------------
col1 | integer | | | | plain | |
col2 | text | | | 'stuff'::text | extended | |
col3 | timestamp with time zone | | not null | now() | plain | |
Partition key: RANGE (col3)
Indexes:
"time_taptest_table_col3_idx" btree (col3)
Partitions: partman.time_taptest_table_p2020_10_26 FOR VALUES FROM ('2020-10-26 00:00:00-04') TO ('2020-10-27 00:00:00-04'),
partman.time_taptest_table_p2020_10_27 FOR VALUES FROM ('2020-10-27 00:00:00-04') TO ('2020-10-28 00:00:00-04'),
partman.time_taptest_table_p2020_10_28 FOR VALUES FROM ('2020-10-28 00:00:00-04') TO ('2020-10-29 00:00:00-04'),
partman.time_taptest_table_p2020_10_29 FOR VALUES FROM ('2020-10-29 00:00:00-04') TO ('2020-10-30 00:00:00-04'),
partman.time_taptest_table_p2020_10_30 FOR VALUES FROM ('2020-10-30 00:00:00-04') TO ('2020-10-31 00:00:00-04'),
partman.time_taptest_table_p2020_10_31 FOR VALUES FROM ('2020-10-31 00:00:00-04') TO ('2020-11-01 00:00:00-04'),
partman.time_taptest_table_p2020_11_01 FOR VALUES FROM ('2020-11-01 00:00:00-04') TO ('2020-11-02 00:00:00-05'),
partman.time_taptest_table_p2020_11_02 FOR VALUES FROM ('2020-11-02 00:00:00-05') TO ('2020-11-03 00:00:00-05'),
partman.time_taptest_table_p2020_11_03 FOR VALUES FROM ('2020-11-03 00:00:00-05') TO ('2020-11-04 00:00:00-05'),
partman.time_taptest_table_default DEFAULT
\d+ partman.time_taptest_table_p2020_10_26
Table "partman.time_taptest_table_p2020_10_26"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------------+----------+--------------+-------------
col1 | integer | | not null | | plain | |
col2 | text | | | 'stuff'::text | extended | |
col3 | timestamp with time zone | | not null | now() | plain | |
Partition of: partman.time_taptest_table FOR VALUES FROM ('2020-10-26 00:00:00-04') TO ('2020-10-27 00:00:00-04')
Partition constraint: ((col3 IS NOT NULL) AND (col3 >= '2020-10-26 00:00:00-04'::timestamp with time zone) AND (col3 < '2020-10-27 00:00:00-04'::timestamp with time zone))
Indexes:
"time_taptest_table_p2020_10_26_pkey" PRIMARY KEY, btree (col1)
"time_taptest_table_p2020_10_26_col3_idx" btree (col3)
Access method: heap
66.4.1.7.2. 10个id值一个分区
10个ID值一个分区
父表创建子表之后添加主键，子表不能自动添加主键，需要手动添加。
CREATE TABLE partman.id_taptest_table (
col1 bigint
, col2 text not null
, col3 timestamptz DEFAULT now()
, col4 text) PARTITION BY RANGE (col1);
CREATE INDEX ON partman.id_taptest_table (col1);
\d+ partman.id_taptest_table
Partitioned table "partman.id_taptest_table"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
col1 | bigint | | | | plain | |
col2 | text | | not null | | extended | |
col3 | timestamp with time zone | | | now() | plain | |
col4 | text | | | | extended | |
Partition key: RANGE (col1)
Indexes:
"id_taptest_table_col1_idx" btree (col1)
Number of partitions: 0
SELECT partman.create_parent('partman.id_taptest_table', 'col1', 'native', '10');
create_parent
---------------
t
(1 row)
\d+ partman.id_taptest_table
Partitioned table "partman.id_taptest_table"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
col1 | bigint | | | | plain | |
col2 | text | | not null | | extended | |
col3 | timestamp with time zone | | | now() | plain | |
col4 | text | | | | extended | |
Partition key: RANGE (col1)
Indexes:
"id_taptest_table_col1_idx" btree (col1)
Partitions: partman.id_taptest_table_p0 FOR VALUES FROM ('0') TO ('10'),
partman.id_taptest_table_p10 FOR VALUES FROM ('10') TO ('20'),
partman.id_taptest_table_p20 FOR VALUES FROM ('20') TO ('30'),
partman.id_taptest_table_p30 FOR VALUES FROM ('30') TO ('40'),
partman.id_taptest_table_p40 FOR VALUES FROM ('40') TO ('50'),
partman.id_taptest_table_default DEFAULT
select template_table from partman.part_config where parent_table = 'partman.id_taptest_table';
template_table
------------------------------------------------
partman.template_partman_id_taptest_table
ALTER TABLE partman.template_partman_id_taptest_table ADD PRIMARY KEY (col2);
INSERT INTO partman.id_taptest_table (col1, col2) VALUES (generate_series(1,20), generate_series(1,20)::text||'stuff'::text);
INSERT INTO partman.id_taptest_table (col1, col2) VALUES (generate_series(1,20), generate_series(1,20)::text||'stuff'::text);
CALL partman.run_maintenance_proc();
\d+ partman.id_taptest_table
Partitioned table "partman.id_taptest_table"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
col1 | bigint | | | | plain | |
col2 | text | | not null | | extended | |
col3 | timestamp with time zone | | | now() | plain | |
col4 | text | | | | extended | |
Partition key: RANGE (col1)
Indexes:
"id_taptest_table_col1_idx" btree (col1)
Partitions: partman.id_taptest_table_p0 FOR VALUES FROM ('0') TO ('10'),
partman.id_taptest_table_p10 FOR VALUES FROM ('10') TO ('20'),
partman.id_taptest_table_p20 FOR VALUES FROM ('20') TO ('30'),
partman.id_taptest_table_p30 FOR VALUES FROM ('30') TO ('40'),
partman.id_taptest_table_p40 FOR VALUES FROM ('40') TO ('50'),
partman.id_taptest_table_p50 FOR VALUES FROM ('50') TO ('60'),
partman.id_taptest_table_p60 FOR VALUES FROM ('60') TO ('70'),
partman.id_taptest_table_default DEFAULT
\d partman.id_taptest_table_p40
Table "partman.id_taptest_table_p40"
Column | Type | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------
col1 | bigint | | |
col2 | text | | not null |
col3 | timestamp with time zone | | | now()
col4 | text | | |
Partition of: partman.id_taptest_table FOR VALUES FROM ('40') TO ('50')
Indexes:
"id_taptest_table_p40_col1_idx" btree (col1)
\d partman.id_taptest_table_p50
Table "partman.id_taptest_table_p50"
Column | Type | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------
col1 | bigint | | |
col2 | text | | not null |
col3 | timestamp with time zone | | | now()
col4 | text | | |
Partition of: partman.id_taptest_table FOR VALUES FROM ('50') TO ('60')
Indexes:
"id_taptest_table_p50_pkey" PRIMARY KEY, btree (col2)
"id_taptest_table_p50_col1_idx" btree (col1)
\d partman.id_taptest_table_p60
Table "partman.id_taptest_table_p60"
Column | Type | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------
col1 | bigint | | |
col2 | text | | not null |
col3 | timestamp with time zone | | | now()
col4 | text | | |
Partition of: partman.id_taptest_table FOR VALUES FROM ('60') TO ('70')
Indexes:
"id_taptest_table_p60_pkey" PRIMARY KEY, btree (col2)
"id_taptest_table_p60_col1_idx" btree (col1)
ALTER TABLE partman.id_taptest_table_p0 ADD PRIMARY KEY (col2); ALTER TABLE partman.id_taptest_table_p10 ADD PRIMARY KEY (col2); ALTER TABLE partman.id_taptest_table_p20 ADD PRIMARY KEY (col2); ALTER TABLE partman.id_taptest_table_p30 ADD PRIMARY KEY (col2); ALTER TABLE partman.id_taptest_table_p40 ADD PRIMARY KEY (col2);
66.4.1.7.3. 分区一个已经存在的表
66.4.1.7.3.1. 离线分区
数据无法在迁移前后同时访问，优势在于可以分为比分区间隔更小的批次传输，同时只需要较少的重命名次数。
CREATE TABLE public.original_table (
col1 bigint not null
, col2 text not null
, col3 timestamptz DEFAULT now()
, col4 text);
CREATE INDEX ON public.original_table (col1);
INSERT INTO public.original_table (col1, col2, col3, col4) VALUES (generate_series(1,100000), 'stuff'||generate_series(1,100000), now(), 'stuff');
ALTER TABLE public.original_table RENAME to old_nonpartitioned_table;
CREATE TABLE public.original_table (
col1 bigint not null
, col2 text not null
, col3 timestamptz DEFAULT now()
, col4 text) PARTITION BY RANGE (col1);
CREATE INDEX ON public.original_table (col1);
SELECT partman.create_parent('public.original_table', 'col1', 'native', '10000');
\d+ original_table;
Partitioned table "public.original_table"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
col1 | bigint | | not null | | plain | |
col2 | text | | not null | | extended | |
col3 | timestamp with time zone | | | now() | plain | |
col4 | text | | | | extended | |
Partition key: RANGE (col1)
Indexes:
"original_table_col1_idx1" btree (col1)
Partitions: original_table_p0 FOR VALUES FROM ('0') TO ('10000'),
original_table_p10000 FOR VALUES FROM ('10000') TO ('20000'),
original_table_p20000 FOR VALUES FROM ('20000') TO ('30000'),
original_table_p30000 FOR VALUES FROM ('30000') TO ('40000'),
original_table_p40000 FOR VALUES FROM ('40000') TO ('50000'),
original_table_default DEFAULT
CALL partman.partition_data_proc('public.original_table', p_interval := '1000', p_batch := 200, p_source_table := 'public.old_nonpartitioned_table');
NOTICE: Batch: 1, Rows moved: 1000
NOTICE: Batch: 2, Rows moved: 1000
NOTICE: Batch: 3, Rows moved: 1000
NOTICE: Batch: 4, Rows moved: 1000
NOTICE: Batch: 5, Rows moved: 1000
NOTICE: Batch: 6, Rows moved: 1000
NOTICE: Batch: 7, Rows moved: 1000
NOTICE: Batch: 8, Rows moved: 1000
NOTICE: Batch: 9, Rows moved: 1000
NOTICE: Batch: 10, Rows moved: 999
NOTICE: Batch: 11, Rows moved: 1000
NOTICE: Batch: 12, Rows moved: 1000
[...]
NOTICE: Batch: 100, Rows moved: 1000
NOTICE: Batch: 101, Rows moved: 1
NOTICE: Total rows moved: 100000
NOTICE: Ensure to VACUUM ANALYZE the parent (and source table if used) after partitioning data
CALL
Time: 103206.205 ms (01:43.206)
VACUUM ANALYZE public.original_table;
VACUUM
Time: 352.973 ms
SELECT count(*) FROM old_nonpartitioned_table ;
count
-------
0
(1 row)
SELECT count(*) FROM original_table;
count
--------
100000
(1 row)
\d+ public.original_table
Partitioned table "public.original_table"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
col1 | bigint | | not null | | plain | |
col2 | text | | not null | | extended | |
col3 | timestamp with time zone | | | now() | plain | |
col4 | text | | | | extended | |
Partition key: RANGE (col1)
Indexes:
"original_table_col1_idx1" btree (col1)
Partitions: original_table_p0 FOR VALUES FROM ('0') TO ('10000'),
original_table_p10000 FOR VALUES FROM ('10000') TO ('20000'),
original_table_p100000 FOR VALUES FROM ('100000') TO ('110000'),
original_table_p20000 FOR VALUES FROM ('20000') TO ('30000'),
original_table_p30000 FOR VALUES FROM ('30000') TO ('40000'),
original_table_p40000 FOR VALUES FROM ('40000') TO ('50000'),
original_table_p50000 FOR VALUES FROM ('50000') TO ('60000'),
original_table_p60000 FOR VALUES FROM ('60000') TO ('70000'),
original_table_p70000 FOR VALUES FROM ('70000') TO ('80000'),
original_table_p80000 FOR VALUES FROM ('80000') TO ('90000'),
original_table_p90000 FOR VALUES FROM ('90000') TO ('100000'),
original_table_default DEFAULT
SELECT count(*) FROM original_table_p10000;
count
-------
10000
(1 row)
66.4.1.7.3.2. 在线分区
CREATE TABLE public.original_table (
col1 bigint not null PRIMARY KEY GENERATED ALWAYS AS IDENTITY
, col2 text not null
, col3 timestamptz DEFAULT now() not null
, col4 text);
CREATE INDEX CONCURRENTLY ON public.original_table (col3);
INSERT INTO public.original_table (col2, col3, col4) VALUES ('stuff', generate_series(now() - '1 week'::interval, now(), '5 minutes'::interval), 'stuff');
CREATE TABLE public.new_partitioned_table (
col1 bigint not null GENERATED BY DEFAULT AS IDENTITY
, col2 text not null
, col3 timestamptz DEFAULT now() not null
, col4 text) PARTITION BY RANGE (col3);
CREATE INDEX ON public.new_partitioned_table (col3);
CREATE TABLE public.new_partitioned_table (
col1 bigint not null PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
, col2 text not null
, col3 timestamptz DEFAULT now() not null
, col4 text) PARTITION BY RANGE (col3);
CREATE TABLE public.original_table_template (LIKE public.original_table); ALTER TABLE public.original_table_template ADD PRIMARY KEY (col1);
select min(col3), max(col3) from original_table;
min | max
-------------------------------+-------------------------------
2020-12-02 19:04:08.559646-05 | 2020-12-09 19:04:08.559646-05
(1 row)
SELECT partman.create_parent('public.new_partitioned_table', 'col3', 'native', 'daily', p_template_table:= 'public.original_table_template', p_premake := 1, p_start_partition := (CURRENT_TIMESTAMP+'2 days'::interval)::text);
default分区是partman自动创建的。
DROP TABLE public.new_partitioned_table_default;
SELECT CURRENT_TIMESTAMP;
current_timestamp
-------------------------------
2020-12-09 19:05:15.358796-05
\d+ new_partitioned_table;
Partitioned table "public.new_partitioned_table"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+----------------------------------+----------+--------------+-------------
col1 | bigint | | not null | generated by default as identity | plain | |
col2 | text | | not null | | extended | |
col3 | timestamp with time zone | | not null | now() | plain | |
col4 | text | | | | extended | |
Partition key: RANGE (col3)
Indexes:
"new_partitioned_table_col3_idx" btree (col3)
Partitions: new_partitioned_table_p2020_12_11 FOR VALUES FROM ('2020-12-11 00:00:00-05') TO ('2020-12-12 00:00:00-05')
设置part_config名字。
UPDATE partman.part_config SET parent_table = 'public.original_table', premake = 4 WHERE parent_table = 'public.new_partitioned_table'; UPDATE 1
迁移自增长将源表设置为default
BEGIN; LOCK TABLE public.original_table IN ACCESS EXCLUSIVE MODE; LOCK TABLE public.new_partitioned_table IN ACCESS EXCLUSIVE MODE; SELECT max(col1) FROM public.original_table; ALTER TABLE public.original_table RENAME TO original_table_default; -- IF using an IDENTITY column ALTER TABLE public.original_table_default ALTER col1 DROP IDENTITY; ALTER TABLE public.new_partitioned_table RENAME TO original_table; ALTER TABLE public.new_partitioned_table_p2020_12_11 RENAME TO original_table_p2020_12_11; -- IF using an IDENTITY column ALTER SEQUENCE public.new_partitioned_table_col1_seq RENAME TO original_table_col1_seq; -- IF using an IDENTITY column ALTER TABLE public.original_table ALTER col1 RESTART WITH <<<VALUE OBTAINED ABOVE>>>; ALTER TABLE public.original_table ATTACH PARTITION public.original_table_default DEFAULT;
COMMIT; or ROLLBACK;
迁移
CALL partman.partition_data_proc('public.original_table', p_batch := 200);
NOTICE: Batch: 1, Rows moved: 60
NOTICE: Batch: 2, Rows moved: 288
NOTICE: Batch: 3, Rows moved: 288
NOTICE: Batch: 4, Rows moved: 288
NOTICE: Batch: 5, Rows moved: 288
NOTICE: Batch: 6, Rows moved: 288
NOTICE: Batch: 7, Rows moved: 288
NOTICE: Batch: 8, Rows moved: 229
NOTICE: Total rows moved: 2017
NOTICE: Ensure to VACUUM ANALYZE the parent (and source table if used) after partitioning data
CALL
Time: 8432.725 ms (00:08.433)
VACUUM ANALYZE original_table;
VACUUM
Time: 60.690 ms
设置自增长
ALTER TABLE public.original_table ALTER col1 SET GENERATED ALWAYS;
\d+ original_table;
Partitioned table "public.original_table"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+----------------------------------+----------+--------------+-------------
col1 | bigint | | not null | generated by default as identity | plain | |
col2 | text | | not null | | extended | |
col3 | timestamp with time zone | | not null | now() | plain | |
col4 | text | | | | extended | |
Partition key: RANGE (col3)
Indexes:
"new_partitioned_table_col3_idx" btree (col3)
Partitions: original_table_p2020_12_02 FOR VALUES FROM ('2020-12-02 00:00:00-05') TO ('2020-12-03 00:00:00-05'),
original_table_p2020_12_03 FOR VALUES FROM ('2020-12-03 00:00:00-05') TO ('2020-12-04 00:00:00-05'),
original_table_p2020_12_04 FOR VALUES FROM ('2020-12-04 00:00:00-05') TO ('2020-12-05 00:00:00-05'),
original_table_p2020_12_05 FOR VALUES FROM ('2020-12-05 00:00:00-05') TO ('2020-12-06 00:00:00-05'),
original_table_p2020_12_06 FOR VALUES FROM ('2020-12-06 00:00:00-05') TO ('2020-12-07 00:00:00-05'),
original_table_p2020_12_07 FOR VALUES FROM ('2020-12-07 00:00:00-05') TO ('2020-12-08 00:00:00-05'),
original_table_p2020_12_08 FOR VALUES FROM ('2020-12-08 00:00:00-05') TO ('2020-12-09 00:00:00-05'),
original_table_p2020_12_09 FOR VALUES FROM ('2020-12-09 00:00:00-05') TO ('2020-12-10 00:00:00-05'),
original_table_p2020_12_11 FOR VALUES FROM ('2020-12-11 00:00:00-05') TO ('2020-12-12 00:00:00-05'),
检查
SELECT partman.run_maintenance('public.original_table');
\d+ original_table;
Partitioned table "public.original_table"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+----------------------------------+----------+--------------+-------------
col1 | bigint | | not null | generated by default as identity | plain | |
col2 | text | | not null | | extended | |
col3 | timestamp with time zone | | not null | now() | plain | |
col4 | text | | | | extended | |
Partition key: RANGE (col3)
Indexes:
"new_partitioned_table_col3_idx" btree (col3)
Partitions: original_table_p2020_12_02 FOR VALUES FROM ('2020-12-02 00:00:00-05') TO ('2020-12-03 00:00:00-05'),
original_table_p2020_12_03 FOR VALUES FROM ('2020-12-03 00:00:00-05') TO ('2020-12-04 00:00:00-05'),
original_table_p2020_12_04 FOR VALUES FROM ('2020-12-04 00:00:00-05') TO ('2020-12-05 00:00:00-05'),
original_table_p2020_12_05 FOR VALUES FROM ('2020-12-05 00:00:00-05') TO ('2020-12-06 00:00:00-05'),
original_table_p2020_12_06 FOR VALUES FROM ('2020-12-06 00:00:00-05') TO ('2020-12-07 00:00:00-05'),
original_table_p2020_12_07 FOR VALUES FROM ('2020-12-07 00:00:00-05') TO ('2020-12-08 00:00:00-05'),
original_table_p2020_12_08 FOR VALUES FROM ('2020-12-08 00:00:00-05') TO ('2020-12-09 00:00:00-05'),
original_table_p2020_12_09 FOR VALUES FROM ('2020-12-09 00:00:00-05') TO ('2020-12-10 00:00:00-05'),
original_table_p2020_12_11 FOR VALUES FROM ('2020-12-11 00:00:00-05') TO ('2020-12-12 00:00:00-05'),
original_table_p2020_12_12 FOR VALUES FROM ('2020-12-12 00:00:00-05') TO ('2020-12-13 00:00:00-05'),
original_table_p2020_12_13 FOR VALUES FROM ('2020-12-13 00:00:00-05') TO ('2020-12-14 00:00:00-05'),
original_table_default DEFAULT
检查default中是否存在数据
SELECT * FROM partman.check_default(p_exact_count := true);
填补分区表之间的间隔
SELECT * FROM partman.partition_gap_fill('public.original_table');
partition_gap_fill
--------------------
1
(1 row)
\d+ original_table;
Partitioned table "public.original_table"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+----------------------------------+----------+--------------+-------------
col1 | bigint | | not null | generated by default as identity | plain | |
col2 | text | | not null | | extended | |
col3 | timestamp with time zone | | not null | now() | plain | |
col4 | text | | | | extended | |
Partition key: RANGE (col3)
Indexes:
"new_partitioned_table_col3_idx" btree (col3)
Partitions: original_table_p2020_12_02 FOR VALUES FROM ('2020-12-02 00:00:00-05') TO ('2020-12-03 00:00:00-05'),
original_table_p2020_12_03 FOR VALUES FROM ('2020-12-03 00:00:00-05') TO ('2020-12-04 00:00:00-05'),
original_table_p2020_12_04 FOR VALUES FROM ('2020-12-04 00:00:00-05') TO ('2020-12-05 00:00:00-05'),
original_table_p2020_12_05 FOR VALUES FROM ('2020-12-05 00:00:00-05') TO ('2020-12-06 00:00:00-05'),
original_table_p2020_12_06 FOR VALUES FROM ('2020-12-06 00:00:00-05') TO ('2020-12-07 00:00:00-05'),
original_table_p2020_12_07 FOR VALUES FROM ('2020-12-07 00:00:00-05') TO ('2020-12-08 00:00:00-05'),
original_table_p2020_12_08 FOR VALUES FROM ('2020-12-08 00:00:00-05') TO ('2020-12-09 00:00:00-05'),
original_table_p2020_12_09 FOR VALUES FROM ('2020-12-09 00:00:00-05') TO ('2020-12-10 00:00:00-05'),
original_table_p2020_12_10 FOR VALUES FROM ('2020-12-10 00:00:00-05') TO ('2020-12-11 00:00:00-05'),
original_table_p2020_12_11 FOR VALUES FROM ('2020-12-11 00:00:00-05') TO ('2020-12-12 00:00:00-05'),
original_table_p2020_12_12 FOR VALUES FROM ('2020-12-12 00:00:00-05') TO ('2020-12-13 00:00:00-05'),
original_table_p2020_12_13 FOR VALUES FROM ('2020-12-13 00:00:00-05') TO ('2020-12-14 00:00:00-05'),
original_table_default DEFAULT
正常使用
INSERT INTO original_table (col2, col3, col4) VALUES ('newstuff', now(), 'newstuff');
INSERT INTO original_table (col2, col3, col4) VALUES ('newstuff', now(), 'newstuff');
SELECT * FROM original_table ORDER BY col1 DESC limit 5;
66.4.1.7.3.3. 撤销本地分区
CREATE TABLE public.new_regular_table (
col1 bigint not null GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, col2 text not null
, col3 timestamptz DEFAULT now() not null
, col4 text);
CREATE INDEX ON public.new_regular_table (col3);
迁移数据
CALL partman.undo_partition_proc('public.original_table', p_interval := '1 hour'::text, p_batch := 500, p_target_table := 'public.new_regular_table', p_keep_table := false);
NOTICE: Moved 13 row(s) to the target table. Removed 1 partitions.
NOTICE: Batch: 1, Partitions undone this batch: 1, Rows undone this batch: 13
NOTICE: Moved 13 row(s) to the target table. Removed 0 partitions.
NOTICE: Batch: 2, Partitions undone this batch: 0, Rows undone this batch: 13
NOTICE: Moved 13 row(s) to the target table. Removed 0 partitions.
NOTICE: Batch: 3, Partitions undone this batch: 0, Rows undone this batch: 13
[...]
NOTICE: Batch: 160, Partitions undone this batch: 0, Rows undone this batch: 13
NOTICE: Moved 5 row(s) to the target table. Removed 1 partitions.
NOTICE: Batch: 161, Partitions undone this batch: 1, Rows undone this batch: 5
NOTICE: Moved 0 row(s) to the target table. Removed 4 partitions.
NOTICE: Total partitions undone: 13, Total rows moved: 2017
NOTICE: Ensure to VACUUM ANALYZE the old parent & target table after undo has finished
CALL
Time: 163465.195 ms (02:43.465)
VACUUM ANALYZE original_table;
VACUUM
Time: 20.706 ms
VACUUM ANALYZE new_regular_table;
VACUUM
Time: 20.375 ms
设置新表自增长
SELECT max(col1) FROM public.original_table; ALTER TABLE original_table RENAME TO old_partitioned_table; ALTER SEQUENCE original_table_col1_seq RENAME TO old_partitioned_table_col1_seq; ALTER TABLE new_regular_table RENAME TO original_table; ALTER SEQUENCE new_regular_table_col1_seq RENAME TO original_table_col1_seq; ALTER TABLE public.original_table ALTER col1 RESTART WITH <<<VALUE OBTAINED ABOVE>>>; ALTER TABLE public.original_table ALTER col1 SET GENERATED ALWAYS;
可以正常使用
INSERT INTO original_table (col2, col3, col4) VALUES ('newstuff', now(), 'newstuff');
INSERT INTO original_table (col2, col3, col4) VALUES ('newstuff', now(), 'newstuff');
SELECT * FROM original_table ORDER BY col1 DESC limit 2;
66.4.1.7.4. 设置触发分区
66.4.1.7.4.1. 每天一个分区
\d partman.time_taptest_table
Table "partman.time_taptest_table"
Column | Type | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------
col1 | integer | | not null |
col2 | text | | |
col3 | timestamp with time zone | | not null | now()
Indexes:
"time_taptest_table_pkey" PRIMARY KEY, btree (col1)
SELECT partman.create_parent('partman.time_taptest_table', 'col3', 'partman', 'daily');
create_parent
---------------
t
(1 row)
\d+ partman.time_taptest_table
Table "partman.time_taptest_table"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
col1 | integer | | not null | | plain | |
col2 | text | | | | extended | |
col3 | timestamp with time zone | | not null | now() | plain | |
Indexes:
"time_taptest_table_pkey" PRIMARY KEY, btree (col1)
Triggers:
time_taptest_table_part_trig BEFORE INSERT ON partman.time_taptest_table FOR EACH ROW EXECUTE PROCEDURE partman.time_taptest_table_part_trig_func()
Child tables: partman.time_taptest_table_p2017_03_23,
partman.time_taptest_table_p2017_03_24,
partman.time_taptest_table_p2017_03_25,
partman.time_taptest_table_p2017_03_26,
partman.time_taptest_table_p2017_03_27,
partman.time_taptest_table_p2017_03_28,
partman.time_taptest_table_p2017_03_29,
partman.time_taptest_table_p2017_03_30,
partman.time_taptest_table_p2017_03_31
\sf partman.time_taptest_table_part_trig_func
CREATE OR REPLACE FUNCTION partman.time_taptest_table_part_trig_func()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
v_count int;
v_partition_name text;
v_partition_timestamp timestamptz;
BEGIN
IF TG_OP = 'INSERT' THEN
v_partition_timestamp := date_trunc('day', NEW.col3);
IF NEW.col3 >= '2017-03-27 00:00:00-04' AND NEW.col3 < '2017-03-28 00:00:00-04' THEN
INSERT INTO partman.time_taptest_table_p2017_03_27 VALUES (NEW.*) ;
ELSIF NEW.col3 >= '2017-03-26 00:00:00-04' AND NEW.col3 < '2017-03-27 00:00:00-04' THEN
INSERT INTO partman.time_taptest_table_p2017_03_26 VALUES (NEW.*) ;
ELSIF NEW.col3 >= '2017-03-28 00:00:00-04' AND NEW.col3 < '2017-03-29 00:00:00-04' THEN
INSERT INTO partman.time_taptest_table_p2017_03_28 VALUES (NEW.*) ;
ELSIF NEW.col3 >= '2017-03-25 00:00:00-04' AND NEW.col3 < '2017-03-26 00:00:00-04' THEN
INSERT INTO partman.time_taptest_table_p2017_03_25 VALUES (NEW.*) ;
ELSIF NEW.col3 >= '2017-03-29 00:00:00-04' AND NEW.col3 < '2017-03-30 00:00:00-04' THEN
INSERT INTO partman.time_taptest_table_p2017_03_29 VALUES (NEW.*) ;
ELSIF NEW.col3 >= '2017-03-24 00:00:00-04' AND NEW.col3 < '2017-03-25 00:00:00-04' THEN
INSERT INTO partman.time_taptest_table_p2017_03_24 VALUES (NEW.*) ;
ELSIF NEW.col3 >= '2017-03-30 00:00:00-04' AND NEW.col3 < '2017-03-31 00:00:00-04' THEN
INSERT INTO partman.time_taptest_table_p2017_03_30 VALUES (NEW.*) ;
ELSIF NEW.col3 >= '2017-03-23 00:00:00-04' AND NEW.col3 < '2017-03-24 00:00:00-04' THEN
INSERT INTO partman.time_taptest_table_p2017_03_23 VALUES (NEW.*) ;
ELSIF NEW.col3 >= '2017-03-31 00:00:00-04' AND NEW.col3 < '2017-04-01 00:00:00-04' THEN
INSERT INTO partman.time_taptest_table_p2017_03_31 VALUES (NEW.*) ;
ELSE
v_partition_name := partman.check_name_length('time_taptest_table', to_char(v_partition_timestamp, 'YYYY_MM_DD'), TRUE);
SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname = 'partman'::name AND tablename = v_partition_name::name;
IF v_count > 0 THEN
EXECUTE format('INSERT INTO %I.%I VALUES($1.*) ', 'partman', v_partition_name) USING NEW;
ELSE
RETURN NEW;
END IF;
END IF;
END IF;
RETURN NULL;
END $function$
66.4.1.7.4.2. 10个数一个分区
\d partman.id_taptest_table
Table "partman.id_taptest_table"
Column | Type | Modifiers
--------+--------------------------+--------------------------------
col1 | integer | not null
col2 | text | not null default 'stuff'::text
col3 | timestamp with time zone | default now()
Indexes:
"id_taptest_table_pkey" PRIMARY KEY, btree (col1)
SELECT create_parent('partman.id_taptest_table', 'col1', 'partman', '10');
create_parent
---------------
t
(1 row)
\d+ partman.id_taptest_table
Table "partman.id_taptest_table"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------------------------+--------------------------------+----------+--------------+-------------
col1 | integer | not null | plain | |
col2 | text | not null default 'stuff'::text | extended | |
col3 | timestamp with time zone | default now() | plain | |
Indexes:
"id_taptest_table_pkey" PRIMARY KEY, btree (col1)
Triggers:
id_taptest_table_part_trig BEFORE INSERT ON partman.id_taptest_table FOR EACH ROW EXECUTE PROCEDURE partman.id_taptest_table_part_trig_func()
Child tables: partman.id_taptest_table_p0,
partman.id_taptest_table_p10,
partman.id_taptest_table_p20,
partman.id_taptest_table_p30,
partman.id_taptest_table_p40
\sf partman.id_taptest_table_part_trig_func
CREATE OR REPLACE FUNCTION partman.id_taptest_table_part_trig_func()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
v_count int;
v_current_partition_id bigint;
v_current_partition_name text;
v_id_position int;
v_last_partition text := 'id_taptest_table_p40';
v_next_partition_id bigint;
v_next_partition_name text;
v_partition_created boolean;
BEGIN
IF TG_OP = 'INSERT' THEN
IF NEW.col1 >= 0 AND NEW.col1 < 10 THEN
INSERT INTO partman.id_taptest_table_p0 VALUES (NEW.*) ;
ELSIF NEW.col1 >= 10 AND NEW.col1 < 20 THEN
INSERT INTO partman.id_taptest_table_p10 VALUES (NEW.*) ;
ELSIF NEW.col1 >= 20 AND NEW.col1 < 30 THEN
INSERT INTO partman.id_taptest_table_p20 VALUES (NEW.*) ;
ELSIF NEW.col1 >= 30 AND NEW.col1 < 40 THEN
INSERT INTO partman.id_taptest_table_p30 VALUES (NEW.*) ;
ELSIF NEW.col1 >= 40 AND NEW.col1 < 50 THEN
INSERT INTO partman.id_taptest_table_p40 VALUES (NEW.*) ;
ELSE
v_current_partition_id := NEW.col1 - (NEW.col1 % 10);
v_current_partition_name := partman.check_name_length('id_taptest_table', v_current_partition_id::text, TRUE);
SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname = 'partman'::name AND tablename = v_current_partition_name::n
ame;
IF v_count > 0 THEN
EXECUTE format('INSERT INTO %I.%I VALUES($1.*) ', 'partman', v_current_partition_name) USING NEW;
ELSE
RETURN NEW;
END IF;
END IF;
END IF;
RETURN NULL;
END $function$
INSERT INTO partman.id_taptest_table(col1,col2) VALUES(1,'insert1');
INSERT 0 0
Time: 4.876 ms
SELECT * FROM partman.id_taptest_table;
col1 | col2 | col3
------+---------+-------------------------------
1 | insert1 | 2017-03-27 14:23:02.769999-04
(1 row)
INSERT INTO partman.id_taptest_table(col1,col2) VALUES(1,'insert2');
INSERT 0 0
Time: 1.583 ms
SELECT * FROM partman.id_taptest_table;
col1 | col2 | col3
------+---------+-------------------------------
1 | insert1 | 2017-03-27 14:23:02.769999-04
(1 row)
66.4.1.7.4.3. 10个数一个分区创建的空表使用upsert丢弃行冲突
Uses same example table as above
SELECT partman.create_parent('partman.id_taptest_table', 'col1', 'partman', '10', p_upsert := 'ON CONFLICT (col1) DO NOTHING');
create_parent
---------------
t
(1 row)
\d+ partman.id_taptest_table
Table "partman.id_taptest_table"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------------+----------+--------------+-------------
col1 | integer | | not null | | plain | |
col2 | text | | not null | 'stuff'::text | extended | |
col3 | timestamp with time zone | | | now() | plain | |
Indexes:
"id_taptest_table_pkey" PRIMARY KEY, btree (col1)
Triggers:
id_taptest_table_part_trig BEFORE INSERT ON partman.id_taptest_table FOR EACH ROW EXECUTE PROCEDURE partman.id_taptest_table_part_trig_func()
Child tables: partman.id_taptest_table_p0,
partman.id_taptest_table_p10,
partman.id_taptest_table_p20,
partman.id_taptest_table_p30,
partman.id_taptest_table_p40
创建插入函数，如果有冲突不会插入冲突行
\sf partman.id_taptest_table_part_trig_func
CREATE OR REPLACE FUNCTION partman.id_taptest_table_part_trig_func()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
v_count int;
v_current_partition_id bigint;
v_current_partition_name text;
v_id_position int;
v_last_partition text := 'id_taptest_table_p40';
v_next_partition_id bigint;
v_next_partition_name text;
v_partition_created boolean;
BEGIN
IF TG_OP = 'INSERT' THEN
IF NEW.col1 >= 0 AND NEW.col1 < 10 THEN
INSERT INTO partman.id_taptest_table_p0 VALUES (NEW.*) ON CONFLICT (col1) DO NOTHING;
ELSIF NEW.col1 >= 10 AND NEW.col1 < 20 THEN
INSERT INTO partman.id_taptest_table_p10 VALUES (NEW.*) ON CONFLICT (col1) DO NOTHING;
ELSIF NEW.col1 >= 20 AND NEW.col1 < 30 THEN
INSERT INTO partman.id_taptest_table_p20 VALUES (NEW.*) ON CONFLICT (col1) DO NOTHING;
ELSIF NEW.col1 >= 30 AND NEW.col1 < 40 THEN
INSERT INTO partman.id_taptest_table_p30 VALUES (NEW.*) ON CONFLICT (col1) DO NOTHING;
ELSIF NEW.col1 >= 40 AND NEW.col1 < 50 THEN
INSERT INTO partman.id_taptest_table_p40 VALUES (NEW.*) ON CONFLICT (col1) DO NOTHING;
ELSE
v_current_partition_id := NEW.col1 - (NEW.col1 % 10);
v_current_partition_name := partman.check_name_length('id_taptest_table', v_current_partition_id::text, TRUE);
SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname = 'partman'::name AND tablename = v_current_partition_name::n
ame;
IF v_count > 0 THEN
EXECUTE format('INSERT INTO %I.%I VALUES($1.*) ON CONFLICT (col1) DO NOTHING', 'partman', v_current_partition_name) USING NEW;
ELSE
RETURN NEW;
END IF;
END IF;
END IF;
RETURN NULL;
END $function$
插入一个行
INSERT INTO partman.id_taptest_table(col1,col2) VALUES(1,'insert1');
INSERT 0 0
Time: 4.876 ms
SELECT * FROM partman.id_taptest_table;
col1 | col2 | col3
------+---------+-------------------------------
1 | insert1 | 2017-03-27 14:23:02.769999-04
(1 row)
插入冲突行，结果为保留原有行
INSERT INTO partman.id_taptest_table(col1,col2) VALUES(1,'insert2');
INSERT 0 0
Time: 1.583 ms
SELECT * FROM partman.id_taptest_table;
col1 | col2 | col3
------+---------+-------------------------------
1 | insert1 | 2017-03-27 14:23:02.769999-04
(1 row)
66.4.1.7.4.4. 用upsert更新行冲突
Uses same example table as above
SELECT partman.create_parent('partman.id_taptest_table', 'col1', 'partman', '10', p_upsert := 'ON CONFLICT (col1) DO UPDATE SET col2=EXCLUDED.col2, col3=EXCLUDED.col3');
create_parent
---------------
t
(1 row)
\d+ partman.id_taptest_table
Table "partman.id_taptest_table"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------------+----------+--------------+-------------
col1 | integer | | not null | | plain | |
col2 | text | | not null | 'stuff'::text | extended | |
col3 | timestamp with time zone | | | now() | plain | |
Indexes:
"id_taptest_table_pkey" PRIMARY KEY, btree (col1)
Triggers:
id_taptest_table_part_trig BEFORE INSERT ON partman.id_taptest_table FOR EACH ROW EXECUTE PROCEDURE partman.id_taptest_table_part_trig_func()
Child tables: partman.id_taptest_table_p0,
partman.id_taptest_table_p10,
partman.id_taptest_table_p20,
partman.id_taptest_table_p30,
partman.id_taptest_table_p40
创建插入函数，如果有冲突行则更新原有行
\sf partman.id_taptest_table_part_trig_func
CREATE OR REPLACE FUNCTION partman.id_taptest_table_part_trig_func()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
v_count int;
v_current_partition_id bigint;
v_current_partition_name text;
v_id_position int;
v_last_partition text := 'id_taptest_table_p40';
v_next_partition_id bigint;
v_next_partition_name text;
v_partition_created boolean;
BEGIN
IF TG_OP = 'INSERT' THEN
IF NEW.col1 >= 0 AND NEW.col1 < 10 THEN
INSERT INTO partman.id_taptest_table_p0 VALUES (NEW.*) ON CONFLICT (col1) DO UPDATE SET col2=EXCLUDED.col2, col3=EXCLUDED.col3;
ELSIF NEW.col1 >= 10 AND NEW.col1 < 20 THEN
INSERT INTO partman.id_taptest_table_p10 VALUES (NEW.*) ON CONFLICT (col1) DO UPDATE SET col2=EXCLUDED.col2, col3=EXCLUDED.col3;
ELSIF NEW.col1 >= 20 AND NEW.col1 < 30 THEN
INSERT INTO partman.id_taptest_table_p20 VALUES (NEW.*) ON CONFLICT (col1) DO UPDATE SET col2=EXCLUDED.col2, col3=EXCLUDED.col3;
ELSIF NEW.col1 >= 30 AND NEW.col1 < 40 THEN
INSERT INTO partman.id_taptest_table_p30 VALUES (NEW.*) ON CONFLICT (col1) DO UPDATE SET col2=EXCLUDED.col2, col3=EXCLUDED.col3;
ELSIF NEW.col1 >= 40 AND NEW.col1 < 50 THEN
INSERT INTO partman.id_taptest_table_p40 VALUES (NEW.*) ON CONFLICT (col1) DO UPDATE SET col2=EXCLUDED.col2, col3=EXCLUDED.col3;
ELSE
v_current_partition_id := NEW.col1 - (NEW.col1 % 10);
v_current_partition_name := partman.check_name_length('id_taptest_table', v_current_partition_id::text, TRUE);
SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname = 'partman'::name AND tablename = v_current_partition_name::n
ame;
IF v_count > 0 THEN
EXECUTE format('INSERT INTO %I.%I VALUES($1.*) ON CONFLICT (col1) DO UPDATE SET col2=EXCLUDED.col2, col3=EXCLUDED.col3', 'partman
', v_current_partition_name) USING NEW;
ELSE
RETURN NEW;
END IF;
END IF;
END IF;
RETURN NULL;
END $function$
插入行
INSERT INTO partman.id_taptest_table(col1,col2) VALUES(1,'insert1');
INSERT 0 0
Time: 6.012 ms
SELECT * FROM partman.id_taptest_table;
col1 | col2 | col3
------+---------+------------------------------
1 | insert1 | 2017-03-27 14:32:26.59552-04
(1 row)
插入冲突行，结果为更新原有行
INSERT INTO partman.id_taptest_table(col1,col2) VALUES(1,'insert2');
INSERT 0 0
Time: 4.235 ms
SELECT * FROM partman.id_taptest_table;
col1 | col2 | col3
------+---------+-------------------------------
1 | insert2 | 2017-03-27 14:33:00.949928-04
(1 row)
66.4.1.7.5. 创建多级子分区
66.4.1.7.5.1. 时间多级子分区
以下过程是先创建表，后插入数据
\d partman.time_taptest_table
Table "partman.time_taptest_table"
Column | Type | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------
col1 | integer | | not null |
col2 | text | | |
col3 | timestamp with time zone | | not null | now()
Indexes:
"time_taptest_table_pkey" PRIMARY KEY, btree (col1)
SELECT partman.create_parent('partman.time_taptest_table', 'col3', 'partman', 'yearly', p_premake := 2);
create_parent
---------------
t
(1 row)
SELECT partman.create_sub_parent('partman.time_taptest_table', 'col3', 'partman', 'monthly', p_premake := 2);
create_sub_parent
-------------------
t
(1 row)
SELECT tablename FROM pg_tables WHERE schemaname = 'partman' ORDER BY tablename;
tablename
-----------------------------------
time_taptest_table
time_taptest_table_p2015
time_taptest_table_p2015_p2015_01
time_taptest_table_p2016
time_taptest_table_p2016_p2016_01
time_taptest_table_p2017
time_taptest_table_p2017_p2017_01
time_taptest_table_p2017_p2017_02
time_taptest_table_p2017_p2017_03
time_taptest_table_p2017_p2017_04
time_taptest_table_p2017_p2017_05
time_taptest_table_p2018
time_taptest_table_p2018_p2018_01
time_taptest_table_p2019
time_taptest_table_p2019_p2019_01
(15 rows)
SELECT partman.create_sub_parent('partman.time_taptest_table_p2015', 'col3', 'partman', 'daily', p_premake := 2);
SELECT partman.create_sub_parent('partman.time_taptest_table_p2016', 'col3', 'partman', 'daily', p_premake := 2);
SELECT partman.create_sub_parent('partman.time_taptest_table_p2017', 'col3', 'partman', 'daily', p_premake := 2);
SELECT partman.create_sub_parent('partman.time_taptest_table_p2018', 'col3', 'partman', 'daily', p_premake := 2);
SELECT partman.create_sub_parent('partman.time_taptest_table_p2019', 'col3', 'partman', 'daily', p_premake := 2);
SELECT tablename FROM pg_tables WHERE schemaname = 'partman' ORDER BY tablename;
tablename
-----------------------------------------------
time_taptest_table
time_taptest_table_p2015
time_taptest_table_p2015_p2015_01
time_taptest_table_p2015_p2015_01_p2015_01_01
time_taptest_table_p2016
time_taptest_table_p2016_p2016_01
time_taptest_table_p2016_p2016_01_p2016_01_01
time_taptest_table_p2017
time_taptest_table_p2017_p2017_01
time_taptest_table_p2017_p2017_01_p2017_01_01
time_taptest_table_p2017_p2017_02
time_taptest_table_p2017_p2017_02_p2017_02_01
time_taptest_table_p2017_p2017_03
time_taptest_table_p2017_p2017_03_p2017_03_25
time_taptest_table_p2017_p2017_03_p2017_03_26
time_taptest_table_p2017_p2017_03_p2017_03_27
time_taptest_table_p2017_p2017_03_p2017_03_28
time_taptest_table_p2017_p2017_03_p2017_03_29
time_taptest_table_p2017_p2017_04
time_taptest_table_p2017_p2017_04_p2017_04_01
time_taptest_table_p2017_p2017_05
time_taptest_table_p2017_p2017_05_p2017_05_01
time_taptest_table_p2018
time_taptest_table_p2018_p2018_01
time_taptest_table_p2018_p2018_01_p2018_01_01
time_taptest_table_p2019
time_taptest_table_p2019_p2019_01
time_taptest_table_p2019_p2019_01_p2019_01_01
(28 rows)
数值间隔的多级子分区
以下过程是表中已经存在数据
\d partman.id_taptest_table
Table "partman.id_taptest_table"
Column | Type | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------------
col1 | integer | | not null |
col2 | text | | not null | 'stuff'::text
col3 | timestamp with time zone | | | now()
Indexes:
"id_taptest_table_pkey" PRIMARY KEY, btree (col1)
SELECT count(*) FROM partman.id_taptest_table ;
count
--------
100000
(1 row)
SELECT min(col1), max(col1) FROM partman.id_taptest_table ;
min | max
-----+--------
1 | 100000
(1 row)
SELECT partman.create_parent('partman.id_taptest_table', 'col1', 'partman', '10000', p_jobmon := false, p_premake := 2);
---------------
t
(1 row)
SELECT tablename FROM pg_tables WHERE schemaname = 'partman' ORDER BY tablename;
tablename
--------------------------
id_taptest_table
id_taptest_table_p100000
id_taptest_table_p110000
id_taptest_table_p120000
id_taptest_table_p80000
id_taptest_table_p90000
(6 rows)
$ python partition_data.py -c host=localhost -p partman.id_taptest_table -t id -i 100
Attempting to turn off autovacuum for partition set...
... Success!
Rows moved: 100
Rows moved: 100
...
Rows moved: 99
...
Rows moved: 100
Rows moved: 1
Total rows moved: 100000
Running vacuum analyze on parent table...
Attempting to reset autovacuum for old parent table and all child tables...
... Success!
SELECT tablename FROM pg_tables WHERE schemaname = 'partman' ORDER BY tablename;
tablename
--------------------------
id_taptest_table
id_taptest_table_p0
id_taptest_table_p10000
id_taptest_table_p100000
id_taptest_table_p110000
id_taptest_table_p120000
id_taptest_table_p20000
id_taptest_table_p30000
id_taptest_table_p40000
id_taptest_table_p50000
id_taptest_table_p60000
id_taptest_table_p70000
id_taptest_table_p80000
id_taptest_table_p90000
(14 rows)
SELECT partman.create_sub_parent('partman.id_taptest_table', 'col1', 'partman', '1000', p_jobmon := false, p_premake := 2);
create_sub_parent
-------------------
t
(1 row)
SELECT tablename FROM pg_tables WHERE schemaname = 'partman' ORDER BY tablename;
tablename
----------------------------------
id_taptest_table
id_taptest_table_p0
id_taptest_table_p0_p0
id_taptest_table_p10000
id_taptest_table_p100000
id_taptest_table_p100000_p100000
id_taptest_table_p100000_p101000
id_taptest_table_p100000_p102000
id_taptest_table_p10000_p10000
id_taptest_table_p110000
id_taptest_table_p110000_p110000
id_taptest_table_p120000
id_taptest_table_p120000_p120000
id_taptest_table_p20000
id_taptest_table_p20000_p20000
id_taptest_table_p30000
id_taptest_table_p30000_p30000
id_taptest_table_p40000
id_taptest_table_p40000_p40000
id_taptest_table_p50000
id_taptest_table_p50000_p50000
id_taptest_table_p60000
id_taptest_table_p60000_p60000
id_taptest_table_p70000
id_taptest_table_p70000_p70000
id_taptest_table_p80000
id_taptest_table_p80000_p80000
id_taptest_table_p90000
id_taptest_table_p90000_p98000
id_taptest_table_p90000_p99000
(30 rows)
此时，数据还在父表中
SELECT * FROM partman.check_parent() ORDER BY 1;
parent_table | count
---------------------------------------+-------
partman.id_taptest_table_p0 | 9999
partman.id_taptest_table_p10000 | 10000
partman.id_taptest_table_p100000 | 1
partman.id_taptest_table_p20000 | 10000
partman.id_taptest_table_p30000 | 10000
partman.id_taptest_table_p40000 | 10000
partman.id_taptest_table_p50000 | 10000
partman.id_taptest_table_p60000 | 10000
partman.id_taptest_table_p70000 | 10000
partman.id_taptest_table_p80000 | 10000
partman.id_taptest_table_p90000 | 10000
(11 rows)
将数据移到子表
python partition_data.py -c host=localhost -p partman.id_taptest_table_p0 -t id -i 100 python partition_data.py -c host=localhost -p partman.id_taptest_table_p10000 -t id -i 100 python partition_data.py -c host=localhost -p partman.id_taptest_table_p20000 -t id -i 100 python partition_data.py -c host=localhost -p partman.id_taptest_table_p30000 -t id -i 100 python partition_data.py -c host=localhost -p partman.id_taptest_table_p40000 -t id -i 100 python partition_data.py -c host=localhost -p partman.id_taptest_table_p50000 -t id -i 100 python partition_data.py -c host=localhost -p partman.id_taptest_table_p60000 -t id -i 100 python partition_data.py -c host=localhost -p partman.id_taptest_table_p70000 -t id -i 100 python partition_data.py -c host=localhost -p partman.id_taptest_table_p80000 -t id -i 100 python partition_data.py -c host=localhost -p partman.id_taptest_table_p90000 -t id -i 100 python partition_data.py -c host=localhost -p partman.id_taptest_table_p100000 -t id -i 100
SELECT * FROM partman.check_parent() ORDER BY 1; parent_table | count --------------+------- (0 rows)
SELECT tablename FROM pg_tables WHERE schemaname = 'partman' ORDER BY tablename;
tablename
----------------------------------
id_taptest_table
id_taptest_table_p0
id_taptest_table_p0_p0
id_taptest_table_p0_p1000
id_taptest_table_p0_p2000
id_taptest_table_p0_p3000
id_taptest_table_p0_p4000
id_taptest_table_p0_p5000
id_taptest_table_p0_p6000
id_taptest_table_p0_p7000
id_taptest_table_p0_p8000
id_taptest_table_p0_p9000
id_taptest_table_p10000
id_taptest_table_p100000
id_taptest_table_p100000_p100000
id_taptest_table_p100000_p101000
id_taptest_table_p100000_p102000
id_taptest_table_p10000_p10000
id_taptest_table_p10000_p11000
id_taptest_table_p10000_p12000
id_taptest_table_p10000_p13000
id_taptest_table_p10000_p14000
id_taptest_table_p10000_p15000
id_taptest_table_p10000_p16000
id_taptest_table_p10000_p17000
id_taptest_table_p10000_p18000
id_taptest_table_p10000_p19000
id_taptest_table_p110000
id_taptest_table_p110000_p110000
id_taptest_table_p120000
id_taptest_table_p120000_p120000
id_taptest_table_p20000
id_taptest_table_p20000_p20000
id_taptest_table_p20000_p21000
id_taptest_table_p20000_p22000
id_taptest_table_p20000_p23000
id_taptest_table_p20000_p24000
id_taptest_table_p20000_p25000
id_taptest_table_p20000_p26000
id_taptest_table_p20000_p27000
id_taptest_table_p20000_p28000
id_taptest_table_p20000_p29000
id_taptest_table_p30000
id_taptest_table_p30000_p30000
id_taptest_table_p30000_p31000
id_taptest_table_p30000_p32000
id_taptest_table_p30000_p33000
id_taptest_table_p30000_p34000
id_taptest_table_p30000_p35000
id_taptest_table_p30000_p36000
id_taptest_table_p30000_p37000
id_taptest_table_p30000_p38000
id_taptest_table_p30000_p39000
id_taptest_table_p40000
id_taptest_table_p40000_p40000
id_taptest_table_p40000_p41000
id_taptest_table_p40000_p42000
id_taptest_table_p40000_p43000
id_taptest_table_p40000_p44000
id_taptest_table_p40000_p45000
id_taptest_table_p40000_p46000
id_taptest_table_p40000_p47000
id_taptest_table_p40000_p48000
id_taptest_table_p40000_p49000
id_taptest_table_p50000
id_taptest_table_p50000_p50000
id_taptest_table_p50000_p51000
id_taptest_table_p50000_p52000
id_taptest_table_p50000_p53000
id_taptest_table_p50000_p54000
id_taptest_table_p50000_p55000
id_taptest_table_p50000_p56000
id_taptest_table_p50000_p57000
id_taptest_table_p50000_p58000
id_taptest_table_p50000_p59000
id_taptest_table_p60000
id_taptest_table_p60000_p60000
id_taptest_table_p60000_p61000
id_taptest_table_p60000_p62000
id_taptest_table_p60000_p63000
id_taptest_table_p60000_p64000
id_taptest_table_p60000_p65000
id_taptest_table_p60000_p66000
id_taptest_table_p60000_p67000
id_taptest_table_p60000_p68000
id_taptest_table_p60000_p69000
id_taptest_table_p70000
id_taptest_table_p70000_p70000
id_taptest_table_p70000_p71000
id_taptest_table_p70000_p72000
id_taptest_table_p70000_p73000
id_taptest_table_p70000_p74000
id_taptest_table_p70000_p75000
id_taptest_table_p70000_p76000
id_taptest_table_p70000_p77000
id_taptest_table_p70000_p78000
id_taptest_table_p70000_p79000
id_taptest_table_p80000
id_taptest_table_p80000_p80000
id_taptest_table_p80000_p81000
id_taptest_table_p80000_p82000
id_taptest_table_p80000_p83000
id_taptest_table_p80000_p84000
id_taptest_table_p80000_p85000
id_taptest_table_p80000_p86000
id_taptest_table_p80000_p87000
id_taptest_table_p80000_p88000
id_taptest_table_p80000_p89000
id_taptest_table_p90000
id_taptest_table_p90000_p90000
id_taptest_table_p90000_p91000
id_taptest_table_p90000_p92000
id_taptest_table_p90000_p93000
id_taptest_table_p90000_p94000
id_taptest_table_p90000_p95000
id_taptest_table_p90000_p96000
id_taptest_table_p90000_p97000
id_taptest_table_p90000_p98000
id_taptest_table_p90000_p99000
(119 rows)
继续缩小粒度
SELECT partman.create_sub_parent('partman.id_taptest_table_p0', 'col1', 'partman', '100', p_jobmon := false, p_premake := 2);
SELECT partman.create_sub_parent('partman.id_taptest_table_p10000', 'col1', 'partman', '100', p_jobmon := false, p_premake := 2);
SELECT partman.create_sub_parent('partman.id_taptest_table_p20000', 'col1', 'partman', '100', p_jobmon := false, p_premake := 2);
SELECT partman.create_sub_parent('partman.id_taptest_table_p30000', 'col1', 'partman', '100', p_jobmon := false, p_premake := 2);
SELECT partman.create_sub_parent('partman.id_taptest_table_p40000', 'col1', 'partman', '100', p_jobmon := false, p_premake := 2);
SELECT partman.create_sub_parent('partman.id_taptest_table_p50000', 'col1', 'partman', '100', p_jobmon := false, p_premake := 2);
SELECT partman.create_sub_parent('partman.id_taptest_table_p60000', 'col1', 'partman', '100', p_jobmon := false, p_premake := 2);
SELECT partman.create_sub_parent('partman.id_taptest_table_p70000', 'col1', 'partman', '100', p_jobmon := false, p_premake := 2);
SELECT partman.create_sub_parent('partman.id_taptest_table_p80000', 'col1', 'partman', '100', p_jobmon := false, p_premake := 2);
SELECT partman.create_sub_parent('partman.id_taptest_table_p90000', 'col1', 'partman', '100', p_jobmon := false, p_premake := 2);
SELECT partman.create_sub_parent('partman.id_taptest_table_p100000', 'col1', 'partman', '100', p_jobmon := false, p_premake := 2);
SELECT tablename FROM pg_tables WHERE schemaname = 'partman' order by tablename;
tablename
------------------------------------------
id_taptest_table
id_taptest_table_p0
id_taptest_table_p0_p0
id_taptest_table_p0_p0_p0
id_taptest_table_p0_p1000
id_taptest_table_p0_p1000_p1000
id_taptest_table_p0_p2000
id_taptest_table_p0_p2000_p2000
...
id_taptest_table_p10000
id_taptest_table_p100000
id_taptest_table_p100000_p100000
id_taptest_table_p100000_p100000_p100000
id_taptest_table_p100000_p100000_p100100
id_taptest_table_p100000_p100000_p100200
id_taptest_table_p100000_p101000
id_taptest_table_p100000_p101000_p101000
id_taptest_table_p100000_p102000
id_taptest_table_p100000_p102000_p102000
id_taptest_table_p10000_p10000
id_taptest_table_p10000_p10000_p10000
id_taptest_table_p10000_p11000
id_taptest_table_p10000_p11000_p11000
...
id_taptest_table_p90000_p98000
id_taptest_table_p90000_p98000_p98000
id_taptest_table_p90000_p99000
id_taptest_table_p90000_p99000_p99800
id_taptest_table_p90000_p99000_p99900
(225 rows)
SELECT 'python partition_data.py -c host=localhost -p '||parent_table||' -t id -i 100' FROM partman.part_config ORDER BY parent_table;
?column?
---------------------------------------------------------------------------------------------------------
python partition_data.py -c host=localhost -p partman.id_taptest_table -t id -i 100
python partition_data.py -c host=localhost -p partman.id_taptest_table_p0 -t id -i 100
python partition_data.py -c host=localhost -p partman.id_taptest_table_p0_p0 -t id -i 100
python partition_data.py -c host=localhost -p partman.id_taptest_table_p0_p1000 -t id -i 100
...
SELECT tablename FROM pg_tables WHERE schemaname = 'partman' order by tablename;
tablename
-----------------------------------------
id_taptest_table
id_taptest_table_p0
id_taptest_table_p0_p0
id_taptest_table_p0_p0_p0
id_taptest_table_p0_p0_p100
id_taptest_table_p0_p0_p200
id_taptest_table_p0_p0_p300
id_taptest_table_p0_p0_p400
id_taptest_table_p0_p0_p500
id_taptest_table_p0_p0_p600
id_taptest_table_p0_p0_p700
id_taptest_table_p0_p0_p800
id_taptest_table_p0_p0_p900
id_taptest_table_p0_p1000
id_taptest_table_p0_p1000_p1000
id_taptest_table_p0_p1000_p1100
id_taptest_table_p0_p1000_p1200
id_taptest_table_p0_p1000_p1300
id_taptest_table_p0_p1000_p1400
id_taptest_table_p0_p1000_p1500
id_taptest_table_p0_p1000_p1600
id_taptest_table_p0_p1000_p1700
id_taptest_table_p0_p1000_p1800
id_taptest_table_p0_p1000_p1900
id_taptest_table_p0_p2000
id_taptest_table_p0_p2000_p2000
id_taptest_table_p0_p2000_p2100
...
id_taptest_table_p90000_p98000_p98800
id_taptest_table_p90000_p98000_p98900
id_taptest_table_p90000_p99000
id_taptest_table_p90000_p99000_p99000
id_taptest_table_p90000_p99000_p99100
id_taptest_table_p90000_p99000_p99200
id_taptest_table_p90000_p99000_p99300
id_taptest_table_p90000_p99000_p99400
id_taptest_table_p90000_p99000_p99500
id_taptest_table_p90000_p99000_p99600
id_taptest_table_p90000_p99000_p99700
id_taptest_table_p90000_p99000_p99800
id_taptest_table_p90000_p99000_p99900
(1124 rows)
66.4.1.7.5.2. 从触发分区迁移到本地
test/test-time-daily.sql创建触发区分
\d+ partman.time_taptest_table
Table "partman.time_taptest_table"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
col1 | integer | | not null | | plain | |
col2 | text | | | | extended | |
col3 | timestamp with time zone | | not null | now() | plain | |
Indexes:
"time_taptest_table_pkey" PRIMARY KEY, btree (col1)
Triggers:
time_taptest_table_part_trig BEFORE INSERT ON partman.time_taptest_table FOR EACH ROW EXECUTE FUNCTION partman.time_taptest_table_part_trig_func()
Child tables: partman.time_taptest_table_p2020_01_27,
partman.time_taptest_table_p2020_01_28,
partman.time_taptest_table_p2020_01_29,
partman.time_taptest_table_p2020_01_30,
partman.time_taptest_table_p2020_01_31,
partman.time_taptest_table_p2020_02_01,
partman.time_taptest_table_p2020_02_02,
partman.time_taptest_table_p2020_02_03,
partman.time_taptest_table_p2020_02_04,
partman.time_taptest_table_p2020_02_05,
partman.time_taptest_table_p2020_02_06,
partman.time_taptest_table_p2020_02_07,
partman.time_taptest_table_p2020_02_08,
partman.time_taptest_table_p2020_02_09,
partman.time_taptest_table_p2020_02_10,
partman.time_taptest_table_p2020_02_11,
partman.time_taptest_table_p2020_02_12
Access method: heap
关闭触发行为
UPDATE partman.part_config SET automatic_maintenance = 'off' WHERE parent_table = 'partman.time_taptest_table';
UPDATE partman.part_config_sub SET sub_automatic_maintenance = 'off' WHERE sub_parent = 'partman.time_taptest_table'; UPDATE partman.part_config_sub SET sub_automatic_maintenance = 'off' WHERE sub_parent = 'partman.time_taptest_table_p2019_12_08'; [...]
创建本地分区
CREATE TABLE partman.time_taptest_table_native
(col1 int, col2 text default 'stuff', col3 timestamptz NOT NULL DEFAULT now())
PARTITION BY RANGE (col3);
CREATE INDEX ON partman.time_taptest_table_native (col3);
\dt partman.time_taptest_table
List of relations
Schema | Name | Type | Owner
--------------+--------------------+-------+---------------
partman | time_taptest_table | table | partman_owner
(1 row)
\dp+ partman.time_taptest_table
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------------+--------------------+-------+-------------------------------------+-------------------+----------
partman | time_taptest_table | table | partman_owner=arwdDxt/partman_owner+| |
| | | partman_basic=arwd/partman_owner +| |
| | | testing=r/partman_owner | |
(1 row)
ALTER TABLE partman.time_taptest_table_native OWNER TO partman_owner; GRANT SELECT, INSERT, UPDATE, DELETE ON partman.time_taptest_table_native TO partman_basic; GRANT SELECT ON partman.time_taptest_table_native TO testing;
数据迁移
BEGIN; LOCK TABLE partman.time_taptest_table IN ACCESS EXCLUSIVE MODE NOWAIT;
SELECT 'ALTER TABLE '||inhrelid::regclass||' NO INHERIT '||inhparent::regclass||';' FROM pg_inherits WHERE inhparent::regclass = 'partman.time_taptest_table'::regclass;
?column?
-----------------------------------------------------------------------------------------------------
ALTER TABLE partman.time_taptest_table_p2019_12_08 NO INHERIT partman.time_taptest_table;
ALTER TABLE partman.time_taptest_table_p2019_12_09 NO INHERIT partman.time_taptest_table;
ALTER TABLE partman.time_taptest_table_p2019_12_10 NO INHERIT partman.time_taptest_table;
ALTER TABLE partman.time_taptest_table_p2019_12_11 NO INHERIT partman.time_taptest_table;
ALTER TABLE partman.time_taptest_table_p2019_12_12 NO INHERIT partman.time_taptest_table;
ALTER TABLE partman.time_taptest_table_p2019_12_13 NO INHERIT partman.time_taptest_table;
ALTER TABLE partman.time_taptest_table_p2019_12_14 NO INHERIT partman.time_taptest_table;
ALTER TABLE partman.time_taptest_table_p2019_12_15 NO INHERIT partman.time_taptest_table;
ALTER TABLE partman.time_taptest_table_p2019_12_16 NO INHERIT partman.time_taptest_table;
ALTER TABLE partman.time_taptest_table_p2019_12_17 NO INHERIT partman.time_taptest_table;
ALTER TABLE partman.time_taptest_table_p2019_12_18 NO INHERIT partman.time_taptest_table;
ALTER TABLE partman.time_taptest_table_p2019_12_19 NO INHERIT partman.time_taptest_table;
ALTER TABLE partman.time_taptest_table_p2019_12_20 NO INHERIT partman.time_taptest_table;
ALTER TABLE partman.time_taptest_table_p2019_12_21 NO INHERIT partman.time_taptest_table;
ALTER TABLE partman.time_taptest_table_p2019_12_22 NO INHERIT partman.time_taptest_table;
ALTER TABLE partman.time_taptest_table_p2019_12_23 NO INHERIT partman.time_taptest_table;
ALTER TABLE partman.time_taptest_table_p2019_12_24 NO INHERIT partman.time_taptest_table;
SELECT (
SELECT 'ALTER TABLE partman.time_taptest_table_native ATTACH PARTITION '||x.child_tablename||' FOR VALUES FROM ('||quote_literal(y.child_start_time)||') TO ('||quote_literal(y.child_end_time)||');'
FROM partman.show_partition_info(x.child_tablename, p_parent_table := 'partman.time_taptest_table') y )
FROM (SELECT inhrelid::regclass::text AS child_tablename FROM pg_inherits WHERE inhparent::regclass = 'partman.time_taptest_table'::regclass) x;
ALTER TABLE partman.time_taptest_table_native ATTACH PARTITION partman.time_taptest_table_p2020_01_27 FOR VALUES FROM ('2020-01-27 00:00:00-05') TO ('2020-01-28 00:00:00-05');
ALTER TABLE partman.time_taptest_table_native ATTACH PARTITION partman.time_taptest_table_p2020_01_28 FOR VALUES FROM ('2020-01-28 00:00:00-05') TO ('2020-01-29 00:00:00-05');
ALTER TABLE partman.time_taptest_table_native ATTACH PARTITION partman.time_taptest_table_p2020_01_29 FOR VALUES FROM ('2020-01-29 00:00:00-05') TO ('2020-01-30 00:00:00-05');
ALTER TABLE partman.time_taptest_table_native ATTACH PARTITION partman.time_taptest_table_p2020_01_30 FOR VALUES FROM ('2020-01-30 00:00:00-05') TO ('2020-01-31 00:00:00-05');
ALTER TABLE partman.time_taptest_table_native ATTACH PARTITION partman.time_taptest_table_p2020_01_31 FOR VALUES FROM ('2020-01-31 00:00:00-05') TO ('2020-02-01 00:00:00-05');
ALTER TABLE partman.time_taptest_table_native ATTACH PARTITION partman.time_taptest_table_p2020_02_01 FOR VALUES FROM ('2020-02-01 00:00:00-05') TO ('2020-02-02 00:00:00-05');
ALTER TABLE partman.time_taptest_table_native ATTACH PARTITION partman.time_taptest_table_p2020_02_02 FOR VALUES FROM ('2020-02-02 00:00:00-05') TO ('2020-02-03 00:00:00-05');
ALTER TABLE partman.time_taptest_table_native ATTACH PARTITION partman.time_taptest_table_p2020_02_03 FOR VALUES FROM ('2020-02-03 00:00:00-05') TO ('2020-02-04 00:00:00-05');
ALTER TABLE partman.time_taptest_table_native ATTACH PARTITION partman.time_taptest_table_p2020_02_04 FOR VALUES FROM ('2020-02-04 00:00:00-05') TO ('2020-02-05 00:00:00-05');
ALTER TABLE partman.time_taptest_table_native ATTACH PARTITION partman.time_taptest_table_p2020_02_05 FOR VALUES FROM ('2020-02-05 00:00:00-05') TO ('2020-02-06 00:00:00-05');
ALTER TABLE partman.time_taptest_table_native ATTACH PARTITION partman.time_taptest_table_p2020_02_06 FOR VALUES FROM ('2020-02-06 00:00:00-05') TO ('2020-02-07 00:00:00-05');
ALTER TABLE partman.time_taptest_table_native ATTACH PARTITION partman.time_taptest_table_p2020_02_07 FOR VALUES FROM ('2020-02-07 00:00:00-05') TO ('2020-02-08 00:00:00-05');
ALTER TABLE partman.time_taptest_table_native ATTACH PARTITION partman.time_taptest_table_p2020_02_08 FOR VALUES FROM ('2020-02-08 00:00:00-05') TO ('2020-02-09 00:00:00-05');
ALTER TABLE partman.time_taptest_table_native ATTACH PARTITION partman.time_taptest_table_p2020_02_09 FOR VALUES FROM ('2020-02-09 00:00:00-05') TO ('2020-02-10 00:00:00-05');
ALTER TABLE partman.time_taptest_table_native ATTACH PARTITION partman.time_taptest_table_p2020_02_10 FOR VALUES FROM ('2020-02-10 00:00:00-05') TO ('2020-02-11 00:00:00-05');
ALTER TABLE partman.time_taptest_table_native ATTACH PARTITION partman.time_taptest_table_p2020_02_11 FOR VALUES FROM ('2020-02-11 00:00:00-05') TO ('2020-02-12 00:00:00-05');
ALTER TABLE partman.time_taptest_table_native ATTACH PARTITION partman.time_taptest_table_p2020_02_12 FOR VALUES FROM ('2020-02-12 00:00:00-05') TO ('2020-02-13 00:00:00-05');
\d+ partman.time_taptest_table
Table "partman.time_taptest_table"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
col1 | integer | | not null | | plain | |
col2 | text | | | | extended | |
col3 | timestamp with time zone | | not null | now() | plain | |
Indexes:
"time_taptest_table_pkey" PRIMARY KEY, btree (col1)
Triggers:
time_taptest_table_part_trig BEFORE INSERT ON partman.time_taptest_table FOR EACH ROW EXECUTE FUNCTION partman.time_taptest_table_part_trig_func()
Access method: heap
\d+ partman.time_taptest_table_native
Partitioned table "partman.time_taptest_table_native"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------------+----------+--------------+-------------
col1 | integer | | | | plain | |
col2 | text | | | 'stuff'::text | extended | |
col3 | timestamp with time zone | | not null | now() | plain | |
Partition key: RANGE (col3)
Indexes:
"time_taptest_table_native_col3_idx" btree (col3)
Partitions: partman.time_taptest_table_p2020_01_27 FOR VALUES FROM ('2020-01-27 00:00:00-05') TO ('2020-01-28 00:00:00-05'),
partman.time_taptest_table_p2020_01_28 FOR VALUES FROM ('2020-01-28 00:00:00-05') TO ('2020-01-29 00:00:00-05'),
partman.time_taptest_table_p2020_01_29 FOR VALUES FROM ('2020-01-29 00:00:00-05') TO ('2020-01-30 00:00:00-05'),
partman.time_taptest_table_p2020_01_30 FOR VALUES FROM ('2020-01-30 00:00:00-05') TO ('2020-01-31 00:00:00-05'),
partman.time_taptest_table_p2020_01_31 FOR VALUES FROM ('2020-01-31 00:00:00-05') TO ('2020-02-01 00:00:00-05'),
partman.time_taptest_table_p2020_02_01 FOR VALUES FROM ('2020-02-01 00:00:00-05') TO ('2020-02-02 00:00:00-05'),
partman.time_taptest_table_p2020_02_02 FOR VALUES FROM ('2020-02-02 00:00:00-05') TO ('2020-02-03 00:00:00-05'),
partman.time_taptest_table_p2020_02_03 FOR VALUES FROM ('2020-02-03 00:00:00-05') TO ('2020-02-04 00:00:00-05'),
partman.time_taptest_table_p2020_02_04 FOR VALUES FROM ('2020-02-04 00:00:00-05') TO ('2020-02-05 00:00:00-05'),
partman.time_taptest_table_p2020_02_05 FOR VALUES FROM ('2020-02-05 00:00:00-05') TO ('2020-02-06 00:00:00-05'),
partman.time_taptest_table_p2020_02_06 FOR VALUES FROM ('2020-02-06 00:00:00-05') TO ('2020-02-07 00:00:00-05'),
partman.time_taptest_table_p2020_02_07 FOR VALUES FROM ('2020-02-07 00:00:00-05') TO ('2020-02-08 00:00:00-05'),
partman.time_taptest_table_p2020_02_08 FOR VALUES FROM ('2020-02-08 00:00:00-05') TO ('2020-02-09 00:00:00-05'),
partman.time_taptest_table_p2020_02_09 FOR VALUES FROM ('2020-02-09 00:00:00-05') TO ('2020-02-10 00:00:00-05'),
partman.time_taptest_table_p2020_02_10 FOR VALUES FROM ('2020-02-10 00:00:00-05') TO ('2020-02-11 00:00:00-05'),
partman.time_taptest_table_p2020_02_11 FOR VALUES FROM ('2020-02-11 00:00:00-05') TO ('2020-02-12 00:00:00-05'),
partman.time_taptest_table_p2020_02_12 FOR VALUES FROM ('2020-02-12 00:00:00-05') TO ('2020-02-13 00:00:00-05')
ALTER TABLE partman.time_taptest_table RENAME TO time_taptest_table_old; ALTER TABLE partman.time_taptest_table_native RENAME TO time_taptest_table;
CREATE TABLE partman.time_taptest_table_default (LIKE partman.time_taptest_table INCLUDING ALL); ALTER TABLE partman.time_taptest_table ATTACH PARTITION partman.time_taptest_table_default DEFAULT;
CREATE TABLE partman.partman_time_taptest_table (LIKE partman.time_taptest_table); ALTER TABLE partman.partman_time_taptest_table OWNER TO partman_owner;
ALTER TABLE partman.partman_time_taptest_table ADD PRIMARY KEY (col1);
UPDATE partman.part_config SET partition_type = 'native', template_table = 'partman.partman_time_taptest_table' WHERE parent_table = 'partman.time_taptest_table';
COMMIT;
66.4.1.7.6. 迁移已有数据表到partman
-
禁用run_maintenance()
-
将被迁移表设置为不可写
-
将表改为和时间相关的名字，如果分区集的命名方式与所包含的相关数据不同，或者根本不相关，则必须改为根据控制列中的最小值进行重命名。
-- 将表改为和时间相关的名字 select 'ALTER TABLE '||n.nspname||'.'||c.relname||' RENAME TO '||substring(c.relname from 1 for 4)||'_p'||to_char(to_timestamp(substring(c.relname from 5), 'YYYYMMDD'), 'IYYY')||'w'||to_char(to_timestamp(substring(c.relname from 5), 'YYYYMMDD'), 'IW')||';' from pg_inherits h join pg_class c on h.inhrelid = c.oid join pg_namespace n on c.relnamespace = n.oid where h.inhparent::regclass = 'tracking.hits'::regclass order by c.relname;
-- 命名方式与所包含的相关数据不同
insert into tracking.hits_aa values (1, generate_series('2016-01-03 01:00:00'::timestamptz, '2016-01-09 23:00:00'::timestamptz, '1 hour'::interval));
insert into tracking.hits_bb values (2, generate_series('2016-01-10 01:00:00'::timestamptz, '2016-01-16 23:00:00'::timestamptz, '1 hour'::interval));
insert into tracking.hits_cc values (3, generate_series('2016-01-17 01:00:00'::timestamptz, '2016-01-23 23:00:00'::timestamptz, '1 hour'::interval));
DO $rename$
DECLARE
v_min_val bigint;
v_row record;
v_sql text;
BEGIN
-- Adjust your parent table name in the for loop query
FOR v_row IN
SELECT n.nspname AS child_schema, c.relname AS child_table
FROM pg_inherits h
JOIN pg_class c ON h.inhrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE h.inhparent::regclass = 'tracking.hits'::regclass
ORDER BY c.relname
LOOP
-- Substitute your control column's name here in the min() function
v_sql := format('SELECT min(id) FROM %I.%I', v_row.child_schema, v_row.child_table);
EXECUTE v_sql INTO v_min_val;
-- Adjust the numerical value after the % to account for whatever your partitioning interval is.
v_min_val := v_min_val - (v_min_val % 1000);
-- Build the sql statement to rename the child table
v_sql := format('ALTER TABLE %I.%I RENAME TO %I'
, v_row.child_schema
, v_row.child_table
, substring(v_row.child_table from 1 for 4)||'_p'||v_min_val::text);
-- I just have it outputing the ALTER statement for review. If you'd like this code to actually run it, uncomment the EXECUTE below.
RAISE NOTICE '%', v_sql;
-- EXECUTE v_sql;
END LOOP;
END
$rename$;
-
迁移
BEGIN;
DROP TRIGGER myoldtrigger ON tracking.hits;
SELECT partman.create_parent('tracking.hits', 'start', 'partman', 'weekly');
COMMIT;
\d+ tracking.hits
Table "tracking.hits"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------------+-----------+---------+--------------+-------------
id | integer | not null | plain | |
start | timestamp without time zone | not null | plain | |
Triggers:
hits_part_trig BEFORE INSERT ON tracking.hits FOR EACH ROW EXECUTE PROCEDURE tracking.hits_part_trig_func()
Child tables: tracking.hits_p2015w53,
tracking.hits_p2016w01,
tracking.hits_p2016w02,
tracking.hits_p2016w03,
tracking.hits_p2016w04,
tracking.hits_p2016w05,
tracking.hits_p2016w06,
tracking.hits_p2016w07,
tracking.hits_p2016w08,
tracking.hits_p2016w09
66.5. 插件pg_partman卸载方法
用户在不需要使用pg_partman插件的时候，可以使用DROP EXTENSION命令将pg_partman插件卸载。
示例：
drop extension pg_partman;
66.6. 插件pg_partman升级方法
pg_partman扩展插件通常随着KingbaseES安装包一并升级。通常情况下用户无须单独升级些插件。
扫一扫
1796
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
