PostgreSQL 官方扩展模块

ServBay 作为一款强大的本地 Web 开发环境，集成了多种开发者工具，其中包括功能丰富的 PostgreSQL 数据库。PostgreSQL 的强大之处不仅在于其核心功能，还在于其庞大的官方扩展生态系统。ServBay 预装了 PostgreSQL 的大部分官方扩展模块，开发者可以直接在本地环境中轻松启用和使用这些模块，极大地扩展了数据库的功能，满足各种复杂的应用需求。

本文将介绍一些常用的 PostgreSQL 官方扩展模块，并指导您如何在 ServBay 中安装和使用它们。

常用扩展模块概览

PostgreSQL 官方提供了众多扩展模块，涵盖了从性能监控到数据类型、全文搜索等多种功能。以下是一些开发者在日常工作中可能经常用到的扩展模块：

1. `pg_stat_statements`

功能：收集和记录服务器执行的所有 SQL 语句的统计信息，例如调用次数、总执行时间、行处理数等。
用途：帮助数据库管理员和开发者分析查询性能，识别慢查询和优化瓶颈。
安装命令：
sql
```
CREATE EXTENSION pg_stat_statements;
```
1
注意：安装后通常还需要在 postgresql.conf 中配置 shared_preload_libraries = 'pg_stat_statements' 并重启 PostgreSQL 服务才能完全启用并收集数据。ServBay 提供了修改配置的便捷方式。

2. `pg_trgm`

功能：提供基于三元组（trigram）的文本相似度测量函数和索引支持。
用途：用于实现高效的模糊匹配、相似性搜索和全文搜索功能，特别适合处理拼写错误或变体。
安装命令：
sql
```
CREATE EXTENSION pg_trgm;
```
1

3. `hstore`

功能：提供一种数据类型，用于在单个 PostgreSQL 值中存储键值对集合。
用途：适用于需要存储动态属性、非结构化或半结构化数据的场景，如配置数据、元数据或用户偏好设置，而无需预定义严格的表结构。
安装命令：
sql
```
CREATE EXTENSION hstore;
```
1

4. `citext`

功能：提供一种大小写不敏感的文本字符串数据类型。
用途：用于需要进行大小写不敏感比较的场景，例如存储和比较用户名、电子邮件地址或标签，避免在查询中频繁使用 LOWER() 函数，简化应用逻辑。
安装命令：
sql
```
CREATE EXTENSION citext;
```
1

5. `uuid-ossp`

功能：提供生成通用唯一标识符（UUID）的函数，支持多种标准的 UUID 版本（如 version 1, 3, 4, 5）。
用途：用于生成全局唯一的标识符，常用于主键、会话 ID、分布式系统中的唯一标记等。
安装命令：
sql
```
CREATE EXTENSION "uuid-ossp";
```
1
注意：扩展名称带有连字符，需要在 CREATE EXTENSION 命令中使用双引号包裹。

6. `intarray`

功能：提供对整数数组 (integer arrays) 的额外操作函数和运算符。
用途：适用于需要在数据库层面直接对整数数组进行集合运算（如交集、并集）、包含判断、排序等复杂操作的场景。
安装命令：
sql
```
CREATE EXTENSION intarray;
```
1

7. `btree_gin`

功能：为 B-tree 数据类型（如整数、文本、日期等）提供 GIN (Generalized Inverted Index) 索引操作支持。
用途：GIN 索引通常用于索引包含多个值的数据类型（如数组、hstore、tsvector），btree_gin 允许在这些复合类型中的 B-tree 可索引元素上使用 GIN 索引，提高查询效率。
安装命令：
sql
```
CREATE EXTENSION btree_gin;
```
1

8. `btree_gist`

功能：为 B-tree 数据类型提供 GiST (Generalized Search Tree) 索引操作支持。
用途：GiST 是一种通用的索引结构，btree_gist 允许在 B-tree 可索引数据上使用 GiST 索引，这对于需要支持多种查询类型（如范围查询、包含查询）或在多列上创建索引的场景非常有用。
安装命令：
sql
```
CREATE EXTENSION btree_gist;
```
1

9. `cube`

功能：提供一种数据类型，用于表示多维立方体或点，以及相应的操作符和函数。
用途：适用于需要处理多维数据的场景，如简单的地理空间数据（二维或三维点）、数据仓库中的多维分析或某些科学计算。
安装命令：
sql
```
CREATE EXTENSION cube;
```
1

10. `fuzzystrmatch`

功能：提供多种函数用于确定字符串之间的相似度和距离，例如 Levenshtein 距离、Soundex、Metaphone 等。
用途：用于实现字符串模糊匹配、拼写检查、数据清洗和去重等功能。
安装命令：
sql
```
CREATE EXTENSION fuzzystrmatch;
```
1

11. `plpgsql`

功能：这是 PostgreSQL 的过程语言，允许用户编写存储过程、触发器函数和匿名代码块。
用途：用于在数据库服务器端实现更复杂的业务逻辑、自动化任务和数据处理流程。plpgsql 是 PostgreSQL 的默认安装语言，通常无需手动安装，但可以通过 CREATE EXTENSION 显式确认其存在或重新创建。
安装命令：
sql
```
CREATE EXTENSION plpgsql;
```
1

12. `tablefunc`

功能：提供一些函数，用于处理和转换整个表格数据，最著名的是 crosstab 函数。
用途：用于执行交叉表查询（也称为数据透视），将一列中的唯一值转换为多列，常用于报表生成和数据分析。
安装命令：
sql
```
CREATE EXTENSION tablefunc;
```
1

13. `dblink`

功能：提供从一个 PostgreSQL 数据库内部连接到另一个 PostgreSQL 数据库并执行查询的功能。
用途：用于在不同的数据库之间进行简单的数据查询或数据迁移，无需在应用层处理跨库连接逻辑。
安装命令：
sql
```
CREATE EXTENSION dblink;
```
1

14. `postgres_fdw`

功能：提供一个外部数据封装器 (Foreign Data Wrapper)，允许将外部 PostgreSQL 数据库中的表当作本地表一样访问和查询。
用途：用于实现跨数据库的数据集成和联合查询，比 dblink 更强大和灵活，支持更复杂的查询优化。
安装命令：
sql
```
CREATE EXTENSION postgres_fdw;
```
1

15. `pgcrypto`

功能：提供各种加密和解密函数，包括哈希、对称加密、公钥加密等。
用途：用于在数据库层面实现数据加密、存储密码的哈希值、生成或验证数字签名等安全相关功能。
安装命令：
sql
```
CREATE EXTENSION pgcrypto;
```
1

这只是 ServBay 中提供的 PostgreSQL 官方扩展模块中的一部分。ServBay 力求包含 PostgreSQL 社区提供的绝大多数官方扩展，以满足开发者多样的需求。

在 ServBay 中安装和使用 PostgreSQL 扩展模块

在 ServBay 环境中安装和使用 PostgreSQL 扩展模块非常直接。扩展模块是安装在特定数据库中的，这意味着您需要在每个需要使用该扩展的数据库中分别启用它。

前提条件：

确保 ServBay 正在运行，并且 PostgreSQL 数据库服务已启动。

操作步骤：

连接到目标 PostgreSQL 数据库：打开您的终端应用程序（如 macOS 的 Terminal 或 iTerm2），并使用 psql 命令行工具连接到您想要安装扩展的数据库。默认情况下，ServBay 安装的 PostgreSQL 用户名为 postgres，默认数据库名也为 postgres。
bash
```
psql -U your_username -d your_database
```
1
例如，连接到默认的 postgres 数据库：
bash
```
psql -U postgres -d postgres
```
1
如果您连接时需要密码，输入 ServBay 中为 PostgreSQL 设置的 root/postgres 用户密码。
安装扩展模块：连接成功后，您将进入 psql 命令行界面。执行 CREATE EXTENSION 命令来安装所需的扩展模块。将 module_name 替换为您要安装的扩展名称。
sql
```
CREATE EXTENSION module_name;
```
1
例如，安装 pg_trgm 扩展：
sql
```
CREATE EXTENSION pg_trgm;
```
1
验证安装：安装完成后，您可以使用 \dx 命令查看当前数据库中已安装的所有扩展模块列表，确认您的扩展是否已成功安装。
sql
```
\dx
```
1

示例：启用 pg_trgm 扩展

以下是在 psql 命令行中连接到 postgres 数据库并启用 pg_trgm 扩展的完整交互示例：

sql

# 在终端中执行以下命令连接到数据库
psql -U postgres -d postgres

# 成功连接后，psql 命令行提示符出现
psql (16.3 (ServBay))
Type "help" for help.

postgres=# -- 查看当前已安装的扩展（示例中可能只包含默认的 plpgsql 和 pgcrypto）
postgres=# \dx
                  List of installed extensions
   Name   | Version |   Schema   |         Description          
----------+---------+------------+------------------------------
 pgcrypto | 1.3     | public     | cryptographic functions
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=# -- 安装 pg_trgm 扩展
postgres=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION

postgres=# -- 再次查看已安装的扩展列表，确认 pg_trgm 已添加
postgres=# \dx
                                    List of installed extensions
   Name   | Version |   Schema   |                            Description                            
----------+---------+------------+-------------------------------------------------------------------
 pg_trgm  | 1.6     | public     | text similarity measurement and index searching based on trigrams
 pgcrypto | 1.3     | public     | cryptographic functions
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

postgres=# -- 安装成功，现在可以在此数据库中使用 pg_trgm 提供的函数和操作符了

查询 ServBay 中 PostgreSQL 提供的可用扩展

在安装扩展之前，了解 ServBay 中 PostgreSQL 版本所附带的所有可用扩展列表是非常有用的。这可以帮助您发现更多潜在有用的功能。

您可以在连接到任何 PostgreSQL 数据库后，执行以下 SQL 查询来获取可用扩展的完整列表：

sql

SELECT * FROM pg_available_extensions ORDER BY name;

执行此查询将返回一个表格，列出 ServBay 集成的 PostgreSQL 版本支持的所有扩展模块，包括它们的名称、默认版本、当前数据库中是否已安装及其描述。

一个典型的返回结果（列表可能很长，且具体内容和版本取决于 ServBay 集成的 PostgreSQL 版本）如下所示：

sql

             name             | default_version | installed_version |                                                       comment                                                       
------------------------------+-----------------+-------------------+---------------------------------------------------------------------------------------------------------------------
 address_standardizer         | 3.4.2           |                   | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
 address_standardizer_data_us | 3.4.2           |                   | Address Standardizer US dataset example
 adminpack                    | 2.1             |                   | administrative functions for PostgreSQL
 amcheck                      | 1.3             |                   | functions for verifying relation integrity
 autoinc                      | 1.0             |                   | functions for autoincrementing fields
 bloom                        | 1.0             |                   | bloom access method - signature file based index
 btree_gin                    | 1.3             |                   | support for indexing common datatypes in GIN
 btree_gist                   | 1.7             |                   | support for indexing common datatypes in GiST
 citext                       | 1.6             |                   | data type for case-insensitive character strings
 cube                         | 1.5             |                   | data type for multidimensional cubes
 dblink                       | 1.2             |                   | connect to other PostgreSQL databases from within a database
 dict_int                     | 1.0             |                   | text search dictionary template for integers
 dict_xsyn                    | 1.0             |                   | text search dictionary template for extended synonym processing
 earthdistance                | 1.1             |                   | calculate great-circle distances on the surface of the Earth
 file_fdw                     | 1.0             |                   | foreign-data wrapper for flat file access
 fuzzystrmatch                | 1.2             |                   | determine similarities and distance between strings
 hstore                       | 1.8             |                   | data type for storing sets of (key, value) pairs
 insert_username              | 1.0             |                   | functions for tracking who changed a table
 intagg                       | 1.1             |                   | integer aggregator and enumerator (obsolete)
 intarray                     | 1.5             |                   | functions, operators, and index support for 1-D arrays of integers
 isn                          | 1.2             |                   | data types for international product numbering standards
 lo                           | 1.1             |                   | Large Object maintenance
 ltree                        | 1.2             |                   | data type for hierarchical tree-like structures
 moddatetime                  | 1.0             |                   | functions for tracking last modification time
 old_snapshot                 | 1.0             |                   | utilities in support of old_snapshot_threshold
 pageinspect                  | 1.12            |                   | inspect the contents of database pages at a low level
 pg_buffercache               | 1.4             |                   | examine the shared buffer cache
 pg_freespacemap              | 1.2             |                   | examine the free space map (FSM)
 pg_jieba                     | 1.1.1           |                   | a parser for full-text search of Chinese
 pg_prewarm                   | 1.2             |                   | prewarm relation data
 pg_stat_statements           | 1.10            |                   | track planning and execution statistics of all SQL statements executed
 pg_surgery                   | 1.0             |                   | extension to perform surgery on a damaged relation
 pg_trgm                      | 1.6             | 1.6               | text similarity measurement and index searching based on trigrams
 pg_visibility                | 1.2             |                   | examine the visibility map (VM) and page-level visibility info
 pg_walinspect                | 1.1             |                   | functions to inspect contents of PostgreSQL Write-Ahead Log
 pgcrypto                     | 1.3             | 1.3               | cryptographic functions
 pgrouting                    | 3.6.2           |                   | pgRouting Extension
 pgrowlocks                   | 1.2             |                   | show row-level locking information
 pgstattuple                  | 1.5             |                   | show tuple-level statistics
 plpgsql                      | 1.0             | 1.0               | PL/pgSQL procedural language
 postgis                      | 3.4.2           |                   | PostGIS geometry and geography spatial types and functions
 postgis_sfcgal               | 3.4.2           |                   | PostGIS SFCGAL functions
 postgis_tiger_geocoder       | 3.4.2             |                   | PostGIS tiger geocoder and reverse geocoder
 postgres_fdw                 | 1.1             |                   | foreign-data wrapper for remote PostgreSQL servers
 refint                       | 1.0             |                   | functions for implementing referential integrity (obsolete)
 seg                          | 1.4             |                   | data type for representing line segments or floating-point intervals
 sslinfo                      | 1.2             |                   | information about SSL certificates
 tablefunc                    | 1.0             |                   | functions that manipulate whole tables, including crosstab
 tcn                          | 1.0             |                   | Triggered change notifications
 tsm_system_rows              | 1.0             |                   | TABLESAMPLE method which accepts number of rows as a limit
 tsm_system_time              | 1.0             |                   | TABLESAMPLE method which accepts time in milliseconds as a limit
 unaccent                     | 1.1             |                   | text search dictionary that removes accents
 vector                       | 0.7.0           |                   | vector data type and ivfflat and hnsw access methods
 xml2                         | 1.1             |                   | XPath querying and XSLT
 zhparser                     | 2.2             |                   | a parser for full-text search of Chinese
(55 rows)

请注意，上述列表是一个示例，您在 ServBay 中查询到的实际列表和版本号可能会因 ServBay 或 PostgreSQL 版本的更新而有所不同。

总结

ServBay 通过预置丰富的 PostgreSQL 官方扩展，极大地简化了本地开发环境中利用这些强大功能的流程。开发者无需手动编译或安装，只需通过简单的 SQL 命令即可在自己的数据库中启用所需扩展，从而专注于应用开发，提升效率。利用这些扩展，您可以轻松为项目添加高级的数据库功能，如性能分析、全文搜索、数据安全等。

PostgreSQL 官方扩展模块 ​

常用扩展模块概览 ​

1. pg_stat_statements ​

2. pg_trgm ​

3. hstore ​

4. citext ​

5. uuid-ossp ​

6. intarray ​

7. btree_gin ​

8. btree_gist ​

9. cube ​

10. fuzzystrmatch ​

11. plpgsql ​

12. tablefunc ​

13. dblink ​

14. postgres_fdw ​

15. pgcrypto ​

在 ServBay 中安装和使用 PostgreSQL 扩展模块 ​

查询 ServBay 中 PostgreSQL 提供的可用扩展 ​

总结 ​