PostgreSQL 11数据库管理员指南第四章：服务端控制

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

本章中，我们将讲解如下小节：

• 手动启动数据库服务端
• 安全快速地停止服务端
• 紧急情况下停止服务端
• 重载服务端配置文件
• 快速重启服务端
• 阻止新连接
• 限制每个用户仅一个会话
• 将用户推出系统
• 多租户设计决策
• 使用多模式（schema）
• 为用户分配私有数据库
• 在同一系统中运行多个服务端
• 设置连接池
• 通过相同主机和端口访问多个服务端

引言

PostgreSQL包含一组服务端进程，其组长名为postmaster。启动服务是创建这些进程的行为，而停止服务是终止这些进程的行为。

每个postmaster监听所定义端口号上的客户端连接请求。多个并发运行的postmaster间不能共享这一端口号。端口号常用于唯一标识特定的postmaster，因此也标识了它所主导的数据库服务端。

在启动数据库服务时，我们引用一个数据目录，其中包含我们数据库的主体和灵魂-或者至少说是数据。子表格空间可能会包含一些主数据目录之外的数据，因此数据目录仅仅是主中央位置，但并不是存储数据库服务数据的唯一位置。每个运行中的服务至少有一个数据目录，并且一个数据目录最多可拥有一个运行中的服务（或实例）。

要为数据库服务执行任意动作，我们必须知道该服务的数据目录。我们对数据库服务可执行的基本动作有启动和停止服务。我们也可以执行重启，但那只是先进行停止再进行启动。此外，我们可以重载服务，这表示我们可以重新读取服务的配置文件。

还应提到其它一些点。

PostgreSQL的默认端口号是5432。这已在互联网号码分配局（IANA）进行过注册，因此在大部分地方该端口都会为PostgreSQL保留。因为每个PostgreSQL服务要求一个独立的端口号，最普遍的惯例是为其它服务使用后续的号码，例如5433、5434等等。后续的端口号可能不会那么容易地由网络基础架构所识别，对于大型企业尤其是重视安全的企业中会让事情变得复杂。

端口号6432已在IANA为PgBouncer注册，这是一个在设置连接池一节中会讲解的连接池管理器。这最近才通过，很多安装使用非标准端口号如6543的原因是它们部署的要更早。

数据库服务端有时也被称为数据库集群。建议在日常中使用该词，因为这会让人们想到多节点，而一个系统中仅一个数据库服务。

手动启动数据库服务端

通常PostgreSQL服务会在系统开启时自动启动。你可以选择手动停止及启动服务，或者可能会出于各种运维原因启动或关闭服务。

准备工作

首先，你需要掌握服务和服务端之间的差别。服务端指数据库服务端及其进程。而服务是指操作系统对服务端调用所做的封装。服务端一开始在每个平台上运作的方式基本相同，而每个操作系统和发行版本有其自己对服务的概念。

此外，最近对服务的管理方式发生了变化，在出版本书的时候，大部分Linux发行版本使用systemd服务管理器。这表示你需要知道在使用哪个发行版及版本来找到本节中对应的变体。

通过systemd，PostgreSQL服务端进程由服务单元表示，它通过systemctl命令管理。systemd命令语法在所有发行版中相同，但不幸的是服务单元的名称却不同，它会需要根据你的发行版进行调整。

在其它情况下，你需要输入实际数据目录来作为命令行的一部分来启动服务端。更多有关查找可用目录路径的信息，请见第二章：探密数据库中的定位数据库服务端文件一节。

如何实现…

在每个平台上，有一个启动服务端的具体命令。

如果你在使用当代的Linux发行版本，可能使用的是systemd。这时，PostgreSQL可通过如下命令进行启动：

在将SERVICEUNIT替换为相应的systemd服务单元名后，这必须通过操作系统超级用户权限执行。

小贴士：systemctl必须保持使用操作系统超级用户有权限来执行。本书中会一直在systemctl调用前放上sudo。

有一些需要记住的点：

• 所执行命令仅在已由系统管理员授予了相应的sudo权限的用户执行时才有效。
• 如果命令通过超级用户执行，那么则不需要使用关键字sudo，但加上也没有坏处。

我们前面已经提到，服务单元名称取决于你所使用的发行版，如下：

• 在Ubuntu和Debian中，有一个名为下面这个的服务单元：
  
  postgresql@RELEASE-CLUSTERNAME

  1	postgresql@RELEASE-CLUSTERNAME

• 对于每个数据库服务实例，有另一名为postgresql的服务单元，可用于管理同时管理所有的数据库服务端。因此，可以执行如下的命令：
  
  sudo systemctl start postgresql

  1	sudo systemctl start postgresql

• 要启用所有可用的实例，并仅启动默认版本11的实例，使用如下命令：
  
  sudo systemctl start postgresql@11-main

  1	sudo systemctl start postgresql@11-main

• 默认Red Hat/Fedora包仅将该服务单元称为postgresql，因此可执行如下语句：
  
  sudo systemctl start postgresql

  1	sudo systemctl start postgresql

• PostgreSQL Yum仓库中的Red Hat/Fedora包创建一个名为postgresql–RELEASE的服务单元，因此我们可以像如下这样启动版本11：
  
  sudo systemctl start postgresql-11

  1	sudo systemctl start postgresql-11

  
  如同我们在前面所提，systemctl是systemd的一部分，仅在Linux中可用，并且通过被大多数近期的发行版本所使用。
  后面的命令可在没有systemd时使用。
• 在Debian和Ubuntu发行版中，你必须调用针对PostgreSQL的工具pg_ctlcluster，如下：
  
  pg_ctlcluster 11 main start

  1	pg_ctlcluster 11 main start

ℹ️这条命令在systemd可用时也同样有效，它将重定向启用请求到systemctl并在屏幕上打印消息，这样下次就会记得直接使用systemctl。

对于Red Hat/Fedora，可以使用这条命令：

对于Windows，命令如下：

对于Red Hat/Fedora，也可以使用如下命令：

这里PGDATA设置为数据目录路径。

实际上，这条命令在大部分发行版中都可以使用，包括macOS、Solaris和FreeBSD，但要记住以下几点：

• 在可能的情况下，推荐使用我们前面所描述的针对具体发行版的语句。
• 如其尚不在你的路径中的话可能需要对pg_ctl可执行命令指定完整路径。通常对于多版本目录模式如Debian/Ubuntu是如此的，这时发生版对应的脚本选择你的版本相应的可执行命令。

运行原理…

在Ubuntu/Debian中，pg_ctlcluster装饰器是允许多个服务端更容易同时存在的便利工具，尤其是在你有带有不同版本服务端时非常好。这一功能非常有用并且转换到了systemd身上，参见在服务单元名称中使用@的示例，其中@ 代表服务文件模板的使用。

另一个有意思的systemd功能是启用/禁用服务单元的功能，来指定是否在下一次开机时自动启动，句法如下：

这对于根据每个实例的目的设置相应的行为非常有用。

相似的功能在Ubuntu和Debian上通过start.conf 文件实现，它和其它配置文件放在一起（即放在相同目录中）。除了一些有信息的注释外它仅包含一个单词，含义如下：

• auto：服务会在开机时自动启动。这在创建新服务时是默认值。适用于频繁使用的服务端，如那些驱动线上服务或那些用于日常开活动的数据库。
• manual：服务端在开机时不会自动启动，但在通过pg_ctlcluster时会启动。这适合于很少使用的自定义服务端。
• disabled：服务不会启动。该设置仅用于防止误启动服务端。pg_ctlcluster封装器不会让你启动它，但有经验的用户可以轻松地绕过这一限制。

小贴士：如果你需要为一个不由pg_ctlcluster管理的服务端保留端口号，例如，在直接通过源代码编译时，那么可以通过设置start.conf 为disabled创建一个集群，并使用该端口。任意新服务端都会被分配不同的端口。

安全快速地停止服务端

有一些根据紧急级别停止服务端的模式。我们将根据每种模式效果的角度进行对比。

如何实现…

我们提供两种方法：通过systemd和不通过systemd。手动启动数据库服务端，我们将用于获取更多信息。例如，systemd服务单元在给定GNU/Linux发行版的给定数据库服务端中的具体名称是什么？

在使用systemd进，可以通过使用相应的systemd服务单元名称替换SERVICEUNIT来执行如下命令停止PostgreSQL：

如果systemd并不可用，并且使用的是Debian或Ubuntu，应用于默认版本11实例的命令如下例所示：

快速模式是PostgreSQL 9.5之后的默认模式，此前版本默认使用智能模式，即等待用户完成后再退出。这会花费很长时间，并且同时会拒绝所有的新连接。

在其它的Linux/Unix发行版中，可以使用快速模式执行数据库服务端的停止命令如下：

运行原理…

在执行fast stop时，所有用户的事务都会终止并且所有的连接都会断开。这对用户并不友好，但同时会小心地处理服务端及其数据，也是极好的。

PostgreSQL类似其它数据库系统，在关闭时确实会进入关闭检查点。这表示之后的启动会快速而不拖泥带水。检查点需要做的工作真多，用于关闭的时间就会越长。

PostgreSQL和一些其它关系型数据库系统(RDBMS)如Oracle、DB2或SQL Server之间的区别在于其事务回滚非常快速。在其它系统中，如果使用需回滚事务的模式进行关闭的话，会导致关闭花费一些时间，甚至是很长时间。这一区别来自于内部，并且也很安全。Debian和Ubuntu的pg_ctlcluster支持–force选项，这非常好，因此首先它会尝试快速关闭，如若失败，则执行立即关闭。然后，它会杀死postmaster进程。

其它内容

为PostgreSQL提供立即回滚的技术名为多版本并发控制（MVCC）。更多相关信息在第九章：常规维护中的识别并修复膨胀的数据表和索引一节中进行讲解。

紧急情况下停止服务端

如果其它方式都不起效，则可能需要不用管友好关闭客户端可快速停止服务。

紧急情况下请打碎玻璃！

如何实现…

1. 在服务端执行紧急停止的基本命令如下：
   
   pg_ctl -D datadir stop -m immediate

   1	pg_ctl -D datadir stop -m immediate

2. 在Debian/Ubuntu中，可以使用如下命令：
   
   pg_ctlcluster 11 main stop -m immediate

   1	pg_ctlcluster 11 main stop -m immediate

如我们在前一小节所述，它仅仅是对pg_ctl的一个封装器。通过这个示例，我们可以看出它能传递-m immediate选项。

ℹ️在前一小节中，我们看到了一些用于安全停止服务的一些systemctl命令示例，但是该命令不能用于执行紧急停止。

运行原理…

在进行立即停止时，所有用户的事务会终止并且所有连接断开。这不是安全的关闭方式，也并未保持任何友好性。

立即模式的停止类似于数据库崩溃。一些缓存文件需要重建，数据库本身在重新启动时会进行崩溃还原。

对那些有过Oracle体验的DBA们，立即模式琚关闭终止（shutdown abort）是一回事。PostgreSQL的立即模式停止与Oracle的即刻关闭并不相同。

重载服务端配置文件

一些PostgreSQL配置参数可仅通过重载整个配置个文件来进行修改。

如何实现…

本节中有两种情况，取决于是你是否使用的是systemd。这与本章前面的小节尤其是与手动启动数据库服务端一节很相似。更多的内容在该小节有讲解，如根据数据库服务端需要重载的systemd服务单元的具体名称，以及所使用的是哪个GNU/Linux发行版。

通过systemd，配置文件可以通过如下语句进行重载：

其中的SERVICEUNIT必须替换为所要重载的服务的systemd服务单元的具体名称。

此外，在每个平台上，有一个无需使用systemd就能重载服务端的具体命令。全部列举如下：

• 在Ubuntu和Debian中，可以执行如下命令：
  
  pg_ctlcluster 11 main reload

  1	pg_ctlcluster 11 main reload

• 在更老的Red Hat/Fedora中，命令如下：
  
  service postgresql reload

  1	service postgresql reload

• 也可以使用如下命令：
  
  pg_ctl -D /var/lib/pgsql/data reload

  1	pg_ctl -D /var/lib/pgsql/data reload

在macOS、Solaris和FreeBSD中同样可以使用，如果数据目录不同则只需替换/var/lib/pgsql/data为实际目录。

在所有的平台中，我们还可以重载配置文件并保持对PostgreSQL的连接。如果你是超级用户，可通过执行如下命令来实现：

输出非常简短：

这个命令经常通过管理工具来执行，如OmniDB。

如果你这么做，应当知道有可能实现一个新的验证规则，由当前会话所破坏。它不会强制你重新连接，但你如果真的重新连接的话，有可能无法再次连接上。

ℹ️配置文件中的错误都会被报到消息日志中，因此推荐在进行重载后立即查看日志。你要快速知道（并修复）参数文件中的语法错误，因为它们会在重载前就阻止用户的登录。其它错误，如参数名的误拼或错误的单元名，只会在日志中报出，另外，只有一些非语法错误会阻止整个整个文件的重载，因此最好保持查看日志。

运行原理…

要重载配置文件，我们向postmaster发送一条SIGHUP信号，然后将其传送到所有连接的后台。这也是你们为什么称重载服务端为sigh-up-ing。

如果查看pg_settings目录表，会看到有一个名为context的列。每个设置都有其能够修改的时间和地点。某些参数仅能通过服务端重载进行重置，因此这些参数的上下文值将是一个sighup。以下是一些你在服务端运维期间可能会修改到的参数（但还有其它参数）：

扩展知识…

因为重载配置文件是通过发送SIGHUP信号来实现的，我们可以仅对单个后台使用kill命令来重载配置文件。你可能已经知道，这或许会产生一些奇怪的结果，因此不要在家里随便尝试。

首先，查找使用pg_stat_activity的后台PID。然后通过操作系统命令行执行如下命令：

此外可以两者同时实现，如以下命令所示：

这仅在使用有效的WHERE语句时才有用。

快速重启服务端

一些数据库服务端参数要求我们完全的重启服务端。在一些情况下尽快地进行这一操作非常重要。这么做的最佳时间通常是无人访问的时候，需要大量的规划、测试和预案。有时很多事是不按计划执行的。

如何实现…

本章中的很多小节都形成了一种习惯，有两种形式，带systemd的和不带systemd的。这可能会稍显无聊，但又无法避免，因为引入新的系统不会自动涵盖所有已有同类系统或将老的软件迁移为新的。

在本章前面的小节手动启动数据库服务端中可以找到有关systemd的更多信息，包括服务单元的详情。

由systemd管理的PostgreSQL服务端可以通过执行如下命令来以快速模式重启：

和前面一样，修改SERVICEUNIT为相应的服务单元名称，例如对于运行在Debian或Ubuntu系统上的 PostgreSQL 11集群使用postgresql@11-main。

如果systemd不可用，那么可以使用如下语句：

重启服务的基本命令如下：

restart仅是停止后接一个启动，因此看起来非常简单。在很多情况下，这会是简单的，但有时会需要在忙碌状态下重启服务。这时就需要通过一些技巧来让重启更为快速了。

首先，所执行的停止应当是一个快速停止。如果使用默认或智能停止，服务端会等待所有人完成。如果我们进行立即停止，服务端又会崩溃，那又会需要进行崩溃数据还原，整体会更慢。

运行中的数据库服务端有整个数据块的缓存 ，很多都是受污染的。PostgreSQL在进行关闭时在这点上与其它数据库系统相似，在关闭前进行检查点操作。这表示之后的启动会快速以顺畅。在检查点进行的操作越多，关闭所花费的时间就越久。

如果我们先执行常规检查点实际的关闭会更快速，因此关闭的检查点要做的工作会更少。因此通过如下命令来刷新掉磁盘上被污染的共享缓冲，需通过超级用户来执行：

接下来在重启时要考虑的是，数据库缓存会再次清空，并需要自己进行刷新。数据库缓存越大，准备缓存花费的时间就越久，在恢复到全速之前花费30到60分钟并不罕见。因此，简单的重启在处理不当时会对业务有很大的影响。

扩展知识…

有一个名为pgfincore的插件，它在操作系统的文件缓存中实现一组管理PostgreSQL数据的函数。一种使用是预载一些表，这样PostgreSQL会有进行请求时对这些表加载的更快。通常的想法是你可以为操作系统缓存提供更详细的信息，这样执行会更为有效。

pgfincore插件是在2009启动的一个稳定项目。更多相关的信息及源代码请参见https://github.com/klando/pgfincore。但是，需要注意大部分发行版中都包含一个预构建的pgfincore包，这会让安装更简单。

还有一个名为pg_prewarm的contrib模块，它处理类似的问题。虽然它和pgfincore的功能有一些重叠，但功能集并不相同，例如，pgfincore可对不在共享缓冲缓存中的文件进行操作，并且还可以仅通过很少的系统调用就在考虑到已有缓存的情况下重载完整的关联，而另一方面，pg_prewarm可以对PostgreSQL共享缓冲缓存执行操作，并且能在Windows中使用。

阻止新连接

在某些紧急情况下，我们可能需要完全锁定服务，或者仅仅是阻止特定用户访问数据库。很验证预测到需要这么做的所有情况，因此我们会呈现一系统的选项。

如何实现…

连接可通过很多方式进行阻止，如下：

• 暂停并恢复会话池。参见本单有关控制连接池的设置连接池一节。
• 停止服务端。参见安全快速地停止服务端和紧急情况下停止服务端这两个小节，但不推荐这么做。
• 通过设置连接设置为0来限制具体数据库的连接为0：
  
  ALTER DATABASE foo_db CONNECTION LIMIT 0;

  1	ALTER DATABASE foo_db CONNECTION LIMIT 0;

• 这会限制普通用户对该数据库的连接，但依然允许超级用户进行连接。
• 通过设置连接限制为0来设置具体用户的连接数为0（参见限制每个用户仅一个会话一节）：
  
  ALTER USER foo CONNECTION LIMIT 0;

  1	ALTER USER foo CONNECTION LIMIT 0;

• 这会限制普通用户对该数据库的连接，但超级用户仍然可以进行连接，因此你不会误把自己也拒之门外。
• 修改基于主机认证（HBA）文件来拒绝所有进入的连接，然后重载服务。
  • 创建名为pg_hba_lockdown.conf的新文件，并在文件中添加如下两行。这会准备好完全锁定服务端的规则，包含超级用户。无疑这是一个很严肃且极端的动作：
    
    # TYPE DATABASE USER ADDRESS METHOD local all all reject host all all 0.0.0.0/0 reject

    1 2 3

    # TYPE DATABASE USER ADDRESS METHOD local all all reject host all all 0.0.0.0/0 reject

    
    如果希望超级用户能够访问，可以尝试如下设置：
    
    # TYPE DATABASE USER ADDRESS METHOD local all postgres peer local all all reject host all all 0.0.0.0/0 reject

    1 2 3 4

    # TYPE DATABASE USER ADDRESS METHOD local all postgres peer local all all reject host all all 0.0.0.0/0 reject

    
    这会阻止postgres超级系统用户 ID以外的其它用户对数据库的连接，超级用户可连接本地任意数据库。当心不要对第二和第三列产生混淆了：第2列是数据库，第2列是用户名。因此建议保留头部注释行。在使用更为复杂的情况时peer应使用其它认证方法进行替代。
• 拷贝已有的pg_hba.conf 文件到pg_hba_access.conf ，这样在稍后需要时可进行替换。
• 拷贝pg_hba_lockdown.conf到pg_hba.conf。
• 根据本章前面的小节重载服务端。

运行原理…

pg_hba.conf文件是我们指定基于主机认证规则的地方。我们不指定认证本身，而只是指定使用哪个认证机制。这是针对PostgreSQL认证的顶级规则集。这些规则在文件中指定并且在尝试连接时通过postmaster进程来应用。要防止拒绝服务攻击，HBA规则从不包含数据库访问，因此我们不知道用户是否为超级用户。结果是你会锁定所有用户，但总是可通过编辑该文件并重载来重新启用访问。

限制每个用户仅一个会话

如果资源需要进行紧密控制，你可能会希望限制用户最多仅能对服务端进行一次连接。同样的技术可用户防止用户进行任何连接。

如何实现…

我们可以使用如下命令来限制用户仅做一次连接：

然后这会造成任意额外的连接会收到如下错误信息：

可以通过设置值为-1来消除这一限制。

可以设置限制为0或任意正整数。可以将数量设置为max_connections外的任意数字，当然若想设置为max_connections也完全取决于你自己。

设置该值为0会完全限制正常连接。注意即使你对超级用户设置连接限制为0，它们依然能够进行连接。

运行原理…

连接限制在会话连接期间应用。提升限制不会影响到已连接的用户。降低限制同样不会产生影响，除非他们进行了断开 和重连接。

因此，如果你降低了限制，应当立即查看是否有比新设置的限制更多的会话连接。否则，如果产生崩溃可能会让你诧异：

如果有比新限制更多的已连接会话，可以礼貌地让用户断开连接，或者可以采用下一节将用户推出系统的方法。

用户无法提升或限低自己的连接限制，因此不必为用户是否可以重载这一设置而担心。

将用户推出系统

有时，出于某些运维原因我们可能需要移除一组用户对数据库服务端的连接。下面就是实现的方法。

如何实现…

可以通过包含PostgreSQL的pg_terminate_backend()函数终止一个用户的会话。这个函数接收用户在服务端会话的PID或进程 ID。这一进程称为后台程序，是与运行客户端的程序所不同的系统进程。

要查看用户的PID，我们可以查看pg_stat_activity视图。使用类似下面的查询：

如果运行这个查询需要注意一些事情。如果WHERE与任何会话都不匹配，那么你就不会通过查询获得任何输出。类似地，如果它匹配到多行，就会得到一些无用的结果，即一组布尔true值。如果你不仔细地将自己的会话从查询中排除，那么你会断开自己的连接！更搞笑的是你可能在对其它用户执行断开连接的中间先断开了自己的连接，因为查询会按从外部查询返回会话的顺序运行pg_terminate_backend() 。

因此，我推荐一种能在所有情况下给出有用的响应的更安全有效的查询，如下：

这假定超级用户在执行管理任务。

以下是一些有用的过滤器：

运行原理…

pg_terminate_backend()函数直接为该会话对操作系统进程发送信号。

这个会话可能会在指定pg_terminate_backend() 时关闭。因为PID数字由操作系统分配，甚至在你尝试终止给定会话（假设为会话 A） 时实际终止的却是另一个会话（假定为会话 B）。

以下是可能发生的状况。假设你记下了会话 A 的PID并决定断开其连接。在实现执行pg_terminate_backend()之前，会话 A 断开了连接，然后一个新的会话 B 被给予了同样的 PID，在终结该 PID 时，会操作到会话 B。

一方面你需要非常小心。另一方面，这种情况不太可能发生，只是为了完整性而进行了讨论。要发生这一状况，还必须要满足如下条件：

• 你所尝试关闭的会话必须要在读取pg_stat_activity 和执行pg_terminate_backend()的短暂空档瞬间独立终止。
• 数据库服务端的另一个会话必须要在老会话关闭和pg_terminate_backend()执行的中间开启，这个间隔更短。
• 新会话必须获取与老会话完全相同的PID值，在32位Linux系统中这个机率小于32,000分之一。

然而概率论哪怕是对于专家来说也是变幻莫测。因此，最好知道有很微小的风险，尤其是在长期每天都多次使用这个查询时，这时就存在撞上的机率。

新会话也可能在我们获取活跃会话列表之后启动。这个只能按照阻止新连接一节来进行避免。

最后，要知道超级用户可以终止任意会话，而非超级用户只能终结属于相同用户的会话。

多租户设计决策

有很多我们可能需要分割数据表组和应用的原因：安全、资源控制、方便性等等。不管原因是什么，我们经常需要分离数据表组（我没有使用数据库这个词，只是为了避免各种混淆）。

这个主题通常被称为多租户（multitenancy），但它还不是一个完全被接受的用词。

本节的目的是讨论其选项并作为其它更为详细小节的引子。

如何实现…

如果你希望在一台服务器上运行多个物理数据库，那么有如下4种主要选择：

• Option 1：在一个PostgreSQL实例数据库中运行多组不同模式的数据表集（在使用多模式一节中进行讨论）。
• Option 2：在同一个PostgreSQL实例中运行多个数据库（在为用户分配私有数据库一节中讲解）。
• Option 3：在同一个虚拟/物理系统上运行多个PostgreSQL实例（在同一系统中运行多个服务端一节中进行讲解）。
• Option 4:：不同一台物理服务器上的多个虚拟主机上运行单独的PostgreSQL实例。

哪种方法最好呢？这一定是很多人都会问的问题，而对此有会存在很多观点。答案取决于具体的要求，如下：

• 如果我们的目标是分离物理资源，那么使用3和4是最好的。分离的数据库服务端可以轻松地分配不同的磁盘、可以分配单独的内存并且可以在不影响其它服务端的情况下启动或关闭该服务端。
• 如果我们的目标是安全，那么选2就可以了。
• 如果我们的目标仅仅是为管理上的清晰而分离数据表，那么可以使用1或2.

Option 2允许出于安全考虑的完整分离。但也会阻止一些对两组表有权限的人执行这些表的连接操作。因此如果未来有进行交叉分析的可能性，可以考虑option 1。但也会有观点认为这种分析应在单独的数据仓库中执行，而非使用协作的生产系统。

Option 3在很多PostgreSQL发行版中存在困难：默认安装对数据库使用单位置点，让其更难于进行该选项的配置。Ubuntu/Debian对这方面的处理尤其要好，让其在这个环境中更具吸引力。

Option 4可使用可视化技术来应用，但这不在本书的讨论范围内。

运行原理…

我见过人们使用带有数千个数据库的PostgreSQL，但是个人的观点是大部分人只使用一个数据库，如postgres（或者至少是仅使用一小部分数据库）。我也见过有人使用很多的模式。

如果有成百上千个项目要展示的话你会发现所有的管理图形化工具会大幅降低使用价值。在大多数情况下，管理工具使用树状视图，在拥有大量项目时处理得并不优雅。

使用多模式（schema）

我们可以将数据表组分隔成各自的命名空间，PostgreSQL中称之为模式。在很多方面，它可被看成是目录，但这不是一个精准的描述。

准备工作

确保你阅读了多租户设计决策一节，这样就会明白这会是想要采纳的方向。还存在其它选项，在某些情况下会更适用。

如何实现…

1. 模式可通过如下命令轻松创建：
   
   CREATE SCHEMA finance; CREATE SCHEMA sales;

   1 2	CREATE SCHEMA finance; CREATE SCHEMA sales;

2. 然后我们可以在这些模式中使用完整名称直接创建对象，如下：
   
   CREATE TABLE finance.month_end_snapshot (.....)

   1	CREATE TABLE finance.month_end_snapshot (.....)

   
   创建对象所使用的模式称为current_schema。我们可以通过使用如下查询找到我们的当前模式：
   
   postgres=# select current_schema;

   1	postgres=# select current_schema;

   
   这会返回如下的输出：
   
   current_schema ---------------- public (1 row)

   1 2 3 4

   current_schema ---------------- public (1 row)

3.  在我们访问数据库对象时，使用用户可设置的search_path参数来识别所要搜索的模式。current_schema是search_path参数中的第一个模式。对于current_schema没有单独的参数。
   因此，如果我们只希望一个具体的用户查看某一组数据表，可以修改他们的search_path参数。这一参数可对每个用户进行设置，这样会在他们连接时设置该值。对此的SQL查询类似下面这样：
   
   ALTER ROLE fiona SET search_path = 'finance'; ALTER ROLE sally SET search_path = 'sales';

   1 2	ALTER ROLE fiona SET search_path = 'finance'; ALTER ROLE sally SET search_path = 'sales';

   ℹ️在search_path中并没有涉及到公共模式，因此不会进行搜索。所有由fiona创建的数据表会默认进入finance模式，而所有由sally创建的数据表会默认进入sales模式。

4. finance和sales的用户可以查看其它存在的模式并修改search_path来进行使用，但我们可以GRANT或REVOKE权限，这样他们既不能创建对象也不能读取其他人的模式中的数据：
   
   REVOKE ALL ON SCHEMA finance FROM public; GRANT ALL ON SCHEMA finance TO fiona; REVOKE ALL ON SCHEMA sales FROM public; GRANT ALL ON SCHEMA sales TO sally;

   1 2 3 4

   REVOKE ALL ON SCHEMA finance FROM public; GRANT ALL ON SCHEMA finance TO fiona; REVOKE ALL ON SCHEMA sales FROM public; GRANT ALL ON SCHEMA sales TO sally;

   
   一种替代技巧是仅对一种模式为用户授予创建权限，但对其它模式授予使用权限。我们可以通过如下设置来进行一安排：
   
   REVOKE ALL ON SCHEMA finance FROM public; GRANT USAGE ON SCHEMA finance TO fiona; GRANT CREATE ON SCHEMA finance TO fiona; REVOKE ALL ON SCHEMA sales FROM public; GRANT USAGE ON SCHEMA sales TO sally; GRANT CREATE ON SCHEMA sales TO sally; GRANT USAGE ON SCHEMA sales TO fiona; GRANT USAGE ON SCHEMA finance TO sally

   1 2 3 4 5 6 7 8

   REVOKE ALL ON SCHEMA finance FROM public; GRANT USAGE ON SCHEMA finance TO fiona; GRANT CREATE ON SCHEMA finance TO fiona; REVOKE ALL ON SCHEMA sales FROM public; GRANT USAGE ON SCHEMA sales TO sally; GRANT CREATE ON SCHEMA sales TO sally; GRANT USAGE ON SCHEMA sales TO fiona; GRANT USAGE ON SCHEMA finance TO sally

5. 注意我们需要在模式上授予使用权限，以及对模式中的对象设置具体权限。因此，你还需要向对象执行具体的授权，如下所示：
   
   GRANT SELECT ON month_end_snapshot TO public;

   1	GRANT SELECT ON month_end_snapshot TO public;

   
   也可以设置默认的权限，这样通过如下命令在对象创建时会进行获取：
   
   ALTER DEFAULT PRIVILEGES FOR USER fiona IN SCHEMA finance GRANT SELECT ON TABLES TO PUBLIC;

   1 2	ALTER DEFAULT PRIVILEGES FOR USER fiona IN SCHEMA finance GRANT SELECT ON TABLES TO PUBLIC;

运行原理…

前面我说过模式类似于目录，或者至少有些类似。PostgreSQL的search_path概念类似于PATH环境变量。

PostgreSQL的当前模式概念类似于当前工作目录的概念。没有cd命令来切换目录。当前工作目录通过修改search_path来进行改变。

还存在一些其它区别，例如，PostgreSQL模式不是像文件系统目录那样按照等级进行排列的。

很多人创建同名用户，因为模式让其类似于Oracle这样的关系型数据库管理系统。

ℹ️finance和sales模式在同一PostgreSQL数据库中都存在，并且它们在相同的数据库服务器上运行。它们使用共同的缓冲池，并且有很多全局设置让两种模式紧密的绑定。

为用户分配私有数据库

单独的数据和用户是管理的重要部分。总是需要给用户私有、安全或简单无风险的区域（沙盒）来使用数据库。以下是方法。

准备工作

再说一次，确保你阅读了多租户设计决策一节。这样可以确定那是你相要采取的方向。还存在其它选项，在某些情况下可能更适用。

如何实现…

按照如下步骤来对指定用户授予受限的权限创建数据库：

1. 我们可轻松地对指定用户创建数据库。通过命令行，对于超级用户，这些操作如下：
   
   postgres=# create user fred; CREATE ROLE postgres=# create database fred owner fred; CREATE DATABASE

   1 2 3 4

   postgres=# create user fred; CREATE ROLE postgres=# create database fred owner fred; CREATE DATABASE

2. 作为数据库所有者，用户有登录权限，因此他们可以默认连接任意数据库。有一个ALTER DEFAULT PRIVILEGES权限 ，但这当前不应用于数据库、数据表空间或语言。ALTER DEFAULT PRIVILEGES命令当前仅用于已存在的角色（即用户）。
   因此我们需要收回除指定用户外的所有人连接新数据库的权限。没有REVOKE … FROM PUBLIC EXCEPT这个命令。因此，我们我们需要撤销所有权限，然后再在一个事务中对所需要的权限进行重新授权，如以下代码：
   
   postgres=# BEGIN; BEGIN postgres=# REVOKE connect ON DATABASE fred FROM public; REVOKE postgres=# GRANT connect ON DATABASE fred TO fred; GRANT postgres=# COMMIT; COMMIT postgres=# create user bob; CREATE ROLE

   1 2 3 4 5 6 7 8 9 10

   postgres=# BEGIN; BEGIN postgres=# REVOKE connect ON DATABASE fred FROM public; REVOKE postgres=# GRANT connect ON DATABASE fred TO fred; GRANT postgres=# COMMIT; COMMIT postgres=# create user bob; CREATE ROLE

3. 然后，尝试以bob连接数据库fred：
   
   os $ psql -U bob fred psql: FATAL: permission denied for database "fred" DETAIL: User does not have CONNECT privilege.

   1 2 3

   os $ psql -U bob fred psql: FATAL: permission denied for database "fred" DETAIL: User does not have CONNECT privilege.

   
   这正是我们想要的效果。

运行原理…

如果你之前没有注意，PostgreSQL在部分地方允许事务性DDL，因此要么前面小节的REVOKE和GRAN命令同时生效，要么共同不生效。这表示用户fred永远不会丢失对该数据库的连接能力。注意CREATE DATABASE不能作为事务的一部分进行执行，但也不会发生什么严重的结果 。

扩展知识…

超级用户仍然可以连接新数据库，并且没有方法阻止它们这么做。其它用户都无法看到新数据库中创建的数据表，他们也不知道任何对象的名称。新数据库的存在会被其他用户看到，并且他们还可以看到拥有该数据库的用户名。

其它内容

参见第六章：访问权限获取有关这一主题的更多信息。

在同一系统中运行多个服务端

如果对你方便的话是可以在一个物理系统中运行多个PostgreSQL服务端的。

准备工作

再说一次，确保你已阅读多租户设计决策一节。这样可以确定那是你相要采取的方向。还存在其它选项，在某些情况下可能更适用。

如何实现…

核心PostgreSQL方便地允许在一个系统中运行多个服务端，但需要注意一些问题。

某些安装器版本创建名为data的PostgreSQL数据目录。然后不使用不同的目录结构和名称的话就会很难获得一个以上的数据目录。

Debian/Ubuntu打包人员选择一种特别设计的布局，可以让多个服务端运运行不同发行版级别。你可能还记得第二章：探密数据库中的定位数据库服务端文件一节。

从postgres用户的家目录/var/lib/postgresql开始，每个大版本都有一个子目录，例如10或9.3，其中放置了单独的数据目录。在安装PostgreSQL服务包时，默认创建名为main的数据目录。配置文件单独放在/etc/postgresql/<version>/<name>中，而日志文件在/var/log/postgresql/postgresql-<version>-<name>.log.中进行创建。

因此在data目录并不能找到所有的文件，让我们来创建更多的数据目录：

1. 我们先通过运行如下命令：
   
   sudo -u postgres pg_createcluster 11 main2

   1	sudo -u postgres pg_createcluster 11 main2

2. 然后可以使用如下命令来启动新的数据库服务端：
   
   sudo -u postgres pg_ctlcluster 11 main2 start

   1	sudo -u postgres pg_ctlcluster 11 main2 start

这足以在版本11中创建和启动额外的数据库集群，名为main2。数据和配置文件分别存储在/var/lib/postgresql/11/main2/和/etc/postgresql/11/main2/目录中，给予新数据库下一个未使用的端口号，例如如果这是本机上的第二个PostgreSQL服务端的话，为5433。

对于多个PostgreSQL服务端的本地访问也可以进行简化。PostgreSQL客户端程序，如psql，由特殊的脚本封装，它接收集群名作为额外的参数，并自动使用对应的端口号。因此，你其实不用执行如下命令：

而可以通过名称来引用数据库服务端，如下所示：

如果你想要（或者需要）在未来修改端口号的话这就很有优势。我觉得它非常的方便，而且同样适用于其它工具，如pg_dump、pg_restore等等。

通过红帽系统，你将需要直接运行initdb，仔细选择目录：

1. 首先，通过下面这样的命令初始化数据目录：
   
   sudo -u postgres initdb -D /var/lib/pgsql/datadir2

   1	sudo -u postgres initdb -D /var/lib/pgsql/datadir2

2. 然后在postgresql.conf文件中修改port参数并使用如下命令进行启动：
   
   sudo -u postgres pg_ctl -D /var/lib/pgsql/datadir2 start

   1	sudo -u postgres pg_ctl -D /var/lib/pgsql/datadir2 start

   
   这会以默认服务端版本创建一个额外的数据库服务端，文件存储在/var/lib/pgsql/datadir2中。

你也可以使用chkconfig工具设置服务端，来在发行版支持的情况下确保在开机时启动。

运行原理…

PostgreSQL服务端使用pg_ctl控制。其它所有都是某种对该工具的封装。运行多版本PostgreSQL的唯一约束来自于文件位置和命令规则，（当然）假定你有足够的资源，如磁盘空间、内存等等。其它所有部分都一目了然。经过这一描述，Debian/Ubuntu的设计是当前唯一让实际运行多个服务端很容易的设计。

设置连接池

连接池是用于已连接会话集合的一个用词，可用于减少连接和再连接过重的头部。

有很多种提供连接池的方式，取决于所使用的软件栈。最好的选择是查看服务端连接池软件，因为它可用于所有连接类型，而不是仅用于单个软件栈。

这里我们将来了解PgBouncer，它设计为一个非常轻量级的连接池。其名称来自于连接池可以暂停并恢复来让服务端重启或弹起（bounced）。

准备工作

首先，决定在哪里存储PgBouncer的参数文件、日志文件和PID文件。PgBouncer可以同时管理一个以上的数据库服务端连接，但这对于简单的架构来说不是一个明智的选择。如果保持PgBouncer文件与数据库服务端的关联，应该很容易进行管理。

如何实现…

执行如下步骤来配置PgBouncer：

1. 创建pgbouncer.ini文件如下：
   
   ; ; pgbouncer configuration example ; [databases] postgres = port=5432 dbname=postgres [pgbouncer] listen_addr = 127.0.0.1 listen_port = 6432 admin_users = postgres ;stats_users = monitoring userid auth_type = any ; put these files somewhere sensible: auth_file = users.txt logfile = pgbouncer.log pidfile = pgbouncer.pid server_reset_query = DISCARD ALL; ; default values pool_mode = session default_pool_size = 20 log_pooler_errors = 0

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

   ; ; pgbouncer configuration example ; [databases] postgres = port=5432 dbname=postgres [pgbouncer] listen_addr = 127.0.0.1 listen_port = 6432 admin_users = postgres ;stats_users = monitoring userid auth_type = any ; put these files somewhere sensible: auth_file = users.txt logfile = pgbouncer.log pidfile = pgbouncer.pid server_reset_query = DISCARD ALL; ; default values pool_mode = session default_pool_size = 20 log_pooler_errors = 0

2. 创建一个users.txt文件。必须包含admin_users和stats_users中所涉及的最少用户。它的格式非常简单：一组带有用户和密码的行。看看如下的示例：
   
   "postgres" ""

   1	"postgres" ""

3. PgBouncer还支持MD5验证。要进行有效使用，你需要将数据库中MD5加密的密码拷贝到users.txt文件中。
   

   ℹ️注意在出版本书时pgBouncer还不支持scramsha-256加密。

4. 你可能会希望通过直接从服务端直接拷贝详情来创建users.txt文件。这可以通过使用如下psql脚本来实现：
   
   postgres=> \o users.txt postgres=> \t postgres=> SELECT '"'||rolname||'" "'||rolpassword||'"' postgres-> FROM pg_authid; postgres=> \q

   1 2 3 4 5

   postgres=> \o users.txt postgres=> \t postgres=> SELECT '"'||rolname||'" "'||rolpassword||'"' postgres-> FROM pg_authid; postgres=> \q

5. 启动pgbouncer：
   
   pgbouncer -d pgbouncer.ini

   1	pgbouncer -d pgbouncer.ini

6. 测试连接，它应当会对重载响应：
   
   psql -p 6432 -h 127.0.0.1 -U postgres pgbouncer -c "reload"

   1	psql -p 6432 -h 127.0.0.1 -U postgres pgbouncer -c "reload"

7. 最后，验证PgBouncer的max_client_conn参数不超过PostgreSQL中的max_connections参数。

运行原理…

PgBouncer是一款很棒的软件。它的功能集很细致的进行了定义来确保其简单、健壮并且非常快速。PgBouncer不是多线程的，因此它在单絯CPU中的单个进程中运行。这非常有效率，但是超大数据集的传输会花费更多时间并减少并发量，因此使用直连来创建这个数据dump。

PgBouncer提供连接池。如果设置pool_mode = transaction，那么PgBouncer还会提供连接集中（connection concentration）。这会允许管理成百甚至是上千的进入连接，而仅进行很少的服务端连接。

在新的连接、事务或语句到达时，连接池会扩充大小为用户定义的最大值。这些连接会在连接池释放他们之前保持到达到server_idle_timeout的值。

PgBouncer还在每个server_lifetime期间释放会话。这允许服务端轮询清空后台来避免过长存在的会话连接的问题。

前面创建users.txt 的查询仅包含拥有密码的数据库用户。所有其他用户将具有一个空rolpassword字段，因此整个字符串运行为NULL，该行会在密码文件中进行忽略。这是有意为之：没有密码的用户表示安全漏洞，除非进行了紧密的监管。其中一个例子是从本机连接的postgres系统用户，它越过PgBouncer，仅用于由相关并可信的人员进行维护。

也可以使用具有相同语法的HBA文件来作为pg_hba.conf。这允许在仅连接远程服务端时启用TLS加密（包含SSL）更强的灵活性，而对本地服务端使用更有效的对等验证（peer authentication）。

It is possible to use an HBA file with the same syntax as pg_hba.conf. This allows for more flexibility when enabling TLS encryption (which includes SSL) only for connections to remote servers, while using the more efficient peer authentication for local servers.

扩展知识…

代替通过userlist.txt文件获取密码，PgBouncer可直接使用可选的auth_user和auth_query参数从PostgreSQL获取密码。如果设置了auth_user，PgBouncer会使用该用户连接数据库并在每次需要从尝试登录的用户获取密码时运行auth_query。auth_query的默认值如下：

这一默认值仅是最小可运行示例，它描述auth_query的概念，但是要求对PgBouncer授予PostgreSQL超级用户权限。因此使用更复杂的方法创建可获取用户名和密码的SECURITY DEFINER函数是一种良好补，可能还要对用户进行检查来仅允许可应用的连接。这是一种很好的限制，因为数据库管理连接不应通过连接池。

也可以连接PgBouncer本身来执行命令。这可交互实现，就像进入了psql一样，或者也可使用单个命令或脚本来实现。

要关闭PgBouncer，我们可以只输入SHUTDOWN或输入单个命令如下：

也可以像我们所做的那样使用RELOAD命令来让PgBouncer重载（也即重新读取）参数文件，来测试一切是否正常。

如果你在进行切换，可以使用WAIT_CLOSE命令接RELOAD或RECONNECT，来等待相应的配置修改完全被激活。

如果在使用pool_mode = transaction或pool_mode = statement，那么你可以使用pool_mode = statement,命令。这在等待当前事务完成前暂停对该会话的进一步操作。因此，它允许你更轻易地执行DDL或重启服务端。

PgBouncer还允许你使用SUSPEND模式，它等待清理所有服务端缓冲。

PAUSE或SUSPEND模式应在完成任务时最终接RESUME。

除PgBouncer控制命令以外，还有很多种SHOW命令，列举如下：

通过相同主机和端口访问多个服务端

现在我们将向你展示对前一小节设置连接池的一个简单但重要的应用。在该节中学习了如何通过PgBouncer重复使用连接，因而减少了断开连接和重新连接的开销。

这里我们将演示另一种使用PgBouncer的方式：一个实例可以同时连接由不同数据库服务器托管的数据。这些数据库可以在不同的主机上，甚至有不同大版本的PostgreSQL。

准备工作

假设我们有三个数据库服务喝咖啡，每个托管一个数据库。首先你只需要知道每个数据库服务器的连接字符串。

可进行更多复杂的安排，但那些留作读者进行练习。

在学习本节前，应已经学习过前一小节。这两个小节有很多共通的步骤，但我们将它们分开了，因为目标明显不同。

如何实现…

每个数据库完全由连接字符串进行标识。PgBouncer将从配置文件读取这一信息。只需按照下面的步骤：

1. 你所需要做的是像前一节那样设置PgBouncer，通过将pgbouncer.ini 中的数据库版块替换如下：
   
   [databases] myfirstdb = port=5432 host=localhost anotherdb = port=5437 host=localhost sparedb = port=5435 host=localhost

   1 2 3 4

   [databases] myfirstdb = port=5432 host=localhost anotherdb = port=5437 host=localhost sparedb = port=5435 host=localhost

2. 一旦你启动了PgBouncer，就可以连接第一个数据库：
   
   $ psql -p 6432 -h 127.0.0.1 -U postgres myfirstdb psql (11.1) Type "help" for help. myfirstdb=# show port; port ------ 5432 (1 row) myfirstdb=# show server_version; server_version ---------------- 11.1 (1 row)

   1 2 3 4 5 6 7 8 9 10 11 12 13

   $ psql -p 6432 -h 127.0.0.1 -U postgres myfirstdb psql (11.1) Type "help" for help. myfirstdb=# show port; port ------ 5432 (1 row) myfirstdb=# show server_version; server_version ---------------- 11.1 (1 row)

3. 现在，可以像在同一台服务器上那样连接anotherdb数据库：
   
   myfirstdb=# \c anotherdb psql (11.1, server 9.5.15) You are now connected to database "anotherdb" as user "postgres".

   1 2 3

   myfirstdb=# \c anotherdb psql (11.1, server 9.5.15) You are now connected to database "anotherdb" as user "postgres".

4. 服务端的欢迎信息表明我们已进入了另一台服务器，可以查看端口和版本：
   
   anotherdb=# show port; port ------ 5437 (1 row) anotherdb=# show server_version; server_version ---------------- 9.5.15 (1 row)

   1 2 3 4 5 6 7 8 9 10

   anotherdb=# show port; port ------ 5437 (1 row) anotherdb=# show server_version; server_version ---------------- 9.5.15 (1 row)

扩展知识…

第二章：探密数据库中的列出数据库服务端的数据库一节讲解了如何列出当前数据库服务器上的可用数据库，使用\l元命令或一些等价变体命令。不幸的是，这在PgBouncer中不适用，因此当前数据库服务端无法知道答案。

我需要用PgBouncer来请求，我们在连接到pgbouncer特殊管理数据库时使用SHOW命令来实现：

喜欢 (0)赏

[]

分享 (0)