PostgreSQL 11数据库管理员指南第一章：迈出第一步

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

PostgreSQL是一个功能丰富、通用目的的数据库管理系统。它是一套复杂的软件，但千里之行，始于足下。

我们将开始我们的第一个连接。很多人都栽在了第一步，因此我们将尝试不要太快速的跳过。我们将快速移步到启动远程用户，然后从那里我们会进入到通过GUI管理工具来获取访问。

我们还将引入psql查询工具，它是用于加载我们的示例数据库以及本书中其它示例的工具。

为提供更多帮助，我们还包含了一些有用的一节来供你在需要的时候参考。

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

• 获取PostgreSQL
• 连接PostgreSQL服务
• 启用对网络/远程用户的访问
• 使用图形化管理工具
• 使用psql查询和脚本工具
• 安全地修改密码
• 避免硬编码密码
• 使用连接服务文件
• 排查失败的连接

PostgreSQL 11简介

PostgreSQL是一个高级SQL数据库服务，可在大量的平台上使用。PostgreSQL最清晰的一个好处是它是开源的，也就意味着你可以安装、使用及发布PostgreSQL，无需支付任何人任何费用及版权费。在此基础上，PostgreSQL也以长期运行的数据库及大多数情况下需要很少或无需维护而见称。总的来说，使用PostgreSQL的总体成本非常低。

PostgreSQL也因其大量的高级功能而著称，经过了30多年的持续开发和增强。最初是由加州大学伯克利分校的数据库研究组所开发，PostgreSQL现在由大量的开发人员和贡献者共同开发和维护。其中很多贡献者有着与PostgreSQL相关的全职工作，有设计师、开发人员、数据库管理员及培训师。有一部分贡献者为专业提供PostgreSQL支持的公司工作。没有哪家公司拥有PostgreSQL，你也无需（甚至说不鼓励）去注册你的使用。

PostgreSQL有以下的主要功能：

• 对直至SQL: 20的SQL标准的良好遵循
• 客户端-服务端架构
• 它拥有高并发设计，读取者和写入者不会相互屏蔽
• 对于很多类型的应用它高度可配置及可扩展
• 它拥有良好的可扩展性和性能，并具有广泛的优化功能
• 它提供对于很多种数据模型的支持，如关系型、后关系型（数组、通过记录类型的嵌套关联）、文档（JSON和XML）及键值对

什么使得PostgreSQL与众不同？

PostgreSQL项目聚焦于如下的目标：

• 健壮、具有可维护、良好注释代码的高质量软件
• 对嵌入式及企业使用的低维护管理
• 遵循SQL标准、互操作性及兼容性
• 性能、安全和高可用

让很多人惊讶的是PostgreSQL的功能集更多接近Oracle或SQL Server而不是MySQL。MySQL和PostgreSQL之间的唯一关联是这两个项目都是开源的，除此之外，它们的功能和设计哲学大相径庭。

Oracle自Oracle 7以来最重要的一个功能是快照隔离，读取者不会阻塞写入者，写入者也不会阻塞读取者。你可能会很惊讶PostgreSQL是第一个设计带有此功能的数据库，并且它提供了完整的实现。在PostgreSQL中，这一功能被称为多版本并发控制（MVCC）我们将在本书中做进一步的讨论。

PostgreSQL是一套通用目的的数据库管理系统。你可以使用它定义你所想要的数据库。PostgreSQL为你提供多种运行得通的方式。你可以使用一个常规的数据库模型，然后扩展一些如数组和记录子类型的功能，也可以借助JSONB和一个名为hstore的扩展来使用完整的动态模式。PostgreSQL还允许你用几十种不同的语言创建自己的服务端函数。

PostgreSQL调试可扩展，因此你可以添加自己的数据类型、运算符、索引类型和函数语言。你甚至可以重载不同部分的系统，使用插件来修改命令的执行或添加新的查询优化器。

所有这些功能对软件架构提供大量的实现选项。在构建应用及长期维护它们时有很多摆脱困境的方法。遗憾的是，本书篇幅有限，不能为开发者罗列所有这些酷炫功能，本书主要关乎管理、维护和备份。

早期在PostgreSQL还是一个研究性数据库时，它的焦点仅仅是一些酷炫的新功能。在过去的20年中，重写并改进了大量的代码，为我们的操作使用提供了最大型且最稳定的软件服务之一。

谁在使用PostgreSQL？知名的用户包含Apple, BASF, Genentech, Heroku, IMDB, Skype, McAfee, NTT, the UK Met Office和美国国家气象局。2010初，根据提供给欧盟委员会的数据，PostgreSQL每年收到了100万份以上的下载，可总结为PostgreSQL被众多数据库用户视为可靠的选择。

我们还需要讨论最后一件事：在PostgreSQL刚刚开出来时，它的名称是Postgres，因此在项目的很多地方依然使用了Postgres这个词，如，默认数据库的名称为postgres，这个软件也经常使用Postgres的用户ID来进行安装。结果很多人将PostgreSQL简称为Postgres，并且在很多情况下交叉使用这两个名称。

PostgreSQL的发音是 post-grez-q-l。Postgres的发音是post-grez。

有些人产生了困惑，称之为Postgre，很难发音并且很可能会困扰到大家。两个名称已经足够了，所以不要再使用第三个名称了！

以下各小节更详细地讲解关键部分内容。

健壮性

PostgreSQL是健壮的、高质量的软件，由功能和并发的测试所支撑。默认数据库提供很强的磁盘写入保证，并且开发人员对他们所做任何事的数据丢失都非常重视。切换健壮性或性能的选项是存在的，但默认并不启用。

对数据库的所有动作都在事务中执行，受到事务日志的保护，它会在软件出问题时执行自动故障恢复。

数据库也可由数据块校验创建，来帮助诊断硬件问题。还存在多备份机制 ，带有完整且详细的时间点恢复（PITR），在你需要详细的还原时使用。还有大量的诊断工具。

数据库复制原生就支持。如果恰当扡配置和管理的话，同步复制可提供5-9（99.999%）以上的可用性及数据保护，伴有合适的冗余甚至会更高。

安全

对PostgreSQL的访问可通过基于主机的访问规则来控制。认证是灵活、可插拔的，允许我们轻松地集成外部安全架构。最新的Salted挑战响应堆机制（SCRAM）提供完整的256位保护。

译者注：salt 在密码保护中经常使用，可参见百度百科。

完整的SSL加密访问对用户和复制都原生支持。一个完整功能的加密函数库也可供数据库用户使用。

PostgreSQL通过命令类型提供基于角色的访问权限来访问数据。PostgreSQL还提供行级隐私安全、医疗和军事级安全。

函数可通过定义者的权限进行执行，而视图可以通过安全屏障来定义用于确保在其它处理之前强化安全。

PostgreSQL的所有层面由活跃的安全团队进行评估，已知的漏洞都会进行分类和报告：http://www.postgresql.org/support/security/。

易用性

因开发过程要求有文档修改而存在清晰、全面且精准的文档。每次发布均有数百个小修改，对使用边边角角进行打磨，由知识渊博的用户提供。

PostgreSQL对于不同操作系统中的小型系统和大型系统的运作方式相同。

客户访问和驱动对每种语言和环境都存在，因此对于现在或将来选择哪种类型的开发环境不存在限制。

紧密地遵循SQL标准，不存在怪异的行为，如默默地对数据截取。

文本数据由允许1 B到1 G存储的单个数据类型所支持。这一存储以多种方式进行优化，因此1 B 进行了有效的存储，并且对更多大值进行了自动的管理和压缩。

PostgreSQL有最小化配置参数数量的清晰策略，并且通过每次发布，我们得出自动优化这些设置的方式。

可扩展性

PostgreSQL设计用于调试可扩展。数据库扩展可使用CREATE EXTENSION轻松加载，它自动进行版本检查、依赖和其它方面的配置。

PostgreSQL支持用户定义的数据类型、运算符、索引、函数和语言。

PostgreSQL有很多扩展可以使用，包含PostGIS插件，它提供世界级的地理信息系统（GIS）功能。

性能和并发

PostgreSQL 11可显著地实现对4个套接字服务的每秒1百万次以上的读操作，并且它的基准为可持续进行3万次每秒的写事务操作，根据硬件会存在不同。通过高级硬件，可以实现更高级的性能。

PostgreSQL有一个高级优化器，考虑大量的join类型、利用用户数据统计来引导它的选择。PostgreSQL提供最广泛的常规可用的数据库服务的索引类型，完全支持所有的数据类型。

PostgreSQL提供MVCC，它启用reader和writer来避免相互的阻塞。

放在一起，PostgreSQL性能特征允许一个混合的事务性系统的工作负载及复杂的搜索和分析任务。这很重要，因为它表示我们不需要一直从生产系统卸载数据，并将其重载到分析数据存储中，而仅仅是为了执行了一些临时的查询。PostgreSQL的能力让其成为新系统的数据库选择，以及几乎所有情况中正确的长期选择。

伸缩性

PostgreSQL 11在一个节点到4个CPU套接字之间的伸缩性良好。PostgreSQL有效地运行几百个活跃会话，以及在使用会话池时几千个连接的会话。进一步的伸缩性在每年的发行版中予以实现。

PostgreSQL通过热备功能提供节点读取扩展。多节点伸缩性正在积极的开发中。起点是这是双向复制（在第十二章：复制和升级中进行讨论）。

SQL和NoSQL数据模型

PostgreSQL非常紧密地遵循SQL标准。SQL自身不强制使用任何特定的模型类型，因此PostgreSQL可以同时在同一个数据库中轻易地使用多种模型类型。

通过PostgreSQL作为关系型数据库，我们可以利用任意级别的去规范化，从完全的完整的第三范式（3NF）到理想加规范化的星级模式模型。PostgreSQL扩展了关系型模型来提供数组、行类型和range类型。

一个以文档为中心的数据也可以使用PostgreSQL的文本、XML和二进制JSON（JSONB）数据类型，通过针对文档优化的索引及全文本搜索功能提供。

使用hstore扩展还支持键值对存储。

流行度

几年前MySQL由一个商业数据库金主购买，在欧盟反垄断调查中认同了PostgreSQL是一个有力的竞争对手。它显然是真实的，PostgreSQL的用户基础已经历10多年的持续增长。

很多投票都表明PostgreSQL是构建新的企业级应用的热门数据库。PostgreSQL的功能集吸引了很多拥有严肃应用的用户。金融服务公司可能是PostgreSQL的最大用户群体，而政府、电信公司以及其它部门也是它的强力用户。这一流行度遍布全球：日本、厄瓜多尔、阿根廷和俄罗斯拥有大量的用户群体，美国、欧洲及澳大利亚也是如此。

亚马逊Web服务（AWS） 的首席技术官Dr. Werner Vogels称PostgreSQL是一个神奇的数据库，并接着说道PostgreSQL已成为众多企业开发人员和初创公司所喜爱的开源关系型数据库，驱动着顶尖的地理空间和移动应用。AWS最近透露PostgreSQL是他们增长最快的服务。

商业支持

很多人都评论说企业在投资开源技术之前需要大量的商业支持。强大的支持由全球的很多公司所提供。

作者（Gianni和Simon）就职的2nd quadrant，就提供对开源PostgreSQL的商业支持，提供24/7的英语和西语bug修复解决次数的支持。

很多其它公司对特定的地理区域、垂直市场和专业技术栈提供强大和资深的支持。

PostgreSQL也可作为大量公司的托管或云解决方案，因为它在云环境中的运行非常良好。

完整且保持更新的公司列表请见：http://www.postgresql.org/support/professional_support/。

研究和开发基金

PostgreSQL最初是加州大学伯克利分校的一个开发研究项目，那是20世纪80年末和90年代初。直到90年代末进一步的工作都由志愿者完成。然后，第一个专业的开发者投身进来。随着时间的推移，越来越多的公司和研究小组加入进来，支持众多的专业贡献者。进一步的研究和开发基金由NSF提供。该项目也以针对云计算的4CaaST项目以及针对可扩展数据分析的AXLE项目的形式接收了欧盟第七框架计划的资助。AXLE值得专门的介绍，因为它是一个三年项目，旨在增强PostgreSQL的商业智能的能力，尤其是针对超大型数据库。该项目涵盖安全、隐私、与数据挖掘的集成和可视化工具以及针对新硬件的接口。

有关AXLE项目的更多详情可参见http://www.axleproject.eu。PostgreSQL开发的其它基金来自于直接资助功能的用户和销售基于PostgreSQL的产品和服务的公司。

获取PostgreSQL

PostgreSQL是100%的开源软件，可自由以你所选的任何方式使用、修改或重新发布。它的证书是许可的开源证书，非常类似伯克利软件发行（BSD）证书，但有一部分区别，因此称之为PostgreSQL证书（TPL）。

如何获取…

PostgreSQL已由众多不同的应用包所使用，因此你会发现在你的服务器上已进行了安装。大量的Linux发行版都将PostgreSQL作为其标准安装包的一部分，或将其放在安装盘中。

需要当心的一件事是所包含的PostgreSQL版本可能不是最新版。它通常是操作系统版本发布时的最新大版本。一般没有理由保持为该版本，它并没有更好的稳定性，后面的生产版本像早前的版本一样被不同的Linux发行版本所支持。

如果你手上还没有软件拷贝，或者没有最新版，可以通过http://www.postgresql.org/download/下载针对大量操作系统的源代码或二进制包。

安装的具体情况随平台不同而存在很大的区别，没有特别的技巧或小节进行讲解。只需要按照安装指南，就可以上手开干了。这里我们刻意的避免描述安装过程，以确保不会对官网发布的帮助信息断章取义或越俎代庖。

如果你想要接收最新消息的邮件更新，可以订阅PostgreSQL的通告邮件列表，它包含来自所有支持PostgreSQL的商家的更新。每个月你还会收到有关核心PostgreSQL新发布、相关软件、会议和用户组信息的一些邮件。保持了解这些开发的进度是很有价值的。

ℹ️更多有关PostgreSQL通告邮件列表的信息，可访问http://archives.postgresql.org/pgsql-announce/。

运行原理…

很多人都会问如下的问题：它怎么会免费呢？你确定我不会支付任何人费用吗？谁不求回报的放出这个软件？

PostgreSQL这样的开源应用是基于社区的，大量的贡献者执行任务来让整个过程运作起来。对很多这些人，他们是作为专业人员而非业余爱好进行参与的，他们会这么做的原因是通常对于贡献者及他们雇主等人这里有巨大的价值。

你可能不相信。也不强求你相信，因为它切切实实地在运作中。

扩展知识…

记住PostgreSQL不仅仅只是那个核心软件。有大量的网站为PostgreSQL提供插件、扩展和工具。你也可以找到描绘能在工作中帮助到你的有用技巧和新发现的博客大军。

除这些以外，大量的专业公司在你需要时也可以为你提供帮助。

连接PostgreSQL服务

如何访问PostgreSQL？

连接数据库是很多人对于PostgreSQL的初体验，因此我们希望让它变成一次美好的体验。下面就开始，同时解决我们在这个旅程中所遇到的问题。记住连接需要是安全的，因此我们可能需要遵循一些规则来确保想要访问的数据是安全的。

在对数据库执行命令之前，我们需要连接数据库服务器来给我们一个会话。

会话设计为长久存在，因此你可以连接一次，执行很多请求并最终断开连接。在连接时会一些开销。如果你反复连接和断开连接就要注意了，可能需要研究一下连接池的使用。连接池允许预连接会话在你想要重新连接时快速提供服务。

准备工作

首先，缓存你的数据库。如果不知道在哪里，你可能会存在访问它的困难。可能会有一个以上的数据库，而你需要知道应该访问的那个数据库，还要有连接它的权限。

如何连接…

你需要指定如下参数来连接PostgreSQL：

• 主机或主机地址
• 端口
• 数据库名
• 用户
• 密码（或所拥有的其它方式的认证）

要进行连接，必须在host上运行有PostgreSQL服务，监听着某个端口号port。在这个服务器上，名为dbname的数据库和名为user的用户也必须存在。主机必须显式地允许来自客户端的连接（在启用对网络/远程用户的访问一节中进行讲解），并且你还必须使用服务端指定的方法传递认证信息，例如，如果服务端要求另一种形式的认证的话指定密码是不生效的。

几乎所有的PostgreSQL接口都使用libpq接口库。在使用libpq时，大部分连接参数的处理都是相同的，因此我们只需要进行一次讨论。

如果你没有指定前述参数的话，PostgreSQL会查找通过环境变量所设置的值，如下：

• PGHOST或PGHOSTADDR
• PGPORT（如已设置请将其设置为5432）
• PGDATABASE
• PGUSER
• PGPASSWORD（这肯定是不予推荐的）

如果你指定了前4个参数，但未指定密码，PostgreSQL会查找密码文件，在避免硬编码密码一节会进行讨论。

一些PostgreSQL接口直接使用客户端-服务端协议，因为默认的处理方式可能会不同。我们所需要提供的信息并不会存在很大差别，因此查看那个接口的具体语法。

连接详情也可通过使用统一资源定位符（URI）的格式来指定，如下：

psql postgresql://myuser:mypasswd@myhost:5432/mydb

以上指定我们将连接psql客户端应用到位于myhost主机的PostgreSQL服务端，端口号为5432，数据库名为mydb，用户名为myuser，密码为mypasswd。

ℹ️如果你没有在前面的URI中设置mypasswd，会弹出要求你输入密码。

运行原理…

PostgreSQL是一种客户端-服务端数据库。它所运行于的系统称为主机。我们可以远程通过网络访问PostgreSQL服务。但必须指定PostgreSQL，即主机名，或hostaddr，即 IP 地址。如果你们所做的TCP/IP连接是针对同一个系统的话可以将主机指定为localhost。通常使用Unix套接字连接会更好，会通过主机是否以斜杠(/)开始检验，名称通常为目录名称（默认为/tmp）。

在任何系统中，都可以有一个以上的数据库服务。每个数据库服务监听一个网络端口，端口不能在同一个系统中的服务之间进行共享。PostgreSQL的默认端口号是5432，已通过互联号码分配机构（IANA）进行注册，这时独家分配给PostgreSQL的端口（可以在大部分*nix 服务器上的/etc/services文件中进行查看）。如存在多个端口号可用于唯一标识一个指定的数据库服务。IANA是一个协调不同互联网协议可用号码分配的一个组织。

一个数据库服务有时也被称为数据库集群，因为PostgreSQL允许我们在每个服务上定义一个或多个数据库。每个连接请求必须指定具体的某个数据库，通过dbname来进行辨别。在你进行连接时，只能看到在该数据库中所创建的数据库对象。

数据库用户用于识别连接。默认，对某一用户的连接数量并没有限制。在启用对网络/远程用户的访问一节中，我们将讲解如何进行这一限制。在PostgreSQL最近的一些版本中，用户被称之为登录角色，但很多地方还是会让我们想起早期命名，且在很多方面也仍然讲得通。一个登录角色是被分配CONNECT权限的角色。

每个连接基本会以相同的方式进行认证。这在服务级别中进行定义：如果管理员配置要求进行认证，那么在连接时客户端认证就是一个必选项。

在连接之后，每个连接一次可以有一个活跃的事务，并且每次可以有一个完整的活跃语句。

服务端会定义对它所能服务的连接数的限制，所以在服务端获取过多连接时会拒绝连接请求。

扩展知识…

如果你已通过psql连接到了数据库服务并且想要确认你以正确的方式连接到了正确的地方的话，可以执行以下的部分或所有命令。以下是显示当前数据库的命令：

以下命令显示当前用户ID：

下面一个命令显示当前连接的IP地址和端口，除非你使用的是Unix套接字，那么这两个值都是NULL：

出于很明显的原因用户的密码不可通过通用SQL进行访问：

你还可以执行如下命令：

PostgreSQL9.1 版本以后，你还可以使用新的psql元信息命令\conninfo。这会在在同一行显示以上的大部分信息：

其它内容

要了解连接，有很多的信息片断。本章中涉及到了其中的一些，其它的将在第六章：访问权限中进行讨论。更多详情请参见PostgreSQL服务的文档。

启用对网络/远程用户的访问

PostgreSQL有大量的发行版本。其中的很多，你都会发现出于安全考虑初始禁用于远程连接。

如何实现…

默认，假定数据库用户有与系统的用户名相同的话，PostgreSQL向使用Unix套接字的客户端提供访问。本节我们将演示如何启用其它连接方式。

ℹ️本节中，我们将讲解配置文件，在第三章：数据库配置中的查找当前配置项一节可能找到相关内容。

步骤如下：

1. 在postgresql.conf文件中添加或编辑该行：
   
   listen_addresses = '*'

   1	listen_addresses = '*'

2. 在 pg_hba.conf的第一行中添加如下行来允许所有用户通过加密的密码来访问所有数据库：
   
   # TYPE DATABASE USER CIDR-ADDRESS METHOD host all all 0.0.0.0/0 md5

   1 2	# TYPE DATABASE USER CIDR-ADDRESS METHOD host all all 0.0.0.0/0 md5

3. 在修改完listen_addresses后，我们重启PostgreSQL服务，这会在第三章：数据库配置中的更新参数文件一节中进行讲解

ℹ️本节假定postgresql.conf中没有包含其它配置文件，默认安装后即是如此。如果修改postgresql.conf中的listen_addresses并没有生效，可能是由其它配置文件的覆盖所致。查看第三章：数据库配置中的更新参数文件一节来获取更多详情。

运行原理…

listen_addresses参数指定所监听的IP地址。这允许我们灵活的启用或禁用对同一系统中多网卡接口（NIC）或虚拟网络的监听。大多数据情况下，我们希望接受对所有NIC的连接，因我们使用了*，它表示所有的IP地址。

pg_hba.conf文件包含了一组基于主机的认证规则。每条规则都按顺序进行考虑，直接使用了某条规则或者请求被某一条reject方法所拒绝为止。

前述的规则表示指定对任意IP地址的任意用户或数据库的远程连接，都会要求进行一个MD5加密的密码的认证。以下为MD5加密密码所需的参数：

• Type：其中host表示一个远程连接
• Database：其中all表示所有的数据库。其它名称精确匹配，如果前面添加了加号(+)，那么表示的是一个组角色而不是单个用户。你还可以指定逗号分隔的用户列表，或使用@符号来包含一个带有用户列表的文件。你甚至可以指定为sameuser， 这样在指定的用户和数据库名称相同时则规则匹配。
• User：其中all表示所有用户。其它名称精确匹配，在前面添加了加号（+）前缀时，表示是组角色而非单个用户。你还可以指定一个逗号分隔的用户列表，或使用@符号来包含一个所有用户列表的文件。
• CIDR-ADDRESS：它包含两部分：一个IP地址和一个子网掩码。子网掩码指定为组成掩码的 IP 地址前置位的数量。因此，/0 表示 IP 地址的0位，所以所有 IP 地址都会被匹配。例如 192.168.0.0/24表示匹配前24位，因此192.168.0.x 形式的 IP 地址会进行匹配，你也可以使用samenet或samehost。
• Method：其中表示PostgreSQL会要求客户端提供一个通过MD5方式加密的密码。另一种常用的设置是trust，即表示无需认证。其它的认证方式包括GSSAPI, SSPI, LDAP, RADIUS和PAM。PostgreSQL的连接也可以使用SSL，这时需要提供客户端SSL证书来进行认证。参见第六章：访问权限中的使用SSL进行客户端认证一节了解更多详情。

不要使用password设置，因它会以普通文本发送密码。如果你使用SSL进行加密这也并不真的是一个安全问题，而通常使用MD5并没有什么坏处，这样对于非SSL连接你也可以获取到更多的安全。

扩展知识…

在PostgreSQL的早期版本中，通过网络进行访问通过启动服务时在命令行中添加-i 开关来进行启用。它还是一个有效选项，但含义如下：

因此，如果你读到了如何设置的笔记并且其中提到了这个选项的话，请注意那可能是很久之前已不再适用的笔记。它们不一定是错误的，但值得进一步查看是否做过什么修改。

其它内容

查看安装和针对具体操作系统的文档来查看各个文件的标准位置。

使用图形化管理工具

图形化管理工具通常是系统管理者们所要使用的。PostgreSQL有很多的工具可选。本书中，我们将讲解pgAdmin4和OmniDB，它们提供对PostgreSQL和其它数据库的访问。

这两个工具都是对PostgreSQL发送和接收SQL的客户端应用，向你展示返回的结果。管理客户端可以访问很多数据库服务，允许管理众多服务端。两个工具都既可使用独立应用模式又可在浏览器中使用。

如何实现…

pgAdmin 4通常称之为pgAdmin。最后那个4有很长的历史了，但这不是重点。它不是发行层面的，pgAdmin 4代替了早期的pgAdmin 3。

在启用pgAdmin时，会弹出一个注册新服务的窗口。

在General标签中指定一个服务名称，然后点击Connection并填写5个基本连接参数，及一些其它信息。你应当取消勾选Save password? 复选框：

如果你有多个数据库服务的话，可以分组到一起。我推荐将所有复制服务放在同一个服务端组中。为每个服务设置一个有意义的名称。

一旦你添加了一个服务，可以对其连接并显示相关的信息。

默认的页面是Dashboard，它展示一些基于从服务端拉取数据的一些有意思的图表。这非常有用，可点击Statistics标签。

然后你会获取一个主浏览界面，对象树状视图在左、统计在右，如下图所示：

pgAdmin可轻易地展示通过PostgreSQL有很多的可用数据。信息是上下文相关的，允许你快速、轻松导航并查看所有内容。这些信息并不是动态更新的，仅会在你点击刷新时都会更新，因此在使用该应用时请记住这一点。

pgAdmin还提供授权向导（Grant Wizard）。这对于DBA们进行审查和立即修复是非常有用的。

pgAdmin查询工具允许你拥有多个活跃会话。查询工具有一个非常美观的视觉Explain功能，它对你的查询展示EXPLAIN图解：

运行原理…

pgAdmin提供大量的功能，其中很多功能其它工具也同样提供。这给我们选择想要使用哪种工具的机会。出于很多原因，最好是为相应的任务选择正确的工具，这一直关乎专业技术、经验和个人品位。

pgAdmin向PostgreSQL服务端提交SQL，并快速、轻松地展示结果。作为一个浏览工具它非常之棒。也是执行一些小型DBA任务的理想工具。你可能从这里的评论中也猜到了，我并不对每个任务都推荐使用pgAdmin。

对于DBA而言脚本是一项重要的技能。保留一个执行任务的拷贝，如果发生问题时可以编辑并重新提交。将脚本中的所有任务放到一个事务中也很容易，但通过当前的图形化工具则无法实现。pgAdmin提供有pgScript，它仅适用于pgAdmin，因此迁移起来会更为困难。我强烈推荐使用psql工具，它有很多你慢慢会觉得非常欣喜的其它功能。

虽然我推荐使用psql来作为脚本工具，但很多是喜欢用它作为一个方便的查询工具。一些人会觉得很奇怪，并认为这是专家们才做的选择。psql的两个伟大功能是对SQL的在线帮助和tab自动补全功能，这让你无需记住语法就能快速构建SQL。参见使用psql查询和脚本工具一节来获取更多信息。

pgAdmin还提供pgAgent，它是一个任务调度器。再说一下，还有很多方便的调度器可以使用，你有可能会想要使用其它的工具。本书中并不讲解调度器。

一个快速的警告！当你在pgAdmin中创建一个对象时，如果你在对象名中到处使用大写字母的话对象会使用混合的大小写名称来创建。如果想要访问名为MyTable的表格，访问该表的唯一方式是将MyTable放到双引号当中。参见第五章；数据表及数据中的使用带引号名称处理对象一节。

OmniDB

OmniDB设计用于在同一个界面中访问PostgreSQL, MySQL, MariaDB和Oracle，但它确保了为PostgreSQL数据库提供完整的功能。

OmniDB的开发很快，每月发布一次功能，因此我推荐你通过https://omnidb.org/查看最新信息。

OmniDB提供一个非常响应式的界面，并在设计时考虑到了完整的安全因素。它可用于桌面应用，也可通过你所喜欢的浏览器来访问网页服务提供服务。

如何实现…

OmniDB拥有标准的树状浏览界面，可对你访问的数据每个数据库服务提供多标签访问。同时连接多个PostgreSQL, MySQL和Oracle数据库服务非常容易：

OmniDB有一个带有代码补全的调试的SQL编辑器。 EXPLAIN ANALYZE输出对花费最多时间的区域进行高亮上色显示：

或者如果你更喜欢命令行的感觉，可以使用Console标签：

可以对查询计划进行可视化显示：

OmniDB中的管理员可以通过图形化来管理用户。界面提供了添加、编辑和删除用户以及将某人添加为超级用户的功能，然后这些用户可以创建对PostgreSQL、MySQL、MariaDB和Oracle的连接，均通过一个统一的网页来进行管理。也可以通过SSH通道来进行连接：

为让在PL/pgSQL中开发代码的过程更为轻松，OmniDB提供了一个强大且功能丰富的调试器。这个调试器作为SQL的内标签，并在5个不同的标签中提供参数、变量、结果、消息和统计数据的信息：

OmniDB中另一个非常有用的功能是监控仪表盘。这个仪表盘为我们提供想要监控的重要数据的实时统计，如系统内存使用、CPU占用和锁：

OmniDB的设计非常灵活，并且是一个可扩展的工具。虽然它有一些默认图表，但你可以使用Python和JSON来编写新图表或使用已有图表作为图表来进行增强和扩充。OmniDB提供一个插件 API，允许用户编写和发布具有扩展能力的自有插件。

其它内容

你可能还对PostgreSQL的各类商业工具有很感兴趣。PostgreSQL的软件目录中给出了完整的列表：http://www.postgresql.org/download/products/1。

使用psql查询和脚本工具

psql是PostgreSQL的内核发行版中所提供的一种查询工具，因此在所有环境中均可使用，它的运行类似所有其它工具。这让它成为我们开发可迁移应用和技术的一种理想选择。

psql同时提供交互查询工具和脚本工具所需的功能。

准备工作

从这里开始，我们都会假定psql命令足够允许你访问PostgreSQL服务。这会假定所有的连接参数都是默认的，但可能并非如此。

完整书写，连接参数有如下两种选择：

端口（-p）的默认值是5432。而默认mydb和myuser都与操作系统的用户名相同。Windows上myhost的默认值为localhost，而在Unix上，我们使用针对Unix套接字连接的默认目录。这一目录的位置根据发行版会有不同，在编译时进行设置。但是请注意其实你并不需要知道这个值，因为对于本地连接来说，服务端和客户端一般是一起编译的，因此它们使用相同的默认值。

如何实现…

执行单条SQL命令并打印输出的命令是最简单的，如下所示：

-c命令是非交互式的。如果我们希望执行多条命令，可以在一个文本文件中写下这些命令，然后使用-f参数来执行它们。这个命令加载一个非常小且简单的示例集：

在执行成功时会产生如下输出：

examples.sql脚本非常类似于PostgreSQL备份工具所生成的dump文件，因此这类文件及它所产生的输出非常常见。在命令成功执行时，PostgreSQL输出一个与该命令名相同的命令标签，这就是以上的输出产生的方式。

psql工具也可以和-c及-f 一起同时使用，每个选项都可以多次使用。这时，它会连续执行这些命令：

psql工具也可以在交互式模式中使用，这也是默认的模式，它无需任何选项：

你需要的第一个交互命令如下：

然后可以输入SQL或其它命令。以下是一条你可能永远不会使用的交互式命令：

不幸的是，你只能输入quit，不能使用\exit或其它选项。抱歉，必须是\quit,或其简写 \q。

运行原理…

在psql中，你可以输入如下两种类型的命令：

• psql元命令
• SQL

元命令是一个针对客户端的命令，而SQL是发送到数据库服务端的。一个元命令的示例为\q，它告诉客户端取消连接。所有以反斜杠\作为非空字符开头的命令都会被看作这种类型的元命令。

如果它不是一个元命令的话， 就是一条SQL。我们读取SQL直到分号截止，因此我们可以将SQL语句放在多行中，并以我们认为便捷的方式对其进行格式化。

help是唯一的例外。我们为一脸茫然的用户提供了这一命令，非常体贴，就让我们从这里开始吧。

有两种类型的help命令，如下：

• \?：提供对psql元命令的帮助
• \h：提供对于具体SQL命令的帮助

考虑使用如下命令片断作为示例：

我觉得这是发现和记住选项及语法很好的方法。你还会庆幸可以回滚到此前使用过的命令。

你会从tab补全中获取巨大的好处，它会在你按下tab键时为你补充语法中的下一部分。这也适用于对象名，因此你可以只输入前几个字母，然后按下Tab，所有的选项就都会显示了。因此，你可以输入几个足以让对象名唯一的字母，然后按下Tab键获取剩下的名称。

单行注释的开头使用双横杠，如下：

多行注释类似于C语言和Java：

一开始你可能会觉得psql使用的奇怪的反斜线命令会很可怕。我希望你能花一点时间理解这一界面并保持深入了解更多信息。psql是PostgreSQL中最惊喜的部分之一，在配合其它工具使用时它对于数据库管理任务极其有用。

扩展知识…

psql可在不同发行版本中使用，并且对老版的支持也非常好。对于更新的服务端版本它可能无法使用，那么请使用与所访问服务端保持一致的最新客户端版本。

其它内容

查看psql其它一些有用的功能，如下：

• 信息功能
• 输出格式化
• 使用 \timing命令执行定时
• 输入/输出和编辑命令
• 自动启动文件，如.psqlrc
• 可替代参数（变量）
• 访问系统命令行
• 跨标签视图
• 条件执行

安全地修改密码

如果你使用密码认证，那么你可能会希望不时地修改密码。

如何实现…

最基本的方法是使用psql工具。\password命令会弹出要求输入新密码及再次确认密码。连接psql工具并输入如下命令：

输入一个新密码。这会让psql向PostgreSQL服务端发送一条SQL语句，它包含一个已加密的密码字符串。所发送的SQL语句示例如下：

确保你使用的是SCRAM-SHA-256加密算法，而非更老且更容易破解的md5加密算法。不论如何，请不要使用postgres来作为密码。这会让数据库暴露给那些游手好闲的黑客，因此请使用更为复杂的密码。

也请不要忘记你的密码。如果你无法访问数据库也就很很难去维护数据库。

运行原理…

既然修改密码只是一条SQL语句的事，那么任何界面都可以操作。其它工具也可以实现，如：

• pgAdmin4
• phpPgAdmin

如果你不使用这些主流方法来修改密码的话，还是可以自己实现，在任意界面中使用SQL。注意应对密码加密，因为如果你像下面这样以普通文本提交密码的话，它就会以普通文本发送到服务端：

所幸的是，这时密码仍以加密形式保存起来。但它也会在psql历史及根据实际的日志级别设置在任意服务和应用日志中以普通文本进行记录。

PostgreSQL并不会强制密码修改周期，因此你可能会希望使用更高级的加密机制，如GSSAPI, SSPI, LDAP或RADIUS。

避免硬编码密码

我们都知道硬编码密码是一个糟糕的想法。本节展示如何在安全密码文件中保存密码。

准备工作

并非所有的数据库用户都需要密码，一些数据库使用其它方式的验证。除非你知道需要使用密码认证且知道自己的密码，否则请不要执行这些步骤。

首先，在此前设置为硬编码密码的地方进行删除。在程序的连接字符串中完全删除password = xxxx字样。否则在测试密码文件时，硬编码的设置会覆盖掉你准备放在文本的中内容。保持密码在密码文件中硬编码并不会更好。也不推荐使用PGPASSWORD，所以请同样删除。

如果你觉得有人看到了你的密码，请修改密码并将其放在安全的密码文件中。

如何实现…

在进行连接时密码文件通常包含5个字段，如下：

将其修改为：

密码文件使用一个名为PGPASSFILE的环境变量进行定位。如未设置PGPASSFILE，则会搜索默认的文件名的位置，如下：

• 在*nix系统中，会查找~/.pgpass
• 在Windows系统中，会查找%APPDATA%\postgresql\pgpass.conf，其中%APPDATA%是路径中的应用数据子目录（对我而言是 C:\）

ℹ️别忘了为文件设置权限，这样才能保持安全。虽然默认的路径是安全的，但在Windows中并没有强制文件权限。在*nix系统中，应使用如下命令：chmod 0600 ~/.pgpass。

如果忘记的话，PostgreSQL会忽略.pgpass文件。而psql工具会发出一个清晰的警告，也别忘了其它工具可能会默默地失败。

运行原理…

很多人不管在不在 Windows 系统中都将密码文件命名为.pgpass，因此在他们这么做时请不要产生混淆。

密码文件可包含多行。每一行与所请求的host:port:dbname:user组合进行匹配，直到我们发现匹配的一行。那时就会使用该密码。

每一项可以为一个明确的值或者使用匹配所有内容的通配符 *。不支持部分匹配。通常适当的权限，用户可以连接任意数据库。在dbname和port字段中使用通配符可以讲得通，但在其它字段中就不应使用通配符了。以下是使用通配符的一些示例：

• myhost:5432:*:sriggs:moresecurepw
• myhost:5432:perf:hannu:okpw
• myhost:*:perf:gianni:sicurissimo

扩展知识…

这对于只有几个数据库服务器时会是一个非常好的改进。如果你有很多不同的数据库服务器的话，可能会希望使用一个连接服务文件来进行取代（参见使用连接服务文件一节），或者甚至是将存储内容放到一个轻量目录访问协议（LDAP）服务器上。

使用连接服务文件

在连接参数增长时，你可能会想要考虑使用一个连接服务文件。

连接服务文件允许我们对一组连接参数赋予单个名称。这可以使用中心化访问，来避免让单个用户知道数据库的主机和端口号，对于未来的修改也更具保障。

你既可以设置一个系统级的文件也可以设置针对单个用户的文件。这些文件的默认文件路径分别是/etc/pg_service.conf和~/.pg_service.conf。

系统级的连接文件通过同一个地方来控制不同用户的服务名称，而单个用户的文件仅应用于该具体用户。记住单个用户的文件会覆盖系统级文件 – 如果一个服务同时在两个文件中定义的话，会使用具体用户的文件。

如何实现…

首先，创建一个名为pg_service.conf的文件，添加内容如下：

你可以将其拷贝到/etc/pg_service.conf或其它所认同的中央位置。然后可以将PGSYSCONFDIR环境变量设置为该目录位置。

另一种方式是你可以将其拷贝到~/.pg_service.conf中。你果想要使用不同的名称，可设置PGSERVICEFILE。不管是哪种方式，然后你可以指定一个连接字符串，如下：

也可以使用名为PGSERVICE的环境变量来设置该服务。

运行原理…

这一功能仅适用于libpq连接，因此对于JDBC不适用。

这一连接服务文件也可以用于指定用户，但这表示用户名会进行共享。

pg_service.conf和.pgpass文件可共同使用，或者你可以仅使用其中的某一个。注意 pg_service.conf 文件是共享的，因此它并不适合放置密码。按用户的连接服务文件是非共享的，但不管是哪种方式，将内容分隔并将密码放到.pgpass中都是最好的选择。

排查失败的连接

本节的所有内容都关乎在出现问题时你应该做的事。

请记住90%的问题都是理解错误，你很快就可以回归正轨。

如何实现…

我们制作一个清单，可以在连接出错时照着操作：

• 查看数据库名和用户是否准确。你可以在一个系统中请求服务，而所请求的数据库位于另一个系统中。重新检查认证信息，确保没有将内容搞混，如把数据库名当成用户名或反之等等。如果你接收到过多的连接的话，那么可能需要在连接前取消另一个会话的连接，或者等待管理员重新启用连接。
• 查看显式的拒绝消息。如果收到了pg_hba.conf rejects connection for host…的错误消息，这表示你的连接请求被该服务器的数据库管理员显式地拒绝了。你将不能使用这些认证信息从当前的客户端系统来完成连接。尝试联系管理员没有什么意义，因为明显你在进行一个你不应当违反的安全策略。
• 查看隐式的拒绝消息。如果你接收到了no pg_hba.conf entry for…的错误消息，这表示没有匹配你的认证信息的显式规则 。这可能是管理员部分的疏忽，在复杂的网络中很常见。联系管理员并请求设置未来你的连接应被允许的规则或显式地进行拒绝。
• 查看连接是否可使用psql。如果你在使用psql命令行工具之外的其它方式连接PostgreSQL，也可进行切换。如若可成功进行psql连接，但无法让主连接正确运行，问题可能在你所使用的本地接口。
• PostgreSQL 9.3及此后的版本自带pg_isready工具，它通过建立最小连接来查看本地或远程数据库服务的状态。仅有主机名和端口是必填项，对于不知道数据库名、用户名或密码的朋友这就很棒了。以下是可能会输出的结果：
  • 服务在运行并接受连接。
  • 服务在运行但不接受连接（因为正在启动、关闭或还原的过程中）。
  • 尝试进行连接但失败了。
  • 因客户端问题（无效参数、内存不足）未进行连接尝试。
  • 查看服务是否启动。如果服务关闭，则无法连接。这里典型的问题是混淆了所要连接的服务端。你需要指定主机名和端口，所以可能混淆这些详细信息。
  • 查看服务端是否启动并接受新连接。正在关闭中的服务端是不接受新连接的，但除超级用户外。同样，备用服务器可能没启用hot_standby参数，也不会让你进行连接。
  • 查看服务端是否在正确的监听，并检查服务端实际监听的端口。确认进入的请求到达了listen_addresses参数中所列出的端口。查看是否对远程连接设置了*、本地连接设置了localhost。
  • 查看数据库名和用户是否存在。该数据库或用户可能已不再存在。
  • 查看连接请求，即连接请求是否成功并在连接过程中断掉了。可在如下启用如下参数时通过查看服务端日志进行确认：
    
    log_connections = on log_disconnections = on

    1 2	log_connections = on log_disconnections = on

  • 查看断开连接的原因。如果你连接的是备服，可能是因为热备冲突而断开连接，参见第十二章：复制和升级获取更多信息。

扩展知识…

客户端认证和权限在后续的PostgreSQL发行版中会是发生重大改变的领域。你也发现在维护发行级别上存在不同。PostgreSQL有关这一主题的内容可参见：http://www.postgresql.org/docs/current/interactive/client-authentication.html。

在查看手册或寻找支持前保持检查使用的发行级别。很多问题都只是因混淆了不同发行级别之前的功能而导致的。

喜欢 (1)赏

[]

分享 (0)