다음을 통해 공유

特定于 Azure Database for PostgreSQL 灵活服务器的扩展注意事项

  • 아티클

本文介绍在 Azure Database for PostgreSQL 灵活服务器实例中使用某些扩展时必须注意的一些特殊注意事项。

先决条件

阅读文章如何使用 Azure Database for PostgreSQL 的 PostgreSQL 扩展，了解如何：

  • 将 Azure Database for PostgreSQL 灵活服务器中的扩展加入允许列表
  • 加载部署二进制库的扩展库，这些库需要分配和访问共享内存，并且需要在服务器启动时加载。
  • 在某个数据库中安装扩展，以便将打包在该扩展中的 SQL 对象部署在该数据库中，并且可在其上下文中访问。
  • 从某个数据库中删除扩展，以便将打包在该扩展中的 SQL 对象从该数据库中删除。
  • 更新已安装的扩展部署的 SQL 项目。
  • 查看已安装的扩展及其相应的版本。
  • 了解在 Azure Database for PostgreSQL 灵活服务器中管理扩展时可能收到哪些错误，以及每个错误的原因。

扩展

以下列表枚举了在 Azure Database for PostgreSQL 灵活服务器服务中使用时需要特别注意的所有受支持的扩展：

  • dblink
  • pg_buffercache
  • pg_cron
  • pg_hint_plan
  • pg_prewarm
  • pg_repack
  • pg_stat_statements
  • postgres_fdw
  • pgstattuple

dblink 扩展允许你从一个 Azure Database for PostgreSQL 灵活服务器实例连接到另一个服务器，或者连接到同一服务器中的另一个数据库。 Azure Database for PostgreSQL 灵活服务器支持任何 PostgreSQL 服务器的传入和传出连接。 发送服务器需要允许到接收服务器的出站连接。 同样，接收服务器需要允许来自发送服务器的连接。

如果你计划使用此扩展，建议使用虚拟网络集成部署服务器。 默认情况下，虚拟网络集成支持在虚拟网络中的服务器之间建立连接。 还可以选择使用虚拟网络网络安全组来自定义访问权限。

pg_buffercache

pg_buffercache 扩展可用于研究 shared_buffers 的内容。 使用此扩展，可以判断特定关系是否已缓存（在 shared_buffers 中）。 此扩展可帮助解决性能问题（缓存相关的性能问题）。

此扩展与 PostgreSQL 的核心安装集成，易于安装。

CREATE EXTENSION pg_buffercache;

pg_cron

pg_cron 扩展是一个简单的、基于 cron 的 PostgreSQL 作业计划程序，作为扩展在数据库内运行。 pg_cron 扩展可在 PostgreSQL 数据库中运行计划性维护任务。 例如，可以定期运行表清空作业或移除旧的数据作业。

pg_cron 扩展可以并行运行多个作业，但是一次最多只能运行一个作业实例。 如果第二次运行应在第一次运行完成之前开始，则第二次运行将排队，并在第一次运行完成后立即开始。 这样，可确保作业完全按计划的次数运行，并且不会与自己并发运行。

确保为 shared_preload_libraries 设置的值包含 pg_cron。 此扩展不支持执行 CREATE EXTENSION 后加载库。 如果扩展未添加到 shared_preload_libraries，或者添加扩展后未重启服务器，则任何尝试运行 CREATE EXTENSION 的行为都会导致错误，其文本显示 pg_cron can only be loaded via shared_preload_libraries，其提示是 Add pg_cron to the shared_preload_libraries configuration variable in postgresql.conf。

若要使用 pg_cron，请确保其库已添加以便在服务器启动时加载，已加入允许列表，并且已安装在想要使用它创建的 SQL 项目与其功能进行交互的任何数据库中。

示例

  1. 在星期六凌晨 3:30 (GMT) 删除旧数据。

    SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

  2. 每天上午 10:00 (GMT) 在默认数据库 postgres 中运行清空作业。

    SELECT cron.schedule('0 10 * * *', 'VACUUM');

  3. 取消 pg_cron 中计划的所有任务。

    SELECT cron.unschedule(jobid) FROM cron.job;

  4. 查看当前使用 pg_cron 计划的所有作业。

    SELECT * FROM cron.job;

  5. 每天上午 10:00 (GMT) 在 azure_pg_admin 角色帐户下的数据库 test cron 中运行清空作业。

    SELECT cron.schedule_in_database('VACUUM',' 0 10 * * * ', 'VACUUM', 'testcron',null,TRUE);

更多示例

从 pg_cron 版本 1.4 开始，可以使用 cron.schedule_in_database 和 cron.alter_job 函数在特定数据库中安排作业并分别更新现有计划。

cron_schedule_in_database 函数允许将用户名作为可选参数。 将用户名设置为非 null 值需要 PostgreSQL 超级用户特权，并且在 Azure Database for PostgreSQL - 灵活服务器中不受支持。 前面的示例显示了运行此函数（可选用户名参数被忽略或设置为 null），该参数在用户计划作业的上下文中运行作业，该作业应具有 azure_pg_admin 角色特权。

  1. 在星期六凌晨 3:30 (GMT) 删除数据库 DBName 的旧数据。

    SELECT cron.schedule_in_database('JobName', '30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$,'DBName');

  2. 更新或更改现有计划的数据库名称

    SELECT cron.alter_job(job_id:=MyJobID,database:='NewDBName');

pg_hint_plan

pg_hint_plan 扩展使得在 SQL 注释中使用所谓的“提示”微调 PostgreSQL 执行计划成为可能，例如：

/*+ SeqScan(a) */

pg_hint_plan 扩展读取随目标 SQL 语句提供的特殊形式的注释中的提示短语。 具体形式以字符序列“/*+”开头，以“*/”结尾。 提示短语由提示名称和以下参数组成，用括号括起来，用空格分隔。 每个提示短语可以由新行分隔，以便于阅读。

示例：

/*+
 HashJoin(a b)
 SeqScan(a)
 */
    SELECT *
    FROM pgbench_branches b
    JOIN pgbench_accounts an ON b.bid = a.bid
    ORDER BY a.aid;

上面的示例会导致计划工具将表 a 上 seqscan 的结果与表 b 组合为 hashjoin。

若要使用 pg_hint_plan 扩展，请确保将扩展加入允许列表中、加载其库，并且在计划使用其功能的数据库中安装扩展。

pg_prewarm

pg_prewarm 扩展可将关系数据加载到缓存中。 预热缓存意味着查询在重启后第一次运行时响应时间更短。 PostgreSQL 灵活服务器的自动预热功能当前在 Azure 数据库中不可用。

pg_repack

首次使用 pg_repack 扩展的用户通常会提出以下问题：pg_repack 是扩展还是像 psql 或 pg_dump 这样的客户端可执行文件？

pg_repack 实际上两者都是。 pg_repack/lib 具有扩展的代码，包括它创建的架构和 SQL 项目，以及实现其中几个函数的代码的 C 库。

另一方面，pg_repack/bin 具有客户端应用程序的代码，该应用程序知道如何与扩展中实现的可编程性元素进行交互。 此客户端应用程序旨在减轻与服务器端扩展所呈现的不同界面进行交互的复杂性。 它为用户提供了一些更易于理解的命令行选项。 如果不在客户端应用程序的目标数据库上创建该扩展，则客户端应用程序没有用处。 服务器端扩展本身功能齐全，但需要用户理解复杂的交互模式。 该模式将包括执行查询以检索数据（这些数据被用作该扩展实现的函数的输入）等。

架构重新打包权限被拒绝

目前，因为我们向此扩展创建的重新打包模式授予权限，仅支持从 azure_pg_admin 的上下文运行 pg_repack 功能。

你可能会注意到，如果表的所有者（不是 azure_pg_admin）尝试运行 pg_repack，则最终会收到如下所示的错误：

NOTICE: Setting up workers.conns
ERROR: pg_repack failed with error: ERROR:  permission denied for schema repack
LINE 1: select repack.version(), repack.version_sql()

若要避免该错误，请从 azure_pg_admin 的上下文运行 pg_repack。

pg_stat_statements

pg_stat_statements 扩展提供在数据库上运行的所有查询的视图。 这对于了解生产系统上的查询工作负载性能非常有用。

pg_stat_statements 扩展已预加载到每个 Azure Database for PostgreSQL 灵活服务器实例上的 shared_preload_libraries 中，以便为你提供跟踪 SQL 语句执行统计信息的方法。

但是，出于安全考虑，仍必须允许列出 pg_stat_statements 扩展并使用 CREATE EXTENSION 命令安装它。

设置 pg_stat_statements.track 可控制哪些语句由扩展跟踪，默认为 top，这意味着跟踪所有由客户端直接发布的语句。 另外两个跟踪级别为 none 和 all。 此设置可作为服务器参数配置。

在记录每个 SQL 语句时，pg_stat_statements 扩展提供的查询执行信息与服务器性能之间存在权衡。 如果不经常使用 pg_stat_statements 扩展，建议将 pg_stat_statements.track 设置为 none。 某些第三方监视服务可能依赖 pg_stat_statements 来提供查询性能见解，因此，请确认这是否适合你。

postgres_fdw

postgres_fdw 扩展允许你从一个 Azure Database for PostgreSQL 灵活服务器实例连接到另一个服务器，或者连接到同一服务器中的另一个数据库。 Azure Database for PostgreSQL 灵活服务器支持任何 PostgreSQL 服务器的传入和传出连接。 发送服务器需要允许到接收服务器的出站连接。 同样，接收服务器需要允许来自发送服务器的连接。

如果你计划使用此扩展，建议使用虚拟网络集成部署服务器。 默认情况下，虚拟网络集成支持在虚拟网络中的服务器之间建立连接。 还可以选择使用虚拟网络网络安全组来自定义访问权限。

pgstattuple

使用 pgstattuple 扩展尝试从 Postgres 11 至 13 版本中保留的 pg_toast 架构中的对象获取元组统计信息时，将收到“架构 pg_toast 权限被拒绝”错误。

架构 pg_toast 权限被拒绝

在适用于灵活服务器的 Azure Database 上使用 PostgreSQL 版本 11 到 13 的客户不能对 pg_toast 架构中的对象使用 pgstattuple 扩展。

在 PostgreSQL 16 中，pg_read_all_data 角色会自动授予 azure_pg_admin，从而允许 pgstattuple 正常运行。 在 PostgreSQL 14 和 15 中，客户可以手动授予 azure_pg_admin pg_read_all_data 角色以实现相同的结果。 但是，在 PostgreSQL 11 到 13 中，pg_read_all_data 角色不存在。

客户无法直接授予所需的权限。 如果需要能够运行 pgstattuple 以访问 pg_toast 架构下的对象，请继续创建 Azure 支持请求。

timescaleDB

timescaleDB 扩展是一个时序数据库，已作为 PostgreSQL 的扩展打包。 它提供以时间为导向的分析功能、优化，并根据时序工作负载来缩放 Postgres。 详细了解 TimescaleDB，它是 Timescale Inc. 的注册商标。Azure Database for PostgreSQL 灵活服务器提供 TimescaleDB Apache-2 版本。

安装 TimescaleDB

若要使用 timescaleDB，请确保将扩展加入允许列表中、加载其库，并且在计划使用其功能的数据库中安装扩展。

现在可以从头开始创建 TimescaleDB hypertable，也可以迁移 PostgreSQL 中的现有时序数据。

使用 pg_dump 和 pg_restore 还原 Timescale 数据库

若要使用 pg_dump 和 pg_restore 还原 Timescale 数据库，必须在目标数据库中运行两个帮助程序过程：timescaledb_pre_restore() 和 timescaledb_post restore()。

首先，准备目标数据库：

--create the new database where you want to perform the restore
CREATE DATABASE tutorial;
\c tutorial --connect to the database
CREATE EXTENSION timescaledb;

SELECT timescaledb_pre_restore();

现在，你可在原始数据库上运行 pg_dump，然后执行 pg_restore。 还原后，请确保在还原的数据库中运行以下命令：

SELECT timescaledb_post_restore();

有关已启用时间刻度的数据库的还原方法的更多信息，请参阅时间刻度文档。

使用 timescaledb-backup 还原 Timescale 数据库

运行 SELECT timescaledb_post_restore() 过程时，在更新 timescaledb.restoring 标记时可能会出现权限被拒绝错误。 这是因为 Cloud PaaS 数据库服务中的 ALTER DATABASE 权限有限。 在这种情况下，可以使用 timescaledb-backup 工具来备份和还原 Timescale 数据库，以执行替代方法。 Timescaledb-backup 是一种使转储和还原 TimescaleDB 数据库更简单、更不容易出错且性能更高的程序。

为此，请按照以下步骤操作：

  1. 安装工具，详细信息见此处。

  2. 创建目标 Azure Database for PostgreSQL 灵活服务器实例和数据库。

  3. 启用 Timescale 扩展。

  4. 向 ts-restore 使用的用户授予 azure_pg_admin 角色。

  5. 运行 ts-restore 以还原数据库。

有关这些实用工具的更多详细信息，请参阅此处。

扩展和主版本升级

Azure Database for PostgreSQL 灵活服务器提供就地主版本升级功能，只需用户进行简单交互即可对 Azure Database for PostgreSQL 灵活服务器实例执行就地升级。 主版本就地升级简化了 Azure Database for PostgreSQL 灵活服务器升级过程，这最大限度地减少了访问服务器的用户和应用程序遭遇中断的次数。 主版本就地升级不支持特定扩展，并且在升级某些扩展时存在一些限制。

使用主版本就地更新功能时，所有 Azure Database for PostgreSQL 灵活服务器版本都不支持 anon、dblink、orafce、pgaudit、postgres_fdw 和 timescaledb 扩展。

相关内容