PostgreSQL 9 Administration Cookbook （第2版）中文版

本书的作者是PostgreSQL的主要开发人员，译者是PostgreSQL中国社区和中国用户会的核心成员。

本书前6章介绍了PostgreSQL基础，后6章由浅入深地介绍了PostgreSQL日常维护技巧、性能分析、备份恢复和重量级的复制技术。

是一本不可多得的管理类书籍。

本书提供了在线生产环境的PostgreSQL数据库的常见问题和故障快速解答，这些都基于作者作为PostgreSQL数据库培训师、用户和核心开发人员时的经验积累。每个技术都被拆分为小的方法或建议，先给出包含可工作代码的演示解决方案，然后解释这样做的原因及工作原理。

本书意在成为新用户和技术专家的桌面参考手册。

本书涵盖了PostgreSQL 9 所有的新功能。

黄坚

2001 年开始从事软件开发工作，拥有超过15 年的计算机软件开发经验，以及大量的电信

业务、计费、电商系统的架构和开发经验，并对计算机安全、反向工程等领域有浓厚的兴趣和

动手能力。从2011 年开始，积极参与PostgreSQL 数据库在国内的推广。是pgpool-II 中文手册

的维护者之一。

本书发行的时候，黄坚已经联合朋友创立了研发移动端云平台的湖南红手指信息技术有限

公司，并任职CTO。

常用网名：洞庭湖的泥鳅和Bambo。

谭峰（francs）

PostgreSQL 中国用户会核心组成员，热衷于博客分享技术心得，致力于 PostgreSQL 中文

技术推广，曾任杭州斯凯的PostgreSQL DBA 一职，维护超过100 个PostgreSQL 实例，拥有超

过六年的PostgreSQL 数据库运维经验。曾和黄坚共同翻译《PostgreSQL 9 Admin Cookbook（第

1 版）》，现就职于中国移动通信集团浙江有限公司，从事开源数据库管理和研究工作。个人博客：

https://francs3.blog.163.com/。

马海庆

现任职于广州市财经职业学校信息网络管理中心，一直从事网络以及数据中心的设计及建

设工作，熟悉PostgreSQL 数据库的架构设计以及运维。致力于推动PostgreSQL 在教育产业以

及智慧校园中的应用。

Simon Riggs 是2ndQuadrant 的CTO，PostgreSQL 社区活跃的代码提交者。作为

PostgreSQL 的主要开发人员，他为PostgreSQL 的贡献超过十年时间，期间，他为每个发行版

本都设计开发了很多新功能。他擅长的领域包括复制、性能调优、商业智能、管理和安全。在

他的领导下，2ndQuadrant 现在已经成为开源PostgreSQL 的领导开发者和PostgreSQL 项目的

白金赞助商，并广泛服务于欧美、亚太、中东和非洲地区成百上千的用户。

Simon 在很多交流会上都是活跃的发言者，并因在PostgreSQL 功能和复制方面的发言而

被广泛知晓。作为多种数据库的开发工程师、架构师、数据分析师和系统设计师，他在近三十

年间为遍布欧美的公司提供过服务。

Gianni Ciolli 是2ndQuadrant 意大利分公司的首席咨询师，从2008 年开始就从事开发、

咨询和培训工作。他在欧洲和海外的PostgreSQL 峰会中都有发言，他还擅长于函数语言和符

号计算（Symbolic Computing）等IT 技能。

Gianni 拥有数学博士学位，并在代数几何、理论物理和形式证明理论方面发表过研究报告。

他之前在佛罗伦萨大学任教师和研究员。

Gianni 在自由软件和开源软件领域工作了近二十年。在2001 到2004 年期间，他是PLUG

（Prato Linux User Group）公司的联合创始人和总裁。他曾多次组织意大利的PostgreSQL 交流

大会，在2013 年，他获选加入ITPUG（Italian PostgreSQL Users Group）董事会。

Hannu Krosing 是2ndQuadrant 的首席咨询师以及Ambient Sound Investments 的技术

顾问。作为Skype 公司的最初数据库架构师，他负责设计了用于复制和扩展技术的SkyTools

套件，为PostgreSQL 项目工作和贡献十二余年。

Gabriele Bartolini 是一个资深开源开发人员，2ndQuadrant 首席咨询师， 国际

PostgreSQL 社区的活跃成员。

Gabriele 拥有佛罗伦萨大学的统计学学位。他擅长于数据挖掘和数据仓库，在澳大利亚和

意大利从事过网页流量分析工作。

目录

第1章迈出第一步............................ ....................1

1.1介绍 .......................................................1

1.2获取PostgreSQL ........................................7

1.3连接到PostgreSQL服务 ...............................................8

……

第2章浏览数据库.................................................................................................................25

2.1介绍 ................................................................................................................................25

2.2当前服务程序的版本号是多少 ....................................................................................26

2.3服务程序运行时间是多少？ ........................................................................................27

2.4定位数据库服务的数据文件 ........................................................................................28

2.5定位数据库服务的日志文件 ........................................................................................31

……

第3章配置............................................................................................................................ 52

3.1介绍 ................................................................................................................................52

3.2阅读手册（RTFM） .....................................................................................................53

3.3规划一个新数据库 ........................................................................................................54

……

第4章服务控制.....................................................................................................................74

4.1介绍 ................................................................................................................................74

4.2手动启动数据库服务 ....................................................................................................75

4.3安全快速地停止服务 ....................................................................................................77

……

第5章表和数据.....................................................................................................................98

5.1介绍 ................................................................................................................................98

5.2为数据库对象选择恰当的名称 ....................................................................................99

5.3处理使用双引号括起来的对象名 ..............................................................................100

5.4确保相同的名称和相同的列定义 ..............................................................................102

……

第6章安全..........................................................................................................................128

6.1介绍 ..............................................................................................................................128

6.2PostgreSQL的超级用户 ..............................................................................................129

6.3收回用户对表的访问权 ..............................................................................................130

第7章 数据库管理...............................................................................................................164

7.1介绍 ..............................................................................................................................164

7.2编写一个脚本，要么全部执行成功，要么全部执行失败 ......................................165

……

第8章 监控和诊断...............................................................................................................207

8.1介绍 ..............................................................................................................................207

8.2检查用户是否已经连接上来 ...................................................................................... 211

8.3检查哪个查询在运行 ..................................................................................................213

8.4检查哪个查询正在运行或被阻塞 ..............................................................................216

……

第9章 常规维护...................................................................................................................236

9.1介绍 ..............................................................................................................................236

9.2控制自动数据库维护 ..................................................................................................237

9.3避免自动冻结和页损坏 ..............................................................................................241

……

第10章 性能和并发.............................................................................................................263

10.1介绍 ............................................................................................................................263

10.2寻找执行缓慢的SQL语句 ........................................................................................264

10.3从pg_stat* 视图收集常规统计信息 .........................................................................267

……

第11章 备份和恢复.............................................................................................................290

11.1介绍 ............................................................................................................................290

11.2理解和控制崩溃恢复 ................................................................................................291

11.3计划备份 ....................................................................................................................293

……

第12章 复制和升级.............................................................................................................330

12.1介绍 ............................................................................................................................330

12.2复制最佳实践 ............................................................................................................337

12.3基于日志文件传送的复制——不推荐使用 ............................................................338

……

译者序

PostgreSQL 的官网有这样一句话“The world’s most advanced open source database”。从这

一点就可以看出PostgreSQL 数据库的底蕴。PostgreSQL 的历史可以追溯到20 世纪70 年代的

Ingres 数据库，而其自成一套的MVCC 实现和对SQL 标准的支持，成为了其作为行业领先数

据库的坚实基础。

PostgreSQL 是一款功能丰富的开源数据库，它支持丰富的数据类型，除了常用的数据

类型外，还支持网络地址、数组、JSON、几何类型等，以满足应用程序开发需求。此外，

PostgreSQL 还支持局部索引、表达式索引、GIST/GIN 索引以及范围索引，支持物化视图、集

合查询、窗口函数；最重要的是，PostgreSQL 还支持众多丰富的外部扩展，外部扩展完善了

PostgreSQL 的功能，奠定了PostgreSQL 强大的扩展能力基础。

从2009 年开始，我就开始在寻求一些商业数据库的开源数据库替代产品，这时，

PostgreSQL 出现在我的视野中。对SQL 标准的支持、性能都满足我们系统的要求，且其非常

宽松的许可，非常符合我们的需求。之后，我就喜欢上了这个数据库，并逐渐引导公司和客户

将项目向PostgreSQL 迁移。同期，国内已经有大量公司使用PostgreSQL 数据库作为其主要的

业务数据库，其中就包含了谭峰当时所在的杭州斯凯网络。

从2011 年首届PostgreSQL 中国用户大会在国内召开，PostgreSQL 在国内的发展势头日渐

迅猛。然而，由于PostgreSQL 在国内引入的时机错过了互联网业发展的黄金十年，因此其知

名度和普及程度仍旧远远落后于MySQL 开源数据库，同样，中文资料也极度缺乏。但社区的

兄弟们努力付出，在何伟平老师翻译的8.1 版本中文手册的基础上，已经完成了9.3 版本的中

文手册的翻译，其中，在写序的这几天，9.4 版本的中文手册也刚刚翻译完成。

仅仅手册不足以快速培养出一名熟练的DBA，任何DBA 的成长都经历了反复的磨练和各

种血淋淋的教训。然而，《PostgreSQL 9 Administration Cookbook（第2 版）》恰恰是这样一本

帮助你快速成长为一名合格DBA 的经典管理类书籍。但是，要想成为合格的DBA，依旧需要

你反反复复地动手练习。

PostgreSQL 9 Administration Cookbook（第2版）中文版

VI

本书是第2 版，第1 版已经由我和谭峰在几年前进行了翻译，但由于种种原因，无法发行。

第一版英文版发行的内容主要是针对于PostgreSQL 9.1 版本提供了大量实战案例。第2 版的变

动内容主要集中于9.4 引入的针对企业应用方面的改动，大部分在于复制和备份相关的内容。

本书前六章介绍了PostgreSQL 数据库基础，后六章由浅入深地介绍了PostgreSQL 日常维

护技巧、性能分析、备份恢复和重量级的复制技术。通过此书读者能学习到新的PostgreSQL

数据库维护技巧，适合对PostgreSQL 有一定基础，同时想进一步了解PostgreSQL 的工程师，

尤其适合于有志于成为PostgreSQL DBA 的朋友，这是一本经典的不可多得的PostgreSQL 管理

类书籍。

为社区朋友提供高质量的书籍是我们翻译本书的最大动力。翻译工作是一份非常痛苦的工

作，非常考验参与翻译人员的毅力。翻译的过程中我们付出了大量的时间和精力，曾几度想放

弃，但为社区提供资料的动力支撑着我们一步步完成此书的翻译。

在翻译的过程中，我们尽量参照原文的语序进行翻译，并尽量使用流行的计算机词汇，但

某些只能通过意会的原文，我们只能按照我们的理解组织语句。另外，在翻译的过程中，有些

单词的翻译，可能随着时间的发展，中文用词会发生变化，甚至有些单词我们直接保留了英文，

希望读者能自己去体会其含义。

翻译过程中出现错误在所难免，敬请各位读者朋友批评指正。如果发现有翻译错误或者不

准确的地方，或者有其他疑问，请发邮件到albeta.huang@gmail.com，我们将尽快给您答复。

前言

PostgreSQL 是一款支持大量平台的高级SQL 数据库服务软件，并快速成为世界上最流行

的服务端数据库之一，尤其是在性能、稳定性以及丰富功能方面享誉盛名。PostgreSQL 是历史

非常悠久的开源数据库之一，完全免费使用，由一个非常多元化的全球性社区开发。总之，它

就是一个很酷的东西！

PostgreSQL 最明显的一个好处是，它是开源的，也就是说，你拥有一个非常宽松的许可

证来安装、使用以及发布PostgreSQL，而不需要付给任何人任何费用或版税。另外，众所周知，

PostgreSQL 是能长时间稳定运行的数据库，而且在很多情况下只需要少量维护甚至不需要维护。

总之，PostgreSQL 的保有成本很低。

《PostgreSQL 9 Administration Cookbook（ 第2 版）》提供了管理你的在线生产环境的

PostgreSQL 数据库所需要的信息。本书包含的一些见解直接来自于PostgreSQL 复制和恢复

功能的主要作者，以及来自最成功的创业公司Skype 的使用PostgreSQL 数据库的架构师。这

本最实际的指南将帮助开发人员操作在线数据库，以及通过任何开发框架使用Java、Python、

Ruby 以及.Net 支撑Web 或者企业软件应用。《PostgreSQL 9 Administration Cookbook（第2 版）》

在手，数据库管理工作万事不愁。

本实用指南为你的常见问题和故障提供快速解答，这些都基于作者作为PostgreSQL 数据

库培训师、用户和核心开发人员的经验积累。

每个技术方面都被拆分为小的方法或建议，先给出包含可工作代码的演示解决方案，然后

解释这样做的原因及工作原理。本书意在成为新用户和技术专家的桌面参考手册。

本书涵盖了PostgreSQL 9 所有的新功能。很快，你将能轻松自如地运行数据库了。

本书包含哪些内容

第1 章，迈出第一步。包含的主题有介绍PostgreSQL 9，下载及安装，连接到PostgreSQL

PostgreSQL 9 Administration Cookbook（第2版）中文版

XIV

服务，为网络/ 远程用户启用服务，使用图形化管理工具，使用psql 这个查询及脚本工具，安

全地修改你的密码，避免硬编码你的密码，使用连接服务文件，以及在连接失败时进行排错。

第2 章，浏览数据库。帮助你获得你正在使用的数据库服务版本以及服务时长。本章帮

助你定位数据库服务的文件，数据库服务的消息日志，以及数据库的系统标识符。这里还为你

展示如何列出数据库服务中的数据库，并提供一些方法，让你知道数据库中表的数量，数据库

和表占用的磁盘空间，以及谁是最大的表，表中有多少行，如何估计表中的行数，以及如何理

解对象的依赖关系。

第3 章，配置。包含的主题包括阅读手册（RTFM，Read The Fine Manual），规划一个新

数据库，在你的程序中更改参数，查看当前的配置参数，非默认的参数，更新参数文件，为特

定用户组设置参数，基本服务配置清单，以及添加一个外部模块到PostgreSQL 服务中。

第4 章，服务控制。主要内容包括手动启动数据库服务，快速安全地停止服务，紧急情

况下停止服务，重新加载服务配置文件，快速重启服务，拒绝新连接，限制用户只用一个会话，

以及强制用户从系统中退出。这里包含一些方法，用于帮助你确定你的多租户系统设计。你可

以学习如何使用多模式（schema），给用户他们自己的私有数据库，在一个系统中运行多个数

据库服务，以及建立连接池。

第5 章，表和数据。引导你贯穿数据库设计的整个过程，包括选择恰当的数据库对象名，

处理使用引号引用的名称的对象，确保列（指在不同表中相同意义的列）的名称和定义相同，

标记和删除重复数据，避免重复的行，为一组数据找到唯一键，生成测试数据，随机采样数据，

从电子表格中导入数据，以及从扁平文件（txt、csv 一类的文件）中导入数据。

第6 章，安全。提供的方法包括撤销用户对表的访问权限，赋予用户对表的访问，建立

一个新用户，临时避免用户连接，删除用户而不删除其数据，检查是否所有的用户都使用安全

的密码，给指定用户受限的超级用户权限，审计DDL 变化，审计数据变化，与LDAP 集成，

使用SSL 进行连接，以及加密敏感数据。

第7 章，数据库管理。涵盖很多有用的方法，包括写一个要么全部执行成功要么全部失

败的脚本，编写一个psql 脚本，一遇到错误就退出，在多张表上执行操作，添加/ 删除表上的

列，更改列的数据类型，添加/ 删除模式，在不同模式之间移动对象，添加/ 删除表空间，在

不同表空间之间移动对象，访问其他 PostgreSQL 数据库中的对象，并使视图可更新。

第8 章，监控和诊断。提供用于回答一些问题的方法，例如：“某个用户是否已经连接上

来？他们在运行什么？他们正在运行还是被阻塞了？是谁阻塞了他们？有人在使用某张表吗？

最后一次使用它是什么时候？临时数据使用了多少磁盘空间？为什么我的查询变慢了？”。它

还帮助你调查和报告bug，生成每日的日志文件错误信息摘要，杀掉一个指定的会话，以及处

理未决的准备事务。

第9 章，日常维护。包含一些有用的方法，包括控制自动数据库维护，避免自动冻结和

前言

XV

页损坏，避免事务重叠，移除过期的准备事务（prepared transactions），对于大量使用临时表时

需要的操作，识别和修复膨胀的表和索引，维护索引，寻找未使用的索引，谨慎删除不需要的

索引以及规划维护。

第10 章，性能和并发。包含的主题包括发现执行缓慢的SQL 语句，从pg_stat* 视图收集

常规统计信息，研究是什么让SQL 变得缓慢，减少返回的行数，简化复杂的 SQL，提高查询

速度而无须重写它，找出为什么某个查询不使用索引，强制让一个查询使用一个索引，使用乐

观锁并报告性能问题。

第11 章，备份及恢复。提供非常有用的用于备份和恢复你的PostgreSQL 数据库的信息，

包括理解和控制崩溃恢复，计划备份，对一个数据库进行热逻辑备份，对所有数据库进行热逻

辑备份，对一个表空间中的所有表进行热逻辑备份，备份数据库对象的定义，独立的数据库热

物理备份，热物理备份和持续归档。它还包括以下主题：恢复所有的数据库，恢复到一个时间

点，恢复一个被删除/ 损坏的表、数据库或表空间，为备份/ 恢复提速，增量/ 差异备份和恢复。

第12 章，复制和升级。涵盖复制最佳实践，建立基于文件的复制和流复制，流复制安全设置，

热备机及读可扩展性，管理流复制，使用repmgr，使用复制槽位，监控复制，性能和同步复制，

延迟、暂停和同步复制，逻辑复制，双向复制（Bi-Directional Replication），归档事务日志数据，

升级到一个新的小版本，主版本原地升级和在线升级。

你需要为本书准备什么

你需要为本书准备以下软件：

„ PostgreSQL 9.4 服务端软件

„ psql客户端工具（9.4 自带）

„ pgAdmin3 1.20

这本书的读者对象是谁

本书为系统管理员、数据库管理员、架构师、开发人员以及任何对其开始感兴趣并计划使

用、或已经在运行，甚至已经用于在线生产的人员而准备。本书特别适用于有一些技术经验

的人。

小节

在本书中，你会发现有几个标题会经常出现（准备，该如何做，如何实现的，更多内容以

及参考）。

为明确给出某个技术建议该怎么做，我们使用了以下小节。

PostgreSQL 9 Administration Cookbook（第2版）中文版

XVI

准备

这部分将告诉你你期待的技术建议会是什么样的，以及描述如何设置软件或任何其他本建

议需要的初步设置。

该如何做……

这部分包含本技术建议需要进行的步骤。

如何实现的……

本节通常包含前一节发生的内容的详细解释。

更多内容……

本节包含一些关于本技术建议的附加信息，让你能深入理解本建议。

参考

本节提供一些有用的链接或者其他有用的信息。

约定

在本书中，你会发现很多文本样式，用以区分不同类型的信息。以下是一些样式的示例以

及对它们的解释。

文本的代码单词、数据库表名、目录名、文件名、文件扩展名、路径名、假设的

URL、用户输入以及Twitter 的句柄通常以以下方式显示：“The service can also be set using an

environment variable named PGSERVICE.”

代码段的格式为：

[dbservice1]

host=postgres1

port=5432

dbname=postgres

当我们想提醒你注意一个代码段的特定部分时，相关的行或项目会被设置为粗体：

Database system identifier: 5805760367713220187

Database cluster state: in production

命令行的输入和输出通常是这样的：

$ psql -c “SELECT current_time”

新的术语和重要的词语会以粗体显示。例如，你在屏幕上的菜单或对话框中看到的某些文

前言

XVII

字，会以以下方式出现：“确保Guru Hints 选项已开启。”

警告或重要注释会出现在这样一个框中。

提示或技巧会是这样。

用户反馈

非常欢迎读者的反馈。这可以让我们了解你对本书的看法—哪些你喜欢，哪些你不喜欢。

读者的反馈可以帮助我们调整内容，从而使读者能够获得更多收益。

准备发送给我们的一般的反馈，只需发送电子邮件到feedback@packtpub.com，并在邮件

内容中指出书名即可。

如果你在某个方面有所专长，并且你有兴趣写书或者为一本书贡献内容，请阅读我们的作

者向导：www.packtpub.com/authors。

客户支持

现在，你可以自豪地说你是一本Packt 发行的书的主人了，并且，我们还在很多东西可以

帮助你充分利用它。

下载源码

你可以在https://www.packtpub.com 站点上使用你的账号下载所有你购买过的书籍的示例代

码。如果你是在其他地方购买的本书，那么你可以访问https://www.packtpub.com/support，注册

后，我们会把代码发送给你。

勘误

尽管我们已经尽力确保我们内容的准确性，但错误在所难免。如果你发现我们书中的错

误—可能是文字错误或者代码错误—如果你将它报告给我们，我们将不胜感激。这样做，

可以让其他读者免于受挫，并且帮助我们改进本书的后续版本。如果你发现任何错误，请访

问https://www.packtpub.com/submit-errata 进行报告，在以上链接的网站中，选择书名，然后单击

Errata Submission 表单链接，填写你所发现的错误的细节。一旦你发现的错误被确认，你提交的

内容将会被接受，勘误信息将被上传到我们的网站或者添加到对应标题相关的勘误小节列表中。

PostgreSQL 9 Administration Cookbook（第2版）中文版

XVIII

如果想查询以前提交的勘误信息，请访问https://www.packtpub.com/books/content/support，

并在搜索区域填写书名。需要的信息将会出现在勘误列表中。

侵权

互联网上的盗版问题是所有媒体一直存在的问题。对于Packt，我们非常重视保护我们的

版权。如果你从互联网上遇到任何我们产品的非法拷贝，请立即为我们提供网址或者网站名称，

以便我们寻求补救。

请通过电子邮件copyright@packtpub.com 联系我们并附带盗版资料的链接。

非常感谢你帮助保护我们的作者，而我们将为你带来有价值的内容。

问题

如果你对本书有任何方面的疑问，都可以通过电子邮件questions@packtpub.com 联系我们，

我们将尽最大的努力解决这个问题。