PostgreSQL 有很多外部模块可以加载，例如 dblink, pg_buffercache 等，在 9.1 版本以前，如果对应的 postgresql-contrib 已经安装，只需要将对应的 sql 文件导入到目标库即可，例如，要在 数据库 skytf 里安装 dblink 模块，只需要执行以下操作就行；

1
2

cd $PGHOME/share/contrib  
psql -d skytf -U postgresql -f dblink.sql

导入成功之后，那么 dblink 模块即加载成功。在 9.1 版本以后，将有些不同，模块加载环节 PostgreSQL 提供命令 “CREATE EXTENSION” 来替代以上操作。

手册上的解释

CREATE EXTENSION
Name
CREATE EXTENSION – install an extension
Synopsis
CREATE EXTENSION [ IF NOT EXISTS ] extension_name
[ WITH ] [ SCHEMA schema ]
[ VERSION version ]
[ FROM old_version ]Description

CREATE EXTENSION loads a new extension into the current database. There must not be an extension of the same name already loaded.
Loading an extension essentially amounts to running the extension’s script file. The script will typically create new SQL objects such as functions, data types, operators and index support methods. CREATE EXTENSION additionally records the identities of all the created objects, so that they can be dropped again if DROP EXTENSION is issued.
Loading an extension requires the same privileges that would be required to create its component objects. For most extensions this means superuser or database owner privileges are needed. The user who runs CREATE EXTENSION becomes the owner of the extension for purposes of later privilege checks, as well as the owner of any objects created by the extension’s script.

备注：上面的内容就不翻译了， 执行 “CREATE EXTENSION “ 时需要使用超级用户来执行。更多信息请查阅 http://www.postgresql.org/docs/9.1/static/sql-createextension.html

加载 dblink 模块

1
2
3
4
5

[postgres@pg1 ~]$ psql skytf  
psql (9.1beta3)  
Type "help" for help.
skytf=# create extension dblink;  
CREATE EXTENSION

备注：这个命令执行之后，即加载了 “/opt/pgsql/share/extension/“ 目录下的 dblnk 模块，而不需要手工指定SQL脚本。这是与之前版本的不同。

9.1 之前版本迁移到9.1 版本需要的操作

1

CREATE EXTENSION module_name FROM unpackaged;

备注：如果将9.1版本以下的数据库迁移到 9.1 版本，并且老库使用了外部模块，那么在数据库迁移后，需要执行以上命令，上面这个命令的作用是更新老库加载的对象信息。如果这步不执行，老库里加载的模块对象在9 .1 版本上将不可用。

查询已安装的 extension

1
2
3
4
5
6
7

skytf=# select extname,extowner,extnamespace,extrelocatable,extversion from pg_extension;  
 extname | extowner | extnamespace | extrelocatable | extversion  
--------------------+----------+--------------+----------------+------------  
plpgsql | 10 | 11 | f | 1.0  
pg_stat_statements | 10 | 2200 | t | 1.0  
dblink | 10 | 2200 | t | 1.0  
(3 rows)

备注：可以通过查询 pg_extension 视图来查看数据库已安装的 extension, 关于所有 extension 信息可以查阅视图 pg_available_extensions 和 pg_available_extension_versions，这里不详细说明了。

pg_extension 字段解释