将 PostgreSQL 扩展与 Amazon RDS for PostgreSQL 结合使用 - Amazon Relational Database Service

将 PostgreSQL 扩展与 Amazon RDS for PostgreSQL 结合使用

您可以通过安装各种扩展和模块来扩展 PostgreSQL 的功能。例如,要处理空间数据,您可以安装和使用 PostGIS 扩展。有关更多信息,请参阅使用 PostGIS 扩展管理空间数据。作为另一个例子,如果要改进非常大的表的数据输入,您可以考虑使用 pg_partman 扩展对数据进行分区。要了解更多信息,请参阅 使用 pg_partman 扩展管理 PostgreSQL 分区

注意

自 RDS for PostgreSQL 14.5 起,RDS for PostgreSQL 支持适用于 PostgreSQL 的可信语言扩展。此特征是作为扩展 pg_tle 实现的,您可以将其添加到 RDS for PostgreSQL 数据库实例中。通过使用此扩展,开发人员可以在安全的环境中创建自己的 PostgreSQL 扩展,从而简化设置和配置要求。有关更多信息,请参阅使用适用于 PostgreSQL 的可信语言扩展

在某些情况下,您可以将特定模块添加到 RDS for PostgreSQL 数据库实例的自定义数据库参数组中的 shared_preload_libraries 列表中,而不是安装扩展。通常,默认的数据库集群参数组仅加载 pg_stat_statements,但还有其他几个模块可供添加到此列表中。例如,您可以通过添加 pg_cron 模块来添加调度功能,详情请见使用 PostgreSQL pg_cron 扩展计划维护。再举一个例子,您可以通过加载 auto_explain 模块来记录查询执行计划。要了解更多信息,请参阅 AWS 知识中心中的记录查询执行计划

根据您的 RDS for PostgreSQL 版本,安装扩展可能需要 rds_superuser 权限,如下所示:

  • 对于 RDS for PostgreSQL 版本 12 和更早版本,安装扩展需要 rds_superuser 权限。

  • 对于 RDS for PostgreSQL 版本 13 及更高版本,对给定数据库实例具有创建权限的用户(角色)可以安装和使用任何可信扩展。有关可信扩展清单,请参阅PostgreSQL 可信扩展

您还可以通过在 rds.allowed_extensions 参数中列出扩展,来精确指定可以在 RDS for PostgreSQL 数据库实例上安装的扩展。有关更多信息,请参阅限制 PostgreSQL 扩展的安装

要了解有关 rds_superuser 角色的更多信息,请参阅了解 PostgreSQL 角色和权限

使用 orafce 扩展中的函数

orafce 扩展提供了模拟 Oracle 数据库中的函数和软件包子集的函数和运算符。orafce 扩展使您能够更轻松地将 Oracle 应用程序移植到 PostgreSQL。RDS for PostgreSQL 版本 9.6.6 及更高版本支持该扩展。有关 orafce 的更多信息,请参阅 GitHub 上的 orafce

注意

RDS for PostgreSQL 不支持属于 orafce 扩展一部分的 utl_file 软件包。这是因为 utl_file 架构函数可提供对操作系统文本文件的读取和写入操作,这需要对底层主机拥有超级用户访问权限。作为一项托管服务,RDS for PostgreSQL 不提供主机访问权限。

使用 orafce 扩展
  1. 使用用于创建数据库实例的主用户名连接到数据库实例。

    如果要为同一数据库实例中的不同数据库启用 orafce,请使用 /c dbname psql 命令。使用此命令,您可以在启动连接后从主数据库进行更改。

  2. 使用 CREATE EXTENSION 语句启用 orafce 扩展。

    CREATE EXTENSION orafce;
  3. 使用 ALTER SCHEMA 语句将 oracle 架构的所有权移交给 rds_superuser 角色。

    ALTER SCHEMA oracle OWNER TO rds_superuser;

    如果要查看 oracle 架构的所有者列表,请使用 \dn psql 命令。

使用 pgactive 支持主动-主动复制

pgactive 扩展使用主动-主动复制来支持和协调多个 RDS for PostgreSQL 数据库上的写入操作。Amazon RDS for PostgreSQL 支持以下版本的 pgactive 扩展。

  • RDS for PostgreSQL 16.1 及更高的 16 版本

  • RDS for PostgreSQL 15.4-R2 及更高的 15 版本

  • RDS for PostgreSQL 14.10 及更高的 14 版本

  • RDS for PostgreSQL 13.13 及更高的 13 版本

  • RDS for PostgreSQL 12.17 及更高的 12 版本

  • RDS for PostgreSQL 11.22

注意

当复制配置中的多个数据库上有写入操作时,可能会发生冲突。有关更多信息,请参阅 处理主动-主动复制中的冲突

初始化 pgactive 扩展功能

要在 RDS for PostgreSQL 数据库实例上初始化 pgactive 扩展功能,请将参数 rds.enable_pgactive 的值设置为 1,然后在数据库中创建扩展。这样做会自动开启参数 rds.logical_replicationtrack_commit_timestamp 并将 wal_level 的值设置为 logical

您必须拥有 rds_superuser 角色的权限才能执行这些任务。

您可以使用 AWS Management Console或 AWS CLI 创建所需的 RDS for PostgreSQL 数据库实例。以下步骤假设您的 RDS for PostgreSQL 数据库实例与自定义数据库参数组相关联。有关创建自定义数据库参数组的信息,请参阅使用参数组

初始化 pgactive 扩展功能
  1. 登录 AWS Management Console 并通过以下网址打开 Amazon RDS 控制台:https://console.aws.amazon.com/rds/

  2. 在导航窗格中,选择 RDS for PostgreSQL 数据库实例。

  3. 打开 RDS for PostgreSQL 数据库实例的配置选项卡。在实例详细信息中,找到数据库实例参数组链接。

  4. 选择此链接以打开与 RDS for PostgreSQL 数据库实例关联的自定义参数。

  5. 找到 rds.enable_pgactive 参数,并将其设置为 1 以初始化 pgactive 功能。

  6. 选择 Save changes(保存更改)

  7. 在 Amazon RDS 控制台的导航窗格中,选择数据库

  8. 选择您的 RDS for PostgreSQL 数据库实例,然后从操作菜单中选择重启

  9. 确认数据库实例重启,以便您的更改生效。

  10. 当数据库实例可用时,您可以使用 psql 或任何其它 PostgreSQL 客户端连接到 RDS for PostgreSQL 数据库实例。

    以下示例假设 RDS for PostgreSQL 数据库实例有一个名为 postgres 的原定设置数据库。

    psql --host=mydb.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=master username --password --dbname=postgres
  11. 要验证 pgactive 是否初始化,可以运行以下命令。

    postgres=>SELECT setting ~ 'pgactive' FROM pg_catalog.pg_settings WHERE name = 'shared_preload_libraries';

    如果 pgactiveshared_preload_libraries 中,则前面的命令将返回以下内容:

    ?column? ---------- t
  12. 创建扩展,如下所示。

    postgres=> CREATE EXTENSION pgactive;
初始化 pgactive 扩展功能

要使用 AWS CLI 设置 pgactive,请调用 modify-db-parameter-group 操作来修改自定义参数组中的某些参数,如以下过程所示。

  1. 使用以下 AWS CLI 命令将 rds.enable_pgactive 设置为 1,以初始化 RDS for PostgreSQL 数据库实例的 pgactive 功能。

    postgres=>aws rds modify-db-parameter-group \ --db-parameter-group-name custom-param-group-name \ --parameters "ParameterName=rds.enable_pgactive,ParameterValue=1,ApplyMethod=pending-reboot" \ --region aws-region
  2. 使用以下 AWS CLI 命令重启 RDS for PostgreSQL 数据库实例,以便初始化 pgactive 库。

    aws rds reboot-db-instance \ --db-instance-identifier your-instance \ --region aws-region
  3. 当实例可用时,使用 psql 连接到 RDS for PostgreSQL 数据库实例

    psql --host=mydb.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=master user --password --dbname=postgres
  4. 创建扩展,如下所示。

    postgres=> CREATE EXTENSION pgactive;

RDS for PostgreSQL 数据库实例设置主动-主动复制

以下过程说明如何在同一区域中运行 PostgreSQL 15.4 或更高版本的两个 RDS for PostgreSQL 数据库实例之间启动主动-主动复制。要运行多区域高可用性示例,您需要在两个不同的区域中部署 Amazon RDS for PostgreSQL 实例,并设置 VPC 对等。有关更多信息,请参阅 VPC 对等

注意

在多个区域之间发送流量可能会产生额外费用。

这些步骤假设已使用 pgactive 扩展设置了 RDS for PostgreSQL 数据库实例。有关更多信息,请参阅初始化 pgactive 扩展功能

使用 pgactive 扩展配置第一个 RDS for PostgreSQL 数据库实例

以下示例说明如何创建 pgactive 组,以及在 RDS for PostgreSQL 数据库实例上创建 pgactive 扩展所需的其它步骤。

  1. 使用 psql 或其它客户端工具连接第一个 RDS for PostgreSQL 数据库实例。

    psql --host=firstinstance.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=master username --password --dbname=postgres
  2. 使用以下命令在 RDS for PostgreSQL 实例上创建数据库:

    postgres=> CREATE DATABASE app;
  3. 使用以下命令将连接切换到新数据库:

    \c app
  4. 要检查 shared_preload_libraries 参数是否包含 pgactive,请运行以下命令:

    app=>SELECT setting ~ 'pgactive' FROM pg_catalog.pg_settings WHERE name = 'shared_preload_libraries';
    ?column? ---------- t
  5. 使用以下 SQL 语句创建并填充示例表:

    1. 使用以下 SQL 语句创建一个示例表。

      app=> CREATE SCHEMA inventory; CREATE TABLE inventory.products ( id int PRIMARY KEY, product_name text NOT NULL, created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP);
    2. 使用以下 SQL 语句用一些示例数据填充表。

      app=> INSERT INTO inventory.products (id, product_name) VALUES (1, 'soap'), (2, 'shampoo'), (3, 'conditioner');
    3. 使用以下 SQL 语句验证表中是否存在数据。

      app=>SELECT count(*) FROM inventory.products; count ------- 3
  6. 在现有数据库上创建 pgactive 扩展。

    app=> CREATE EXTENSION pgactive;
  7. 使用以下命令创建并初始化 pgactive 组:

    app=> SELECT pgactive.pgactive_create_group( node_name := 'node1-app', node_dsn := 'dbname=app host=firstinstance.111122223333.aws-region.rds.amazonaws.com user=master username password=PASSWORD');

    node1-app 是您分配的名称,用于唯一标识 pgactive 组中的节点。

    注意

    要在可公开访问的数据库实例上成功执行此步骤,必须通过将 rds.custom_dns_resolution 参数设置为 1 将其开启。

  8. 要检查数据库实例是否就绪,请使用以下命令:

    app=> SELECT pgactive.pgactive_wait_for_node_ready();

    如果命令成功,您可以看到以下输出:

    pgactive_wait_for_node_ready ------------------------------ (1 row)
配置第二个 RDS for PostgreSQL 实例并将其加入 pgactive

以下示例说明如何将 RDS for PostgreSQL 数据库实例加入 pgactive 组,以及在数据库实例上创建 pgactive 扩展所需的其它步骤。

这些步骤假设已使用 pgactive 扩展设置了其它 RDS for PostgreSQL 数据库实例。有关更多信息,请参阅初始化 pgactive 扩展功能

  1. 使用 psql 连接到要从发布者接收更新的实例。

    psql --host=secondinstance.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=master username --password --dbname=postgres
  2. 使用以下命令在第二个 RDS for PostgreSQL 数据库实例上创建数据库:

    postgres=> CREATE DATABASE app;
  3. 使用以下命令将连接切换到新数据库:

    \c app
  4. 在现有数据库上创建 pgactive 扩展。

    app=> CREATE EXTENSION pgactive;
  5. RDS for PostgreSQL 第二个数据库实例加入 pgactive 组,如下所示。

    app=> SELECT pgactive.pgactive_join_group( node_name := 'node2-app', node_dsn := 'dbname=app host=secondinstance.111122223333.aws-region.rds.amazonaws.com user=master username password=PASSWORD', join_using_dsn := 'dbname=app host=firstinstance.111122223333.aws-region.rds.amazonaws.com user=postgres password=PASSWORD');

    node2-app 是您分配的名称,用于唯一标识 pgactive 组中的节点。

  6. 要检查数据库实例是否就绪,请使用以下命令:

    app=> SELECT pgactive.pgactive_wait_for_node_ready();

    如果命令成功,您可以看到以下输出:

    pgactive_wait_for_node_ready ------------------------------ (1 row)

    如果第一个 RDS for PostgreSQL 数据库相对较大,则可以看到 pgactive.pgactive_wait_for_node_ready() 正在发出还原操作的进度报告。输出看上去类似于以下内容:

    NOTICE: restoring database 'app', 6% of 7483 MB complete NOTICE: restoring database 'app', 42% of 7483 MB complete NOTICE: restoring database 'app', 77% of 7483 MB complete NOTICE: restoring database 'app', 98% of 7483 MB complete NOTICE: successfully restored database 'app' from node node1-app in 00:04:12.274956 pgactive_wait_for_node_ready ------------------------------ (1 row)

    此后,pgactive 将在两个数据库实例之间同步数据。

  7. 您可以使用以下命令来验证第二个数据库实例的数据库是否具有数据:

    app=> SELECT count(*) FROM inventory.products;

    如果数据成功同步,您将看到以下输出:

    count ------- 3
  8. 运行以下命令插入新值:

    app=> INSERT INTO inventory.products (id, product_name) VALUES ('lotion');
  9. 连接到第一个数据库实例的数据库并运行以下查询:

    app=> SELECT count(*) FROM inventory.products;

    如果已初始化主动-主动复制,则输出类似于以下内容:

    count ------- 4
pgactive 组中分离和移除数据库实例

您可以使用以下步骤从 pgactive 组中分离和移除数据库实例:

  1. 您可以使用以下命令将第二个数据库实例与第一个数据库实例分离:

    app=> SELECT * FROM pgactive.pgactive_detach_nodes(ARRAY[‘node2-app']);
  2. 使用以下命令从第二个数据库实例中移除 pgactive 扩展:

    app=> SELECT * FROM pgactive.pgactive_remove();

    要强制移除此扩展,请执行以下操作:

    app=> SELECT * FROM pgactive.pgactive_remove(true);
  3. 使用以下命令删除扩展:

    app=> DROP EXTENSION pgactive;

处理主动-主动复制中的冲突

pgactive 扩展适用于每个数据库,而不是每个集群。使用 pgactive 的每个数据库实例都是一个独立的实例,可以接受来自任何来源的数据更改。将更改发送到数据库实例时,PostgreSQL 会在本地提交更改,然后使用 pgactive 将更改异步复制到其它数据库实例。当两个 PostgreSQL 数据库实例几乎同时更新同一记录时,可能会发生冲突。

pgactive 扩展提供了冲突检测和自动解决机制。它跟踪在这两个数据库实例上提交事务的时间戳,并自动应用带有最新时间戳的更改。pgactive 扩展还会记录 pgactive.pgactive_conflict_history 表中发生冲突的时间。

pgactive.pgactive_conflict_history 会继续增加。您可能需要定义清除策略。可以通过定期删除一些记录或为此关系定义分区方案(然后分离、删除、截断感兴趣的分区)来完成此操作。要定期实施清除策略,一种选择是使用 pg_cron 扩展。请参阅 pg_cron 历史记录表示例的以下信息:使用 PostgreSQL pg_cron 扩展安排维护计划

处理主动-主动复制中的序列

带有 pgactive 扩展的 RDS for PostgreSQL 数据库实例使用两种不同的序列机制来生成唯一值。

全局序列

要使用全局序列,请使用 CREATE SEQUENCE 语句创建一个本地序列。使用 pgactive.pgactive_snowflake_id_nextval(seqname) 而非 usingnextval(seqname) 来获取序列的下一个唯一值。

以下示例创建全局序列:

postgres=> CREATE TABLE gstest ( id bigint primary key, parrot text );
postgres=>CREATE SEQUENCE gstest_id_seq OWNED BY gstest.id;
postgres=> ALTER TABLE gstest \ ALTER COLUMN id SET DEFAULT \ pgactive.pgactive_snowflake_id_nextval('gstest_id_seq');
分区序列

在分步或分区序列中,每个节点上都使用普通的 PostgreSQL 序列。每个序列的增量相同,从不同的偏移量开始。例如,在步骤 100 中,节点 1 生成序列为 101、201、301,依此类推,而节点 2 生成序列为 102、202、302,依此类推。即使节点长时间无法通信,该方案也能正常工作,但要求设计人员在建立模式时指定最大节点数,并且需要按节点进行配置。错误很容易导致序列重叠。

通过在节点上创建所需的序列,使用 pgactive 配置这种方法相对简单,如下所示:

CREATE TABLE some_table (generated_value bigint primary key);
postgres=> CREATE SEQUENCE some_seq INCREMENT 100 OWNED BY some_table.generated_value;
postgres=> ALTER TABLE some_table ALTER COLUMN generated_value SET DEFAULT nextval('some_seq');

然后,对每个节点调用 setval 以给出不同的偏移起始值,如下所示。

postgres=> -- On node 1 SELECT setval('some_seq', 1); -- On node 2 SELECT setval('some_seq', 2);

pgactive 扩展的参数参考

您可以使用以下查询来查看与 pgactive 扩展关联的所有参数。

postgres=> SELECT * FROM pg_settings WHERE name LIKE 'pgactive.%';

衡量 pgactive 成员之间的复制延迟

您可以使用以下查询来查看 pgactive 成员之间的复制延迟。在每个 pgactive 节点上运行此查询以了解全部信息。

postgres=# SELECT *, (last_applied_xact_at - last_applied_xact_committs) AS lag FROM pgactive.pgactive_node_slots; -{ RECORD 1 ]----------------+----------------------------------------------------------------- node_name | node2-app slot_name | pgactive_5_7332551165694385385_0_5__ slot_restart_lsn | 0/1A898A8 slot_confirmed_lsn | 0/1A898E0 walsender_active | t walsender_pid | 69022 sent_lsn | 0/1A898E0 write_lsn | 0/1A898E0 flush_lsn | 0/1A898E0 replay_lsn | 0/1A898E0 last_sent_xact_id | 746 last_sent_xact_committs | 2024-02-06 18:04:22.430376+00 last_sent_xact_at | 2024-02-06 18:04:22.431359+00 last_applied_xact_id | 746 last_applied_xact_committs | 2024-02-06 18:04:22.430376+00 last_applied_xact_at | 2024-02-06 18:04:52.452465+00 lag | 00:00:30.022089

pgactive 扩展的局限性

  • 所有表都需要主键,否则不允许使用更新和删除。不应更新“主键”列中的值。

  • 序列可能存在间隙,有时可能不遵循顺序。不会复制序列。有关更多信息,请参阅处理主动-主动复制中的序列

  • 不会复制 DDL 和大型对象。

  • 辅助唯一索引可能会导致数据差异。

  • 组中所有节点上的排序规则需要相同。

  • 跨节点负载均衡是一种反模式。

  • 大型事务可能会导致复制滞后。

使用 pg_repack 扩展减少表和索引的膨胀

您可以使用 pg_repack 扩展从表和索引中移除多余内容,作为 VACUUM FULL 的备选方法。RDS for PostgreSQL 版本 9.6.3 及更高版本支持该扩展。有关 pg_repack 扩展和完整表重新打包的更多信息,请参阅 GitHub 项目文档

VACUUM FULL 不同,在以下情况下,pg_repack 扩展只需在表重建操作期间短时间使用独占锁(AccessExclusiveLock):

  • 初始创建日志表 - 创建日志表以记录在数据初始复制期间发生的更改,如以下示例所示:

    postgres=>\dt+ repack.log_* List of relations -[ RECORD 1 ]-+---------- Schema | repack Name | log_16490 Type | table Owner | postgres Persistence | permanent Access method | heap Size | 65 MB Description |
  • 最后的交换和删除阶段。

在重建操作的其余部分中,它只需对原始表使用 ACCESS SHARE 锁,即可将行从该表复制到新表。这有助于 INSERT、UPDATE 和 DELETE 操作照常进行。

建议

当您使用 pg_repack 扩展从表和索引中移除膨胀内容时,以下建议适用:

  • 在非工作时间或在维护时段内执行重新打包,以最大限度地减少它对其它数据库活动性能的影响。

  • 在重建活动期间,密切监视阻止会话,并确保原始表上不存在可能阻止 pg_repack 的活动,特别是在最后的交换和删除阶段,此时它需要对原始表进行独占锁定。有关更多信息,请参阅识别阻止查询的内容

    当您看到阻止会话时,经慎重考虑后,可以使用以下命令将其终止。这有助于继续执行 pg_repack 以完成重建:

    SELECT pg_terminate_backend(pid);
  • 在事务速率非常高的系统上应用 pg_repack's 日志表中的应计更改时,应用过程可能无法跟上更改速率。在这种情况下,pg_repack 将无法完成应用过程。有关更多信息,请参阅在重新打包期间监控新表。如果索引严重膨胀,另一种解决方案是执行仅限索引的重新打包。这还有助于 VACUUM 的索引清理周期更快地完成。

    您可以使用 PostgreSQL 版本 12 中的手动 VACUUM 跳过索引清理阶段,在 PostgreSQL 版本 14 中的紧急 autovacuum 期间会自动跳过索引清理阶段。这有助于在不消除索引膨胀的情况下更快地完成 VACUUM,并且仅适用于紧急情况,例如防止重叠 VACUUM。有关更多信息,请参阅《Amazon Aurora 用户指南》中的避免索引膨胀

先决条件

  • 该表必须具有 PRIMARY KEY 或非 null UNIQUE 约束。

  • 客户端和服务器的扩展版本必须相同。

  • 确保 RDS 实例的 FreeStorageSpace 超过表的总大小,而不会出现膨胀。例如,假设表(包括 TOAST 和索引)的总大小为 2TB,表中的总膨胀为 1TB。所需 FreeStorageSpace 必须大于以下计算返回的值:

    2TB (Table size) - 1TB (Table bloat) = 1TB

    您可以使用以下查询来检查表的总大小,并使用 pgstattuple 来派生膨胀。有关更多信息,请参阅《Amazon Aurora 用户指南》中的诊断表和索引膨胀

    SELECT pg_size_pretty(pg_total_relation_size('table_name')) AS total_table_size;

    活动完成后,将回收此空间。

  • 确保 RDS 实例有足够的计算和 IO 容量来处理重新打包操作。您可以考虑纵向扩展实例类以实现性能的最佳平衡。

使用 pg_repack 扩展
  1. 通过运行以下命令在 RDS for PostgreSQL 数据库实例上安装 pg_repack 扩展。

    CREATE EXTENSION pg_repack;
  2. 运行以下命令以授予对由 pg_repack 创建的临时日志表的写入访问权限。

    ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT INSERT ON TABLES TO PUBLIC; ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT USAGE, SELECT ON SEQUENCES TO PUBLIC;
  3. 使用 pg_repack 客户端实用程序连接到数据库。使用具有 rds_superuser 特权的账户。例如,假设 rds_test 角色有 rds_superuser 特权。以下语法对完整表执行 pg_repack,包括 postgres 数据库中的所有表索引。

    pg_repack -h db-instance-name.111122223333.aws-region.rds.amazonaws.com -U rds_test -k postgres
    注意

    必须使用 -k 选项进行连接。不支持 -a 选项。

    来自 pg_repack 客户端的响应提供有关重新打包的数据库实例上的表的信息。

    INFO: repacking table "pgbench_tellers" INFO: repacking table "pgbench_accounts" INFO: repacking table "pgbench_branches"
  4. 以下语法对 postgres 数据库中包含索引的单个表 orders 进行重新打包。

    pg_repack -h db-instance-name.111122223333.aws-region.rds.amazonaws.com -U rds_test --table orders -k postgres

    以下语法仅重新打包 postgres 数据库中 orders 表的索引。

    pg_repack -h db-instance-name.111122223333.aws-region.rds.amazonaws.com -U rds_test --table orders --only-indexes -k postgres

在重新打包期间监控新表

  • 在重新打包的交换和删除阶段之前,数据库的大小增加量为表的总大小减去膨胀。您可以监控数据库大小的增长率,计算重新打包的速度,并粗略估计完成初始数据传输所需的时间。

    例如,假设表的总大小为 2TB,数据库的大小为 4TB,表中的总膨胀为 1TB。重新打包操作结束时计算返回的数据库总大小值如下:

    2TB (Table size) + 4 TB (Database size) - 1TB (Table bloat) = 5TB

    您可以通过对两个时间点之间的增长率(以字节为单位)进行采样来粗略估计重新打包操作的速度。如果增长率为每分钟 1GB,则可能需要 1000 分钟或大约 16.6 小时才能完成初始表构建操作。除了初始表构建外,pg_repack 还需要应用应计更改。所需时间取决于应用持续更改以及应计更改的速率。

    注意

    您可以使用 pgstattuple 扩展来计算表中的膨胀。有关更多信息,请参阅 pgstattuple

  • 重新打包架构下 pg_repack's 日志表中的行数表示在初始加载后待应用于新表的更改量。

    您可以检查 pg_stat_all_tables 中的 pg_repack's 日志表以监控应用于新表的更改。pg_stat_all_tables.n_live_tup 表示待应用于新表的记录数。有关更多信息,请参阅 pg_stat_all_tables

    postgres=>SELECT relname,n_live_tup FROM pg_stat_all_tables WHERE schemaname = 'repack' AND relname ILIKE '%log%'; -[ RECORD 1 ]--------- relname | log_16490 n_live_tup | 2000000
  • 您可以使用 pg_stat_statements 扩展来找出重新打包操作中每个步骤所花费的时间。这有助于准备在生产环境中应用相同的重新打包操作。您可以调整 LIMIT 子句以进一步扩展输出。

    postgres=>SELECT SUBSTR(query, 1, 100) query, round((round(total_exec_time::numeric, 6) / 1000 / 60),4) total_exec_time_in_minutes FROM pg_stat_statements WHERE query ILIKE '%repack%' ORDER BY total_exec_time DESC LIMIT 5; query | total_exec_time_in_minutes -----------------------------------------------------------------------+---------------------------- CREATE UNIQUE INDEX index_16493 ON repack.table_16490 USING btree (a) | 6.8627 INSERT INTO repack.table_16490 SELECT a FROM ONLY public.t1 | 6.4150 SELECT repack.repack_apply($1, $2, $3, $4, $5, $6) | 0.5395 SELECT repack.repack_drop($1, $2) | 0.0004 SELECT repack.repack_swap($1) | 0.0004 (5 rows)

重新打包完全是一项不合时宜的操作,因此原始表不会受到影响,我们预计不会出现任何需要恢复原始表的意外挑战。如果重新打包意外失败,则必须检查错误的原因并加以解决。

问题解决后,在表所在的数据库中删除并重新创建 pg_repack 扩展,然后重试 pg_repack 步骤。此外,计算资源的可用性和表的并行可访问性在及时完成重新打包操作方面起着至关重要的作用。

升级和使用 PLV8 扩展

PLV8 是适用于 PostgreSQL 的可信 Javascript 语言扩展。您可以将其用于存储过程、触发条件和其他可从 SQL 调用的过程代码。PostgreSQL 的所有当前版本都支持此语言扩展。

如果使用 PLV8 并将 PostgreSQL 升级到新的 PLV8 版本,您可以立即利用新扩展。按下列步骤将目录元数据与新版本的 PLV8 进行同步。这些步骤是可选的,但我们强烈建议您完成这些步骤以避免元数据错配警告。

升级过程会丢弃所有现有的 PLV8 功能。因此,建议您在升级之前创建 RDS for PostgreSQL 数据库实例的快照。有关更多信息,请参阅为单可用区数据库实例创建数据库快照

将目录元数据与新版本的 PLV8 同步
  1. 确认您需要更新。为此,请在连接到实例的情况下运行以下命令。

    SELECT * FROM pg_available_extensions WHERE name IN ('plv8','plls','plcoffee');

    如果您的结果包含的已安装版本的值低于默认版本,请继续该过程以更新扩展。例如,以下结果集指示您应更新。

    name | default_version | installed_version | comment --------+-----------------+-------------------+-------------------------------------------------- plls | 2.1.0 | 1.5.3 | PL/LiveScript (v8) trusted procedural language plcoffee| 2.1.0 | 1.5.3 | PL/CoffeeScript (v8) trusted procedural language plv8 | 2.1.0 | 1.5.3 | PL/JavaScript (v8) trusted procedural language (3 rows)
  2. 如果尚未创建 RDS for PostgreSQL 数据库实例的快照,请先创建一个。创建快照时,您可以继续下列步骤。

  3. 获取数据库实例中 PLV8 函数的数量,以便在升级后验证是否遗漏函数。例如,以下 SQL 查询会返回用 plv8、plcoffee 和 plls 编写的函数数量。

    SELECT proname, nspname, lanname FROM pg_proc p, pg_language l, pg_namespace n WHERE p.prolang = l.oid AND n.oid = p.pronamespace AND lanname IN ('plv8','plcoffee','plls');
  4. 使用 pg_dump 创建仅架构转储文件。例如,在客户端计算机上的 /tmp 目录中创建文件。

    ./pg_dump -Fc --schema-only -U master postgres >/tmp/test.dmp

    该示例使用以下选项:

    • -Fc - 自定义格式

    • --仅架构 - 仅转储创建架构所需的命令(在本例中为功能)

    • -U - RDS 主用户名

    • database - 数据库实例的数据库名称

    有关 pg_dump 的更多信息,请参阅 PostgreSQL 文档中的 pg_dump

  5. 提取位于转储文件中的“CREATE FUNCTION”DDL 语句。以下示例使用 grep 命令提取创建函数的 DDL 语句并将函数保存到文件中。将在后续步骤中使用该语句重新创建函数。

    ./pg_restore -l /tmp/test.dmp | grep FUNCTION > /tmp/function_list/

    有关 pg_restore 的更多信息,请参阅 PostgreSQL 文档中的 pg_restore

  6. 删掉函数和扩展。以下示例将删除基于 PLV8 的任何对象。级联选项确保删除任何依赖项。

    DROP EXTENSION plv8 CASCADE;

    如果您的 PostgreSQL 实例包含基于 plcoffee 或 plls 的对象,请对这些扩展重复此步骤。

  7. 创建扩展。以下示例将创建 plv8、plcoffee 和 plls 扩展。

    CREATE EXTENSION plv8; CREATE EXTENSION plcoffee; CREATE EXTENSION plls;
  8. 使用转储文件和“驱动程序”文件创建函数。

    以下示例将重新创建之前提取的函数。

    ./pg_restore -U master -d postgres -Fc -L /tmp/function_list /tmp/test.dmp
  9. 使用以下查询验证是否已重新创建所有函数。

    SELECT * FROM pg_available_extensions WHERE name IN ('plv8','plls','plcoffee');

    PLV8 版本 2 在结果集中添加以下额外行:

    proname | nspname | lanname ---------------+------------+---------- plv8_version | pg_catalog | plv8

使用 PL/Rust 通过 Rust 语言编写 PostgreSQL 函数

PL/Rust 是 PostgreSQL 的可信 Rust 语言扩展。您可以将其用于存储过程、函数和其他可从 SQL 调用的过程代码。PL/Rust 语言扩展有以下版本可用:

  • RDS for PostgreSQL 16.1 及更高的 16 版本

  • RDS for PostgreSQL 15.2-R2 和更高的 15 版本

  • RDS for PostgreSQL 14.9 和更高的 14 版本

  • RDS for PostgreSQL 13.12 和更高的 13 版本

有关更多信息,请参阅 GitHub 上的 PL/Rust

设置 PL/Rust

要在数据库实例上安装 plrust 扩展,请将 plrust 添加到与数据库实例相关联的数据库参数组中的 shared_preload_libraries 参数。安装了 plrust 扩展后,您可以创建函数。

要修改 shared_preload_libraries 参数,数据库实例必须与自定义参数组相关联。有关创建自定义数据库参数组的信息,请参阅使用参数组

您可以使用 AWS Management Console或 AWS CLI 安装 plrust 扩展程序。

以下步骤假设您的数据库实例与自定义数据库参数组相关联。

shared_preload_libraries 参数中安装 plrust 扩展

使用作为 rds_superuser 组(角色)成员的账户完成以下步骤。

  1. 登录 AWS Management Console 并通过以下网址打开 Amazon RDS 控制台:https://console.aws.amazon.com/rds/

  2. 在导航窗格中,选择 Databases(数据库)

  3. 选择数据库实例的名称以显示其详细信息。

  4. 打开数据库实例的配置选项卡,并找到数据库实例参数组链接。

  5. 选择此链接以打开与您的数据库实例关联的自定义参数。

  6. Parameters(参数)搜索字段中,键入 shared_pre 以查找 shared_preload_libraries 参数。

  7. 选择 Edit parameters(编辑参数)以访问属性值。

  8. 将 plrust 添加到字段的列表中。使用逗号分隔值列表中的项目。

  9. 重启数据库实例以使对 shared_preload_libraries 参数的更改生效。初次重启可能需要额外的时间才能完成。

  10. 当实例可用时,验证 plrust 是否已初始化。使用 psql 连接到数据库实例,然后并运行以下命令。

    SHOW shared_preload_libraries;

    您的输出应类似于以下内容:

    shared_preload_libraries -------------------------- rdsutils,plrust (1 row)
在 shared_preload_libraries 参数中安装 plrust 扩展

使用作为 rds_superuser 组(角色)成员的账户完成以下步骤。

  1. 使用 modify-db-parameter-group AWS CLI 命令将 plrust 添加到 shared_preload_libraries 参数。

    aws rds modify-db-parameter-group \ --db-parameter-group-name custom-param-group-name \ --parameters "ParameterName=shared_preload_libraries,ParameterValue=plrust,ApplyMethod=pending-reboot" \ --region aws-region
  2. 使用 reboot-db-instance AWS CLI 命令重启数据库实例并初始化 plrust 库。初次重启可能需要额外的时间才能完成。

    aws rds reboot-db-instance \ --db-instance-identifier your-instance \ --region aws-region
  3. 当实例可用时,您可以验证 plrust 已初始化。使用 psql 连接到数据库实例,然后并运行以下命令。

    SHOW shared_preload_libraries;

    您的输出应类似于以下内容:

    shared_preload_libraries -------------------------- rdsutils,plrust (1 row)

使用 PL/Rust 创建函数

PL/Rust 会将函数编译为动态库、加载动态库并执行该库。

以下 Rust 函数从数组中筛选出倍数。

postgres=> CREATE LANGUAGE plrust; CREATE EXTENSION
CREATE OR REPLACE FUNCTION filter_multiples(a BIGINT[], multiple BIGINT) RETURNS BIGINT[] IMMUTABLE STRICT LANGUAGE PLRUST AS $$ Ok(Some(a.into_iter().filter(|x| x.unwrap() % multiple != 0).collect())) $$; WITH gen_values AS ( SELECT ARRAY(SELECT * FROM generate_series(1,100)) as arr) SELECT filter_multiples(arr, 3) from gen_values;

将 crate 与 PL/Rust 结合使用

从 Amazon RDS for PostgreSQL 版本 15.4、14.9 和 13.12 开始,PL/Rust 支持以下 crate:

  • aes

  • ctr

  • rand

从 RDS for PostgreSQL 版本 15.5-R2、14.10-R2 和 13.13-R2 开始,PL/Rust 支持另外两个 crate:

  • croaring-rs

  • num-bigint

这些 crate 仅支持默认特征。新的 RDS for PostgreSQL 版本可能包含更新版本的 crate,并且可能不再支持旧版本的 crate。

按照执行主要版本升级的最佳实践来测试您的 PL/Rust 函数是否与新的主要版本兼容。有关更多信息,请参阅博客将 Amazon RDS 升级到 PostgreSQL 主要和次要版本的最佳实践,以及《Amazon RDS 用户指南》中的为 Amazon RDS 升级 PostgreSQL 数据库引擎

有关在创建 PL/Rust 函数时使用依赖关系的示例,请访问使用依赖关系

PL/Rust 限制

默认情况下,数据库用户无法使用 PL/Rust。要提供对 PL/Rust 的访问权限,请以具有 rds_superuser 权限的用户身份进行连接,然后运行以下命令:

postgres=> GRANT USAGE ON LANGUAGE PLRUST TO user;