在 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.rolerun_maintenance() 过程的运行角色。 默认值为 postgres。 仅支持单个角色名称。

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

    • pg_partman_bgw.jobmon:与 p_jobmonrun_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_configpart_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. extensionsshared_preload_librariescron.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  
    );