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

Coding Alan 6天前 52次浏览 0个评论

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

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

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

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

本章中将讲解如下小节:

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

阅读精装手册(RTFM)

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

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

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

如何实现…

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

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

你也可以自己获取一个手册的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允许我们为每个会话或事务设置一些参数设置。

如何实现…

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

  1. 可以在会话期间修改设置的值,如下:
  2. 然后值会被用作每个未来的事务。你也可以对当前事务进行修改:
  3. 该设置会在执行如下命令前有效:
  4. 此外也可以执行如下命令:

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

运行原理…

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

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

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

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

扩展知识…

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

会产生如下的输出:

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

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

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

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

找到当前的配置设置

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

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

如何实现…

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

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

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

其输出如下:

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

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

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

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

这将返回如下输出:

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

运行原理…

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

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

哪些参数是非默认设置?

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

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

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

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

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

如何实现…

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

输出如下:

运行原理…

通过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服务进程相同的操作系统用户执行如下命令:

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

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

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

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

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

对于Debian和Ubuntu,使用如下命令:

通过system使用如下命令:

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

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

运行原理…

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

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

扩展知识…

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

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

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

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

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

为指定用户组设置参数

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

如何实现…

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

  1. 对saas数据中的所有用户,使用如下命令:
  2. 对于连接任一数据库名为simon的用户,使用如下命令:
  3. 此外,可以对用户仅在连接到某个具体数据库时设置参数,如下:

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

运行原理…

可以为以下设置参数:

  • 数据库
  • 用户(在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中添加如下行:

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

 

为PostgreSQL添加外部模块

准备工作

如何实现…

使用软件安装器安装模块

通过PGXN安装模块

通过源代码安装模块

运行原理…

使用已安装模块

准备工作

如何实现…

运行原理…

管理已安装插件

如何实现…

运行原理…

扩展知识…

 

更新中…

喜欢 (1)
[]
分享 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址