• 金仓数据库 KingbaseES 插件参考手册 pg_partman


    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 <<>>;
    
    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 <<>>;
    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

    1. 禁用run_maintenance()

    2. 将被迁移表设置为不可写

    3. 将表改为和时间相关的名字,如果分区集的命名方式与所包含的相关数据不同,或者根本不相关,则必须改为根据控制列中的最小值进行重命名。

    -- 将表改为和时间相关的名字
    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$;
    
    1. 迁移

    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安装包一并升级。通常情况下用户无须单独升级些插件。

  • 相关阅读:
    多商户商城系统功能拆解19讲-平台端发票管理
    Woodpecker CI 设计分析|一个 Go 编写的开源持续集成引擎
    API文档工具knife4j使用详解
    PWA 应用 Service Worker 缓存的一些可选策略和使用场景
    3.1网安学习第三阶段第一周回顾(个人学习记录使用)
    CPU设计(单周期和流水线)
    小白学爬虫:通过关键词搜索1688商品列表数据接口|1688商品列表数据接口|1688商品列表数据采集|1688API接口
    c++访问修饰符与继承关系
    定制型CRM助力百趣生物开辟代谢组学新蓝海
    API测试基础之http协议
  • 原文地址:https://blog.csdn.net/arthemis_14/article/details/126600818