[ 博客首页 ]
黄杰, 2022-02-27
root[a]linuxsand.info
前两篇文章（（一） 和 （二））写了手动管理 PostgreSQL 的分区表。
本文记录了使用 pg_partman 这个 PostgreSQL 拓展（extension）进行分区的创建和自动维护。 pg_partman 支持继承式分区表和声明式（原生）分区表，本文使用原生分区表。
对于 Windows 用户来说，pg_partman 的安装可比配置和使用麻烦多了：需要自己克隆代码（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 库，它会在后台定期执行维护例程，我需要它阅读 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 文件；接着把它俩拷贝到 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)
尽管现在可以进入配置和使用环节，但不如等 BGW 安装完毕后一起操作。
关于 BGW 在 Windows 上的构建方法，我参考了这篇文章。
用 Visual Studio 2019 创建空白 C++ 项目，（略），复制了 pg_partman_bgw.c 内的代码（src/pg_partman_bgw.c），（略），构建得到了 pg_partman_bgw.dll。
将 pg_partman_bgw.dll 复制到 C:\Program Files\PostgreSQL\14\lib 内，并且通过 services.msc 重启 postgresql 服务。
附上我构建完毕的 pg_partman 压缩包（64bit）。http://media.linuxsand.info/misc/pg_partman_windows_linuxsand.info.7z
参照文档，修改 postgresql.conf。
# - Shared Library Preloading -
shared_preload_libraries = 'pg_partman_bgw' # (change requires restart)
# Add settings for extensions here
pg_partman_bgw.dbname = 'postgres' # 可以使用 , 来分隔多个数据库
pg_partman_bgw.interval = 3600
重新加载配置：
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(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);
根据上一节的配置，BGW 会每隔 3600 秒自动调用 run_maintenance；自动维护时不能传入表名作为参数，BGW 会自动维护 part_config 内的 automatic_maintenance 为真的表。
postgres=# select automatic_maintenance from partman.part_config where parent_table='dbo.test3';
automatic_maintenance
-----------------------
on
(1 row)
如何丢弃分区？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 这个工具的安装、配置和使用。