Azure Database for PostgreSQL 灵活服务器中的 PostgreSQL 扩展
适用于:Azure Database for PostgreSQL 灵活服务器
Azure Database for PostgreSQL 灵活服务器提供了使用扩展来扩展数据库功能的能力。 扩展在单个包中捆绑多个相关 SQL 对象,可以使用命令在数据库中加载或删除该包。 将包加载到数据库中后,扩展会如同内置功能一样运行。
如何使用 PostgreSQL 扩展
在 Azure Database for PostgreSQL 灵活服务器中安装扩展之前,需要将这些扩展添加到允许列表以供使用。
使用 Azure 门户:
使用 Azure CLI:
可以通过 CLI 参数集命令将扩展添加到允许列表。
az postgres flexible-server parameter set --resource-group <your resource group> --server-name <your server name> --subscription <your subscription id> --name azure.extensions --value <extension name>,<extension name>
使用 ARM 模板:下面显示的示例将服务器 mypostgreserver 上的扩展 dblink、dict_xsyn 和 pg_buffercache 添加到允许列表
{
"$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"flexibleServers_name": {
"defaultValue": "mypostgreserver",
"type": "String"
},
"azure_extensions_set_value": {
"defaultValue": " dblink,dict_xsyn,pg_buffercache",
"type": "String"
}
},
"variables": {},
"resources": [
{
"type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
"apiVersion": "2021-06-01",
"name": "[concat(parameters('flexibleServers_name'), '/azure.extensions')]",
"properties": {
"value": "[parameters('azure_extensions_set_value')]",
"source": "user-override"
}
}
]
}
shared_preload_libraries
是一个服务器配置参数,用于确定 Azure Database for PostgreSQL 灵活服务器启动时要加载哪些库。 必须通过此参数加载使用共享内存的所有库。 如果需要将扩展添加到共享预加载库,则可以执行以下操作:
使用 Azure 门户:
使用 Azure CLI:
可以通过 CLI 参数集命令设置 shared_preload_libraries
。
az postgres flexible-server parameter set --resource-group <your resource group> --server-name <your server name> --subscription <your subscription id> --name shared_preload_libraries --value <extension name>,<extension name>
将扩展添加到允许列表并加载后,必须先在数据库中安装这些扩展,然后才能使用它们。 若要安装特定扩展,应运行 CREATE EXTENSION 命令。 此命令将打包的对象加载到数据库中。
注意
Azure Database for PostgreSQL 灵活服务器中提供的第三方扩展是开源许可代码。 目前,我们不提供任何具有高级或专有许可模型的第三方扩展或扩展版本。
Azure Database for PostgreSQL 灵活服务器实例支持下面列出的一部分重要 PostgreSQL 扩展。 还可以通过运行 SHOW azure.extensions;
获取此信息。 Azure Database for PostgreSQL 灵活服务器不支持本文档中未列出的扩展。 不能在 Azure Database for PostgreSQL 灵活服务器中创建或加载自己的扩展。
扩展版本
以下扩展在 Azure Database for PostgreSQL 灵活服务器中提供:
扩展名称 | 描述 | PostgreSQL 16 | PostgreSQL 15 | PostgreSQL 14 | PostgreSQL 13 | PostgreSQL 12 | PostgreSQL 11 |
---|---|---|---|---|---|---|---|
address_standardizer | 用于将地址分析成构成元素。 通常用于支持地理编码地址规范化步骤。 | 3.3.3 | 3.3.1 | 3.2.3 | 3.2.3 | 3.2.3 | 3.2.3 |
address_standardizer_data_us | Address Standardizer US 数据集示例 | 3.3.3 | 3.3.1 | 3.2.3 | 3.2.3 | 3.2.3 | 3.2.3 |
amcheck | 用于验证关系完整性的函数 | 1.3 | 1.3 | 1.3 | 1.2 | 1.2 | 1.1 |
anon(预览版) | 数据匿名化工具 | 1.2.0 | 1.2.0 | 1.2.0 | 1.2.0 | 1.2.0 | 1.2.0 |
bloom | Bloom 访问方法 - 基于签名文件的索引 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 |
btree_gin | 支持在 GIN 中为通用数据类型编制索引 | 1.3 | 1.3 | 1.3 | 1.3 | 1.3 | 1.3 |
btree_gist | 支持在 GiST 中为通用数据类型编制索引 | 1.7 | 1.7 | 1.6 | 1.5 | 1.5 | 1.5 |
citext | 不区分大小写的字符串的数据类型 | 1.6 | 1.6 | 1.6 | 1.6 | 1.6 | 1.5 |
cube | 用于多维数据集的数据类型 | 1.5 | 1.5 | 1.5 | 1.4 | 1.4 | 1.4 |
dblink | 从数据库中连接到其他 PostgreSQL 数据库 | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 |
dict_int | 用于整数的文本搜索字典模板 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 |
dict_xsyn | 用于扩展同义词处理的文本搜索字典模板 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 |
earthdistance | 计算地球表面上的大圆距离 | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 |
fuzzystrmatch | 确定字符串间的相似性和差异 | 1.2 | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 |
hstore | 用于存储(键/值)对集的数据类型 | 1.8 | 1.8 | 1.8 | 1.7 | 1.6 | 1.5 |
hypopg | 用于 PostgreSQL 的假设索引 | 1.4.0 | 1.4.0 | 1.4.0 | 1.4.0 | 1.4.0 | 1.4.0 |
intagg | 整数聚合器和枚举器(已过时) | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 |
intarray | 针对 1-D 整数数组的函数、运算符和索引支持 | 1.5 | 1.5 | 1.5 | 1.3 | 1.2 | 1.2 |
isn | 用于国际产品编号标准的数据类型 | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 |
lo | 大型对象维护 | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 |
login_hook | Login_hook - 用于在登录时执行 login_hook.login() 的挂钩 | 1.5 | 1.4 | 1.4 | 1.4 | 1.4 | 1.4 |
ltree | 用于分层树形结构的数据类型 | 1.2 | 1.2 | 1.2 | 1.2 | 1.1 | 1.1 |
orafce | 函数和运算符,用于模拟 Oracle RDBMS 提供的部分函数和包 | 4.4 | 3.24 | 3.18 | 3.18 | 3.18 | 3.7 |
pageinspect | 在较低级别检查数据库页的内容 | 1.12 | 1.11 | 1.9 | 1.8 | 1.7 | 1.7 |
pgaudit | 提供审核功能 | 16.0 | 1.7 | 1.6.2 | 1.5 | 1.4 | 1.3.2 |
pg_buffercache | 检查共享缓冲区缓存 | 1.4 | 1.3 | 1.3 | 1.3 | 1.3 | 1.3 |
pg_cron | PostgreSQL 的作业计划程序 | 1.5 | 1.4-1 | 1.4-1 | 1.4-1 | 1.4-1 | 1.4-1 |
pgcrypto | 加密函数 | 1.3 | 1.3 | 1.3 | 1.3 | 1.3 | 1.3 |
pg_freespacemap | 检查可用空间映射 (FSM) | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 |
pg_hint_plan | 1.6.0 | 1.5 | 1.4 | 1.3.7 | 1.3.7 | 1.3.7 | |
pglogical | PostgreSQL 逻辑复制 | 2.4.4 | 2.4.2 | 2.4.1 | 2.4.1 | 2.4.1 | 2.4.1 |
pg_partman | 一种扩展,用于按时间或 ID 管理已分区表 | 4.7.1 | 4.7.1 | 4.6.1 | 4.5.0 | 4.5.0 | 4.5.0 |
pg_repack | 通过极少量的锁重新组织 PostgreSQL 数据库中的表 | 1.4.7 | 1.4.7 | 1.4.7 | 1.4.7 | 1.4.7 | 1.4.7 |
pgrouting | PgRouting 扩展 | 空值 | 3.5.0 | 3.3.0 | 3.3.0 | 3.3.0 | 3.3.0 |
pgrowlocks | 显示行级别锁定信息 | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 |
pg_squeeze | 用于从关系中移除未使用空间的工具。 | 1.6 | 1.6 | 1.5 | 1.5 | 1.5 | 1.5 |
pg_stat_statements | 跟踪已执行的所有 SQL 语句的规划和执行统计信息 | 1.10 | 1.10 | 1.9 | 1.8 | 1.7 | 1.6 |
pgstattuple | 显示元组级别统计信息 | 1.5 | 1.5 | 1.5 | 1.5 | 1.5 | 1.5 |
pg_trgm | 基于三元匹配的文本相似度度量和索引搜索 | 1.6 | 1.6 | 1.6 | 1.5 | 1.4 | 1.4 |
pg_visibility | 检查可见性映射 (VM) 和页面级别的可见性信息 | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 |
plpgsql | PL/pgSQL 过程语言 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 |
plv8 | PL/JavaScript (v8) 信任的过程语言 | 3.1.7 | 3.1.7 | 3.0.0 | 3.0.0 | 3.0.0 | 3.0.0 |
postgis | PostGIS 几何与地理空间类型和函数 | 3.3.3 | 3.3.1 | 3.2.3 | 3.2.3 | 3.2.3 | 3.2.3 |
postgis_raster) | PostGIS 光栅类型和函数 | 3.3.3 | 3.3.1 | 3.2.3 | 3.2.3 | 3.2.3 | 3.2.3 |
postgis_sfcgal) | PostGIS SFCGAL 函数 | 3.3.3 | 3.3.1 | 3.2.3 | 3.2.3 | 3.2.3 | 3.2.3 |
postgis_tiger_geocoder) | PostGIS tiger 地理编码器和逆向地理编码器 | 3.3.3 | 3.3.1 | 3.2.3 | 3.2.3 | 3.2.3 | 3.2.3 |
postgis_topology | PostGIS 拓扑空间类型和函数 | 3.3.3 | 3.3.1 | 3.2.3 | 3.2.3 | 3.2.3 | 3.2.3 |
postgres_fdw | 外部数据包装器,用于远程 PostgreSQL 服务器 | 1.1 | 1.1 | 1.1 | 1.0 | 1.0 | 1.0 |
semver | 语义版本数据类型 | 0.32.1 | 0.32.0 | 0.32.0 | 0.32.0 | 0.32.0 | 0.32.0 |
session_variable | Session_variable - 会话变量和常量的注册与操作 | 3.3 | 3.3 | 3.3 | 3.3 | 3.3 | 3.3 |
sslinfo | 有关 SSL 证书的信息 | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 |
tablefunc | 可操作整个表(包括交叉表)的函数 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 |
tds_fdw | 用于查询 TDS 数据库(Sybase 或 Microsoft SQL Server)的外部数据包装器 | 2.0.3 | 2.0.3 | 2.0.3 | 2.0.3 | 2.0.3 | 2.0.3 |
timescaledb | 允许对时序数据进行可缩放的插入和复杂查询 | 空值 | 2.10.0 | 2.10.0 | 2.10.0 | 2.10.0 | 1.7.4 |
tsm_system_rows | TABLESAMPLE 方法,接受行数作为限制 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 |
tsm_system_time | TABLESAMPLE 方法,接受以毫秒为时间单位的限制 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 |
unaccent | 移除了重音的文本搜索字典 | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 |
uuid ossp | 生成全局唯一标识符 (UUID) | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 |
vector | 矢量数据类型以及 ivfflat 和 hnsw 访问方法 | 0.6.1 | 0.6.1 | 0.6.1 | 0.6.1 | 0.6.1 | 0.5.1 |
dblink 和 postgres_fdw
dblink 和 postgres_fdw 允许你从一个 Azure Database for PostgreSQL 灵活服务器实例连接到另一个服务器,或者连接到同一服务器中的另一个数据库。 Azure Database for PostgreSQL 灵活服务器支持任何 PostgreSQL 服务器的传入和传出连接。 发送服务器需要允许到接收服务器的出站连接。 同样,接收服务器需要允许来自发送服务器的连接。
如果计划使用这两个扩展,建议使用虚拟网络集成部署服务器。 默认情况下,虚拟网络集成支持在虚拟网络中的服务器之间建立连接。 还可以选择使用虚拟网络网络安全组来自定义访问权限。
pg_prewarm
pg_prewarm 扩展可将关系数据加载到缓存中。 预热缓存意味着查询在重启后第一次运行时响应时间更短。 自动预热功能当前在 Azure Database for PostgreSQL 灵活服务器中不可用。
pg_cron
pg_cron 是一项简单的、基于 cron 的 PostgreSQL 作业计划程序,作为扩展在数据库内运行。 pg_cron 扩展可用于在 PostgreSQL 数据库中运行计划性维护任务。 例如,可以定期运行表清空作业或删除旧的数据作业。
pg_cron
可以并行运行多个作业,但是一次最多只能运行一个作业实例。 如果第二次运行应在第一次运行完成之前开始,则第二次运行将排队,并在第一次运行完成后立即开始。 这样可以确保作业完全按计划的次数运行,并且不会与它们自己并发运行。
下面是一些示例:
在星期六凌晨 3:30 (GMT) 删除旧数据
SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
每天上午 10:00 (GMT) 在默认数据库“postgres”中运行清空作业
SELECT cron.schedule('0 10 * * *', 'VACUUM');
取消 pg_cron 中计划的所有任务
SELECT cron.unschedule(jobid) FROM cron.job;
查看当前使用 pg_cron 计划的所有作业
SELECT * FROM cron.job;
每天上午 10:00 (GMT) 在 azure_pg_admin 角色账户下的数据库“testcron”中运行清空作业
SELECT cron.schedule_in_database('VACUUM','0 10 * * * ','VACUUM','testcron',null,TRUE)
注意
在 postgres 数据库内的每个 Azure Database for PostgreSQL 灵活服务器的 shared_preload_libraries 中预加载了 pg_cron 扩展,你可以使用它将作业计划为在 Azure Database for PostgreSQL 灵活服务器 DB 实例内的其他数据库中运行,而不会影响安全性。 但是,出于安全考虑,仍必须允许列出 pg_cron 扩展并使用 CREATE EXTENSION 命令进行安装。
从 pg_cron 版本 1.4 开始,可以使用 cron.schedule_in_database 和 cron.alter_job 函数在特定数据库中安排作业并分别更新现有计划。
下面是一些示例:
在星期六凌晨 3:30 (GMT) 删除数据库 DBName 的旧数据
SELECT cron.schedule_in_database('JobName', '30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$,'DBName');
注意
cron_schedule_in_database 函数允许将用户名作为可选参数。 将用户名设置为非 null 值需要 PostgreSQL 超级用户特权,并且在 Azure Database for PostgreSQL 灵活服务器中不受支持。 前面的示例显示了运行此函数(可选用户名参数被忽略或设置为 null),该参数在用户计划作业的上下文中运行作业,该作业应具有 azure_pg_admin 角色特权。
更新或更改现有计划的数据库名称
select cron.alter_job(job_id:=MyJobID,database:='NewDBName');
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
。 此设置可作为服务器参数配置。
pg_stat_statements 提供的查询执行信息与记录每个 SQL 语句时对服务器性能的影响之间存在权衡。 如果不经常使用 pg_stat_statements 扩展,则建议将 pg_stat_statements.track
设置为 none
。 某些第三方监视服务可能依赖 pg_stat_statements 来提供查询性能见解,因此,请确认这是否适合你。
TimescaleDB
TimescaleDB 是一个时序数据库,已作为 PostgreSQL 的扩展打包。 TimescaleDB 提供以时间为导向的分析功能、优化,并根据时序工作负荷来缩放 Postgres。 详细了解 TimescaleDB,它是 Timescale Inc. 的注册商标。Azure Database for PostgreSQL 灵活服务器提供 TimescaleDB Apache-2 版本。
安装 TimescaleDB
若要安装 TimescaleDB,除了允许列出它之外(如上所示),还需将其包括在服务器的共享预加载库中。 更改 Postgres 的 shared_preload_libraries
参数需要shared_preload_libraries
才能生效。 可以使用 Azure 门户或 Azure CLI 更改参数。
使用 Azure 门户:
选择 Azure Database for PostgreSQL 灵活服务器实例。
在侧栏中选择“服务器参数”。
搜索
shared_preload_libraries
参数。选择“TimescaleDB” 。
选择“保存” ,保留所做的更改。 保存更改后会获得通知。
获得通知后,请重启服务器以应用这些更改。
你现在可以在 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'll 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 数据库的过程更简单、更不易出错且更高效。
为此,应执行以下操作
- 安装工具,详细信息见此处
- 创建目标 Azure Database for PostgreSQL 灵活服务器实例和数据库
- 按如上所述启用 Timescale 扩展
- 向用户授予将由 ts-restore 使用的 azure_pg_admin 角色
- 运行 ts-restore 还原数据库
有关这些实用工具的更多详细信息,请参阅此处。
注意
使用 timescale-backup
实用程序还原到 Azure 时,由于 Azure Database for PostgresQL 单一服务器的数据库用户名必须使用 <user@db-name>
格式,因此需要将 @
替换为 %40
字符编码。
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 上 seq scan
的结果与表 b 组合起来作为 hash join
。
若要安装 pg_hint_plan,除了允许列出它之外(如上所示),还需将其包括在服务器的共享预加载库中。 更改 Postgres 的 shared_preload_libraries
参数需要shared_preload_libraries
才能生效。 可以使用 Azure 门户或 Azure CLI 更改参数。
使用 Azure 门户:
选择 Azure Database for PostgreSQL 灵活服务器实例。
在侧栏中选择“服务器参数”。
搜索
shared_preload_libraries
参数。选择“pg_hint_plan”。
选择“保存” ,保留所做的更改。 保存更改后会获得通知。
获得通知后,请重启服务器以应用这些更改。
你现在可以在 Azure Database for PostgreSQL 灵活服务器数据库中启用 pg_hint_plan。 连接到数据库并发出以下命令:
CREATE EXTENSION pg_hint_plan ;
pg_buffercache
Pg_buffercache
可用于研究 shared_buffers 的内容。 使用此扩展,可以判断特定关系是否已缓存(在 shared_buffers 中查看)。 此扩展可帮助你解决性能问题(缓存相关的性能问题)
这是 contrib 的一部分,安装此扩展很简单。
CREATE EXTENSION pg_buffercache;
扩展和主版本升级
Azure Database for PostgreSQL 灵活服务器引入了主版本就地升级功能,它让你只需单击一下就可对 Azure Database for PostgreSQL 灵活服务器实例执行就地升级。 主版本就地升级简化了 Azure Database for PostgreSQL 灵活服务器升级过程,这最大限度地减少了访问服务器的用户和应用程序遭遇中断的次数。 主版本就地升级不支持特定扩展,并且在升级某些扩展时存在一些限制。 使用主版本就地更新功能时,所有 Azure Database for PostgreSQL 灵活服务器版本都不支持 Timescaledb、pgaudit、dblink、orafce 和 postgres_fdw 扩展。