启用扩展

如何通过 CREATE EXTENSION 在 PostgreSQL 数据库中真正启用扩展?

快速上手

PostgreSQL 扩展 安装完毕 后,用户可以通过 CREATE EXTENSION 命令启用扩展:

CREATE EXTENSION vector;      -- 安装向量数据库扩展(无需加载)
CREATE EXTENSION timescaledb; -- 安装时序数据库扩展(需要加载)

有的扩展插件有依赖的扩展,在这种情况下,您可以首先安装依赖的扩展,或者选择执行 CREATE EXTENSION CASCADE 命令,一次性安装所有依赖的扩展。

CREATE EXTENSION documentdb CASCADE; -- 安装 documentdb 扩展,并安装它所有依赖的扩展

您还可以在 CREATE EXTENSION 命令中指定安装扩展的模式与具体版本。


配置扩展

扩展(数据库逻辑对象)在逻辑上属于 PostgreSQL 数据库 的组成部分,因此在 Pigsty 中,您可以在 pg_databases 中指定要在数据库中启用/创建的扩展。

pg_databases:
  - name: postgres
    baseline: supabase.sql
    schemas: [ extensions ,auth ,realtime ,storage ,graphql_public ,supabase_functions ,_analytics ,_realtime ]
    extensions:                                 # 这里定义了在 postgres 数据库中 创建启用 的扩展列表
      - { name: pgcrypto  ,schema: extensions } # 加密函数
      - { name: pg_net    ,schema: extensions } # 异步 HTTP
      - { name: pgjwt     ,schema: extensions } # PostgreSQL 的 JSON Web Token API
      - { name: uuid-ossp ,schema: extensions } # 生成通用唯一标识符 (UUID)
      - { name: pgsodium        }               # pgsodium 是 PostgreSQL 的现代加密库
      - { name: supabase_vault  }               # Supabase 保险库扩展
      - { name: pg_graphql      }               # pg_graphql: GraphQL 支持
      - { name: pg_jsonschema   }               # pg_jsonschema: 验证 JSON 模式
      - { name: wrappers        }               # wrappers: 外部数据包装器集合
      - { name: http            }               # http: 允许在数据库内检索网页
      - { name: pg_cron         }               # pg_cron: PostgreSQL 的任务调度器
      - { name: timescaledb     }               # timescaledb: 支持时间序列数据的可扩展插入和复杂查询
      - { name: pg_tle          }               # pg_tle: PostgreSQL 的可信语言扩展
      - { name: vector          }               # pgvector: 向量相似性搜索
      - { name: pgmq            }               # pgmq: 类似 AWS SQS 和 RSMQ 的轻量级消息队列

在这里,数据库对象中的 extensions 是一个列表,每个元素可能是:

  • 一个扩展名,简单的字符串,例如 vector
  • 一个字典,可能包含以下字段:
    • name: 唯一的 必选字段,指定扩展名,注意扩展名有可能不同于扩展包名
    • schema :指定扩展安装的模式,默认是当前数据库超级用户搜索路径中的第一个模式,通常是默认的 public
    • version :指定扩展的版本,默认是最新版本

如果数据库没有创建,在这里定义的扩展会在通过 Pigsty 创建集群创建数据库 的时候被自动启用。

如果数据库已经启用并投产,我建议您通过标准的数据库模式变更流程(Schema Migration)来管理扩展的启用。 您可以在 Pigsty 配置文件中记录相应的修改,便于日后迁移等用例。


默认启用的扩展

Pigsty 默认会为托管的 PostgreSQL 数据库安装一些默认扩展,这些扩展会被创建至默认的 template1 模板数据库与 postgres 数据库中。 任何新建的数据库都会继承 template1 中的扩展配置,因此您无需额外配置,新建的数据库也会拥有这些扩展。

您可以通过修改 pg_default_extensions 参数,来修改全局默认安装启用的扩展,以及它们安装的位置。

所有在本参数重默认 启用 的扩展都是 PostgreSQL 自带的第一方 Contrib 扩展,唯一的例外是 pg_repack 扩展,它是一个来自 PGDG 的第三方扩展。 pg_repack 对于 PostgreSQL 表膨胀治理 非常重要,因此 Pigsty 默认会默认安装它并在所有数据库中默认启用。

pg_default_extensions:
  - { name: pg_stat_statements ,schema: monitor }
  - { name: pgstattuple        ,schema: monitor }
  - { name: pg_buffercache     ,schema: monitor }
  - { name: pageinspect        ,schema: monitor }
  - { name: pg_prewarm         ,schema: monitor }
  - { name: pg_visibility      ,schema: monitor }
  - { name: pg_freespacemap    ,schema: monitor }
  - { name: postgres_fdw       ,schema: public  }
  - { name: file_fdw           ,schema: public  }
  - { name: btree_gist         ,schema: public  }
  - { name: btree_gin          ,schema: public  }
  - { name: pg_trgm            ,schema: public  }
  - { name: intagg             ,schema: public  }
  - { name: intarray           ,schema: public  }
  - { name: pg_repack } # <-- 唯一默认启用的三方扩展

在 Pigsty 的设计中,与监控相关的扩展会被创建至 monitor 模式中,其他功能特性性质的扩展会被创建至 public 模式中。

此外,向量数据库扩展 pgvector 有着特殊地位,它在 Pigsty 中会默认 安装,并在演示用的 meta 数据库中默认启用,作为演示之用。

最后,用于实现 CDC 变更数据捕获 的关键扩展 wal2json 扩展也会默认安装,但因为这是一个“无需启用”的逻辑解码插件,因此不会出现在 pg_default_extensions 中。


无需启用的扩展

并非所有扩展都需要通过 CREATE EXTENSION 命令启用。

从原理上来说:PostgreSQL 的扩展通常由以下三部分组成:

  • 控制文件:包含关键元数据,必须
  • SQL文件:包含 SQL 语句,可选
  • 库文件:包含二进制动态连接库(.so, .dylib, .dll),可选

对于扩展来说,SQL 文件是可选项,因此如果扩展没有提供 SQL 文件,则通常不需要执行 CREATE EXTENSION 命令。

LOAD \ DDL 需要 CREATE EXTENSION 不需要 CREATE EXTENSION
需要动态加载 使用 Hook 的扩展 无头扩展
不需要动态加载 不用 Hook 的扩展 逻辑解码输出插件

例如提供 CDC 抽取能力的 wal2json 扩展,提供慢查询统计能力的 pg_stat_statementsauto_explain 扩展。 它们只有共享库文件和扩展 .control 文件,没有 SQL 文件,因此不需要/也无法通过 CREATE EXTENSION 命令启用。

请注意,没有 CREATE EXTENSION 命令,不影响扩展是否需要 LOAD 命令。一些扩展虽然没有 SQL/DDL,但仍然需要 显式加载 ,比如一些安全审计相关的扩展。


无需启用的扩展列表

以下是所有无需 DDL 的扩展列表:

扩展名 扩展包名 分类 说明
plan_filter pg_plan_filter FEAT 使用执行计划代价过滤阻止特定查询语句
pg_checksums pg_checksums ADMIN 在离线模式下激活/启用/禁用数据库集群的校验和功能
pg_crash pg_crash ADMIN 向数据库进程随机发送信号模拟故障
safeupdate safeupdate ADMIN 强制在 UPDATE 和 DELETE 时提供 Where 条件
basic_archive basic_archive ADMIN 归档模块样例
basebackup_to_shell basebackup_to_shell ADMIN 添加一种备份到Shell终端到基础备份方式
bgw_replstatus bgw_replstatus STAT 用于汇报本机主从状态的后台工作进程
pg_relusage pg_relusage STAT 打印查询引用的表与列
auto_explain auto_explain STAT 提供一种自动记录执行计划的手段
passwordcheck_cracklib passwordcheck SEC 使用cracklib加固PG用户密码
supautils supautils SEC 用于在云环境中确保数据库集群的安全
pg_snakeoil pg_snakeoil SEC PostgreSQL动态链接库反病毒功能
pgextwlist pgextwlist SEC PostgreSQL扩展白名单功能
sepgsql sepgsql SEC 基于SELinux标签的强制访问控制
auth_delay auth_delay SEC 在返回认证失败前暂停一会,避免爆破
passwordcheck passwordcheck SEC 用于强制拒绝修改弱密码的扩展
pg_statement_rollback pg_statement_rollback SIM 在服务端提供类似Oracle/DB2的语句级回滚能力
pg_failover_slots pg_failover_slots ETL 在Failover过程中保留复制槽
wal2json wal2json ETL 用逻辑解码捕获 JSON 格式的 CDC 变更
wal2mongo wal2mongo ETL 使用逻辑解码捕获MongoDB JSON格式的CDC变更
decoderbufs decoderbufs ETL 将WAL逻辑解码为ProtocolBuffer协议的消息
decoder_raw decoder_raw ETL 逻辑复制解码输出插件:RAW SQL格式
pgoutput pgoutput ETL PG内置的逻辑解码输出插件
test_decoding test_decoding ETL 基于SQL的WAL逻辑解码样例




最后修改 2025-03-07: routine update (2106723)