[ 博客首页 ]
黄杰, 2022-02-27
root[a]linuxsand.info
前两篇文章（（一） 和 （二））写了手动管理 PostgreSQL 的分区表。
本文记录了使用 pg_partman 这个 PostgreSQL 拓展（extension）进行分区的创建和自动维护。 pg_partman 支持继承式分区表和声明式（原生）分区表，本文使用原生分区表。
对于 Windows 用户，需要自己克隆代码（git clone https://github.com/pgpartman/pg_partman/）并构建（make install）出 pg_partman。
构建后，预期得到的文件有：
pg_partman.control 和 pg_partman--4.6.0.sql，这是必须的文件；pg_partman_bgw.dll，这是在后台定期执行维护例程的 C 库（需要加载和配置）；暂时不需要它，我将使用 Windows 的计划任务来定期执行维护例程；但是阅读 Makefile 可以知道，在 Windows 下没法直接、方便地进行构建。
通过搜索 github issues，找到了一个应变方法； 我修改了这个 make.sh 文件的 Shebang 、添加了注释如下。
#! /usr/bin/bash
# huangjie comments: execute in git bash
# then copy to C:\Program Files\PostgreSQL\14\share\extension
# then in psql, execute `create extension pg_partman`
# finally, check if it displayed in extensions list
# end huangjie comments
EXTENSION=pg_partman
VERSION=$(grep default_version $EXTENSION.control | \
sed -e "s/default_version[[:space:]]*=[[:space:]]*'\([^']*\)'/\1/")
cat sql/types/*.sql > "${EXTENSION}--${VERSION}.sql"
cat sql/tables/*.sql >> "${EXTENSION}--${VERSION}.sql"
cat sql/functions/*.sql >> "${EXTENSION}--${VERSION}.sql"
直接在 git bash 内执行 ./make.sh，得到了 pg_partman.control 和 pg_partman--4.6.0.sql 文件；
把上一步得到的 pg_partman.control 和 pg_partman--4.6.0.sql 拷贝到 C:\Program Files\PostgreSQL\14\share\extension。然后在 psql 内执行如下代码安装该拓展：
-- 切到 postgres 这个 db 下（以下所有示例，均在 postgres 这个数据库下演示）
\c postgres;
CREATE SCHEMA IF NOT EXISTS partman;
create extension IF NOT EXISTS pg_partman SCHEMA partman;
验证如下。
postgres=# select * from pg_available_extensions where name='pg_partman';
name | default_version | installed_version | comment
------------+-----------------+-------------------+------------------------------------------------------
pg_partman | 4.6.0 | 4.6.0 | Extension to manage partitioned tables by time or ID
(1 row)
我们直接使用 pg_partman.create_parent 按月来创建分区表：
create table dbo.test3
(
"Id" bigserial,
-- ...other columns
"DetectedTime" timestamp without time zone
) PARTITION BY Range("DetectedTime");
-- 略过创建索引
-- create index on dbo.test3 ("DetectedTime");
-- create index on dbo.test3 ("Id");
-- 'native' 即原生分区表（声明式分区表）
select partman.create_parent('dbo.test3', 'DetectedTime', 'native', 'monthly');
查看 create_parent 创建的子表，我们可以看到一共创建了 10 张表（p_premake参数默认为 4 时，1 张当月表，4 张过去的月份表，4 张未来的月份表，1 张默认表）：
postgres=# \d+ dbo.test3;
Partitioned table "dbo.test3"
Column | Type | Collation | Nullable | Default |
--------------+-----------------------------+-----------+----------+-----------------------------------------+
Id | bigint | | not null | nextval('dbo."test3_Id_seq"'::regclass) |
DetectedTime | timestamp without time zone | | | |
Partition key: RANGE ("DetectedTime")
Partitions: dbo.test3_p2021_10 FOR VALUES FROM ('2021-10-01 00:00:00') TO ('2021-11-01 00:00:00'),
dbo.test3_p2021_11 FOR VALUES FROM ('2021-11-01 00:00:00') TO ('2021-12-01 00:00:00'),
dbo.test3_p2021_12 FOR VALUES FROM ('2021-12-01 00:00:00') TO ('2022-01-01 00:00:00'),
dbo.test3_p2022_01 FOR VALUES FROM ('2022-01-01 00:00:00') TO ('2022-02-01 00:00:00'),
dbo.test3_p2022_02 FOR VALUES FROM ('2022-02-01 00:00:00') TO ('2022-03-01 00:00:00'),
dbo.test3_p2022_03 FOR VALUES FROM ('2022-03-01 00:00:00') TO ('2022-04-01 00:00:00'),
dbo.test3_p2022_04 FOR VALUES FROM ('2022-04-01 00:00:00') TO ('2022-05-01 00:00:00'),
dbo.test3_p2022_05 FOR VALUES FROM ('2022-05-01 00:00:00') TO ('2022-06-01 00:00:00'),
dbo.test3_p2022_06 FOR VALUES FROM ('2022-06-01 00:00:00') TO ('2022-07-01 00:00:00'),
dbo.test3_default DEFAULT
就这么简单。
贴一下 create_parent 的完整签名备忘。
create_parent(p_parent_table text, p_control text, p_type text, p_interval text,
p_constraint_cols text[] DEFAULT NULL, p_premake int DEFAULT 4,
p_automatic_maintenance text DEFAULT 'on', p_start_partition text DEFAULT NULL,
p_inherit_fk boolean DEFAULT true, p_epoch text DEFAULT 'none',
p_upsert text DEFAULT '', p_publications text[] DEFAULT NULL,
p_trigger_return_null boolean DEFAULT true, p_template_table text DEFAULT NULL,
p_jobmon boolean DEFAULT true, p_date_trunc_interval text DEFAULT NULL)
RETURNS boolean
使用 run_maintenance 或 run_maintenance_proc 进行分区维护，两者的签名如下。
run_maintenance(p_parent_table text DEFAULT NULL, p_analyze boolean DEFAULT NULL,
p_jobmon boolean DEFAULT true)
RETURNS void
run_maintenance_proc(p_wait int DEFAULT 0, p_analyze boolean DEFAULT NULL,
p_jobmon boolean DEFAULT true)
可以手动针对 dbo.test3 进行维护：
select partman.run_maintenance('dbo.test3', false);
上面是我们在某个数据库下，手动指定某一张表，因此传入了表名作为参数；自动维护时，我们可以调用使用默认参数（NULL, NULL, true）的例程 select partman.run_maintenance(); 就能自动维护 part_config 内的 automatic_maintenance 为真的表。
postgres=# select automatic_maintenance from partman.part_config where parent_table='dbo.test3';
automatic_maintenance
-----------------------
on
(1 row)
我们只需编写一个可执行程序，在连接字符串内拼接好指定的数据库名字（数据库名字可以从配置文件读取），调用维护例程即可；主体源码大概如下。
string connString = "Server=127.0.0.1;Port=5432;User Id=postgres;Password=XXX;Database=";
foreach (string dbname in new string[] { "db1", "db2", "db3" })
{
using (var conn = new NpgsqlConnection(connString + dbname))
{
try
{
conn.Open();
conn.Execute("select partman.run_maintenance();");
conn.Close();
logger.LogInfo($"maintain {s}: success");
}
catch (Exception e)
{
logger.LogError($"maintain {s}: failed -- {e}");
}
} // end using
} // end foreach
最后利用 schtasks /create /? 添加到 Windows 计划任务内。
维护分区，不但包含新建分区，也包含丢弃分区。
如何丢弃分区？retention 为 NULL，默认是不丢弃的。
postgres=# select retention, retention_keep_table from partman.part_config where parent_table='dbo.test3';
retention | retention_keep_table
-----------+----------------------
| t
(1 row)
对于本文中基于时间（DetectedTime 栏位）的分区方式，将 retention 设置为某个 interval 即可。而 retention_keep_table 为真表示只 detach 或 no inherit 表，并不删除数据。
postgres=# update partman.part_config set retention=interval '1 year' where parent_table='dbo.test3';
UPDATE 1
postgres=# select retention, retention_keep_table from partman.part_config where parent_table='dbo.test3';
retention | retention_keep_table
-----------+----------------------
1 year | t
(1 row)
本文介绍了 pg_partman 这个工具的构建、安装和使用。