本章包含以下主题：

  • 扩展介绍
  • 管理扩展
  • 探索 PGXN 客户端
  • 安装扩展
  • 创建您自己的扩展

扩展介绍

无论叫extension，plug-in还是add-on，都是相对于core而言，都是用来做功能扩充的。这对于PG这样的开源系统尤为重要，因为这提供了一个标准，任何人都可以对PG做贡献，以构建良好的生态系统。

根据官方文档：

PostgreSQL 的设计目标是易于扩展。因此，加载到数据库中的扩展可以像内置功能一样运行。源代码附带的 contrib/ 目录包含几个扩展。其他扩展是独立开发的，例如 PostGIS。甚至 PostgreSQL 复制解决方案也可以由外部开发。例如，Slony-I 是一个流行的主/备复制解决方案，它是独立于核心项目开发的。

也就是说，扩展也分为官方提供（如contrib）和第三方提供（如PostGIS）。

这里提供了contrib中的部分扩展。其中的hstore在数据类型一章已经接触过。

$ rpm -q postgresql16-contrib
postgresql16-contrib-16.9-3PGDG.rhel9.x86_64

$ cd /usr/share/pgsql/extension

$ ls -1 *.control|sort|xargs -I '{}' basename '{}' .control|pr -6|more
2025-06-24 00:25                                                  Page 1


adminpack   dict_int    insert_user ltree_plpyt pg_stat_sta sslinfo
amcheck     dict_xsyn   intagg      moddatetime pgstattuple tablefunc
autoinc     earthdistan intarray    pageinspect pg_trgm     tcn
bloom       file_fdw    isn         pg_bufferca pg_visibili tsm_system_
btree_gin   fuzzystrmat jsonb_plper pgcrypto    plpgsql     tsm_system_
btree_gist  hstore      jsonb_plper pg_freespac postgres_fd unaccent
citext      hstore_plpe jsonb_plpyt pg_prewarm  refint      uuid-ossp
cube        hstore_plpe lo          pgrowlocks  seg         xml2
dblink      hstore_plpy ltree

其实，contrib中也包含少量实用程序，详见这里：

$ rpm -ql postgresql16-contrib|grep oid2name
/usr/pgsql-16/bin/oid2name
/usr/pgsql-16/share/man/man1/oid2name.1

扩展生态系统

PGXN（PostgreSQL Extension Network），即 PostgreSQL 扩展网络，是开源 PostgreSQL 扩展库的中央分发系统。

根据文档，

PostgreSQL 的一个实用扩展通常包含多个 SQL 对象；例如，新的数据类型将需要新的函数、新的操作符，甚至可能还需要新的索引操作符类。将所有这些对象集中到一个包中，可以简化数据库管理。PostgreSQL 将这样的包称为扩展。要定义扩展，您至少需要一个包含用于创建扩展对象的 SQL 命令的脚本文件，以及一个指定扩展本身一些基本属性的控制文件。如果扩展包含 C 代码，通常还会有一个构建了 C 代码的共享库文件。获得这些文件后，只需一个简单的 CREATE EXTENSION 命令即可将这些对象加载到数据库中。
 
使用扩展的主要优势在于，与仅仅运行 SQL 脚本将一堆“松散”的对象加载到数据库中相比，PostgreSQL 能够理解扩展的对象是组合在一起的。

扩展组件

包括控制文件（后缀.control）和脚本文件（SQL文件）。扩展建立在 PostgreSQL 扩展系统 (PGXS) 之上。PGXS包含在postgresql-devel包中，如postgresql16-devel。

$ pg_config --pgxs
/usr/pgsql-16/lib/pgxs/src/makefiles/pgxs.mk
[postgres@ol9-vagrant ~]$ ls /usr/pgsql-16/lib/pgxs/src/makefiles/pgxs.mk
/usr/pgsql-16/lib/pgxs/src/makefiles/pgxs.mk

扩展安装在以下目录：

$ pg_config --sharedir
/usr/pgsql-16/share
控制文件

以hstore为例：

$ cat $(pg_config --sharedir)/extension/hstore.control
# hstore extension
comment = 'data type for storing sets of (key, value) pairs'
default_version = '1.8'
module_pathname = '$libdir/hstore'
relocatable = true
trusted = true

$ pg_config --libdir
/usr/pgsql-16/lib
脚本文件

以hstore为例：

$ ls $(pg_config --sharedir)/extension/hstore*sql
/usr/pgsql-16/share/extension/hstore--1.1--1.2.sql  /usr/pgsql-16/share/extension/hstore--1.4.sql       /usr/pgsql-16/share/extension/hstore_plperl--1.0.sql
/usr/pgsql-16/share/extension/hstore--1.2--1.3.sql  /usr/pgsql-16/share/extension/hstore--1.5--1.6.sql  /usr/pgsql-16/share/extension/hstore_plperlu--1.0.sql
/usr/pgsql-16/share/extension/hstore--1.3--1.4.sql  /usr/pgsql-16/share/extension/hstore--1.6--1.7.sql
/usr/pgsql-16/share/extension/hstore--1.4--1.5.sql  /usr/pgsql-16/share/extension/hstore--1.7--1.8.sql

$ cat $(pg_config --sharedir)/extension/hstore--1.7--1.8.sql
/* contrib/hstore/hstore--1.7--1.8.sql */

-- complain if script is sourced in psql, rather than via ALTER EXTENSION
\echo Use "ALTER EXTENSION hstore UPDATE TO '1.8'" to load this file. \quit

CREATE FUNCTION hstore_subscript_handler(internal)
RETURNS internal
AS 'MODULE_PATHNAME', 'hstore_subscript_handler'
LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

ALTER TYPE hstore SET (
  SUBSCRIPT = hstore_subscript_handler
);

-- Remove @ and ~
DROP OPERATOR @ (hstore, hstore);
DROP OPERATOR ~ (hstore, hstore);

管理扩展

扩展安装在数据库一级，当然你也可以安装在template1数据库中。

管理扩展的命令为：

CREATE|ALTER|DROP EXTENSION

每个扩展都由助记符和版本指定；如果没有指定版本，则使用可用的最新版。

💡 数据库集群范围的对象（例如数据库、角色和表空间）不能成为扩展成员，因为扩展仅在一个数据库中可见。

💡 表可以成为扩展的成员，但其子对象（例如索引）不直接被视为扩展的成员。

💡 模式可以属于扩展，但反之则不行：扩展本身具有非限定名称，并且不存在于任何模式内。

创建扩展

postgres=# create extension hstore;
CREATE EXTENSION

查看已安装的扩展

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

postgres=# SELECT x.extname, x.extversion, n.nspname
          FROM pg_extension x JOIN pg_namespace n
          ON n.oid = x.extnamespace;
 extname | extversion |  nspname
---------+------------+------------
 plpgsql | 1.0        | pg_catalog
 hstore  | 1.8        | public
(2 rows)

查找可用的扩展版本

postgres=# SELECT name, version
          FROM pg_available_extension_versions
          WHERE name = 'hstore';
  name  | version
--------+---------
 hstore | 1.4
 hstore | 1.5
 hstore | 1.6
 hstore | 1.7
 hstore | 1.8
(5 rows)

修改现有扩展

postgres=# drop extension hstore;
DROP EXTENSION

postgres=# create extension hstore with version '1.6';
CREATE EXTENSION

postgres=# alter extension hstore update to '1.8';
ALTER EXTENSION

也可以在扩展中添加和删除对象：

postgres=# create extension hstore;
CREATE EXTENSION
postgres=# \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           |          |

postgres=# alter extension hstore add table t1;
ALTER EXTENSION
postgres=# alter extension hstore drop table t1;
ALTER EXTENSION

删除现有扩展

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

postgres=# drop extension hstore;
DROP EXTENSION
postgres=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

探索 PGXN 客户端

pgxn客户端指出pgxn或pgxnclient，是PostgreSQL 扩展网络的命令行客户端，允许在 PostgreSQL 数据库中搜索、编译、安装和删除扩展。

在 Fedora Linux 和基于 Red Hat 的发行版上安装 pgxnclient

# if phthon3 not installed
sudo dnf install -y python3

# if pip3 not installed
sudo dnf install -y python3-pip

pip install pgxnclient

从源代码安装 pgxnclient

略。

pgxnclient 命令行界面

根据安装提示，首先要把pgxn加入路径：

export PATH=/usr/pgsql-16/bin:/var/lib/pgsql/.local/bin:$PATH

查看帮助：

$ pgxn
usage: pgxnclient [--version] [--help] COMMAND ...

Interact with the PostgreSQL Extension Network (PGXN).

optional arguments:
  --version  print the version number and exit
  --help     show this help message and exit

available commands:
  COMMAND    the command to execute. The complete list is available using `pgxn help --all`. Builtin commands are:
    check    run a distribution's test
    download
             download a distribution from the network
    help     display help and other program information
    info     print information about a distribution
    install  download, build and install a distribution
    load     load a distribution's extensions into a database
    mirror   return information about the available mirrors
    search   search in the available extensions
    uninstall
             remove a distribution from the system
    unload   unload a distribution's extensions from a database

安装扩展

以orafce（Oracle 的兼容性函数和包）为例。

$ pgxn search --ext orafce
orafce 4.14.4
    Oracle's compatibility functions and packages

通过 pgxnclient 安装扩展

# 安装 perl-IPC-Run，因为postgresql16-devel需要
sudo dnf config-manager --enable ol9_codeready_builder
dnf repolist
sudo dnf install perl-IPC-Run

# 安装 postgresql16-devel，因为其可以提供PGXS框架中的pgxs.mk
# 后续 pgxnclient 需要
# 这个包较大，安装时间稍长
sudo dnf install postgresql16-devel
ls /usr/pgsql-16/lib/pgxs/src/makefiles/pgxs.mk

# 安装以下包（具体哪些看报错），后续 pgxnclient 需要
sudo dnf install -y bison
sudo dnf install -y flex
sudo dnf install -y openssl-devel

# 最后安装extension，终于成功了，解决问题花了一小时
# 安装的过程稍长，因为需要编译
pgxn install orafce --stable --verbose --sudo

查看安装的扩展：

$ pgxn info orafce
name: orafce
abstract: Oracle's compatibility functions and packages
description: This module allows use a well known Oracle's functions and packages inside PostgreSQL
maintainer: Pavel Stehule <pavel.stehule@gmail.com>
maintainer: Takahiro Itagaki <itagaki.takahiro@gmail.com>
license: PostgreSQL: http://www.postgresql.org/about/licence
release_status: stable
version: 4.14.4
date: 2025-06-03T13:11:48Z
sha1: 115e15fa996dde0214e3b4bd39bfead77b99660b
provides: orafce: 4.14.4
runtime: recommends: PostgreSQL 17.0.0
runtime: requires: PostgreSQL 11.0.0
runtime: requires: plpgsql 0

手动安装扩展

相对于从源码安装，从网站（Github或PGXN）下载zip包，解压、编译、安装即可。

使用已安装的扩展

$ psql demo
psql (16.9)
Type "help" for help.

demo=# \dx
                           List of installed extensions
  Name   | Version |   Schema   |                   Description
---------+---------+------------+--------------------------------------------------
 hstore  | 1.8     | demo_new   | data type for storing sets of (key, value) pairs
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

demo=# create extension orafce;
CREATE EXTENSION
demo=# \dx
                                                  List of installed extensions
  Name   | Version |   Schema   |                                          Description
---------+---------+------------+-----------------------------------------------------------------------------------------------
 hstore  | 1.8     | demo_new   | data type for storing sets of (key, value) pairs
 orafce  | 4.15    | demo       | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

demo=# select * from oracle.dual;
 dummy
-------
 X
(1 row)

demo=# show search_path;
 search_path
-------------
 demo
(1 row)

demo=# SET search_path TO "$user", oracle;
SET
demo=# select * from dual;
 dummy
-------
 X
(1 row)
通过 pgxnclient 删除扩展
pgxn uninstall orafce --sudo --verbose
echo $?
删除手动编译的扩展

略。

创建你的自己的扩展

按照这篇文章Creating simple extensions for PostgreSQL做的。

创建扩展文件

获取共享目录：

$ pg_config --sharedir
/usr/pgsql-16/share

因此后续所有文件均放在/usr/pgsql-16/share/extension目录下。

export XNDIR=/usr/pgsql-16/share/extension

控制文件如下：

$ cat $XNDIR/my_cool_extension.control
# my cool extension control file
comment = 'my cool extension for providing a table with countries'
default_version = '1.0'
relocatable = false
trusted = true

脚本文件是通过pg_dump生成的，我们先准备数据文件。原文用的是github上的csv文件，网络有点问题，我拷贝到数据库服务器上。

create table countries ( id int primary key, name text, alpha2 text, alpha3 text );

copy countries 
           from program 'cat /var/lib/pgsql/test/countries.csv'
           with (header true, delimiter ',', format csv);

以下为生成脚本文件：

$ pg_dump --column-inserts --table=countries demo > my_cool_extension--1.0.sql

# 此目录需要root权限
$ sudo cp my_cool_extension--1.0.sql /usr/pgsql-16/share/extension

# 删掉表，因后续要安装
$ psql -c "drop table countries" demo
DROP TABLE

按照最佳建议，在文件首部加2行。目的就是防止psql执行此脚本（会退出），只应让CREATE EXTENSION执行：

$ cat $XNDIR/my_cool_extension--1.0.sql |head
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
\echo Use "CREATE EXTENSION my_coll_extension to load this file. \quit

--
-- PostgreSQL database dump
--

-- Dumped from database version 16.9
-- Dumped by pg_dump version 16.9

安装扩展

-- 这是从控制文件中查到的
demo=# select * from pg_available_extensions where comment like 'my cool%';
       name        | default_version | installed_version |                        comment
-------------------+-----------------+-------------------+--------------------------------------------------------
 my_cool_extension | 1.0             |                   | my cool extension for providing a table with countries
(1 row)

demo=# create extension my_cool_extension;
CREATE EXTENSION
demo=# \dx
                                   List of installed extensions
       Name        | Version |   Schema   |                      Description
-------------------+---------+------------+--------------------------------------------------------
 my_cool_extension | 1.0     | demo       | my cool extension for providing a table with countries
 plpgsql           | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

可以查到数据了：

demo=# select * from countries limit 5;
 id | name | alpha2 |   alpha3
----+------+--------+-------------
  4 | af   | afg    | Afghanistan
  8 | al   | alb    | Albania
 12 | dz   | dza    | Algeria
 20 | ad   | and    | Andorra
 24 | ao   | ago    | Angola
(5 rows)

创建扩展升级

可以加个函数，或者加新数据，本例是在表中新增一条数据。升级类似于打补丁，是增量的。

升级版（1.1）脚本文件：

$ cat $XNDIR/my_cool_extension--1.0--1.1.sql
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
\echo Use "ALTER EXTENSION my_coll_extension to load this file. \quit

insert into countries (id,name,alpha2,alpha3) values (-1,'my new country','aaa','aaa');

再将控制文件中的默认版本改为1.1：

$ cat my_cool_extension.control
# my cool extension control file
comment = 'my cool extension for providing a table with countries'
default_version = '1.1'
relocatable = false
trusted = true

此时可以查询到升级版的信息：

demo=# SELECT name, version
          FROM pg_available_extension_versions
          WHERE name ='my_cool_extension';
       name        | version
-------------------+---------
 my_cool_extension | 1.0
 my_cool_extension | 1.1
(2 rows)

demo=# select * from pg_available_extensions where name = 'my_cool_extension';
       name        | default_version | installed_version |                        comment
-------------------+-----------------+-------------------+--------------------------------------------------------
 my_cool_extension | 1.1             | 1.0               | my cool extension for providing a table with countries
(1 row)

demo=# \dx my_cool_extension
                                 List of installed extensions
       Name        | Version | Schema |                      Description
-------------------+---------+--------+--------------------------------------------------------
 my_cool_extension | 1.0     | demo   | my cool extension for providing a table with countries
(1 row)

demo=# \dx+ my_cool_extension
Objects in extension "my_cool_extension"
 Object description
--------------------
 table countries
(1 row)

执行扩展升级

升级成功：

demo=# alter extension my_cool_extension update;
ALTER EXTENSION

demo=# \dx my_cool_extension
                                 List of installed extensions
       Name        | Version | Schema |                      Description
-------------------+---------+--------+--------------------------------------------------------
 my_cool_extension | 1.1     | demo   | my cool extension for providing a table with countries
(1 row)

demo=# select * from countries where id = -1;
 id |      name      | alpha2 | alpha3
----+----------------+--------+--------
 -1 | my new country | aaa    | aaa
(1 row)

验证你的知识

  • 什么是extension？
  • 什么是pgxnclient命令？
  • 安装extension需要哪些文件？
  • 如何安装和升级extension。

参考文献

# postgresql # database
Logo
CSDN-OPC开发者社区

这里是“一人公司”的成长家园。我们提供从产品曝光、技术变现到法律财税的全栈内容，并连接云服务、办公空间等稀缺资源，助你专注创造，无忧运营。

更多推荐

cover

『低代码开发的终极形态：AI 驱动的可视化编程』

avatar CSDN-OPC开发者社区
cover

上下文工程完全指南：打造高确定性AI Agent的四大支柱（建议收藏）

avatar CSDN-OPC开发者社区

Python内存管理终极指南：优化大型数据集处理性能的5个技巧

Python内存管理在处理大型数据集时常常成为性能瓶颈，掌握有效的内存优化技巧能让你的程序运行速度提升数倍。在python-mastery课程中，我们深入探讨了如何通过不同的数据结构选择来显著降低内存使用。## 🚀 为什么Python内存管理如此重要？当处理像芝加哥公交系统数据（包含577,000多行记录）这样的大型数据集时，内存使用可能从12MB激增到50MB以上！这不仅仅是数字问题，

avatar CSDN-OPC开发者社区