PostgreSQL 11数据库管理员指南第三章：数据库配置

Alan

5 年前

本书目录请见：PostgreSQL 11数据库管理员指南

我经常被问到有关PostgreSQL参数设置的问题。每个人都很忙，大部分人都希望5分钟就知道如何使用。这正是本指南存在的目的，我们会尽力。

有些人认为存在魔法设置可以提升性能，花费很多小时来拼接不同书本的页面来费力的收集。另外的一些人很轻松，因为他们在一个地方找到网站讲解所有内容，并知道他们的数据配置没有问题。

对大部分，设置很容易掌握。找到最好的设置很困难，最优的设置可能随时间会发生改变。本章主要有关了解如何、何时以及在休息修改参数设置。

本章中将讲解如下小节：

阅读精装手册（RTFM）
规划新数据库
在程序中修改参数
找到当前的配置设置
哪些参数是非默认设置？
更新参数文件
为指定用户组设置参数
基本服务配置清单
为PostgreSQL添加外部模块
使用已安装模块
管理已安装插件

阅读精装手册（RTFM）

阅读精装手册（RTFM）通常用于（无礼地）表达别烦我，我很忙或者用于更强形式的指责。奇怪的事是要求你阅读手册通常是很好的建议。不要逼迫提建议者，而是接受建议！要记住的最重要的点是你应该参考所运行服务相匹配版本的手册。

PostgreSQL的手册编写得很好也很全面地涵盖了特定的主题。但是，它主要的失败在于没有以有助帮助人们学习PostgreSQL方式进行组织。它组织的视角是那些来查看具体技术点的人来决定他们所遇到的困难是否是一个用户错误。它有时回答了是什么？但很少回答为什么？也极少回答怎么做？

我帮助编写了PostgreSQL文档的一些小节，对于指引你去阅读文档我并不感到尴尬。毕竟有很多可读之处是有用的。

如何实现…

每个PostgreSQL发行版的主文档位于：https://www.postgresql.org/docs/manuals/。

文档中最常访问的部分如下：

SQL命令参考，以下和客户端和服务端参考： http://www.postgresql.org/docs/current/interactive/reference.html
配置： http://www.postgresql.org/docs/current/interactive/runtime-config.html
函数：http://www.postgresql.org/docs/current/interactive/functions.html

你也可以自己获取一个手册的PDF版本，这样在很多情况下更易于搜索。千万不要打印！文档A4有超过2000页的大小的页面。

运行原理…

PostgreSQL文档采用标准能用标记语言（SGML）编写，类似但不同于XML。然后这些文件处理为通用HTML文件、PDF等等。这确保了所有格式都拥有完全相同的内容。然后，你可以选择所喜欢的格式，甚至可以编译为其它的格式，如EPUB、INFO等。

此外，PostgreSQL手册实际上是PostgreSQL源代码的一个子集，因此它与软件共同发展。它由开发出PostgreSQL相同的人进行编写，这让你更有理由去阅读它！

扩展知识…

更多信息可参见 http://wiki.postgresql.org。

很多发行版提供安装HTML文件静态版本的包。例如，在Debian和Ubuntu中，最新稳定版PostgreSQL的文档名称为postregsql-doc-11。

规划新数据库

规划一个新数据库会是一项很吓人的工作。很易于陷入巨大的负担，因此这里会展示一些规划的看法。也会有陷入草率的可能，觉得所知的内容就是未来所需要考虑的全部了。

准备工作

你准备好了。不要等待别人告诉你做什么。如果还没有人告诉你要求，那么写下你所认为的要求，清楚地将它们标记为假定而非要求，千万不混淆这两者。

反复迭代直至获取认同，然后开始构建原型。

如何实现…

写下包含如下项的文档：

数据库设计 – 规划数据库设计
计算初始数据库大小
事务分析 – 如何访问数据库？
查看最常访问的路径（例如，查询）。
响应时间的要求是什么？
硬件配置
初始性能考虑 – 是否所有数据放到可用RAM中？
选择操作系统和文件系统类型。
如何对磁盘分区？
本地化规划
决定服务端编码、地区和时区
权限和安全规则
确定客户端系统并指定所需驱动
根据访问控制规划创建角色
指定pg_hba.conf
监控 – 对你所使用的监控方案有没有PostgreSQL插件（通常有）？我们需要监控的具体业务指标有哪些？
维护规划 – 谁来保持其运行？如何保持？
可用性规则 – 考虑可用性的要求
checkpoint_timeout（有关这个参数的更多详情，请参见第十一章：备份和还原中理解和控制崩溃还原的一节）
规划备份机制并进行测试
高可用规划
如需，决定你需要哪种形式的拷贝

运行原理…

提前规划数据库的一个重要原因是事后改进非常困难。对于服务编码和地区尤其如此，这会导致事后修改存在宕机时间并花费大量精力。安全设置在系统上线后进行设置也会更加困难。

扩展知识…

提供规划总是有益的。你可能知道自己在干什么，但也许其他人并不知道。在做之前告诉其他人你的计划会避免时间的浪费。如何你尚不确定，那么构建一个原型来帮助你做决定。把管理框架看成一个开发任务。列出你还不知道的事，并逐一攻克。

本节特意保持了简短。每个人都有自己做事的方式，不要对如何做事规定的过于明确非常重要。如何你已经有规划，很棒！如果没有，思考你所需的内容、制作任务清单然后执行。

在程序中修改参数

PostgreSQL允许我们为每个会话或事务设置一些参数设置。

如何实现…

执行如下步骤来设置自定义参数设置：

可以在会话期间修改设置的值，如下：

SET work_mem = '16MB';

1

SET work_mem = '16MB';
然后值会被用作每个未来的事务。你也可以对当前事务进行修改：

SET LOCAL work_mem = '16MB';

1

SET LOCAL work_mem = '16MB';
该设置会在执行如下命令前有效：

RESET work_mem;

1

RESET work_mem;
此外也可以执行如下命令：

RESET ALL;

1

RESET ALL;

SET和RESET是可以通过任意界面执行的SQL命令。它仅用于PostgreSQL服务参数，但这并不意味着不会影响到整个服务。事实上，使用SET和RESET所做的修改仅用于当前会话。同时注意可能存在其它参数，如JDBC驱动参数，无法通过这种方式设置。参见第一章：迈出第一步中的连接PostgreSQL服务一节来获取有关这些参数的知识。

运行原理…

假设你在会话期间通过如下命令修改了设置的值：

SET work_mem = '16MB';

1	SET work_mem = '16MB';

那么以下命令会显示pg_settings目录视图：

postgres=# SELECT name, setting, reset_val, source FROM pg_settings WHERE
source = 'session';
 name | setting | reset_val | source
----------+---------+-----------+---------
 work_mem | 16384 | 4096 | session

postgres=# SELECT name, setting, reset_val, source FROM pg_settings WHERE

source = 'session';

name | setting | reset_val | source

----------+---------+-----------+---------

work_mem | 16384 | 4096 | session

在执行如下命令前会一直显示：

RESET work_mem;

1	RESET work_mem;

在执行之后，设置返回到reset_val并且源回归为默认：

 name | setting | reset_val | source
---------+---------+-----------+---------
work_mem | 4096 | 4096 | default

name | setting | reset_val | source

---------+---------+-----------+---------

work_mem | 4096 | 4096 | default

扩展知识…

也可以在事务期间修改设置的值，如下：

SET LOCAL work_mem = '16MB';

1	SET LOCAL work_mem = '16MB';

会产生如下的输出：

WARNING: SET LOCAL can only be used in transaction blocks SET

1	WARNING: SET LOCAL can only be used in transaction blocks SET

为理解警告的含义，我们来看pg_settings目录视图中的设置：

postgres=# SELECT name, setting, reset_val, source FROM pg_settings WHERE
source = 'session';
 name | setting | reset_val | source
----------+---------+-----------+---------
 work_mem | 4096 | 4096 | session

postgres=# SELECT name, setting, reset_val, source FROM pg_settings WHERE

source = 'session';

name | setting | reset_val | source

----------+---------+-----------+---------

work_mem | 4096 | 4096 | session

？参数设置发生了些什么？ SET LOCAL命令仅对它所执行的事务生效，本例中仅为SET LOCAL 命令。我们需要在事务区块中执行它来查看设置生效的状况，如下：

BEGIN;
SET LOCAL work_mem = '16MB';

1 2	BEGIN; SET LOCAL work_mem = '16MB';

以下是pg_settings目录视图中显示的内容：

postgres=# SELECT name, setting, reset_val, source
 FROM pg_settings WHERE source = 'session';
 name | setting | reset_val | source
----------+---------+-----------+---------
 work_mem | 16384 | 4096 | session

postgres=# SELECT name, setting, reset_val, source

FROM pg_settings WHERE source = 'session';

name | setting | reset_val | source

----------+---------+-----------+---------

work_mem | 16384 | 4096 | session

还应注意源的值为会话而不是你可能所期待的事务。

找到当前的配置设置

在某个时间点，你会问道，当前的配置设置是什么？

大部分设置都可以通过一种以上的方式修改，而有些方式不会影响到所有用户或所有会话，因此很有可能会产生混淆。

如何实现…

你的第一想法可能是查看postgresql.conf，它是配置文件，并在更新参数文件一节中进行了详细的讲述。它会起作用，但仅在只有一个参数文件的时候。如果有两个，那么你可能在读取错误的文件。怎么知道呢？因此谨慎和精确的方式是不要相信文件，而是要相信服务本身。

此外，在前一节在程序中修改参数中，我们学习到每个参数都有作用域来决定在何时进行设置。一些参数可通过postgresql.conf进行设置，而另一些可在稍后进行修改。因此配置设置的当前值可能随后会进行改变。

我们可以这样使用SHOW命令：

postgres=# SHOW work_mem;

1	postgres=# SHOW work_mem;

其输出如下：

work_mem
----------
4MB
(1 row)

work_mem

----------

4MB

(1 row)

但是，记住它会在其运行时报告当前的设置，但在其它地方可能会被修改。

另一种查找当前设置的方式是访问名为pg_settings的PostgreSQL日志视图：

postgres=# \x
Expanded display is on.
postgres=# SELECT * FROM pg_settings WHERE name = 'work_mem';
[ RECORD 1 ] --------------------------------------------------------
name | work_mem
setting | 4096
unit | kB
category | Resource Usage / Memory
short_desc | Sets the maximum memory to be used for query workspaces.
extra_desc | This much memory can be used by each internal sort operation
and hash table before switching to temporary disk files.
context | user
vartype | integer
source | default
min_val | 64
max_val | 2147483647
enumvals |
boot_val | 4096
reset_val | 4096
sourcefile |
sourceline |

postgres=# \x

Expanded display is on.

postgres=# SELECT * FROM pg_settings WHERE name = 'work_mem';

[ RECORD 1 ] --------------------------------------------------------

name | work_mem

setting | 4096

unit | kB

category | Resource Usage / Memory

short_desc | Sets the maximum memory to be used for query workspaces.

extra_desc | This much memory can be used by each internal sort operation

and hash table before switching to temporary disk files.

context | user

vartype | integer

source | default

min_val | 64

max_val | 2147483647

enumvals |

boot_val | 4096

reset_val | 4096

sourcefile |

sourceline |

因此，你可以使用SHOW命令来获取一个设置的值，或者可以使用日志表来访问完整的详情。

每个配置文件的实际位置可以通过直接向PostgreSQL服务问询，如下例所示：

postgres=# SHOW config_file;

1	postgres=# SHOW config_file;

这将返回如下输出：

 config_file
------------------------------------------
 /etc/postgresql/11/main/postgresql.conf
(1 row)

config_file

------------------------------------------

/etc/postgresql/11/main/postgresql.conf

(1 row)

其它配置文件可通过查询类似的变量来定位，即hba_file和ident_file。

运行原理…

每个参数设置在各个会话中缓存，因此我们可以获取对参数设置的快速访问。这允许我们轻松访问参数设置。

记住所显示的值不一定是整体服务的设置。很多参数是具体到当前会话的。这与你通过很多其它类型的数据库软件不同，也非常有用。

哪些参数是非默认设置？

通常，我们需要查看所修改的参数或者我们的修改是否正确的生效了。

在前两节中，我们看到了参数可通过多种方式在不同作用域中进行修改。也学习到了如何查看一个参数的值或者获取参数的完整列表。

本节中，我们将展示如何使用SQL功能来仅列出那些与系统级默认值不同的当前会话的参数。

出于多个原因这个列表非常有价值。首先，它仅包含200多个可用参数，因此更为及时。同时很难记住我们过去的所有动作，尤其是在很长或很复杂的会话当中。

PostgreSQL还支持ALTER SYSTEM 语法，这会在下一节更新参数文件中进行讲解。从本节的视角看，该语法的行为对比其它设置关联命令相当的不同，它修改默认值，但不修改会话中的值。

如何实现…

我们编写列出所有参数值的SQL查询，不包含那些默认值或通过配置文件设置的值。

postgres=# SELECT name, source, setting
 FROM pg_settings
 WHERE source != 'default'
 AND source != 'override'
 ORDER by 2, 1;

postgres=# SELECT name, source, setting

FROM pg_settings

WHERE source != 'default'

AND source != 'override'

ORDER by 2, 1;

输出如下：

 name | source | setting
----------------------------+----------------------+-----------------
 application_name | client | psql
 client_encoding | client | UTF8
 DateStyle | configuration file | ISO, DMY
 default_text_search_config | configuration file | pg_catalog.english
 dynamic_shared_memory_type | configuration file | posix
 lc_messages | configuration file | en_GB.UTF-8
 lc_monetary | configuration file | en_GB.UTF-8
 lc_numeric | configuration file | en_GB.UTF-8
 lc_time | configuration file | en_GB.UTF-8
 log_timezone | configuration file | Europe/Rome
 max_connections | configuration file | 100
 port | configuration file | 5460
 shared_buffers | configuration file | 16384
 TimeZone | configuration file | Europe/Rome
 max_stack_depth | environment variable | 2048

name | source | setting

----------------------------+----------------------+-----------------

application_name | client | psql

client_encoding | client | UTF8

DateStyle | configuration file | ISO, DMY

default_text_search_config | configuration file | pg_catalog.english

dynamic_shared_memory_type | configuration file | posix

lc_messages | configuration file | en_GB.UTF-8

lc_monetary | configuration file | en_GB.UTF-8

lc_numeric | configuration file | en_GB.UTF-8

lc_time | configuration file | en_GB.UTF-8

log_timezone | configuration file | Europe/Rome

max_connections | configuration file | 100

port | configuration file | 5460

shared_buffers | configuration file | 16384

TimeZone | configuration file | Europe/Rome

max_stack_depth | environment variable | 2048

运行原理…

通过pg_settings，我们可以看到哪些参数没有默认值，以及当前值的源是什么。SHOW命令不会告诉我们参数是否设置为非默认值，然后也设置了sourcefile和sourceline。这些对于理解配置来自何处非常有用。

扩展知识…

pg_settings的setting字段显示当前值，但你也可以查看boot_val和reset_val参数。boot_val参数显示在PostgreSQL数据库集群在初始化时（initdb）分配的值，而reset_val显示若执行RESET命令会返回的参数。

max_stack_depth参数是一个例外，因为pg_settings描述它通过环境变量设置，虽然它实际上是由Linux和Unix系统中的ulimit -s来进行设置。max_stack_depth只需在Windows上直接设置即可。

时区设置也是通过操作系统环境获取，因此我们无需直接进行设置。它们在数据目录初始化时写入到postgresql.conf中，因此它们显示为配置文件。

更新参数文件

参数文件是用于为PostgreSQL服务定义参数值的主要位置。所有的参数可在参数文件即postgresql.conf中进行设置。还有其它两个参数文件：pg_hba.conf和pg_ident.conf。这两者都与连接和安全相关，因此我们会在随后的相应章节中进行讲解。

准备工作

在开始本节之前，我们需要定位到postgresql.conf文件，参见找到当前的配置设置一节。

如何实现…

一些参数仅在服务初次启动时生效。典型的例子可能是shared_buffers，它定义共享内存缓存的大小。很多参数可在服务仍在运行时进行修改。

在修改所要求参数之后，我们对服务执行reload命令，强制PostgreSQL重新读取postgresql.conf文件（及所有其它配置文件）。有很多实现的方式，取决于你使用的发行版本和操作系统。最常见的为使用与运行PostgreSQL服务进程相同的操作系统用户执行如下命令：

pg_ctl reload

1	pg_ctl reload

这假定使用默认数据目录，否则，你需要通过-D选项指定正确的数据目录。

如前面所述，Debian和Ubuntu有不同的多版本架构，因此应改为执行如下的命令：

pg_ctlcluster 11 main reload

1	pg_ctlcluster 11 main reload

在近期的发行版本中，你应当使用systemd如下：

sudo systemctl reload postgresql@11-main

1	sudo systemctl reload postgresql@11-main

小贴士：参见第四章：服务端控制中手动启动数据库服务一节查看更多有关通过systemd管理PostgreSQL的知识；同样在第四章：服务端控制中的重载服务配置文件一节展示了重载配置文件的更多方式。

一些其它参数要求重启服务来让修改生效，例如max_connections、listen_addresses等等。语句与重载操作非常相近，如下所示：

pg_ctl restart

1	pg_ctl restart

对于Debian和Ubuntu，使用如下命令：

pg_ctlcluster 11 main restart

1	pg_ctlcluster 11 main restart

通过system使用如下命令：

sudo systemctl restart postgresql@11-main

1	sudo systemctl restart postgresql@11-main

当然，重启对已有连接也会产生影响。参见第四章：服务端控制中的快速重启服务一节获取更多详情。

postgresql.conf文件通常为可简单修改的普通文本文件。大部分参数都在文件中列出，因此你可以进行搜索然后在对应的位置插入所需的值。

运行原理…

如果你在文件不同的地方两次设置相同的参数，会应用最后一次设置。如果在文件末尾处添加设置会带来很多困扰，因此不建议这么做。

一种持续的良好实现是与其它代码一样使用Git来对配置文件的修改进行版本管理。更好的方案是使用配置管理软件如Ansible，Chef或Puppet，而非直接编辑配置文件。

扩展知识…

postgresql.conf文件还支持include指令。这允许postgresql.conf文件引用其它文件，该文件又可以引用其它的文件，以此类推。如果不把它搞得过于复杂的话，这可能会有助于你更好地组织参数。

更多有关重载的知识，参见第四章：服务端控制中的重载服务端配置文件一节。

此外，你也可以通过会话直接修改存储在参数文件中的付下，语法如下：

ALTER SYSTEM SET shared_buffers = '1GB';

1	ALTER SYSTEM SET shared_buffers = '1GB';

该命令不会实际编辑postgresql.conf。而是在另一个名为postgresql.auto.conf的文件中编写新设置。效果是相同的，等更为安全。原始配置未被写入，因此在发生崩溃事件时也不会损坏。如果你过多地使用了ALTER SYSTEM命令的话，可以保持手动删除postgresql.auto.conf并根据所修改的参数重载配置或重启PostgreSQL。

ℹ️如果内存足够，PostgreSQL 11现在可支持多达7 TB的缓存。

为指定用户组设置参数

PostgreSQL支持很多为不同用户组定义参数设置的方式。这非常方便，克斯是对于管理具有不同要求的用户组时。

如何实现…

按照如下步骤根据要求对不同级别设置参数：

对saas数据中的所有用户，使用如下命令：

ALTER DATABASE saas SET configuration_parameter = value1;

1
2

ALTER DATABASE saas
SET configuration_parameter = value1;
对于连接任一数据库名为simon的用户，使用如下命令：

ALTER ROLE simon SET configuration_parameter = value2;

1
2

ALTER ROLE simon
SET configuration_parameter = value2;
此外，可以对用户仅在连接到某个具体数据库时设置参数，如下：

ALTER ROLE simon IN DATABASE saas SET configuration_parameter = value3;

1
2
3

ALTER ROLE simon
IN DATABASE saas
SET configuration_parameter = value3;

用户不会知道这些命令是针对他们运行的。这些是默认设置，大部分情况下可应用户要求重载为非默认值。

运行原理…

可以为以下设置参数：

数据库
用户（在PostgreSQL中也称为角色）
数据库和用户的组合

每个默认参数由其后的值进行重载。

以上三个SQL语句会应用如下修改：

若gianni连接saas数据库，会应用value1
若simon连接saas以外的数据库，会应用value2
若simon连接saas数据库，会应用value3

PostgreSQL实现的方式与用户在连接后手动执行等价的SET语句完全相同。

基本服务配置清单

PostgreSQL是为在共享系统中进行配置而出现的，但很用人希望在独立的数据库系统中进行运行。PostgreSQL项目希望确保PostgreSQL与其它服务端软件可很好地共同运行，并且不假定它拥有对全部服务端资源的访问。如果你作为系统管理员，知道该系统中没有运行其它重要的服务端软件，那么可以提升配置为更高的值。

准备工作

在开始之前，我们需要知道两组信息：

分配给PostgreSQL的物理内存（RAM）大小
我们将要使用PostgreSQL的应用的类型

如何实现…

如果你的数据库大于32 MB，那么将可能通过提升shared_buffers获取更好的性能。可以提升为一个大得多的值，但请记住内存溢出会导致很多问题。

例如，PostgreSQL可以在可用内存过小时将信息存储在磁盘上，它采用高级算法来区别对待每种状况，根据各种用例将每块数据放在磁盘或内存中。

另一方面，过多设置了可用内存会导致这种功能的混淆并产生次优的行为。例如，如果内存交换到磁盘中，那么PostgreSQL会低效地将所有数据看作RAM。另一种不幸的情况发生在Linux内存溢出（OOM）进程杀死器将由PostgreSQL所产生的某个进程杀死时。因此，最好保守一点。在postgresql.conf中设置一个较低的值，并逐步提高来确保通过每次修改能获得益处。

如果你提高了shared_buffers并且在一个非Windows服务器上运行的话，几乎肯定会需要提升SHMMAX系统参数值（或在其它平台上的其它参数）。

在Linux、macOS和FreeBSD中，你会需要通过如下值编辑/etc/sysctl.conf或使用sysctl -w：

在Linux中，使用kernel.shmmax=value
在macOS中，使用kern.sysv.shmmax=value
在FreeBSD中，使用kern.ipc.shmmax=value

扩展知识…

更多信息，可参见http://www.postgresql.org/docs/11/static/kernel-resources.html#SYSVIPC

例如，Linux中在/etc/sysctl.conf中添加如下行：

kernel.shmmax=value

1	kernel.shmmax=value

不要担心设置effective_cache_size。它比你可能会想象的重要性要低。无需花费太多精力在进行值的选择。

如果有很重的写入活动，可以设置wal_buffers为比默认值更高的值。事实上，wal_buffers的值是通过shared_buffers根据适用于大部分情况的规则自动设置的。但是，可以指定一个具体值来在规则不足以应对时的一部分情况下覆盖掉计算值。

如果在进行很重的写入活动以及/或大型数据加载，可以设置max_wal_size和min_wal_size为比默认值更大的值来避免过度频繁的检查点浪费 I/O。可能还需要设置checkpoint_timeout和checkpoint_completion_target。

PostgreSQL尽其所能来解耦查询延时和存储性能：同步的写入限于WAL目录，而大部分计算在内存缓存中进行。但是，有时查询会在返回前会需要使用到磁盘（例如，读取尚未缓存的数据），这意味着更少的检查点实际上会提升查询延时。

如果你的数据库有大量的查询，可能会需要设置work_mem为大于默认值的值。但是，别忘记这种限制在查询方案中分别应用于各个节点，因此会存在过度分配内存的风险，以及此前所讨论过的各种问题。

请确保开启autovacuum，除非有关闭的足够原因，大部分人都不需要关闭。参见后续章节有关autovacuum的更多信息，具体来说是第九章：常规维护。

保留设置为现有设置。不要过度的调整去获取正确的设置。可以在后面进行大部分的修改，这样可以采取迭代的方式来进行提升。

同时请记住不要去动fsync参数。它可以保证程序的安全。

为PostgreSQL添加外部模块

PostgreSQL的另一个优势是其可扩展性。它也是在上世纪80年代刚开始设计PostgreSQL时的目标。现在的PostgreSQL 11中，有大量插入核心PostgreSQL服务的附加模块。

在很多种类的附加模块，如下：

附加函数
附加数据类型
附加运算符
附加索引类型

ℹ️注意很多工具和客户端界面都无需特别的安装即可使用PostgreSQL。这里我们在讨论扩展和更改服务端SQL标准语法、函数和行为之外的行为的模块。

让模块可使用的过程实际上有两步。首先，在系统中安装模块文件来将其添加到数据库服务端。然后，连接想要使用模块的一个或多个数据库，并创建所需的对象。第一步来本节中进行了讨论，第二步请参见下一节使用已安装模块。

在本书，我们将像PostgreSQL文档中那样将插件和模块用作近义词。但是注意管理插件的SQL的命令，我们在下一节中将会讨论，如下：

CREATE EXTENSION myext;

ALTER EXTENSION myext UPDATE;

具体来说，无法使用CREATE MODULE这类的命令。

准备工作

首先，需要选择相应的模块来进行安装。

PostgreSQL的完整、自动化包管理系统还未完成，因此你需要在多处查找可用的模块，如下：

Contrib：PostgreSQL的内核有很多函数。还存在一个针对插件模块（称为contrib模块）的官方版块。在数据库服务中已存在这些模块，但并不会对每个数据库进行开启，因此有些用户可能不需要它们。在PostgreSQL 11中，我们有45个这种模块。文档请见http://www.postgresql.org/docs/11/static/contrib.html。
PGXN：这是PostgreSQL的插件网络，是针对共享PostgreSQL插件的中央分发系统。这网站从2010年开始成为致力于共享插件文件的仓库。截至2018年11月，有来自317个不同作者的279个插件。可访问http://pgxn.org/了解更多详情。
独立项目：这些是大型外部项目，如PostGIS，提供广泛和复杂的PostgreSQL模块。更多信息请见http://www.postgis.org/。

如何实现…

有很多方式来让附加模块在数据库服务中可用，如下：

使用软件安装包
通过PGXN安装
通过下载包手动安装
通过源代码安装

通常，具体的模块可通过一种以上的方式获取，用户可根据喜爱的方式选择，如同PostgreSQL自身一样，可以通过不同的流程下载并进行安装。

使用软件安装包安装模块

一些模块像其它你希望在服务器上安装的软件可以获取的方式一样。所有主流的Linux发行版本提供大部分流行的模块，如PostGIS、SkyTools、内核中分发以外的过程语言等等。

如果使用独立的安装包应用，模型有时可以在安装时添加，例如，一键安装包或Linux发行版中rpm、apt-get和YaST这样的工具。在需要某个模块时PostgreSQL也只可以按照同样的流程。我们将实现描述这个用例，非常普遍。

例如，假设你需要管理一组Debian包文件并且有一个任务是选择其中一个包的最新版本。你开始构建一个数据库来记录所有包文件。很显然，需要存储每个包的版本号。但是Debian的版本号要比普通电话号码复杂的多。例如，在Debian笔记本，我当前有一个11.1-1.pgdg90+1版本的PostgreSQL客户端安装包。虽然很复杂，但这个字符串遵循一个既定的规格，包含很多比特的信息，包括如何对比两个版本来决定哪个版本更老。

国为本节讨论如何通过自定义数据类型和运算符扩展PostgreSQL，你可能已经猜到了我现在会考虑为Debian版本号使用自定义数据，来让其有能力理解Debian版本号格式，对版本号排序，在更定组中选择最新版本号，诸如此类。其实已经有人创建了所需要的PostgreSQL数据类型，附带了有用的内容：比较运算符、输入/输出函数、索引支持、最大/最小计算。所有这个打包为PostgreSQL插件，以及一个Debian包（不出所料），因此仅需使用apt-get, aptitude或synaptic等Debian工具安装postgresql-11-debversion。在我的笔记本上，形成了如下的命令：

apt-get install postgresql-11-debversion

1	apt-get install postgresql-11-debversion

这会下载所需的包并在合适的位置解包所有文件，让其在我的PostgreSQL服务中可用。

通过PGXN安装模块

PostgreSQL插件网络（PostgreSQL Extension Network），简称PGXN，是一个2010末启动的网站（http://pgxn.org），旨在为PostgreSQL插件库提供一个中央分发系统。任何人都可以注册并上传自己的模块、包来作为插件存档。该网站允许你通过搜索界面或包和用户名目录浏览可用的插件和它们的版本。

简单的方式是使用名为pgxnclient的命令行工具。可以在大部分系统中进行轻松的安装，参见PGXN网站来了解如何实现。其用途是与PGXN进行交互交处理管理任务，如浏览可用插件、下载安装包、编译源码、在相应位置安装文件以及删除已安装包文件。此外，你可以通过网站下载插件文件并将它们按照安装指南放到对应的位置。

PGXN和官方仓库不同，因为其目的不同。官方仓库通常仅包含久经验证的插件，因为它仅接受一定量运行和测试后的新软件。而任何人都可以注册一个PGXN账号并上传自己的插件，因在除了要求必须有开源证书和极少的几个文件外并不对插件做任何过滤。

通过源代码安装模块

在很多情况下，有用的模块可能不存在完整的包装。在这种情况下，你需要手动安装该模块。这并不太困难，并且是一个不错的练习，有助于你理解所发生的事情。

每个模块会有不同的安装要求。通常安装模块有两个方面，如下：

构建库（仅针对带有库的模块）
在相应的位置安装模块

需要安装相应模块的指南来构建所需的库。然后安装就很直接了，通常会有相应已为make工具准备好的配置文件，因此仅需输入如下命令：

make install

1	make install

每个文件会被拷贝到对应的目录。不要忘记通常你需要是超级用户才能在系统目录中安装文件。

一旦在目录中获取到PostgreSQL所需的库文件，它会在function请求时自动加载。auto_explain等模块并未提供额外的用户定义函数，因此它们不会自动加载，那会需要超级用户手动执行LOAD语句。

运行原理…

PostgreSQL可以如下方式动态加载库：

在会话中显式使用LOAD命令
服务启动时在postgresql.conf 中使用shared_preload_libraries参数
在会话开始时，对指定用户使用local_preload_libraries参数，由ALTER ROLE设置

PostgreSQL的函数和对象可以引用这些库中的代码，允许插件与运行服务进程紧密绑定。这种紧密绑定让这个方法甚至适用于高性能应用，对额外提供的功能和原生功能并不存在明显差别。

使用已安装模块

本节中，我们将讲解如何启用已安装模块，这样它可以在具体的数据库中使用。额外的类型、函数等仅在我们执行这些步骤的数据库中存在。

ℹ️虽然大部分模块要求有这一流程，但实际存在一些例外。例如前面提到的auto_explain模块，它与PostgreSQL一并分发，并不会创建函数、类型或运算符。要使用它，，你必须使用LOAD命令加载其对象文件。那时所有超过所配置阀值长度的语句会与它们的执行计划一并被记录。本节其它部分会讲解所有其它插件。它们不要求使用LOAD语句，因为PostgreSQL在需要时会自动加载相关的库。

我们在前一节，为PostgreSQL添加外部模块中提到，特别包装的模块称为插件。它们可以通过指定的SQL命令进行管理。

准备工作

假设你选择了为系统安装了一个可用的模块（参见前一节为PostgreSQL添加外部模块），那么只需要知道插件的名称即可。

如何实现…

每个插件有一个独立的名称，因此只需执行如下命令：

CREATE EXTENSION myextname;

1	CREATE EXTENSION myextname;

这会自动在当前数据库中创建所有的所需对象。

出于安全考虑，需要使用数据库超级用户来执行。例如，如果你希望安装dblink插件，输入如下命令：

CREATE EXTENSION dblink;

1	CREATE EXTENSION dblink;

运行原理…

在执行CREATE EXTENSION 命令时，数据库服务会在SHAREDIR/extension目录中查找名为EXTNAME.control的文件。该文件告知PostgreSQL一些插件的属性，包含描述、一些安装信息以及默认插件版本号（与PostgreSQL版本号无关）。然后会在单个事务中执行一个创建脚本，因此在失败时数据库不会做更改。数据库服务还会在目录表中记录下插件名称及所有属于它的对象。

管理已安装插件

在前面的两节中，我们展示了如何在PostgreSQL中安装外部模块来增强其功能。

本节中，我们将展示插件框架所提供的更多功能。

如何实现…

以下是管理插件的步骤：

首先，我们列出所有可用插件：

postgres=# \x on
Expanded display is on.
postgres=# SELECT *
postgres-# FROM pg_available_extensions
postgres-# ORDER BY name;
-[ RECORD 1 ]-----+------------------------------------------------
--
name | adminpack
default_version | 2.0
installed_version |
comment | administrative functions for PostgreSQL
-[ RECORD 2 ]-----+------------------------------------------------
--
name | pg_stat statements
default_version | 1.6
installed_version |
comment | track execution statistics of all SQL statements executed
(...)

postgres=# \x on

Expanded display is on.

postgres=# SELECT *

postgres-# FROM pg_available_extensions

postgres-# ORDER BY name;

-[ RECORD 1 ]-----+------------------------------------------------

name | adminpack

default_version | 2.0

installed_version |

comment | administrative functions for PostgreSQL

-[ RECORD 2 ]-----+------------------------------------------------

name | pg_stat statements

default_version | 1.6

installed_version |

comment | track execution statistics of all SQL statements executed

(...)

具体来说，如果安装了dblink插件，那么我们会这样进行记录

-[ RECORD 10 ]----+------------------------------------------------
--
name | dblink
default_version | 1.2
installed_version | 1.2
comment | connect to other PostgreSQL databases from within a database

-[ RECORD 10 ]----+------------------------------------------------

name | dblink

default_version | 1.2

installed_version | 1.2

comment | connect to other PostgreSQL databases from within a database

此时我们可以在dblink插件中列出所有对象，如下：

postgres=# \x off
Expanded display is off.
postgres=# \dx+ dblink
 Objects in extension "dblink"
 Object Description
-------------------------------------------------------------------
--
 function dblink_build_sql_delete(text,int2vector,integer,text[])
 function
dblink_build_sql_insert(text,int2vector,integer,text[],text[])
 function
dblink_build_sql_update(text,int2vector,integer,text[],text[])
 function dblink_cancel_query(text)
 function dblink_close(text)
 function dblink_close(text,boolean)
 function dblink_close(text,text)
(...)

postgres=# \x off

Expanded display is off.

postgres=# \dx+ dblink

Objects in extension "dblink"

Object Description

-------------------------------------------------------------------

function dblink_build_sql_delete(text,int2vector,integer,text[])

function

dblink_build_sql_insert(text,int2vector,integer,text[],text[])

function

dblink_build_sql_update(text,int2vector,integer,text[],text[])

function dblink_cancel_query(text)

function dblink_close(text)

function dblink_close(text,boolean)

function dblink_close(text,text)

(...)

对象作为插件的一部分进行创建并没有特别之处，只是它可以单独进行删除。这是为了防止你的误操作：

postgres=# DROP FUNCTION dblink_close(text);
ERROR: cannot drop function dblink_close(text) because extension dblink requires it
HINT: You can drop extension dblink instead.

postgres=# DROP FUNCTION dblink_close(text);

ERROR: cannot drop function dblink_close(text) because extension dblink requires it

HINT: You can drop extension dblink instead.

插件也可能会存在依赖。cube和earthdistance contrib 插件就是很好的示例，因此后者依赖于前者：

postgres=# CREATE EXTENSION earthdistance;
ERROR: required extension "cube" is not installed
HINT: Use CREATE EXTENSION ... CASCADE to install required extensions too.
postgres=# CREATE EXTENSION earthdistance CASCADE;
NOTICE: installing required extension "cube"
CREATE EXTENSION

postgres=# CREATE EXTENSION earthdistance;

ERROR: required extension "cube" is not installed

HINT: Use CREATE EXTENSION ... CASCADE to install required extensions too.

postgres=# CREATE EXTENSION earthdistance CASCADE;

NOTICE: installing required extension "cube"

CREATE EXTENSION

注意如何使用CASCADE来自动创建创建的插件所依赖的所有其它插件，HINT消息中进行了清晰的提示。

你可能也推理出在删除对象时也会像其它对象一样考虑到依赖：

postgres=# DROP EXTENSION cube;
ERROR: cannot drop extension cube because other objects depend on it
DETAIL: extension earthdistance depends on extension cube
HINT: Use DROP ... CASCADE to drop the dependent objects too.
postgres=# DROP EXTENSION cube CASCADE;
NOTICE: drop cascades to extension earthdistance
DROP EXTENSION

postgres=# DROP EXTENSION cube;

ERROR: cannot drop extension cube because other objects depend on it

DETAIL: extension earthdistance depends on extension cube

HINT: Use DROP ... CASCADE to drop the dependent objects too.

postgres=# DROP EXTENSION cube CASCADE;

NOTICE: drop cascades to extension earthdistance

DROP EXTENSION

运行原理…

pg_available_extensions系统视图在SHAREDIR/extension目录中为每个插件控制文件显示一行（参见使用已安装模块一节）。pg_extension目录表仅记录实际创建的插件。

psql命令行工具提供\d元命令来检查插件。它支持一个可选的加号（+）来控制过多的信息，以及一个限制插件名范围的可选模式。可以使用如下命令：

\dx+ db*

\dx+ db*

这将列出所有名称以db开始的名称及其所有对象。

CREATE EXTENSION命令创建所有属于给定插件的对象，然后记录pg_depend中每个插件对象的依赖。这样PostgreSQL才能确保你无法在未删除插件时删除该对象。

插件控制文件允许一个可选行，requires，为当前所依赖的一个或多个插件的名称。依赖的实现依然非常简单，例如，没有办法指定对其它指定版本号插件的依赖。

按照PostgreSQL的通用规则，CASCADE关键字告诉DROP删除所有依赖于cube的对象，本例中为earthdistance插件。

扩展知识…

另一个系统视图pg_available_extension_versions，显示所有对每个插件可用的版本。它在同时有多个插件版本可用时会很有价值，例如在准备插件升级的时候。

例如在已安装版本的更新版本在数据库服务中可用时，因安装已更新安装版本文件的发行版升级时，超级用户可通过执行如下命令来进行升级：

ALTER EXTENSION mytext UPDATE TO '1.1';

1	ALTER EXTENSION mytext UPDATE TO '1.1';

这假定插件的作者讲授了如何执行升级。

插件与逻辑备份及还原做了很好的交互，相关话题会在第十一章：备份和还原中进行讨论。作为示例，如果数据库中包含cube插件，那么你一定希望在dump文件中存在单行代码（CREATE EXTENSION cube），而不是多行代码去分别创建每个对象，那会很低效也很危险。

CREATE语句中使用CASCADE仅应用于插件，因为对于其它对象类型，依赖在对象元数据中没有预定义，仅在创建了具体的对象（如外键）后才存在。

要知道CREATE EXTENSION … CASCADE仅在所尝试安装的插件已放在相应的位置时才会生效。