PHP小码农

Appearance

CREATE EXTENSION 扩展管理 ​

PostgreSQL 的核心设计哲学之一是「内核保持精简，能力靠扩展叠加」。当你需要地理计算、时序聚合、分布式分片、审计统计这些能力时，PostgreSQL 不会把它们塞进内核，而是让你通过 CREATE EXTENSION 把一个个「插件包」装进数据库。理解 CREATE EXTENSION 一族的语法与权限模型，是后续使用 PostGIS、TimescaleDB、Citus 乃至开发自有扩展的必经入口。

很多开发者第一次接触扩展时会有困惑：为什么 CREATE EXTENSION postgis; 之后函数就出现在 public 里了？为什么有的扩展必须写进 shared_preload_libraries 才生效？为什么普通用户装不了扩展，而有的扩展又能装？这些问题都指向同一套机制 —— 扩展的打包格式、控制文件属性（superuser / trusted / relocatable）以及 schema 归属规则。

本篇围绕「装什么、装到哪、谁能装、怎么升、怎么删」这条主线，把 CREATE EXTENSION 的语法、系统视图、权限模型与升级机制讲透。

前置知识

建议先阅读 FDW 性能调优 了解 shared_preload_libraries 与预加载库的关系，以及 bytea 与 lo 的取舍 了解大对象与扩展对象的存储差异。

基础概念 ​

什么是扩展（Extension） ​

扩展（Extension）是 PostgreSQL 提供的一种打包机制：把一个或多个 SQL 对象（函数、类型、操作符、索引访问方法、转换等）以及可选的 C 共享库，打成一个可以被 CREATE EXTENSION、ALTER EXTENSION、DROP EXTENSION 整体管理的单元。

官方文档的说法是：

An extension is essentially a collection of SQL objects that are packaged together for installation or removal as a unit.

关键点：扩展不是「数据库」，而是「一组以整体为单位安装/卸载/升级的对象集合」。它本身不是一个容器对象，你在 pg_class 里找不到「扩展」这种对象；扩展只在 pg_extension 目录表中有一条元数据记录。

扩展 vs 手写 SQL 脚本 ​

对比一下两种引入能力的做法：

维度手动执行 SQL 脚本扩展机制
安装复制粘贴/psql -f install.sqlCREATE EXTENSION name;
卸载需要手写 DROP FUNCTION ... 一大堆DROP EXTENSION name;
升级人工比对版本、手工执行 diffALTER EXTENSION name UPDATE;
依赖管理靠人肉记住控制文件 requires + CASCADE 自动装
归属追踪无pg_depend 中记录 e（extension）依赖
dump/restore把对象全 dump 出来只 dump CREATE EXTENSION 一行
权限执行者拥有所有对象由控制文件 superuser / trusted 决定

第三、第六行是扩展机制最大的价值：pg_dump 遇到扩展对象时只输出一条 CREATE EXTENSION，而不是把每个函数的定义都导出。这让备份清晰、恢复快速。

扩展的三种属性 ​

每个扩展在控制文件里可以声明三个关键属性，它们直接决定安装体验：

  • superuser（默认 true）：是否只允许超级用户安装。设为 false 时普通用户也能装。
  • trusted（默认 false）：是否为「可信扩展」。trusted = true 表示该扩展的脚本被审核过、不会提权，因而普通用户即使没有超级用户权限，也可以在数据库里安装它（PostgreSQL 13 引入）。
  • relocatable（默认 false）：是否可以整体搬到别的 schema。relocatable = true 时可以用 ALTER EXTENSION ... SET SCHEMA ... 搬家。

这三个属性不在 pg_available_extensions 里，要看它们得查 pg_available_extension_versions：

sql
-- pg_available_extensions 只有 4 列，不含权限属性
SELECT * FROM pg_available_extensions LIMIT 3;

结果示意（text）：

text
        name        | default_version | installed_version |          comment
--------------------+-----------------+-------------------+----------------------------
 adminpack          | 2.1             |                   | administrative functions
 amcheck            | 1.3             |                   | functions for verifying rela
 auto_explain       | 2.0             |                   | log execution plans of slow q

而权限属性要来这里看：

sql
SELECT name, version, installed, superuser, trusted, relocatable, schema
FROM pg_available_extension_versions
WHERE name IN ('hstore', 'postgis', 'pg_stat_statements', 'plpgsql')
ORDER BY name;

结果示意（text）：

text
        name        | version | installed | superuser | trusted | relocatable | schema
--------------------+---------+-----------+-----------+---------+-------------+--------
 hstore             | 1.8     | f         | t         | t       | t           |
 pg_stat_statements | 1.11    | f         | t         | f       | t           | public
 plpgsql            | 1.0     | t         | f         | f       | f           | pg_catalog
 postgis            | 3.5.0   | f         | t         | f       | f           |

注意 plpgsql 本身就是个扩展 —— 它是 PostgreSQL 默认安装的扩展，用来提供 PL/pgSQL 语言处理器。

扩展的文件布局 ​

一个扩展在磁盘上通常包含三类文件，都放在 $(pg_config --sharedir)/extension/ 下：

文件作用是否必需
name.control控制文件，声明版本、依赖、权限属性必需
name--version.sql安装脚本必需（纯 SQL 扩展）
name--old--new.sql升级脚本升级时必需
name.so / name.dylibC 共享库仅 C 扩展需要

查看本机扩展目录：

bash
pg_config --sharedir
# 输出示例：/usr/local/pgsql/share
ls $(pg_config --sharedir)/extension/ | head -20

语法与用法 ​

CREATE EXTENSION ​

官方语法：

text
CREATE EXTENSION [ IF NOT EXISTS ] extension_name
    [ WITH ] [ SCHEMA schema_name ]
             [ VERSION version ]
             [ CASCADE ]

逐个参数说明：

参数含义
IF NOT EXISTS已存在时只发 NOTICE，不报错
SCHEMA schema_name把扩展对象安装到此 schema
VERSION version安装指定版本（默认 default_version）
CASCADE自动安装 requires 声明的依赖扩展

最常见用法：

sql
-- 最简形式
CREATE EXTENSION hstore;

-- 显式指定 schema
CREATE EXTENSION hstore WITH SCHEMA extensions;

-- 幂等写法（部署脚本里强烈推荐）
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 自动装依赖
CREATE EXTENSION IF NOT EXISTS postgis CASCADE;

ALTER EXTENSION ​

text
ALTER EXTENSION name UPDATE [ TO new_version ]
ALTER EXTENSION name SET SCHEMA new_schema
ALTER EXTENSION name ADD member_object
ALTER EXTENSION name DROP member_object

升级：

sql
-- 升到控制文件里的 default_version
ALTER EXTENSION pg_trgm UPDATE;

-- 升到指定版本
ALTER EXTENSION pg_trgm UPDATE TO '1.5';

搬家（仅当 relocatable = true 时可行）：

sql
ALTER EXTENSION hstore SET SCHEMA extensions;

把已有对象「收编」进扩展（一般只有扩展作者需要）：

sql
ALTER EXTENSION my_ext ADD FUNCTION my_func(int);

DROP EXTENSION ​

text
DROP EXTENSION [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
sql
-- 如果其他对象依赖它，会报错
DROP EXTENSION hstore;

-- 连同依赖对象一起删（危险！）
DROP EXTENSION hstore CASCADE;

-- 幂等删除
DROP EXTENSION IF EXISTS hstore;

RESTRICT 是默认行为：只要有别的对象依赖被删扩展，就拒绝执行。CASCADE 会递归删除依赖对象，例如某个普通表上的 GIN 索引用了 gin_trgm_ops，DROP EXTENSION pg_trgm CASCADE 会把那个索引一起删掉。

psql 元命令 ​

日常运维最常用的三个：

text
\dx              列出已安装扩展
\dx+ <name>      列出该扩展包含的所有对象
\df <pattern>    列出函数（验证扩展函数是否装好）

实际效果：

text
postgres=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 hstore  | 1.8     | public     | data type for storing sets of
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=# \dx+ hstore
           Objects in extension "hstore"
                  Object description
-----------------------------------------------------
 function akeys(hstore)
 function avals(hstore)
 function defined(hstore,text)
 ...
 operator ->(hstore,text)
 type hstore
(31 rows)

查询系统视图 ​

sql
-- 可安装的扩展总览
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
ORDER BY name;

-- 只看已安装的
SELECT e.extname, e.extversion, n.nspname, e.extrelocatable
FROM pg_extension e
JOIN pg_namespace n ON n.oid = e.extnamespace
ORDER BY e.extname;

-- 查看某扩展的依赖（requires）
SELECT name, version, requires
FROM pg_available_extension_versions
WHERE requires IS NOT NULL;

详细说明 ​

扩展的命名与版本规则 ​

扩展名是大小写敏感的裸标识符风格（不是 SQL 标识符，不折叠大小写）。惯例是小写加下划线：pg_stat_statements、postgis_raster、btree_gin。

版本号是字符串，不做数值比较。官方建议用「数字加点的形式」，例如 1.0、1.2.3。因为升级路径是按版本名字符串匹配的，混用 1.0 和 1.0.0 会把自己绕晕。

sql
-- 查看某个扩展有哪些可用版本
SELECT name, version, installed, superuser, trusted, relocatable
FROM pg_available_extension_versions
WHERE name = 'pg_trgm'
ORDER BY version;

结果示意（text）：

text
  name   | version | installed | superuser | trusted | relocatable
---------+---------+-----------+-----------+---------+-------------
 pg_trgm | 1.3     | f         | t         | t       | t
 pg_trgm | 1.4     | f         | t         | t       | t
 pg_trgm | 1.5     | f         | t         | t       | t
 pg_trgm | 1.6     | f         | t         | t       | t

安装时指定一个较老的版本再逐步升级，是版本升级演练的常用手法：

sql
CREATE EXTENSION pg_trgm VERSION '1.5';
-- 演练升级
ALTER EXTENSION pg_trgm UPDATE TO '1.6';

三种属性如何影响你的安装 ​

回顾一下三个属性在行为上的差别。用一张表说清楚：

场景superuser=true, trusted=true（如 hstore）superuser=true, trusted=false（如 postgis）
超级用户安装✅✅
普通用户安装✅❌ 报错 permission denied to create extension
脚本执行身份bootstrap superuser调用者
扩展对象 owner调用者调用者

错误信息长这样：

text
ERROR:  permission denied to create extension "postgis"
HINT:  Must have superuser privilege to create extension "postgis".

而 trusted 扩展在普通用户手上是可以装的：

sql
-- 以普通用户 dba_dev 身份执行
CREATE EXTENSION hstore;
-- CREATE EXTENSION

官方文档对这个语义的说明是：

If the extension is marked trusted, ... the installation script is run as the bootstrap superuser, not as the calling user, ... the objects created by the script are owned by the calling user (or the superuser, if the bootstrap superuser is the calling user).

也就是说：trusted 扩展的脚本以引导超级用户身份执行（避免普通用户借脚本提权），但创建出来的对象归属于调用者。这就是「可信」的含义 —— 扩展作者已经保证脚本不会被滥用。

如何查看一个已安装扩展是不是 trusted？答案是：pg_extension 里没有这个字段，得去 pg_available_extension_versions 查：

sql
SELECT e.extname, e.extversion, v.trusted, v.superuser, e.extrelocatable
FROM pg_extension e
JOIN pg_available_extension_versions v
  ON v.name = e.extname AND v.version = e.extversion;

pg_extension 目录表逐列解析 ​

pg_extension 是「已安装扩展」的目录表，字段不多但每个都有用：

sql
\d pg_extension

结果示意（text）：

text
        Column        |  Type  | Collation | Nullable | Default
----------------------+--------+-----------+----------+---------
 extname              | name   |           | not null |
 extowner             | oid    |           | not null |
 extnamespace         | oid    |           | not null |
 extrelocatable       | boolean|           | not null |
 extversion           | text   |           | not null |
 extconfig            | oid[]  |           |          |
 extcondition         | text[] |           |          |
列含义
extname扩展名
extowner扩展所有者（OID）
extnamespace扩展所在 schema 的 OID（不是对象实际所在 schema）
extrelocatable是否可搬家
extversion已安装版本
extconfig需要 dump/restore 内容的配置表 OID 数组
extcondition与 extconfig 一一对应的 WHERE 过滤条件数组

查询时把它们翻译成人话：

sql
SELECT
  e.extname        AS extension,
  pg_get_userbyid(e.extowner) AS owner,
  n.nspname        AS schema,
  e.extversion     AS version,
  e.extrelocatable AS relocatable,
  e.extconfig      AS config_tables,
  e.extcondition   AS conditions
FROM pg_extension e
JOIN pg_namespace n ON n.oid = e.extnamespace
ORDER BY e.extname;

extconfig / extcondition 与 pg_dump ​

这两个字段是给 pg_dump 看的，是扩展机制里最容易被忽略的一环。

问题背景：有些扩展会在运行时往自己创建的表里写用户数据。例如扩展 my_ext 建了一张 my_ext_config(k text, v text) 表，用户往里塞了配置。如果 pg_dump 只输出 CREATE EXTENSION my_ext;，这些用户数据就丢了。

解决方式：扩展在安装脚本里调用 pg_extension_config_dump()，把这张表登记到 extconfig 里：

sql
-- 扩展安装脚本中的写法（普通用户一般看不到，是扩展作者写的）
SELECT pg_extension_config_dump('my_ext_config', '');

之后 pg_dump 的行为就变成：

text
-- pg_dump 输出（示意）
CREATE EXTENSION IF NOT EXISTS my_ext WITH SCHEMA public;
-- 然后单独 dump 这张表的数据
COPY public.my_ext_config (k, v) FROM stdin;
...
\.

可以手动检查哪些扩展登记了配置表：

sql
SELECT
  e.extname,
  e.extversion,
  ARRAY(
    SELECT c.relname
    FROM unnest(e.extconfig) AS cfg(oid)
    JOIN pg_class c ON c.oid = cfg.oid
  ) AS config_tables,
  e.extcondition
FROM pg_extension e
WHERE e.extconfig IS NOT NULL;

extcondition 的用途是给这些表加过滤条件，只 dump 一部分行。例如：

sql
-- 只 dump key 前缀为 'public_' 的行
SELECT pg_extension_config_dump('my_ext_config', $$
  WHERE key LIKE 'public_%'
$$);

注意：pg_extension_config_dump() 只能在扩展的安装脚本中调用，且必须在创建目标表之后、脚本结束之前。普通用户在运行时调用会报「function ... is not allowed」之类的错。

schema 归属与 search_path 的坑 ​

CREATE EXTENSION 内部的执行逻辑，官方文档给出了精确描述：

The extension itself is ... installed into the schema given by the SCHEMA clause. ... CREATE EXTENSION effectively runs with SET LOCAL search_path TO @extschema@, pg_temp; ...

也就是说，安装脚本执行时，search_path 被临时设为「目标 schema + pg_temp」。因此：

  • 未限定的对象会被建到目标 schema 里；
  • 脚本里通过 @extschema@ 引用自己的 schema，保证内部自引用正确；
  • 如果扩展的 relocatable = false，且脚本里硬编码了 schema 名，SET SCHEMA 就会失败。

生产环境的常见坑：把 postgis 装到默认的 public，然后业务表也放 public，随后 pg_dump --schema=public 会把 PostGIS 的几百个函数全 dump 出来。推荐做法是单独建 schema：

sql
CREATE SCHEMA IF NOT EXISTS extensions;
CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA extensions;

-- 让应用能直接调用，而不必写 extensions.ST_*
-- 注意：影响全局，需谨慎
ALTER DATABASE mydb SET search_path TO public, extensions;

验证对象落在哪里：

sql
SELECT n.nspname AS schema, count(*) AS objects
FROM pg_depend d
JOIN pg_extension e ON e.oid = d.refobjid
JOIN pg_class c ON c.oid = d.objid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE d.refclassid = 'pg_extension'::regclass
  AND e.extname = 'postgis'
  AND d.deptype = 'e'
GROUP BY 1;

如果不想改 search_path，也可以只在当前会话里临时用：

sql
SET search_path TO public, extensions;
SELECT ST_AsText(ST_MakePoint(116.4, 39.9));

shared_preload_libraries 与扩展的关系 ​

有些扩展不只是「注册 SQL 对象」，它们需要在服务端进程启动时就把共享库加载进内存，比如：

  • pg_stat_statements：要挂接执行器钩子，统计所有语句；
  • auto_explain：要挂接 planner/executor 钩子；
  • pg_prewarm：要注册后台工作进程；
  • pg_buffercache：要能读到共享缓冲区。

这类扩展光 CREATE EXTENSION 不够，必须改配置并重启：

bash
# 在 postgresql.conf 中
shared_preload_libraries = 'pg_stat_statements'
sql
-- 然后重启服务（不是 reload！）
-- pg_ctl restart -D $PGDATA

重启后再装扩展：

sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT count(*) FROM pg_stat_statements;

如果忘了预加载，CREATE EXTENSION 可能成功，但一调用就报错：

text
ERROR:  pg_stat_statements must be loaded via shared_preload_libraries

判断一个扩展是否需要预加载，可以看它的文档，或直接检查是否创建了后台工作进程/钩子。也可以观察 pg_shmem_allocations：

sql
SELECT name, size, allocated_size
FROM pg_shmem_allocations
WHERE name LIKE '%stat_statements%';

CASCADE 自动装依赖 ​

扩展之间可以声明依赖。经典例子是 postgis 依赖 postgis_raster 一类的组件，或者 earthdistance 依赖 cube：

sql
-- earthdistance 的 requires 是 cube
SELECT name, version, requires
FROM pg_available_extension_versions
WHERE name IN ('earthdistance', 'cube');

结果示意（text）：

text
     name      | version | requires
---------------+---------+----------
 cube          | 1.5     |
 earthdistance | 1.2     | {cube}

不加 CASCADE 直接装会失败：

sql
CREATE EXTENSION earthdistance;
-- ERROR:  required extension "cube" is not installed
-- HINT:  Use CREATE EXTENSION ... CASCADE to install required extensions too.

加上就好了：

sql
CREATE EXTENSION earthdistance CASCADE;
-- NOTICE:  installing required extension "cube"
-- CREATE EXTENSION

查看已安装扩展之间的依赖关系：

sql
SELECT
  e.extname AS dependent_extension,
  d.deptype,
  ref.extname AS required_extension
FROM pg_depend d
JOIN pg_extension e ON e.oid = d.objid
JOIN pg_extension ref ON ref.oid = d.refobjid
WHERE d.classid = 'pg_extension'::regclass
  AND d.refclassid = 'pg_extension'::regclass
  AND e.oid <> ref.oid;

权限模型：谁能装、谁能删 ​

三个动作对应三重权限：

动作权限要求
CREATE EXTENSION超级用户；或扩展为 trusted；或数据库所有者安装 trusted 扩展
ALTER EXTENSION ... UPDATE扩展所有者（extowner）
ALTER EXTENSION ... SET SCHEMA扩展所有者 + extrelocatable = true + 对新 schema 有 CREATE 权限
DROP EXTENSION扩展所有者

查看当前用户能装哪些扩展（trusted 的）：

sql
SELECT name, default_version, comment
FROM pg_available_extensions
WHERE name IN (
  SELECT name FROM pg_available_extension_versions
  WHERE trusted AND superuser
)
ORDER BY name;

实践中最省事的做法是：让 DBA 用超级用户预装好，普通用户只负责使用。

sql
-- 授予普通用户使用扩展函数的权限（以 public 为例）
GRANT USAGE ON SCHEMA extensions TO app_rw;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA extensions TO app_rw;

-- 让以后新建的函数也自动可执行
ALTER DEFAULT PRIVILEGES IN SCHEMA extensions
  GRANT EXECUTE ON FUNCTIONS TO app_rw;

扩展与 pg_upgrade 的兼容性 ​

大版本升级（pg_upgrade）时，扩展是最容易出问题的部分。原因是：pg_upgrade 只搬运数据文件，不重装扩展的共享库。所以你必须：

  1. 在新版本的 PostgreSQL 里，同样装好所有扩展的二进制包（.so 文件）；
  2. 确保这些包支持的扩展版本与新集群的目录表记录一致。

官方在 pg_upgrade 文档里明确写了要检查的部分。实操检查脚本：

sql
-- 在旧集群上跑，导出扩展清单
SELECT e.extname, e.extversion, n.nspname
FROM pg_extension e
JOIN pg_namespace n ON n.oid = e.extnamespace
ORDER BY e.extname;
bash
# 把清单存下来
psql -Atc "SELECT extname || ' ' || extversion FROM pg_extension" > /tmp/ext-before.txt

# 升级后在新集群比对
psql -Atc "SELECT extname || ' ' || extversion FROM pg_extension" > /tmp/ext-after.txt
diff /tmp/ext-before.txt /tmp/ext-after.txt && echo "扩展版本一致"

常见的升级失败与对策：

现象原因对策
could not open extension control file新版没装该扩展的二进制先 apt install postgresql-18-postgis-3 之类
extension "x" has no update path from version A to B缺中间升级脚本逐级升级：先 UPDATE TO '1.5' 再 UPDATE TO '1.6'
函数调用报 undefined symbol.so 版本与 SQL 定义不匹配重装匹配版本的扩展包

pg_available_extension_versions 的 installed 列在升级后能帮你快速核对：

sql
SELECT name, version, installed
FROM pg_available_extension_versions
WHERE name = 'pg_trgm';

在模板数据库里预装扩展 ​

如果你希望「每个新建数据库都自动带上某些扩展」，可以在 template1 里装好：

sql
\c template1
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

之后 CREATE DATABASE newdb; 建出来的库就自带这些扩展。

但有一个重要限制：shared_preload_libraries 类的扩展（如 pg_stat_statements）虽然可以装在 template1 里，但统计信息是按库隔离的，每个新库仍然需要它自己的视图被访问才能看到该库的统计。

另外，如果 template1 里装了扩展，而某个扩展的 .so 文件后来被卸载了，会导致所有基于模板创建的新库都建不出来。所以模板预装要谨慎，尤其是第三方扩展。

已废弃的 tsearch2 迁移思路 ​

老项目中常见的 tsearch2 是 PostgreSQL 8.3 之前的内置全文检索方案，从 8.3 起已被内核全文检索取代，扩展本身早已废弃。如果你手上还有 tsearch2 的库，迁移路径是：

sql
-- 1. 确认没有残留
SELECT * FROM pg_extension WHERE extname = 'tsearch2';
SELECT * FROM pg_available_extensions WHERE name = 'tsearch2';

-- 2. 新方案改用内核的 tsvector/tsquery，无需任何扩展
SELECT to_tsvector('simple', 'PostgreSQL 全文检索');
SELECT plainto_tsquery('simple', 'PostgreSQL');

也就是说：新项目不要再引入 tsearch2，to_tsvector、tsquery、GIN 索引已经全部内置。

实战示例 ​

场景一：给生产库规划 extensions schema ​

目标：所有扩展装进独立 schema，业务代码仍能直接用函数。

sql
-- 1. 建专用 schema
CREATE SCHEMA IF NOT EXISTS extensions;

-- 2. 装扩展
CREATE EXTENSION IF NOT EXISTS hstore      WITH SCHEMA extensions;
CREATE EXTENSION IF NOT EXISTS pg_trgm     WITH SCHEMA extensions;
CREATE EXTENSION IF NOT EXISTS btree_gin   WITH SCHEMA extensions;
CREATE EXTENSION IF NOT EXISTS unaccent    WITH SCHEMA extensions;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA extensions;

-- 3. 让整个数据库都能解析到 extensions
ALTER DATABASE mydb SET search_path TO public, extensions;

-- 4. 重连生效
-- \c mydb
SHOW search_path;

验证：

sql
SELECT extname, n.nspname AS schema, extversion
FROM pg_extension e
JOIN pg_namespace n ON n.oid = e.extnamespace
ORDER BY 1;

场景二：普通用户安装 trusted 扩展 ​

sql
-- 以普通用户 app_user 身份连接
-- 先确认 pgcrypto 是 trusted 的
SELECT name, version, trusted FROM pg_available_extension_versions
WHERE name = 'pgcrypto';

-- 安装
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- 使用
SELECT encode(digest('hello', 'sha256'), 'hex');

如果这个用户想在别人的数据库里装，则会因为不是数据库所有者而被拒：

text
ERROR:  permission denied to create extension "pgcrypto"
HINT:  Must be superuser to create this extension.

场景三：升级 pg_trgm 并观察对象归属 ​

sql
-- 现状
SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_trgm';

-- 查看升级路径（有哪些中间版本）
SELECT name, version FROM pg_available_extension_versions
WHERE name = 'pg_trgm' ORDER BY version;

-- 升级到默认版本
ALTER EXTENSION pg_trgm UPDATE;

-- 确认
SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_trgm';

升级前后对象归属不变，这正是扩展机制的价值：

sql
SELECT count(*) AS trgm_objects
FROM pg_depend d
JOIN pg_extension e ON e.oid = d.refobjid AND e.extname = 'pg_trgm'
WHERE d.deptype = 'e';

场景四：安全卸载一个不再需要的扩展 ​

sql
-- 1. 先看扩展带了多少对象
\dx+ hstore

-- 2. 检查是否有用户对象依赖它
SELECT
  c.relname  AS dependent_object,
  n.nspname  AS schema,
  pg_get_userbyid(c.relowner) AS owner
FROM pg_depend d
JOIN pg_class c ON c.oid = d.objid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_extension e ON e.oid = d.refobjid
WHERE e.extname = 'hstore'
  AND d.deptype = 'n';

-- 3. 若有依赖，先处理依赖对象（改类型/删索引）
-- 4. 无依赖则直接删
DROP EXTENSION IF EXISTS hstore;

deptype = 'n' 表示「普通依赖」—— 有别的东西依赖扩展对象。如果这里返回了行，DROP EXTENSION hstore; 会失败并提示你哪些对象挡路。

场景五：扩展安装与卸载的可重入脚本 ​

部署脚本常见需求：可重复执行。

sql
BEGIN;

-- 幂等安装
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 幂等建索引（用了 pg_trgm 的 GIN 索引）
CREATE INDEX IF NOT EXISTS idx_users_name_trgm
  ON users USING gin (name gin_trgm_ops);

COMMIT;
bash
#!/usr/bin/env bash
set -euo pipefail
PSQL="psql -v ON_ERROR_STOP=1 -X -q -d ${DB_NAME:-mydb}"

# 预检：扩展二进制是否存在
if ! $PSQL -Atc "SELECT 1 FROM pg_available_extensions WHERE name='pg_trgm'" | grep -q 1; then
  echo "pg_trgm 未安装到本机，请先安装 postgresql-contrib" >&2
  exit 1
fi

$PSQL -f db/install_extensions.sql
echo "extensions ok"

场景六：审计「谁的数据库装了什么」 ​

DBA 在多租户或共享实例里，常需要全局盘点。可以逐库遍历：

sql
-- 方式一：在目标库内查询
SELECT current_database() AS db, extname, extversion
FROM pg_extension
ORDER BY extname;
bash
# 方式二：shell 遍历所有库
for db in $(psql -Atc "SELECT datname FROM pg_database WHERE datistemplate = false"); do
  echo "=== $db ==="
  psql -d "$db" -Atc "SELECT extname || ' ' || extversion FROM pg_extension ORDER BY 1"
done

关注这几类扩展：需要预加载却在某库缺失的、被 CASCADE 误删过对象的、版本落后于 default_version 的。

sql
-- 找出可以升级的扩展
SELECT e.extname, e.extversion AS installed, v.default_version AS available
FROM pg_extension e
JOIN pg_available_extensions v ON v.name = e.extname
WHERE e.extversion <> v.default_version;

注意事项 ​

注意一：CREATE EXTENSION 不加 IF NOT EXISTS 会在自动化脚本里炸掉。 部署脚本会被反复执行，第二次跑就报 extension "x" already exists：

sql
CREATE EXTENSION pg_trgm;            -- 第二次执行报错
CREATE EXTENSION IF NOT EXISTS pg_trgm;  -- 幂等，推荐

注意二：DROP EXTENSION ... CASCADE 会连环删掉你的业务对象。

sql
DROP EXTENSION pg_trgm CASCADE;   -- 错误！会把所有用 gin_trgm_ops 的索引一起删掉
DROP EXTENSION pg_trgm RESTRICT;  -- 有机会报错并让你先处理依赖

注意三：把扩展装进 public 会让 pg_dump --schema=public 输出爆炸。 PostGIS 会往目标 schema 塞几百个函数和类型：

sql
CREATE EXTENSION postgis;                      -- 不推荐：污染 public
CREATE SCHEMA extensions;
CREATE EXTENSION postgis WITH SCHEMA extensions;  -- 推荐

注意四：ALTER EXTENSION ... SET SCHEMA 对 relocatable = false 的扩展一定失败。

sql
ALTER EXTENSION postgis SET SCHEMA extensions;
-- ERROR:  extension "postgis" does not support SET SCHEMA

装之前就要定好 schema，别装完再想搬。

注意五：需要 shared_preload_libraries 的扩展，只 CREATE EXTENSION 不会工作。

sql
-- 未预加载时
SELECT * FROM pg_stat_statements;
-- ERROR:  pg_stat_statements must be loaded via shared_preload_libraries
bash
# 正确顺序：改配置 → 重启 → 建扩展
echo "shared_preload_libraries = 'pg_stat_statements'" >> $PGDATA/postgresql.conf
pg_ctl restart -D $PGDATA
psql -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements"

注意六：升级扩展前先看有没有直达升级路径。

sql
ALTER EXTENSION my_ext UPDATE TO '2.0';
-- ERROR:  extension "my_ext" has no update path from version "1.0" to version "2.0"

这时要逐级升：

sql
ALTER EXTENSION my_ext UPDATE TO '1.5';
ALTER EXTENSION my_ext UPDATE TO '2.0';

注意七：pg_available_extensions 不含权限属性，别去猜。

sql
-- 错误！这三列不存在
SELECT name, trusted, relocatable FROM pg_available_extensions;
-- ERROR:  column "trusted" does not exist

正确姿势是查 pg_available_extension_versions。

注意八：在 template1 里装第三方扩展有全局风险。

sql
-- 一旦该扩展的二进制被卸载，所有 CREATE DATABASE 都会失败
\c template1
CREATE EXTENSION postgis;  -- 不推荐

推荐只在 template1 放稳定的小扩展（hstore、pg_trgm），第三方大扩展按库单独装。

注意九：pg_extension_config_dump() 只能在安装脚本里用。

sql
-- 运行时报错
SELECT pg_extension_config_dump('my_table', '');
-- ERROR:  pg_extension_config_dump() can only be called from an extension script

注意十：不要手工改动扩展创建的对象。 改函数体、改类型定义后，ALTER EXTENSION ... UPDATE 会覆盖你的修改，甚至因校验失败而中断：

sql
-- 不推荐：手工改扩展函数
CREATE OR REPLACE FUNCTION hstore_to_json(hstore) ...;  -- 会被升级覆盖

需要定制就自己包一层函数，放在业务 schema 里。

最佳实践 ​

  1. 部署脚本一律用 CREATE EXTENSION IF NOT EXISTS，让脚本可重复执行，这是可重入部署的最低要求。

  2. 为扩展建立专属 schema（如 extensions），避免污染 public，也便于 pg_dump --schema=public 时保持干净输出。

  3. 扩展由 DBA 用超级用户预装，业务账号只授予 USAGE / EXECUTE，遵循最小权限原则。

  4. 装前先查 pg_available_extension_versions，确认目标扩展的 trusted / superuser / relocatable 属性符合预期，避免装完才发现不能搬 schema。

  5. 需要 shared_preload_libraries 的扩展，把配置变更纳入部署流程：先改 postgresql.conf → 重启 → 再 CREATE EXTENSION，并在监控里确认钩子已生效。

  6. 卸载扩展优先用默认的 RESTRICT，让数据库替你拦住依赖；只有在明确知道会删掉什么时才用 CASCADE。

  7. 大版本升级前导出扩展清单，升级后逐项比对 extname 与 extversion，把扩展包安装纳入升级 runbook。

  8. 为扩展版本升级预留演练环境，尤其第三方扩展（PostGIS / TimescaleDB / Citus）常有跨大版本的兼容性变化。

  9. 监控「可升级的扩展」，把 pg_extension.extversion <> pg_available_extensions.default_version 做成巡检项，避免长期落后于安全修复。

  10. 不要在扩展创建的对象上做二次修改，需要定制时用业务 schema 里的包装函数。

  11. 在 pg_upgrade 前确认新集群已安装所有扩展二进制，并通过 pg_available_extensions 逐项核对，避免升级到一半发现 could not open extension control file。

  12. 把扩展清单写进项目的 database migration 里（如 Flyway / Liquibase 的 V__extensions.sql），让「装了哪些扩展」成为可追踪、可评审的变更。

下一节

下一篇 常用扩展（pg_stat_statements / pgcrypto） 会逐个介绍 contrib 包里最常用的扩展，从 pg_stat_statements 的语句统计到 pgcrypto 的加密函数，帮你按场景快速选型。

参考链接 ​