扩展包机制
扩展包机制
扩展包(extension)是一种用于扩展 PostgreSQL 系统功能的插件机制, 该机制的前身被称为 "contrib".
对于一台 PostgreSQL 服务器来说, 并不是其中每个数据库都要安装全部的扩展包, 只有当某个数据库的确需要此扩展包提供的功能才应安装.
如果你的 PostgreSQL 服务器上的所有数据库都需要某些扩展包的功能, 那么可以新建一个模板数据库, 然后在此模板数据库中预先安装好这些扩展包. 这样后续以此模板新建的数据库都包含了这些扩展包, 避免没新建一个数据库就需要再安装一遍扩展包的麻烦.
建议定期检查并卸载掉已经不再需要的扩展包以避免系统过于臃肿, 因为有的扩展包需要占用很大的空间, 例如 PostGIS.
查看 pg_available_extensions.
postgres=# SELECT name, default_version, installed_version, left(comment, 30) As comment
postgres-# FROM pg_available_extensions
postgres-# WHERE installed_version IS NOT NULL
postgres-# ORDER BY name;
name | default_version | installed_version | comment
---------+-----------------+-------------------+------------------------------
plpgsql | 1.0 | 1.0 | PL/pgSQL procedural language
(1 行记录)
这表明, 仅安装了一个扩展包, 名为 plpgsql.
使用命令 \dx 或 \dx+ 查看已安装扩展包的详细描述.
postgres=# \dx plpgsql
已安装扩展列表
名称 | 版本 | 架构模式 | 描述
---------+------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 行记录)
postgres=# \dx+ plpgsql
对象用于扩展 "plpgsql"
对象描述
---------------------------------------
函数 plpgsql_call_handler()
函数 plpgsql_inline_handler(internal)
函数 plpgsql_validator(oid)
语言 plpgsql
(4 行记录)
如果直接输入 SELECT * FROM pg_available_extensions; 则列出所有可用的扩展包.
postgres=> SELECT * FROM pg_available_extensions;
name | default_version | installed_version | comment
--------------------+-----------------+-------------------+------------------------------------------------------------------------
amcheck | 1.4 | | functions for verifying relation integrity
autoinc | 1.0 | | functions for autoincrementing fields
bloom | 1.0 | | bloom access method - signature file based index
bool_plperl | 1.0 | | transform between bool and plperl
bool_plperlu | 1.0 | | transform between bool and plperlu
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
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
hstore_plperl | 1.0 | | transform between hstore and plperl
hstore_plperlu | 1.0 | | transform between hstore and plperlu
hstore_plpython3u | 1.0 | | transform between hstore and plpython3u
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
jsonb_plperl | 1.0 | | transform between jsonb and plperl
jsonb_plperlu | 1.0 | | transform between jsonb and plperlu
jsonb_plpython3u | 1.0 | | transform between jsonb and plpython3u
lo | 1.1 | | Large Object maintenance
ltree | 1.3 | | data type for hierarchical tree-like structures
ltree_plpython3u | 1.0 | | transform between ltree and plpython3u
moddatetime | 1.0 | | functions for tracking last modification time
pageinspect | 1.12 | | inspect the contents of database pages at a low level
pgcrypto | 1.3 | | cryptographic functions
pgrowlocks | 1.2 | | show row-level locking information
pgstattuple | 1.5 | | show tuple-level statistics
pg_buffercache | 1.5 | | examine the shared buffer cache
pg_freespacemap | 1.2 | | examine the free space map (FSM)
pg_prewarm | 1.2 | | prewarm relation data
pg_stat_statements | 1.11 | | 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 | | 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
pldbgapi | 1.1 | | server-side support for debugging PL/pgSQL functions
plperl | 1.0 | | PL/Perl procedural language
plperlu | 1.0 | | PL/PerlU untrusted procedural language
plpgsql | 1.0 | 1.0 | PL/pgSQL procedural language
plpython3u | 1.0 | | PL/Python3U untrusted procedural language
pltcl | 1.0 | | PL/Tcl procedural language
pltclu | 1.0 | | PL/TclU untrusted procedural language
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
system_stats | 3.0 | | EnterpriseDB system statistics for PostgreSQL
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
uuid-ossp | 1.1 | | generate universally unique identifiers (UUIDs)
xml2 | 1.1 | | XPath querying and XSLT
(61 行记录)
共计有 61 个可用的扩展包. 注意若 installed_version 为空, 则表示该扩展包未安装. 例如:
mydb=> \dx+ xml2
没有找到任何名称为 "xml2" 的扩展.
扩展包的安装
扩展包的安装
将扩展包安装到系统需要两个步骤.
- 首先, 下载安装包并安装到数据库服务器上;
- 其次, 将此扩展包安装到目标数据库中.
这两个步骤中的“安装”所指代的具体动作并不一样.
步骤一
很多库文件在 PostgreSQL 安装好后就有了, 比如 fuzzystrmatch.
如果没有, 则需要先下载该扩展包的安装文件以及该扩展包所依赖的库文件. 例如 postgis_tiger_geocoder 需要先安装 fuzzystrmatch.
然后将它们分别复制到操作系统的 bin 和 lib 文件夹, 同时把 SQL 脚本文件复制到 share/extension 文件夹(9.1版及之后版本)或者 share/contrib 文件夹(9.1版之前的版本).
步骤二
将扩展包安装到数据库中.
9.1版之前的版本
9.1版及之后的版本
只需使用 CREATE EXTENSION 命令就可以将扩展包安装到指定的数据库中.
mydb=> create extension fuzzystrmatch;
CREATE EXTENSION
mydb=> select * from pg_available_extensions
mydb-> where installed_version IS NOT NULL;
name | default_version | installed_version | comment
---------------+-----------------+-------------------+-----------------------------------------------------
fuzzystrmatch | 1.2 | 1.2 | determine similarities and distance between strings
plpgsql | 1.0 | 1.0 | PL/pgSQL procedural language
(2 行记录)
postgresql_book=# create extension adminpack;
閿欒: extension "adminpack" is not available
DETAIL: Could not open extension control file "C:/Program Files/PostgreSQL/17/share/extension/adminpack.con
trol": No such file or directory.
HINT: The extension must first be installed on the system where PostgreSQL is running.
postgres=# create extension adminpack;
ERROR: extension "adminpack" is not available
DETAIL: Could not open extension control file "/usr/share/postgresql17/extension/adminpack.control": No such file or directory.
HINT: The extension must first be installed on the system where PostgreSQL is running.
