PostgreSQL扩展：pg_pathman，pg_partman 分区表维护
数据库在维护过程中，分区表一般是个重头戏：建分区，扩分区，拆分区，删分区。
需要一个工具来对分区进行自动的维护，所以有了pg_pathman与pg_partman，这两孪生兄弟。
名字只一字母之差，但差别还是很大。
pg_pathman产生于早期，主要作用于PostgreSQL分区表还是继承模式，主要支持的PG版本为：9.5, 9.6, 10, 11, 12, 13，虽然支持10，11，12，13，但对分区表的维护方式还是继承实现；pg_partman对分区的支持从10开始，到15，主要支持声明式分区表。
下面通过测试说明两个扩展对PG分区表的维护差异。
pg_pathman
支持特性
- 哈希和范围分区;
- 按表达式和复合键分区;
- 自动和手动分区管理;
- 支持整数，浮点，日期和其他类型的，包括域;
- 对分区表（JOIN、子选择等）进行有效的查询规划;
- 运行时应用> 运行时合并应用自定义计划节点，用于在运行时选取分区;
- 分区过滤器：插入触发器的高效插入式替代品;
- 分区路由和分区转换器，用于跨分区更新查询（而不是触发器）;
- 为新的插入数据自动创建分区（仅适用于范围分区）;
- 改进了 COPY FROM 语句，能够将行直接插入到分区中;
- 用于分区创建事件处理的用户定义回调;
- 非阻塞并发表分区;
- FDW support (foreign partitions);;
- 各种 GUC 切换和可配置设置。
- 部分支持声明性分区（来自后greSQL 10）。
下载
https://github.com/postgrespro/pg_pathman
安装
make install USE_PGXS=1 PG_CONFIG=/opt/pgsql/bin/pg_config
参数修改
shared_preload_libraries = 'pg_stat_statements, pg_pathman'
重启生效
创建扩展
CREATE SCHEMA pathman;
GRANT USAGE ON SCHEMA pathman TO PUBLIC;
CREATE EXTENSION pg_pathman WITH SCHEMA pathman;
举例：创建哈希分区
# 创建一张基本表
CREATE TABLE items (
id SERIAL PRIMARY KEY,
name TEXT,
code BIGINT);
INSERT INTO items (id, name, code)
SELECT g, md5(g::text), random() * 100000
FROM generate_series(1, 100000) as g;
# 对表进行哈希分区，同时将数据迁移到分区中
SELECT pathman.create_hash_partitions('items', 'id', 100);
create_hash_partitions
------------------------
100
# 查看分区数据
postgres=# SELECT * FROM items WHERE id = 1234;
id | name | code
------+----------------------------------+-------
1234 | 81dc9bdb52d04dc20036dbd8313ed055 | 48770
(1 row)
postgres=# explain SELECT * FROM items WHERE id = 1234;
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using items_11_pkey on items_11 (cost=0.28..8.29 rows=1 width=45)
Index Cond: (id = 1234)
(2 rows)
# 访问父表
EXPLAIN SELECT * FROM ONLY items;
QUERY PLAN
------------------------------------------------------
Seq Scan on items (cost=0.00..0.00 rows=1 width=45)
(1 row)
# 查看分区子表
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+-----------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('items_id_seq'::regclass) | plain | |
name | text | | | | extended | |
code | bigint | | | | plain | |
Indexes:
"items_99_pkey" PRIMARY KEY, btree (id)
Check constraints:
"pathman_items_99_check" CHECK (pathman.get_hash_part_idx(hashint4(id), 100) = 99)
Inherits: items <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<继承自items父表
Access method: heap
举例：创建范围分区
# 创建基本表
CREATE TABLE journal (
id SERIAL,
dt TIMESTAMP NOT NULL,
level INTEGER,
msg TEXT);
# 索引将在每一个子表上创建。
CREATE INDEX ON journal(dt);
# 造数
INSERT INTO journal (dt, level, msg)
SELECT g, random() * 6, md5(g::text)
FROM generate_series('2015-01-01'::date, '2015-12-31'::date, '1 minute') as g;
postgres=#
# 运行create_range_partitions（）函数创建分区，以便每个分区包含1天的数据
postgres=# SELECT pathman.create_range_partitions('journal', 'dt', '2015-01-01'::date, '1 day'::interval);
create_range_partitions
-------------------------
365
(1 row)
# 查看分区数据 可以看到父表已无数据
postgres=# \dt+ journal*
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-------------+-------+----------+--------+-------------
public | journal | table | postgres | 24 kB |
public | journal_1 | table | postgres | 152 kB |
public | journal_10 | table | postgres | 152 kB |
public | journal_100 | table | postgres | 152 kB |
public | journal_101 | table | postgres | 152 kB |
public | journal_102 | table | postgres | 152 kB |
public | journal_103 | table | postgres | 152 kB |
......
postgres=# select * from only journal;
id | dt | level | msg
----+----+-------+-----
(0 rows)
# 添加分区 指定范围分区
postgres=# SELECT pathman.add_range_partition('journal', '2016-01-01'::date, '2016-01-07'::date);
add_range_partition
---------------------
journal_366
(1 row)
# 添加分区 默认范围分区
postgres=# SELECT pathman.append_range_partition('journal');
append_range_partition
------------------------
journal_367
(1 row)
postgres=# \d journal_366
Table "public.journal_366"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('journal_id_seq'::regclass)
dt | timestamp without time zone | | not null |
level | integer | | |
msg | text | | |
Indexes:
"journal_366_dt_idx" btree (dt)
Check constraints:
"pathman_journal_366_check" CHECK (dt >= '2016-01-01 00:00:00'::timestamp without time zone AND dt < '2016-01-07 00:00:00'::timestamp without time zone)
Inherits: journal <<<<<<<<<<<<<<<<<
# 归档旧数据
## 创建数据归档表，结构要求与数据表一致
postgres=# CREATE TABLE journal_archive (
id INTEGER NOT NULL,
dt TIMESTAMP NOT NULL,
level INTEGER,
msg TEXT);
CREATE TABLE
postgres=# select count(*) from journal_archive ;
count
-------
0
(1 row)
## 将归档表添加到分区表中
postgres=# SELECT pathman.attach_range_partition('journal', 'journal_archive', '2014-01-01'::date, '2015-01-01'::date);
attach_range_partition
------------------------
journal_archive
## 合并分区
postgres=# SELECT pathman.merge_range_partitions('journal_archive', 'journal_1');
merge_range_partitions
------------------------
journal_archive
(1 row)
## 查看归档表中数据
postgres=# select count(*) from journal_archive ;
count
-------
1440
(1 row)
## 查看原有分区已不存在
postgres=# \dt journal_1
Did not find any relation named "journal_1".
# 拆分区
postgres=# SELECT pathman.split_range_partition('journal_366', '2016-01-03'::date);
split_range_partition
-----------------------
journal_368
## 查看拆分区结果
postgres=# \d journal_368
Table "public.journal_368"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('journal_id_seq'::regclass)
dt | timestamp without time zone | | not null |
level | integer | | |
msg | text | | |
Indexes:
"journal_368_dt_idx" btree (dt)
Check constraints:
"pathman_journal_368_check" CHECK (dt >= '2016-01-03 00:00:00'::timestamp without time zone AND dt < '2016-01-07 00:00:00'::timestamp without time zone) <<<<<<<3-7天
Inherits: journal
postgres=# \d journal_366
Table "public.journal_366"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('journal_id_seq'::regclass)
dt | timestamp without time zone | | not null |
level | integer | | |
msg | text | | |
Indexes:
"journal_366_dt_idx" btree (dt)
Check constraints:
"pathman_journal_366_check" CHECK (dt >= '2016-01-01 00:00:00'::timestamp without time zone AND dt < '2016-01-03 00:00:00'::timestamp without time zone) <<<<<<<<<<<<<<<<原有1-7天已为 1-3天
Inherits: journal
# 将归档表分离分区
postgres=# SELECT pathman.detach_range_partition('journal_archive');
detach_range_partition
------------------------
journal_archive
(1 row)
postgres=# \d journal_archive
Table "public.journal_archive"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | integer | | not null |
dt | timestamp without time zone | | not null |
level | integer | | |
msg | text );
<<<<<<<<<<<<<结果已无父表信息
## 取消pathman 管理
postgres=# select * from pathman.pathman_config_params ;
partrel | enable_parent | auto | init_callback | spawn_using_bgw
---------+---------------+------+---------------+-----------------
items | f | t | | f
journal | f | t | | f
(2 rows)
postgres=# select pathman.disable_pathman_for('journal');
disable_pathman_for
---------------------
(1 row)
postgres=# select * from pathman.pathman_config_params ;
partrel | enable_parent | auto | init_callback | spawn_using_bgw
---------+---------------+------+---------------+-----------------
items | f | t | | f
(1 row)
pg_partman
下载
https://github.com/pgpartman/pg_partman
安装
make install PG_CONFIG=/opt/pgsql/bin/pg_config
参数修改
shared_preload_libraries = 'pg_partman_bgw' # (change requires restart)
创建扩展
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;
举例：创建范围分区
CREATE SCHEMA IF NOT EXISTS partman_test;
# 创建分区表
CREATE TABLE partman_test.time_taptest_table
(col1 int,
col2 text default 'stuff',
col3 timestamptz NOT NULL DEFAULT now())
PARTITION BY RANGE (col3);
CREATE INDEX ON partman_test.time_taptest_table (col3);
# 查看分区个数
\d+ partman_test.time_taptest_table
# 创建模板表
CREATE TABLE partman_test.time_taptest_table_template (LIKE partman_test.time_taptest_table);
ALTER TABLE partman_test.time_taptest_table_template ADD PRIMARY KEY (col1);
# 查看临时表结构
\d partman_test.time_taptest_table_template
# 创建分区表
SELECT partman.create_parent('partman_test.time_taptest_table', 'col3', 'native', 'daily', p_template_table := 'partman_test.time_taptest_table_template');
create_parent
---------------
t
(1 row)
# 查看分区表
postgres=# \d+ partman_test.time_taptest_table
Partitioned table "partman_test.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_test.time_taptest_table_p2022_09_28 FOR VALUES FROM ('2022-09-28 00:00:00+08') TO ('2022-09-29 00:00:00+08'),
partman_test.time_taptest_table_p2022_09_29 FOR VALUES FROM ('2022-09-29 00:00:00+08') TO ('2022-09-30 00:00:00+08'),
partman_test.time_taptest_table_p2022_09_30 FOR VALUES FROM ('2022-09-30 00:00:00+08') TO ('2022-10-01 00:00:00+08'),
partman_test.time_taptest_table_p2022_10_01 FOR VALUES FROM ('2022-10-01 00:00:00+08') TO ('2022-10-02 00:00:00+08'),
partman_test.time_taptest_table_p2022_10_02 FOR VALUES FROM ('2022-10-02 00:00:00+08') TO ('2022-10-03 00:00:00+08'),
partman_test.time_taptest_table_p2022_10_03 FOR VALUES FROM ('2022-10-03 00:00:00+08') TO ('2022-10-04 00:00:00+08'),
partman_test.time_taptest_table_p2022_10_04 FOR VALUES FROM ('2022-10-04 00:00:00+08') TO ('2022-10-05 00:00:00+08'),
partman_test.time_taptest_table_p2022_10_05 FOR VALUES FROM ('2022-10-05 00:00:00+08') TO ('2022-10-06 00:00:00+08'),
partman_test.time_taptest_table_p2022_10_06 FOR VALUES FROM ('2022-10-06 00:00:00+08') TO ('2022-10-07 00:00:00+08'),
partman_test.time_taptest_table_default DEFAULT
#调存储过程手动，对分区表进行维护，默认预先创建4个分区。
CALL partman.run_maintenance_proc();
举例： 离线分区
此方法被标记为“脱机”，因为在此过程中的某个时间点，新表和旧表都无法从单个对象访问数据。
数据将从原始表移动到全新的表。
此方法的优点是，您可以以比目标分区大小小得多的批次移动数据，这对于非常大的分区集（您可以以几千比几百万的批次提交）可能是一个巨大的效率
关于外键的重要说明
使分区表脱机是当您具有要分区的表的外键时实际有效的唯一方法。
由于无论如何都必须创建全新的表，因此还必须重新创建外键，因此必须执行涉及属于 FK 关系的所有表的中断。
下面的在线方法可以缩短中断时间，但是如果您必须进行中断，则这种离线方法更容易。
举例：
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_parent（）。
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;
现在，我们可以使用partition_data_proc（）过程将数据从旧表迁移到新表。
我们将以1000行为增量，而分区集的间隔为10000行。
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: 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
同样，在分区一个可能有数十亿行的表时，这样的小批量提交可以避免具有大量行数和长运行时间的事务。
最好避免长时间运行事务，以使PostgreSQL的自动真空处理能够有效地用于数据库的其余部分。
查看旧表数据
SELECT count(*) FROM old_nonpartitioned_table ;
count
-------
0
(1 row)
查看新表数据
SELECT count(*) FROM original_table;
count
--------
100000
(1 row)
举例： 在线分区
有时，无法使表长时间脱机以将其迁移到分区表。以下是允许在线完成的一种方法。它不像离线方法那样灵活，但应该允许非常短的停机时间，并且对表的最终用户几乎是透明的。
如上所述，这些方法不考虑原始表存在外键。您可以从新分区表上的原始表创建外键，一切应该按预期方式工作。但是，如果有外键进入表，我不知道有任何迁移方法不需要中断来删除原始外键并针对新的分区表重新创建它们。
这将是一个基于时间的每日分区集，以 IDENTITY 序列作为主键
举例：
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);
您会注意到我没有在此处将“col1”设置为主键。这是因为我们不能。
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);
ERROR: unique constraint on partitioned table must include all partitioning columns
DETAIL: PRIMARY KEY constraint on table "new_partitioned_table" lacks column "col3" which is part of the partition key.
pg_partman确实有一种机制，可以仍然应用不属于分区列的主/唯一键。请注意，它们不会在整个分区集中强制执行;仅适用于单个分区。
这是使用模板表完成的。为了确保在创建初始子表时应用键，必须预先创建该模板表，并将其名称提供给create_parent（）调用。
我们将使用原始表作为基础，并给出一个与此类似的名称，以便在以后的名称交换后有意义。
另一个重要的注意事项是，我们将“标识”列从“始终生成”更改为“默认生成”。
这是因为我们需要将该标识列的现有值移动到适当的位置。“始终”通常阻止手动输入值。
CREATE TABLE public.original_table_template (LIKE public.original_table);
ALTER TABLE public.original_table_template ADD PRIMARY KEY (col1);
如果不预先创建模板表，pg_partman将始终在安装扩展的相同架构中为您创建一个模板表。您可以通过查看part_config表中的template_table列来查看其名称。但是，如果在create_parent（） 调用后将索引添加到该模板表中，则已存在的子表将不会应用该索引，您必须返回并手动执行此操作。但是，在此之后创建的任何新子表都将具有索引
这里棘手的部分是，我们还不能在分区集中与原始表中当前存在的数据匹配的任何子表。这是因为我们要将旧表作为 DEFAULT 表添加到新分区表中。
如果默认表包含与当前子表的约束匹配的任何数据，则 PostgreSQL 将不允许添加该表。
因此，通过下面的create_parent（）调用，我们将在插入的数据之前启动分区集。
就您而言，您必须查看当前数据集，并在当前工作数据集之前选择一个值，这些数据集可能会在当前工作数据集之前插入，然后才能运行下面的表名交换过程。
我们还将预制值设置为较低的值，以避免以后必须重命名过多的子表。我们稍后会将预制版备份增加到默认值（或者您可以将其设置为所需的任何内容）。
select min(col3), max(col3) from original_table;
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);
下一步是删除pg_partman为您创建的默认分区。
DROP TABLE public.new_partitioned_table_default;
新分区表的状态现在应如下所示。给出了编写此HowTo的当前日期以供参考：
SELECT CURRENT_TIMESTAMP;
\d+ new_partitioned_table;
您还需要更新part_config表，使其具有原始表名。如果您没有自己手动创建模板表，也可以更新模板表，只需确保同时重命名表并更新part_config表即可。
我们还将在此处将预制版重置为默认值。 李代桃疆
UPDATE partman.part_config SET parent_table = 'public.original_table', premake = 4 WHERE parent_table = 'public.new_partitioned_table';
下一步实际上是单个交易中的多个步骤，是需要预料到任何意义的唯一中断。
1.开始交易
2.在原始桌子和新表上拿一个独家锁定，以确保不会误解数据的差距
3.如果使用身份列，请获取原始的最后值
4.将原始表重命名为分区集的默认表名称
5.如果使用身份列，请从旧表中删除身份
6.将新表重命名为原始表的名称，并重命名儿童表和序列以匹配。
7.如果使用身份列，请将新表格的身份重置为最新值，以便任何新的插入物接收到Old Taber的序列关闭的位置。
8.添加原始表作为分区集的默认表格
9.提交交易
如果您使用的是身份列，那么在原始表被锁定和删除旧身份之前，要获得其最后一个值很重要。然后，使用语句中的返回值来重置新表中的身份列。以下的SQL语句中提供了以获取此问题的查询。
如果在任何时候都有其中一个迷你步骤的问题，只需执行回滚，您应该返回以前的状态并允许原始表像以前一样工作。
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_p2022_10_07 RENAME TO original_table_p2022_10_07;
-- IF using an IDENTITY column
ALTER SEQUENCE public.new_partitioned_table_col1_seq RENAME TO original_table_col1_seq;
-- IF using an IDENTITY column
ALTER TABLE public.original_table ALTER col1 RESTART WITH <<<VALUE OBTAINED ABOVE>>>;
ALTER TABLE public.original_table ATTACH PARTITION public.original_table_default DEFAULT;
COMMIT; or ROLLBACK;
一旦运行提交，新的分区表现在应从原始的非分区表中接管。而且，只要所有属性都应用于新表，它就应该在没有任何问题的情况下工作。任何新的数据都应输入相关的子表，或者如果尚不存在，则应转到默认值。后者不是问题，因为…
下一步是将数据从默认设置中分配出来。
您不想将数据留在默认分区设置的任何时间内，尤其是留下大量数据。
如果您查看在分区默认值上存在的约束，则基本上是所有其他儿童表的反构件。
当添加新的子表时，PostgreSQL会根据需要管理更新该默认约束。
但是它必须检查默认情况下是否已经存在该新子表中的任何数据。
如果发现任何东西，它将失败。但更重要的是，它必须检查默认值中的每个条目，即使有数十亿的行，即使有索引也可能需要很长时间。
在此检查期间，整个分区集都有一个独家锁定。
partition_data_proc（）可以将数据从默认值中移出。但是，当将数据从默认值移出时，它不能以任何小时的间隔移动数据。这与刚刚提到的内容有关：如果新子表的约束涵盖默认情况中已经存在的数据，则不能将子表添加到分区集中。
pgpartman通过首先将给定子表的所有数据移到临时表中，然后创建子表，然后将数据从临时表移到新的子表中来处理这个问题。
由于我们正在将数据移出DEFAULT，并且不能使用较小的间隔，因此我们需要传递的唯一参数是批大小。
默认的批处理大小为1时，只会生成一个子表，然后停止。
如果要在单个调用中移动所有数据，只需传递一个足够大的值，以覆盖预期的子表数。
然而，对于活动表和LOTS行，这可能会生成大量WAL文件，特别是因为此方法比脱机方法（默认->temp->子表）的写入次数增加了一倍。
因此，如果需要控制磁盘使用量，只需给出一个较小的批处理值，然后给PostgreSQL一些时间来运行几个CHECKPOINT并清理自己的WAL，然后再转到下一个批处理。
CALL partman.partition_data_proc('public.original_table', p_batch := 200);
VACUUM ANALYZE original_table;
如果您之前使用的标识列具有“始终生成”，则需要将分区表上的标识更改回“默认”的当前设置
如果之前在GENERATED ALWAYS中使用IDENTITY列，则需要将分区表上的标识更改回当前设置BY DEFAULT中的标识
ALTER TABLE public.original_table ALTER col1 SET GENERATED ALWAYS;
现在，仔细检查是否存在不符合pg_partman用于新子表的模式的子表名称。pg_partman需要为其管理的子表指定特定格式，因此，如果它们不完全匹配，维护可能无法按预期工作
postgres=# \d+ original_table;
Partitioned table "public.original_table"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+------------------------------+----------+--------------+-------------
col1 | bigint | | not null | generated always 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_p2022_09_28 FOR VALUES FROM ('2022-09-28 00:00:00+08') TO ('2022-09-29 00:00:00+08'),
original_table_p2022_09_29 FOR VALUES FROM ('2022-09-29 00:00:00+08') TO ('2022-09-30 00:00:00+08'),
original_table_p2022_09_30 FOR VALUES FROM ('2022-09-30 00:00:00+08') TO ('2022-10-01 00:00:00+08'),
original_table_p2022_10_01 FOR VALUES FROM ('2022-10-01 00:00:00+08') TO ('2022-10-02 00:00:00+08'),
original_table_p2022_10_02 FOR VALUES FROM ('2022-10-02 00:00:00+08') TO ('2022-10-03 00:00:00+08'),
original_table_p2022_10_03 FOR VALUES FROM ('2022-10-03 00:00:00+08') TO ('2022-10-04 00:00:00+08'),
original_table_p2022_10_04 FOR VALUES FROM ('2022-10-04 00:00:00+08') TO ('2022-10-05 00:00:00+08'),
original_table_p2022_10_05 FOR VALUES FROM ('2022-10-05 00:00:00+08') TO ('2022-10-06 00:00:00+08'),
original_table_p2022_10_07 FOR VALUES FROM ('2022-10-07 00:00:00+08') TO ('2022-10-08 00:00:00+08'),
original_table_default DEFAULT
现在，为了确保传入的任何新数据都将进入正确的子表而不是默认子表，请对新的分区表运行维护，以确保创建当前的预制分区
SELECT partman.run_maintenance('public.original_table');
在此之前，根据生成的子表和传入的新数据，可能有一些数据仍为默认值。您可以使用pg_partman附带的功能进行检查：
SELECT * FROM partman.check_default(p_exact_count := true);
default_table | count
---------------+-------
(0 rows)
如果不将“true”传递给函数，它只会返回1或0，以指示任何默认值中是否存在任何数据。
这对于监视情况很方便，而且也可以更快，因为它会在任何子表中找到数据后立即停止检查。
但是，在这种情况下，我们想要确切地看到我们的情况是什么，因此传递true将给我们一个默认值中剩余行的确切计数。
您还会注意到上面的集合中缺少一个子表（2022 年 10 月 06 日）。这是因为我们将分区表设置为提前 2 天开始，而原始表中没有任何该日期的数据。
您可以通过以下两种方式之一解决此问题：
1.等待插入该时间段的数据，一旦确定该间隔已完成，请像以前一样将数据从 DEFAULT 中分区出来。
2.运行partition_gap_fill（）函数以立即填充任何空白：
SELECT * FROM partman.partition_gap_fill(‘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 always 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_p2022_09_28 FOR VALUES FROM (‘2022-09-28 00:00:00+08’) TO (‘2022-09-29 00:00:00+08’),
original_table_p2022_09_29 FOR VALUES FROM (‘2022-09-29 00:00:00+08’) TO (‘2022-09-30 00:00:00+08’),
original_table_p2022_09_30 FOR VALUES FROM (‘2022-09-30 00:00:00+08’) TO (‘2022-10-01 00:00:00+08’),
original_table_p2022_10_01 FOR VALUES FROM (‘2022-10-01 00:00:00+08’) TO (‘2022-10-02 00:00:00+08’),
original_table_p2022_10_02 FOR VALUES FROM (‘2022-10-02 00:00:00+08’) TO (‘2022-10-03 00:00:00+08’),
original_table_p2022_10_03 FOR VALUES FROM (‘2022-10-03 00:00:00+08’) TO (‘2022-10-04 00:00:00+08’),
original_table_p2022_10_04 FOR VALUES FROM (‘2022-10-04 00:00:00+08’) TO (‘2022-10-05 00:00:00+08’),
original_table_p2022_10_05 FOR VALUES FROM (‘2022-10-05 00:00:00+08’) TO (‘2022-10-06 00:00:00+08’),
original_table_p2022_10_06 FOR VALUES FROM (‘2022-10-06 00:00:00+08’) TO (‘2022-10-07 00:00:00+08’),
original_table_p2022_10_07 FOR VALUES FROM (‘2022-10-07 00:00:00+08’) TO (‘2022-10-08 00:00:00+08’),
original_table_p2022_10_08 FOR VALUES FROM (‘2022-10-08 00:00:00+08’) TO (‘2022-10-09 00:00:00+08’),
original_table_p2022_10_09 FOR VALUES FROM (‘2022-10-09 00:00:00+08’) TO (‘2022-10-10 00:00:00+08’),
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;
举例：删除本机分区
正如普通表不能转换为本机分区的表一样，情况也正好相反。若要撤消本机分区，必须将数据移动到全新的表中。可能有一种方法可以在线执行此操作，但我目前还没有计划出这样的方法。如果有人想提交一种方法或要求我进一步研究它，请随时在Github上提出问题。下面的方法显示了如何撤消上面的每日分区示例，包括在必要时处理 IDENTITY 列。
首先，我们创建一个新表以将数据迁移到其中。我们可以设置主键，或者在模板上创建的任何唯一索引。如果有任何标识列，则必须将方法设置为“默认生成”，因为我们将在迁移过程中手动添加值。如果需要始终，可以在以后进行更改。
如果此表将继续用作分区表，请确保在此表上也创建了所有特权、约束和索引。索引 &约束的创建可以延迟到数据移动后，以加快迁移速度。
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);
现在，我们可以使用 undo_partition_proc（） 过程将数据从分区表移动到常规表。我们甚至可以为此选择较小的间隔大小，以减少每个批次的事务运行时间。
批大小是默认值 1，它只会运行给定的时间间隔一次。
我们希望通过一次调用撤消整个操作，因此请传递一个足够高的数字以运行所有批处理。当所有数据都已移动时，它将停止，即使您传递了更高的批号。
一旦旧的子表为空，我们也不需要保留它们，因此将其设置为 false。有关撤消函数/过程的其他选项的详细信息，请参阅文档。
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);
VACUUM ANALYZE original_table;
VACUUM ANALYZE new_regular_table;
现在，如果需要，可以交换对象名称并重置标识序列并更改方法。请务必获取原始序列值并在重置时使用它。
SELECT max(col1) FROM public.original_table;
ALTER TABLE original_table RENAME TO old_partitioned_table;
ALTER SEQUENCE original_table_col1_seq RENAME TO old_partitioned_table_col1_seq;
ALTER TABLE new_regular_table RENAME TO original_table;
ALTER SEQUENCE new_regular_table_col1_seq RENAME TO original_table_col1_seq;
ALTER TABLE public.original_table ALTER col1 RESTART WITH <<<VALUE OBTAINED ABOVE>>>;
ALTER TABLE public.original_table ALTER col1 SET GENERATED ALWAYS;
INSERT INTO original_table (col2, col3, col4) VALUES ('newstuff', now(), 'newstuff');
INSERT INTO original_table (col2, col3, col4) VALUES ('newstuff', now(), 'newstuff');
SELECT * FROM original_table ORDER BY col1 DESC limit 2;
免责声明：
1、本站资源由自动抓取工具收集整理于网络。
2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。
3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。
4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。
5、如果您喜欢本资源，请您支持作者，购买正版内容。
6、资源失效，请下方留言，欢迎分享资源链接
文章评论