Azure Database for PostgreSQL(单一服务器)中的 PostgreSQL 扩展PostgreSQL extensions in Azure Database for PostgreSQL - Single Server

PostgreSQL 支持使用扩展来扩展数据的功能。PostgreSQL provides the ability to extend the functionality of your database using extensions. 扩展在单个包中将多个相关 SQL 对象捆绑在一起,可以使用单个命令在数据库中加载或删除该包。Extensions bundle multiple related SQL objects together in a single package that can be loaded or removed from your database with a single command. 在数据库中加载之后,扩展会如同内置功能一样运行。After being loaded in the database, extensions function like built-in features.

如何使用 PostgreSQL 扩展How to use PostgreSQL extensions

必须先在数据库中安装 PostgreSQL 扩展,然后才能使用它们。PostgreSQL extensions must be installed in your database before you can use them. 若要安装特定扩展,请通过 psql 工具运行  CREATE EXTENSION  命令,将打包的对象加载到数据库中。To install a particular extension, run the CREATE EXTENSION command from psql tool to load the packaged objects into your database.

用于 PostgreSQL 的 Azure 数据库支持下面列出的一部分键扩展。Azure Database for PostgreSQL supports a subset of key extensions as listed below. 还可以通过运行 SELECT * FROM pg_available_extensions; 获取此信息。This information is also available by running SELECT * FROM pg_available_extensions;. 不支持未列出的其他扩展。Extensions beyond the ones listed are not supported. 不能在 Azure Database for PostgreSQL 中创建自己的扩展。You cannot create your own extension in Azure Database for PostgreSQL.

Postgres 11 扩展Postgres 11 extensions

以下扩展在 Azure Database for PostgreSQL 服务器中提供,此类服务器的 Postgres 版本为 11。The following extensions are available in Azure Database for PostgreSQL servers which have Postgres version 11.

扩展名Extension 扩展版本Extension version 说明Description
address_standardizeraddress_standardizer 2.5.12.5.1 用于将地址分析成构成元素。Used to parse an address into constituent elements.
address_standardizer_data_usaddress_standardizer_data_us 2.5.12.5.1 Address Standardizer US 数据集示例Address Standardizer US dataset example
btree_ginbtree_gin 1.31.3 支持在 GIN 中索引常见数据类型support for indexing common datatypes in GIN
btree_gistbtree_gist 1.51.5 支持在 GiST 中索引常见数据类型support for indexing common datatypes in GiST
citextcitext 1.51.5 不区分大小写的字符串的数据类型data type for case-insensitive character strings
cubecube 1.41.4 用于多维数据集的数据类型data type for multidimensional cubes
dblinkdblink 1.21.2 从数据库中连接到其他 PostgreSQL 数据库connect to other PostgreSQL databases from within a database
dict_intdict_int 1.01.0 用于整数的文本搜索字典模板text search dictionary template for integers
earthdistanceearthdistance 1.11.1 计算地球表面上的大圆距离calculate great-circle distances on the surface of the Earth
fuzzystrmatchfuzzystrmatch 1.11.1 确定字符串间的相似性和差异determine similarities and distance between strings
hstorehstore 1.51.5 用于存储(键/值)对集的数据类型data type for storing sets of (key, value) pairs
hypopghypopg 1.1.21.1.2 用于 PostgreSQL 的假设索引Hypothetical indexes for PostgreSQL
intarrayintarray 1.21.2 针对 1-D 整数数组的函数、运算符和索引支持functions, operators, and index support for 1-D arrays of integers
isnisn 1.21.2 用于国际产品编号标准的数据类型data types for international product numbering standards
ltreeltree 1.11.1 用于分层树形结构的数据类型data type for hierarchical tree-like structures
orafceorafce 3.73.7 函数和运算符,用于模拟商业 RDBMS 提供的部分函数和包Functions and operators that emulate a subset of functions and packages from commercial RDBMS
pgauditpgaudit 1.3.11.3.1 提供审核功能provides auditing functionality
pgcryptopgcrypto 1.31.3 加密函数cryptographic functions
pgroutingpgrouting 2.6.22.6.2 pgRouting 扩展pgRouting Extension
pgrowlockspgrowlocks 1.21.2 显示行级锁定信息show row-level locking information
pgstattuplepgstattuple 1.51.5 显示元组级别统计信息show tuple-level statistics
pg_buffercachepg_buffercache 1.31.3 检查共享缓冲区缓存examine the shared buffer cache
pg_partmanpg_partman 4.0.04.0.0 一种扩展,用于按时间或 ID 管理已分区表Extension to manage partitioned tables by time or ID
pg_prewarmpg_prewarm 1.21.2 prewarm 关系数据prewarm relation data
pg_stat_statementspg_stat_statements 1.61.6 跟踪已执行的所有 SQL 语句的执行统计信息track execution statistics of all SQL statements executed
pg_trgmpg_trgm 1.41.4 基于三元匹配的文本相似度度量和索引搜索text similarity measurement and index searching based on trigrams
plpgsqlplpgsql 1.01.0 PL/pgSQL 过程语言PL/pgSQL procedural language
plv8plv8 2.3.112.3.11 PL/JavaScript (v8) 信任的过程语言PL/JavaScript (v8) trusted procedural language
postgispostgis 2.5.12.5.1 PostGIS 几何结构、地理以及光栅空间类型和函数PostGIS geometry, geography, and raster spatial types and functions
postgis_sfcgalpostgis_sfcgal 2.5.12.5.1 PostGIS SFCGAL 函数PostGIS SFCGAL functions
postgis_tiger_geocoderpostgis_tiger_geocoder 2.5.12.5.1 PostGIS tiger 地理编码器和逆向地理编码器PostGIS tiger geocoder and reverse geocoder
postgis_topologypostgis_topology 2.5.12.5.1 PostGIS 拓扑空间类型和函数PostGIS topology spatial types and functions
postgres_fdwpostgres_fdw 1.01.0 外部数据包装器,用于远程 PostgreSQL 服务器foreign-data wrapper for remote PostgreSQL servers
tablefunctablefunc 1.01.0 可操作整个表(包括交叉表)的函数functions that manipulate whole tables, including crosstab
timescaledbtimescaledb 1.3.21.3.2 允许对时序数据进行可缩放的插入和复杂查询Enables scalable inserts and complex queries for time-series data
unaccentunaccent 1.11.1 删除了重音的文本搜索字典text search dictionary that removes accents
uuid osspuuid-ossp 1.11.1 生成全局唯一标识符 (UUID)generate universally unique identifiers (UUIDs)

Postgres 10 扩展Postgres 10 extensions

以下扩展在 Azure Database for PostgreSQL 服务器中提供,此类服务器的 Postgres 版本为 10。The following extensions are available in Azure Database for PostgreSQL servers which have Postgres version 10.

扩展名Extension 扩展版本Extension version 说明Description
address_standardizeraddress_standardizer 2.5.12.5.1 用于将地址分析成构成元素。Used to parse an address into constituent elements.
address_standardizer_data_usaddress_standardizer_data_us 2.5.12.5.1 Address Standardizer US 数据集示例Address Standardizer US dataset example
btree_ginbtree_gin 1.31.3 支持在 GIN 中索引常见数据类型support for indexing common datatypes in GIN
btree_gistbtree_gist 1.51.5 支持在 GiST 中索引常见数据类型support for indexing common datatypes in GiST
chkpasschkpass 1.01.0 用于自动加密密码的数据类型data type for auto-encrypted passwords
citextcitext 1.41.4 不区分大小写的字符串的数据类型data type for case-insensitive character strings
cubecube 1.21.2 用于多维数据集的数据类型data type for multidimensional cubes
dblinkdblink 1.21.2 从数据库中连接到其他 PostgreSQL 数据库connect to other PostgreSQL databases from within a database
dict_intdict_int 1.01.0 用于整数的文本搜索字典模板text search dictionary template for integers
earthdistanceearthdistance 1.11.1 计算地球表面上的大圆距离calculate great-circle distances on the surface of the Earth
fuzzystrmatchfuzzystrmatch 1.11.1 确定字符串间的相似性和差异determine similarities and distance between strings
hstorehstore 1.41.4 用于存储(键/值)对集的数据类型data type for storing sets of (key, value) pairs
hypopghypopg 1.1.11.1.1 用于 PostgreSQL 的假设索引Hypothetical indexes for PostgreSQL
intarrayintarray 1.21.2 针对 1-D 整数数组的函数、运算符和索引支持functions, operators, and index support for 1-D arrays of integers
isnisn 1.11.1 用于国际产品编号标准的数据类型data types for international product numbering standards
ltreeltree 1.11.1 用于分层树形结构的数据类型data type for hierarchical tree-like structures
orafceorafce 3.73.7 函数和运算符,用于模拟商业 RDBMS 提供的部分函数和包Functions and operators that emulate a subset of functions and packages from commercial RDBMS
pgauditpgaudit 1.21.2 提供审核功能provides auditing functionality
pgcryptopgcrypto 1.31.3 加密函数cryptographic functions
pgroutingpgrouting 2.5.22.5.2 pgRouting 扩展pgRouting Extension
pgrowlockspgrowlocks 1.21.2 显示行级锁定信息show row-level locking information
pgstattuplepgstattuple 1.51.5 显示元组级别统计信息show tuple-level statistics
pg_buffercachepg_buffercache 1.31.3 检查共享缓冲区缓存examine the shared buffer cache
pg_partmanpg_partman 2.6.32.6.3 一种扩展,用于按时间或 ID 管理已分区表Extension to manage partitioned tables by time or ID
pg_prewarmpg_prewarm 1.11.1 prewarm 关系数据prewarm relation data
pg_stat_statementspg_stat_statements 1.61.6 跟踪已执行的所有 SQL 语句的执行统计信息track execution statistics of all SQL statements executed
pg_trgmpg_trgm 1.31.3 基于三元匹配的文本相似度度量和索引搜索text similarity measurement and index searching based on trigrams
plpgsqlplpgsql 1.01.0 PL/pgSQL 过程语言PL/pgSQL procedural language
plv8plv8 2.1.02.1.0 PL/JavaScript (v8) 信任的过程语言PL/JavaScript (v8) trusted procedural language
postgispostgis 2.4.32.4.3 PostGIS 几何结构、地理以及光栅空间类型和函数PostGIS geometry, geography, and raster spatial types and functions
postgis_sfcgalpostgis_sfcgal 2.4.32.4.3 PostGIS SFCGAL 函数PostGIS SFCGAL functions
postgis_tiger_geocoderpostgis_tiger_geocoder 2.4.32.4.3 PostGIS tiger 地理编码器和逆向地理编码器PostGIS tiger geocoder and reverse geocoder
postgis_topologypostgis_topology 2.4.32.4.3 PostGIS 拓扑空间类型和函数PostGIS topology spatial types and functions
postgres_fdwpostgres_fdw 1.01.0 外部数据包装器,用于远程 PostgreSQL 服务器foreign-data wrapper for remote PostgreSQL servers
tablefunctablefunc 1.01.0 可操作整个表(包括交叉表)的函数functions that manipulate whole tables, including crosstab
timescaledbtimescaledb 1.1.11.1.1 允许对时序数据进行可缩放的插入和复杂查询Enables scalable inserts and complex queries for time-series data
unaccentunaccent 1.11.1 删除了重音的文本搜索字典text search dictionary that removes accents
uuid osspuuid-ossp 1.11.1 生成全局唯一标识符 (UUID)generate universally unique identifiers (UUIDs)

Postgres 9.6 扩展Postgres 9.6 extensions

以下扩展在 Azure Database for PostgreSQL 服务器中提供,此类服务器的 Postgres 版本为 9.6。The following extensions are available in Azure Database for PostgreSQL servers which have Postgres version 9.6.

扩展名Extension 扩展版本Extension version 说明Description
address_standardizeraddress_standardizer 2.3.22.3.2 用于将地址分析成构成元素。Used to parse an address into constituent elements.
address_standardizer_data_usaddress_standardizer_data_us 2.3.22.3.2 Address Standardizer US 数据集示例Address Standardizer US dataset example
btree_ginbtree_gin 1.01.0 支持在 GIN 中索引常见数据类型support for indexing common datatypes in GIN
btree_gistbtree_gist 1.21.2 支持在 GiST 中索引常见数据类型support for indexing common datatypes in GiST
chkpasschkpass 1.01.0 用于自动加密密码的数据类型data type for auto-encrypted passwords
citextcitext 1.31.3 不区分大小写的字符串的数据类型data type for case-insensitive character strings
cubecube 1.21.2 用于多维数据集的数据类型data type for multidimensional cubes
dblinkdblink 1.21.2 从数据库中连接到其他 PostgreSQL 数据库connect to other PostgreSQL databases from within a database
dict_intdict_int 1.01.0 用于整数的文本搜索字典模板text search dictionary template for integers
earthdistanceearthdistance 1.11.1 计算地球表面上的大圆距离calculate great-circle distances on the surface of the Earth
fuzzystrmatchfuzzystrmatch 1.11.1 确定字符串间的相似性和差异determine similarities and distance between strings
hstorehstore 1.41.4 用于存储(键/值)对集的数据类型data type for storing sets of (key, value) pairs
hypopghypopg 1.1.11.1.1 用于 PostgreSQL 的假设索引Hypothetical indexes for PostgreSQL
intarrayintarray 1.21.2 针对 1-D 整数数组的函数、运算符和索引支持functions, operators, and index support for 1-D arrays of integers
isnisn 1.11.1 用于国际产品编号标准的数据类型data types for international product numbering standards
ltreeltree 1.11.1 用于分层树形结构的数据类型data type for hierarchical tree-like structures
orafceorafce 3.73.7 函数和运算符,用于模拟商业 RDBMS 提供的部分函数和包Functions and operators that emulate a subset of functions and packages from commercial RDBMS
pgauditpgaudit 1.1.21.1.2 提供审核功能provides auditing functionality
pgcryptopgcrypto 1.31.3 加密函数cryptographic functions
pgroutingpgrouting 2.3.22.3.2 pgRouting 扩展pgRouting Extension
pgrowlockspgrowlocks 1.21.2 显示行级锁定信息show row-level locking information
pgstattuplepgstattuple 1.41.4 显示元组级别统计信息show tuple-level statistics
pg_buffercachepg_buffercache 1.21.2 检查共享缓冲区缓存examine the shared buffer cache
pg_partmanpg_partman 2.6.32.6.3 一种扩展,用于按时间或 ID 管理已分区表Extension to manage partitioned tables by time or ID
pg_prewarmpg_prewarm 1.11.1 prewarm 关系数据prewarm relation data
pg_stat_statementspg_stat_statements 1.41.4 跟踪已执行的所有 SQL 语句的执行统计信息track execution statistics of all SQL statements executed
pg_trgmpg_trgm 1.31.3 基于三元匹配的文本相似度度量和索引搜索text similarity measurement and index searching based on trigrams
plpgsqlplpgsql 1.01.0 PL/pgSQL 过程语言PL/pgSQL procedural language
plv8plv8 2.1.02.1.0 PL/JavaScript (v8) 信任的过程语言PL/JavaScript (v8) trusted procedural language
postgispostgis 2.3.22.3.2 PostGIS 几何结构、地理以及光栅空间类型和函数PostGIS geometry, geography, and raster spatial types and functions
postgis_sfcgalpostgis_sfcgal 2.3.22.3.2 PostGIS SFCGAL 函数PostGIS SFCGAL functions
postgis_tiger_geocoderpostgis_tiger_geocoder 2.3.22.3.2 PostGIS tiger 地理编码器和逆向地理编码器PostGIS tiger geocoder and reverse geocoder
postgis_topologypostgis_topology 2.3.22.3.2 PostGIS 拓扑空间类型和函数PostGIS topology spatial types and functions
postgres_fdwpostgres_fdw 1.01.0 外部数据包装器,用于远程 PostgreSQL 服务器foreign-data wrapper for remote PostgreSQL servers
tablefunctablefunc 1.01.0 可操作整个表(包括交叉表)的函数functions that manipulate whole tables, including crosstab
timescaledbtimescaledb 1.1.11.1.1 允许对时序数据进行可缩放的插入和复杂查询Enables scalable inserts and complex queries for time-series data
unaccentunaccent 1.11.1 删除了重音的文本搜索字典text search dictionary that removes accents
uuid osspuuid-ossp 1.11.1 生成全局唯一标识符 (UUID)generate universally unique identifiers (UUIDs)

Postgres 9.5 扩展Postgres 9.5 extensions

以下扩展在 Azure Database for PostgreSQL 服务器中提供,此类服务器的 Postgres 版本为 9.5。The following extensions are available in Azure Database for PostgreSQL servers which have Postgres version 9.5.

扩展名Extension 扩展版本Extension version 说明Description
address_standardizeraddress_standardizer 2.3.02.3.0 用于将地址分析成构成元素。Used to parse an address into constituent elements.
address_standardizer_data_usaddress_standardizer_data_us 2.3.02.3.0 Address Standardizer US 数据集示例Address Standardizer US dataset example
btree_ginbtree_gin 1.01.0 支持在 GIN 中索引常见数据类型support for indexing common datatypes in GIN
btree_gistbtree_gist 1.11.1 支持在 GiST 中索引常见数据类型support for indexing common datatypes in GiST
chkpasschkpass 1.01.0 用于自动加密密码的数据类型data type for auto-encrypted passwords
citextcitext 1.11.1 不区分大小写的字符串的数据类型data type for case-insensitive character strings
cubecube 1.01.0 用于多维数据集的数据类型data type for multidimensional cubes
dblinkdblink 1.11.1 从数据库中连接到其他 PostgreSQL 数据库connect to other PostgreSQL databases from within a database
dict_intdict_int 1.01.0 用于整数的文本搜索字典模板text search dictionary template for integers
earthdistanceearthdistance 1.01.0 计算地球表面上的大圆距离calculate great-circle distances on the surface of the Earth
fuzzystrmatchfuzzystrmatch 1.01.0 确定字符串间的相似性和差异determine similarities and distance between strings
hstorehstore 1.31.3 用于存储(键/值)对集的数据类型data type for storing sets of (key, value) pairs
hypopghypopg 1.1.11.1.1 用于 PostgreSQL 的假设索引Hypothetical indexes for PostgreSQL
intarrayintarray 1.01.0 针对 1-D 整数数组的函数、运算符和索引支持functions, operators, and index support for 1-D arrays of integers
isnisn 1.01.0 用于国际产品编号标准的数据类型data types for international product numbering standards
ltreeltree 1.01.0 用于分层树形结构的数据类型data type for hierarchical tree-like structures
orafceorafce 3.73.7 函数和运算符,用于模拟商业 RDBMS 提供的部分函数和包Functions and operators that emulate a subset of functions and packages from commercial RDBMS
pgauditpgaudit 1.0.71.0.7 提供审核功能provides auditing functionality
pgcryptopgcrypto 1.21.2 加密函数cryptographic functions
pgroutingpgrouting 2.3.02.3.0 pgRouting 扩展pgRouting Extension
pgrowlockspgrowlocks 1.11.1 显示行级锁定信息show row-level locking information
pgstattuplepgstattuple 1.31.3 显示元组级别统计信息show tuple-level statistics
pg_buffercachepg_buffercache 1.11.1 检查共享缓冲区缓存examine the shared buffer cache
pg_partmanpg_partman 2.6.32.6.3 一种扩展,用于按时间或 ID 管理已分区表Extension to manage partitioned tables by time or ID
pg_prewarmpg_prewarm 1.01.0 prewarm 关系数据prewarm relation data
pg_stat_statementspg_stat_statements 1.31.3 跟踪已执行的所有 SQL 语句的执行统计信息track execution statistics of all SQL statements executed
pg_trgmpg_trgm 1.11.1 基于三元匹配的文本相似度度量和索引搜索text similarity measurement and index searching based on trigrams
plpgsqlplpgsql 1.01.0 PL/pgSQL 过程语言PL/pgSQL procedural language
postgispostgis 2.3.02.3.0 PostGIS 几何结构、地理以及光栅空间类型和函数PostGIS geometry, geography, and raster spatial types and functions
postgis_sfcgalpostgis_sfcgal 2.3.02.3.0 PostGIS SFCGAL 函数PostGIS SFCGAL functions
postgis_tiger_geocoderpostgis_tiger_geocoder 2.3.02.3.0 PostGIS tiger 地理编码器和逆向地理编码器PostGIS tiger geocoder and reverse geocoder
postgis_topologypostgis_topology 2.3.02.3.0 PostGIS 拓扑空间类型和函数PostGIS topology spatial types and functions
postgres_fdwpostgres_fdw 1.01.0 外部数据包装器,用于远程 PostgreSQL 服务器foreign-data wrapper for remote PostgreSQL servers
tablefunctablefunc 1.01.0 可操作整个表(包括交叉表)的函数functions that manipulate whole tables, including crosstab
unaccentunaccent 1.01.0 删除了重音的文本搜索字典text search dictionary that removes accents
uuid osspuuid-ossp 1.01.0 生成全局唯一标识符 (UUID)generate universally unique identifiers (UUIDs)

pg_stat_statementspg_stat_statements

pg_stat_statements 扩展已预加载到每个 Azure Database for PostgreSQL 服务器上,以便为你提供跟踪 SQL 语句执行统计信息的方法。The pg_stat_statements extension is preloaded on every Azure Database for PostgreSQL server to provide you a means of tracking execution statistics of SQL statements. 设置 pg_stat_statements.track,它可以控制哪些语句由扩展计数,默认为 top,这意味着跟踪所有由客户端直接发布的语句。The setting pg_stat_statements.track, which controls what statements are counted by the extension, defaults to top, meaning all statements issued directly by clients are tracked. 另外两个跟踪级别为 noneallThe two other tracking levels are none and all. 此设置可通过 Azure 门户Azure CLI 作为服务器参数进行配置。This setting is configurable as a server parameter through the Azure portal or the Azure CLI.

查询执行信息 pg_stat_statements 提供的权限与记录每个 SQL 语句时对服务器性能的影响之间存在权衡。There is a tradeoff between the query execution information pg_stat_statements provides and the impact on server performance as it logs each SQL statement. 如果不经常使用 pg_stat_statements 扩展,则建议将 pg_stat_statements.track 设置为 noneIf you are not actively using the pg_stat_statements extension, we recommend that you set pg_stat_statements.track to none. 请注意,某些第三方监视服务可能依赖 pg_stat_statements 来提供查询性能见解,因此,请确认这是否适合你。Note that some third party monitoring services may rely on pg_stat_statements to deliver query performance insights, so confirm whether this is the case for you or not.

dblinkpostgres_fdw 允许你从一个 PostgreSQL 服务器连接到另一个 PostgreSQL 服务器,或者连接到同一服务器中的另一个数据库。dblink and postgres_fdw allow you to connect from one PostgreSQL server to another, or to another database in the same server. 接收服务器需要允许来自发送服务器的连接通过其防火墙。The receiving server needs to allow connections from the sending server through its firewall. 当使用这些扩展在 Azure Database for PostgreSQL 服务器之间进行连接时,可以通过将“允许访问 Azure 服务”设置为“开启”来实现此目的。When using these extensions to connect between Azure Database for PostgreSQL servers, this can be done by setting "Allow access to Azure services" to ON. 如果希望使用扩展来环回到同一服务器,也需要进行此设置。This is also needed if you want to use the extensions to loop back to the same server. 可以在 Postgres 服务器的 Azure 门户页面中的“连接安全性”下找到“允许访问 Azure 服务”设置。The "Allow access to Azure services" setting can be found in the Azure portal page for the Postgres server, under Connection Security. 开启“允许访问 Azure 服务”会将所有 Azure IP 置于允许列表中。Turning "Allow access to Azure services" ON puts all Azure IPs on the allow list.

目前不支持从 Azure Database for PostgreSQL 进行出站连接,连接到其他 Azure Database for PostgreSQL 服务器的情况除外。Currently, outbound connections from Azure Database for PostgreSQL are not supported, except for connections to other Azure Database for PostgreSQL servers.

uuiduuid

如果计划使用 uuid-ossp 扩展中的 uuid_generate_v4(),请考虑将其与 pgcrypto 扩展中的 gen_random_uuid() 进行比较,以了解性能优势。If you are planning to use uuid_generate_v4() from the uuid-ossp extension, consider comparing with gen_random_uuid() from the pgcrypto extension for performance benefits.

pgAuditpgAudit

pgAudit 扩展提供会话和对象审核日志记录。The pgAudit extension provides session and object audit logging. 若要了解如何在 Azure Database for PostgreSQL 中使用此扩展,请访问“审核概念”一文To learn how to use this extension in Azure Database for PostgreSQL, visit the auditing concepts article.

pg_prewarmpg_prewarm

pg_prewarm 扩展可将关系数据加载到缓存中。The pg_prewarm extension loads relational data into cache. 预热缓存意味着查询在重启后第一次运行时响应时间更短。Prewarming your caches means that your queries have better response times on their first run after a restart. 在 Postgres10 及更低版本中,使用 prewarm 函数手动完成预热。In Postgres 10 and below, prewarming is done manually using the prewarm function.

在 Postgres 11 及更高版本中,可以将预热配置为自动进行。In Postgres 11 and above, you can configure prewarming to happen automatically. 需要在 shared_preload_libraries 参数列表中包含 pg_prewarm,然后重启服务器以应用更改。You need to include pg_prewarm in your shared_preload_libraries parameter's list and restart the server to apply the change. 可以通过 Azure 门户CLI、REST API 或 ARM 模板设置参数。Parameters can be set from the Azure portal, CLI, REST API, or ARM template.

TimescaleDBTimescaleDB

TimescaleDB 是一个时序数据库,已作为 PostgreSQL 的扩展打包。TimescaleDB is a time-series database that is packaged as an extension for PostgreSQL. TimescaleDB 提供以时间为导向的分析功能、优化,并根据时序工作负荷来缩放 Postgres。TimescaleDB provides time-oriented analytical functions, optimizations, and scales Postgres for time-series workloads.

详细了解 TimescaleDB,它是 Timescale, Inc. 的注册商标。Learn more about TimescaleDB, a registered trademark of Timescale, Inc.. Azure Database for PostgreSQL 提供 Timescale 的开放源代码版本。Azure Database for PostgreSQL provides the open-source version of Timescale. 若要了解此版本中有哪些 Timescale 功能可用,请参阅 Timescale 产品比较To learn which Timescale features are available in this version, see the Timescale product comparison.

安装 TimescaleDBInstalling TimescaleDB

若要安装 TimescaleDB,需将其包括在服务器的共享预加载库中。To install TimescaleDB, you need to include it in the server's shared preload libraries. 更改 Postgres 的 shared_preload_libraries 参数需要重启服务器才能生效。A change to Postgres's shared_preload_libraries parameter requires a server restart to take effect. 可以使用 Azure 门户Azure CLI 更改参数。You can change parameters using the Azure portal or the Azure CLI.

使用 Azure 门户Using the Azure portal:

  1. 选择你的 Azure Database for PostgreSQL 服务器。Select your Azure Database for PostgreSQL server.

  2. 在侧栏中选择“服务器参数”。 On the sidebar, select Server Parameters.

  3. 搜索 shared_preload_libraries 参数。Search for the shared_preload_libraries parameter.

  4. 选择“TimescaleDB” 。Select TimescaleDB.

  5. 选择“保存” ,保留所做的更改。Select Save to preserve your changes. 保存更改后会获得通知。You get a notification once the change is saved.

  6. 获得通知后,请重启服务器以应用这些更改。After the notification, restart the server to apply these changes. 若要了解如何重启服务器,请参阅重启 Azure Database for PostgreSQL 服务器To learn how to restart a server, see Restart an Azure Database for PostgreSQL server.

现在可以在 Postgres 数据库中启用 TimescaleDB。You can now enable TimescaleDB in your Postgres database. 连接到数据库并发出以下命令:Connect to the database and issue the following command:

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

提示

如果看到错误,请确认是否已在保存 shared_preload_libraries 后重启服务器If you see an error, confirm that you restarted your server after saving shared_preload_libraries.

现在可以从头开始创建 TimescaleDB hypertable,也可以迁移 PostgreSQL 中的现有时序数据You can now create a TimescaleDB hypertable from scratch or migrate existing time-series data in PostgreSQL.

还原 Timescale 数据库Restoring a Timescale database

若要使用 pg_dump 和 pg_restore 还原 Timescale 数据库,需要在目标数据库中运行两个帮助程序过程:timescaledb_pre_restore()timescaledb_post restore()To restore a Timescale database using pg_dump and pg_restore, you need to run two helper procedures in the destination database: timescaledb_pre_restore() and timescaledb_post restore().

首先,准备目标数据库:First prepare the destination database:

--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。Now you can run pg_dump on the original database and then do pg_restore. 还原后,请确保在还原的数据库中运行以下命令:After the restore, be sure to run the following command in the restored database:

SELECT timescaledb_post_restore();