通过

在 Azure Database for PostgreSQL 灵活服务器上启用和使用 pg_partman

适用于： Azure Database for PostgreSQL 灵活服务器

本文介绍如何使用 PostgreSQL 分区管理器 （pg_partman） 扩展优化 Azure Database for PostgreSQL 灵活服务器。

当数据库中的表格规模变得很大时，往往会难以管理它们的清理频率、占用的空间，在如何保持索引的效率方面也会遇到难题。 这种状况会减缓查询速度并影响性能。 针对这些情况，解决方案是对大型表进行分区。

在本文中，你将使用 pg_partman 在 Azure Database for PostgreSQL 灵活服务器中为表创建基于范围的分区。

先决条件

若要启用 pg_partman 扩展，请执行以下步骤：

  1. 在 Azure 门户中，在 pg_partman 的服务器参数列表中选择 azure.extensions。

    该屏幕截图显示在 Azure 扩展列表中选择 pg_partman 扩展。 

    CREATE EXTENSION pg_partman;

  2. 在 pg_partman_bgw 中包含相关的 shared_preload_libraries 扩展。 它提供计划的功能 run_maintenance()。 它负责处理在 part_config 中将 automatic_maintenance 设置为 ON 的分区集。

    该屏幕截图显示的是对扩展 pg_partman_bgw 的选择。

    可以在 Azure 门户中使用服务器参数来更改影响后台写入器 (BGW) 进程的以下配置选项：

    • pg_partman_bgw.dbname：必需。 此参数应包含一个或多个需要运行 run_maintenance() 的数据库。 如果有多个数据库，请使用逗号分隔的列表。 如果未设置任何内容，pg_partman_bgw 不会运行该过程。

    • pg_partman_bgw.interval：调用 run_maintenance() 过程的间隔秒数。 默认值为 3600（1 小时）。 可以根据项目的要求更新此值。

    • pg_partman_bgw.role：run_maintenance() 过程的运行角色。 默认值为 postgres。 仅支持单个角色名称。

    • pg_partman_bgw.analyze：与 p_analyze 的 run_maintenance() 参数的用途相同。 默认情况下它设置为 OFF。

    • pg_partman_bgw.jobmon：与 p_jobmon 的 run_maintenance() 参数的用途相同。 默认情况下它设置为 ON。

注意

  • 当标识功能使用序列时，来自父表的数据将获得新的序列值。 直接向子表添加数据时，它不会生成新的序列值。

  • pg_partman 扩展使用模板来控制表是否为 UNLOGGED。 这意味着 ALTER TABLE 命令无法更改分区集的此状态。 通过更改模板上的状态，可以将其应用到所有将来的分区。 但对于现有子表，必须手动使用 ALTER TABLE 命令。 这个错误显示出原因。  

设置权限

无需超级用户角色即可使用 pg_partman。 唯一的要求是运行 pg_partman 函数的角色对所有要在其中创建新对象的分区集和架构具有所有权。

建议为 pg_partman 创建一个单独的角色，并赋予其对 pg_partman 将操作的架构和所有对象的所有权：

CREATE ROLE partman_role WITH LOGIN; 
CREATE SCHEMA partman; 
GRANT ALL ON SCHEMA partman TO partman_role; 
GRANT ALL ON ALL TABLES IN SCHEMA partman TO partman_role; 
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO partman_role; 
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA partman TO partman_role; 
GRANT ALL ON SCHEMA <partition_schema> TO partman_role; 
GRANT TEMPORARY ON DATABASE <databasename> to partman_role; --  This allows temporary table creation to move data.

创建分区

pg_partman 扩展仅支持范围类型分区，而不支持基于触发器的分区。 以下代码演示 pg_partman 如何帮助对表进行分区：

CREATE SCHEMA partman; 
CREATE TABLE partman.partition_test 
(a_int INT, b_text TEXT,c_text TEXT,d_date TIMESTAMP DEFAULT now()) 
PARTITION BY RANGE(d_date); 
CREATE INDEX idx_partition_date ON partman.partition_test(d_date); 

pg_partman 的表输出的屏幕截图。

使用 create_parent 函数，可以在分区表上设置所需的分区数：

SELECT public.create_parent( 
p_parent_table := 'partman.partition_test', 
p_control := 'd_date', 
p_type := 'native', 
p_interval := 'daily', 
p_premake :=20, 
p_start_partition := (now() - interval '10 days')::date::text  
);

UPDATE public.part_config   
SET infinite_time_partitions = true,  
    retention = '1 hour',   
    retention_keep_table=true   
        WHERE parent_table = 'partman.partition_test';  

上述命令使用本机分区，根据 p_parent_table 列将 p_control 划分为较小的部分。 （另一个选项是基于触发器的分区，但 pg_partman 目前不支持它。）分区每天创建一次。

该示例提前创建 20 个未来分区，而不是使用默认值 4。 它还会指定 p_start_partition，其中提到了分区应从过去的哪个日期开始。

create_parent() 函数填充两个表：part_config 和 part_config_sub。 还有一个维护函数 run_maintenance()。 可以计划一个 cron 作业，以使此过程定期运行。 此函数会检查 part_config 表中的所有父表并为它们创建新分区或运行表集保留策略。 若要详细了解 pg_partman 中的函数和表，请参阅 GitHub 上的 PostgreSQL 分区管理器扩展文档。

若要在每次通过 pg_partman_bgw 扩展在后台运行 run_maintenance() 时创建新分区，请运行以下 UPDATE 语句：

UPDATE partman.part_config SET premake = premake+1 WHERE parent_table = 'partman.partition_test'; 

如果 premake 相同且 run_maintenance() 过程已运行，则不会为当天创建新分区。 对于第二天，因为基于当前日期定义 premake，所以在执行 run_maintenance() 函数时会为当天创建一个新分区。

使用以下 INSERT INTO 命令，为每个月份插入 100,000 行：

INSERT INTO partman.partition_test SELECT GENERATE_SERIES(1,100000),GENERATE_SERIES(1, 100000) || 'abcdefghijklmnopqrstuvwxyz', 

GENERATE_SERIES(1, 100000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-03-01',timestamp '2024-03-30', interval '1 day ') ; 

INSERT INTO partman.partition_test SELECT GENERATE_SERIES(100000,200000),GENERATE_SERIES(100000,200000) || 'abcdefghijklmnopqrstuvwxyz', 

GENERATE_SERIES(100000,200000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-04-01',timestamp '2024-04-30', interval '1 day') ; 

INSERT INTO partman.partition_test SELECT GENERATE_SERIES(200000,300000),GENERATE_SERIES(200000,300000) || 'abcdefghijklmnopqrstuvwxyz', 

GENERATE_SERIES(200000,300000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-05-01',timestamp '2024-05-30', interval '1 day') ; 

INSERT INTO partman.partition_test SELECT GENERATE_SERIES(300000,400000),GENERATE_SERIES(300000,400000) || 'abcdefghijklmnopqrstuvwxyz', 

GENERATE_SERIES(300000,400000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-06-01',timestamp '2024-06-30', interval '1 day') ; 

INSERT INTO partman.partition_test SELECT GENERATE_SERIES(400000,500000),GENERATE_SERIES(400000,500000) || 'abcdefghijklmnopqrstuvwxyz', 

GENERATE_SERIES(400000,500000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-07-01',timestamp '2024-07-30', interval '1 day') ; 

在 PostgreSQL 上运行以下命令来查看创建的分区：

\d+ partman.partition_test;

带分区的表输出的屏幕截图。

下面是运行的 SELECT 语句的输出：

解释计划输出的屏幕截图。

手动运行维护过程

可以手动运行 partman.run_maintenance() 命令，而不必使用 pg_partman_bgw。 使用以下命令手动运行维护过程：

SELECT partman.run_maintenance(p_parent_table:='partman.partition_test');

警告

如果在创建分区之前插入数据，则数据将转到默认分区。 如果默认分区包含属于你稍后要创建的新分区的数据，那么你会收到默认分区冲突错误，并且该过程将不会运行。 更改之前建议的 premake 值，然后运行该过程。

计划维护过程

使用 pg_cron 运行维护过程：

  1. 首先，在服务器上启用 pg_cron。 在 Azure 门户中，将 pg_cron 添加到 azure. extensions、shared_preload_libraries 和 cron.database_name 服务器参数。

    该屏幕截图显示将 pg_cron 添加到 Azure 扩展的服务器参数。

    该屏幕截图显示将 pg_cron 添加到共享预加载库的服务器参数。

    该屏幕截图显示 cron 数据库名称的服务器参数。

  2. 选择“保存”按钮并等待部署完成。

    部署完成后，会自动创建 pg_cron。 如果你尝试安装它，将会收到以下消息：

    CREATE EXTENSION pg_cron;   

    ERROR: extension "pg_cron" already exists

  3. 若要计划 cron 作业，请使用以下命令：

    SELECT cron.schedule_in_database('sample_job','@hourly', $$SELECT partman.run_maintenance(p_parent_table:= 'partman.partition_test')$$,'postgres'); 

  4. 若要查看所有 cron 作业，请使用以下命令：

    SELECT * FROM cron.job; 

    -[ RECORD 1 ]----------------------------------------------------------------------- 

jobid    | 1 
schedule | @hourly 
command  | SELECT partman.run_maintenance(p_parent_table:= 'partman.partition_test') 
nodename | /tmp 
nodeport | 5432 
database | postgres 
username | postgres 
active   | t 
jobname  | sample_job 

  5. 若要检查作业的运行历史记录，请使用以下命令：

    SELECT * FROM cron.job_run_details; 

    结果显示零条记录，因为尚未运行该作业。

  6. 若要取消计划 cron 作业，请使用以下命令：

    SELECT cron.unschedule(1); 

常见问题解答

  • 为什么 pg_partman_bgw 不根据我提供的间隔运行维护过程？

    检查服务器参数 pg_partman_bgw.dbname，并使用正确的数据库名称对其进行更新。 此外，请检查服务器参数 pg_partman_bgw.role 并提供适当的角色。 还应该确保使用同一用户而不是 Postgres 连接到服务器来创建扩展。

  • 在 pg_partman_bgw 运行维护过程时出现错误。 这是为什么？

    请参阅前面的解答。

  • 如何将分区设置为从前一天开始？

    p_start_partition 函数是指必须从中创建分区的日期。 运行以下命令：

    SELECT public.create_parent( 
p_parent_table := 'partman.partition_test', 
p_control := 'd_date', 
p_type := 'native', 
p_interval := 'daily', 
p_premake :=20, 
p_start_partition := (now() - interval '10 days')::date::text  
);

相关内容