Compartir a través de

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

  • Artículo

若要检查哪些版本的扩展可用于当前数据库安装，请查询 pg_available_extensions 系统目录视图。

例如，若要确定可用于 Anon 扩展的版本，请执行：

SELECT * FROM pg_available_extensions WHERE name = 'anon';

这些命令可用于对数据库的扩展配置进行必要的了解，有助于高效且安全地维护系统。 由于可以轻松地更新到最新扩展版本，因此 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 扩展可以并行运行多个作业，但是一次最多只能运行一个作业实例。 如果第二次运行应在第一次运行完成之前开始，则第二次运行将排队，并在第一次运行完成后立即开始。 这样，可以确保作业完全按计划的次数运行，并且不会与自己并发运行。

在 Postgres 数据库内的每个 Azure Database for PostgreSQL 灵活服务器的 shared_preload_libraries 中预加载了 pg_cron 扩展，你可以使用它将作业计划为在 Azure Database for PostgreSQL 灵活服务器 DB 实例内的其他数据库中运行，而不会影响安全性。

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

示例

  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。

如如何使用 PostgreSQL 扩展中所示，若要安装 pg_hint_plan，除了将其添加到允许列表之外，还需将其包括在服务器的共享预加载库中。 更改 Postgres 的 shared_preload_libraries 参数需要服务器重启才能生效。 可以使用 Azure 门户或 Azure CLI 更改参数。

使用 Azure 门户：

  1. 选择 Azure Database for PostgreSQL 灵活服务器实例。

  2. 从资源菜单的“设置”部分下，选择“服务器参数”。

  3. 搜索 shared_preload_libraries 参数并编辑其值以包括 pg_hint_plan。

  4. 选择“保存”来保留所做的更改。 现在可以选择“保存并重启”选项。

    1. 选择此选项可确保更改生效，因为修改 shared_preload_libraries 需要重启服务器。

你现在可以在 Azure Database for PostgreSQL 灵活服务器数据库中启用 pg_hint_plan。 连接到数据库并发出以下命令：

CREATE EXTENSION pg_hint_plan;

pg_prewarm

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

pg_repack

当他们首次尝试使用此扩展时，他们会问一个典型的问题：pg_repack 是一个扩展还是像 psql 或 pg_dump 这样的客户端可执行文件？

答案是它两者都是。 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 扩展并将其添加到允许列表之外，还需将其包括在服务器的共享预加载库中。 更改 Postgres 的 shared_preload_libraries 参数需要服务器重启才能生效。 可以使用 Azure 门户或 Azure CLI 更改参数。

使用 Azure 门户：

  1. 选择 Azure Database for PostgreSQL 灵活服务器实例。

  2. 从资源菜单的“设置”部分下，选择“服务器参数”。

  3. 搜索 shared_preload_libraries 参数并编辑其值以包括 TimescaleDB。

  4. 选择“保存”来保留所做的更改。 现在可以选择“保存并重启”选项。 选择此选项可确保更改生效，因为修改 shared_preload_libraries 需要重启服务器。

你现在可以在 Azure Database for PostgreSQL 灵活服务器数据库中启用 TimescaleDB。 连接到数据库并发出以下命令：

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

提示

如果看到错误，请确认是否已在保存 shared_preload_libraries 后重启服务器。

现在可以从头开始创建 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 扩展。

相关内容