PostgreSQL 9 Administration Cookbook （第2版）中文版-图书

推荐0
收藏1
浏览723

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

（美）Simon Riggs （西蒙.里格斯）, Gianni Ciolli（贾尼.乔里）, Hannu Krosing（汉努.克劳辛）, Gabriele Bartolini（加布里埃.巴托里尼） (作者)　黄坚 (译者)

书　　号：978-7-121-28596-7
出版日期：2016-05-27
页　　数：396
开　　本：16(185*235)
出版状态：上市销售
原书名： PostgreSQL 9 Administration Cookbook Second Edition
原书号：9781849519069
维护人：安娜

纸质版￥79.00

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

PostgreSQL是一款功能丰富的、支持大量平台的高级数据库服务软件，而本书是一本能帮你快速成长为一名合格的PostgreSQL DBA的经典管理书籍。

前言
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 发行的书的主人了，并且，我们还在很多东西可以
帮助你充分利用它。
下载源码
你可以在http://www.packtpub.com 站点上使用你的账号下载所有你购买过的书籍的示例代
码。如果你是在其他地方购买的本书，那么你可以访问http://www.packtpub.com/support，注册
后，我们会把代码发送给你。
勘误
尽管我们已经尽力确保我们内容的准确性，但错误在所难免。如果你发现我们书中的错
误—可能是文字错误或者代码错误—如果你将它报告给我们，我们将不胜感激。这样做，
可以让其他读者免于受挫，并且帮助我们改进本书的后续版本。如果你发现任何错误，请访
问http://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 联系我们，
我们将尽最大的努力解决这个问题。

目录
第1章　迈出第一步...................................................................................................................1
1.1　介绍 ..................................................................................................................................1
1.2　获取PostgreSQL ..............................................................................................................7
1.3　连接到PostgreSQL服务 ..................................................................................................8
1.4　启用网络/远程用户访问 ............................................................................................... 11
1.5　使用图形化管理工具 ....................................................................................................12
1.6　使用 psql 查询和脚本工具 ...........................................................................................16
1.7　安全地修改你的密码 ....................................................................................................19
1.8　避免硬编码你的密码 ....................................................................................................20
1.9　使用连接服务文件 ........................................................................................................22
1.10　连接失败故障排除 ......................................................................................................23
第2章　浏览数据库.................................................................................................................25
2.1　介绍 ................................................................................................................................25
2.2　当前服务程序的版本号是多少 ....................................................................................26
2.3　服务程序运行时间是多少？ ........................................................................................27
2.4　定位数据库服务的数据文件 ........................................................................................28
2.5　定位数据库服务的日志文件 ........................................................................................31
2.6　定位数据库的系统标识 ................................................................................................33
2.7　列出数据库服务中的数据库 ........................................................................................35
2.8　数据库中有多少张表 ....................................................................................................38
2.9　一个数据库占用了多少磁盘空间 ................................................................................40
PostgreSQL 9 Administration Cookbook（第2版）中文版
XX
2.10　一张表占用了多少磁盘空间 ......................................................................................41
2.11　哪张表是最大的表 ......................................................................................................42
2.12　表里有多少行记录 ......................................................................................................43
2.13　快速估算表里的记录总数 ..........................................................................................44
2.14　列出数据库中的扩展模块 ..........................................................................................48
2.15　理解对象的依赖关系 ..................................................................................................49
第3章　配置............................................................................................................................ 52
3.1　介绍 ................................................................................................................................52
3.2　阅读手册（RTFM） .....................................................................................................53
3.3　规划一个新数据库 ........................................................................................................54
3.4　在程序中更改参数 ........................................................................................................55
3.5　查看当前的配置参数 ....................................................................................................57
3.6　哪些参数为非默认设置 ................................................................................................58
3.7　更新参数文件 ................................................................................................................60
3.8　为特定用户组设置参数 ................................................................................................62
3.9　基本服务配置清单 ........................................................................................................63
3.10　添加一个外部模块到PostgreSQL服务中 ..................................................................64
3.11　使用已安装的模块 ......................................................................................................68
3.12　管理已安装的扩展 ......................................................................................................70
第4章　服务控制.....................................................................................................................74
4.1　介绍 ................................................................................................................................74
4.2　手动启动数据库服务 ....................................................................................................75
4.3　安全快速地停止服务 ....................................................................................................77
4.4　在紧急情况下关闭服务 ................................................................................................77
4.5　重新加载服务配置文件 ................................................................................................78
4.6　快速重启服务 ................................................................................................................80
4.7　阻止新的连接 ................................................................................................................81
4.8　限制每个用户只允许一个会话 ....................................................................................83
4.9　断开用户连接 ................................................................................................................84
4.10　为多租户进行数据库设计 ..........................................................................................86
4.11　使用多个模式（schema） ..........................................................................................87
4.12　单独给用户分配数据库 ..............................................................................................89
目录
XXI
4.13　在一个系统上运行多个服务 ......................................................................................90
4.14　配置连接池 ..................................................................................................................92
4.15　在相同的主机和端口上访问多个服务 ......................................................................95
第5章　表和数据.....................................................................................................................98
5.1　介绍 ................................................................................................................................98
5.2　为数据库对象选择恰当的名称 ....................................................................................99
5.3　处理使用双引号括起来的对象名 ..............................................................................100
5.4　确保相同的名称和相同的列定义 ..............................................................................102
5.5　标记和删除重复数据 ..................................................................................................106
5.6　避免出现重复行 .......................................................................................................... 110
5.7　为一组数据找到一个唯一主键 .................................................................................. 115
5.8　生成测试数据 .............................................................................................................. 117
5.9　随机抽样数据 ..............................................................................................................120
5.10　从电子表格导入数据 ................................................................................................122
5.11　从扁平文件导入数据 ................................................................................................124
第6章　安全..........................................................................................................................128
6.1　介绍 ..............................................................................................................................128
6.2　PostgreSQL的超级用户 ..............................................................................................129
6.3　收回用户对表的访问权 ..............................................................................................130
6.4　赋予用户对表的访问权 ..............................................................................................134
6.5　建立一个新用户 ..........................................................................................................135
6.6　临时阻止一个用户的连接 ..........................................................................................137
6.7　删除用户而不删除其数据 ..........................................................................................138
6.8　检查是否所有的用户都是使用安全的密码 ..............................................................139
6.9　将受限的超级用户权限赋予指定用户 ......................................................................140
6.10　审计DDL的改变 ........................................................................................................143
6.11　审计数据的改变 ........................................................................................................145
6.12　总是认清登录的用户 ................................................................................................150
6.13　与LDAP集成 .............................................................................................................152
6.14　使用SSL进行连接 .....................................................................................................153
6.15　使用SSL证书来验证客户端 .....................................................................................155
6.16　映射外部用户为数据库角色 ....................................................................................158
PostgreSQL 9 Administration Cookbook（第2版）中文版
XXII
6.17　加密敏感数据 ............................................................................................................159
第7章数据库管理...............................................................................................................164
7.1　介绍 ..............................................................................................................................164
7.2　编写一个脚本，要么全部执行成功，要么全部执行失败 ......................................165
7.3　编写一个psql脚本，一遇到错误就退出 ...................................................................168
7.4　在多张表上执行多个操作 ..........................................................................................169
7.5　添加/删除表上的列 .....................................................................................................174
7.6　更改列的数据类型 ......................................................................................................175
7.7　更改数据类型的定义 ..................................................................................................179
7.8　添加/删除模式（schema） .........................................................................................180
7.9　在不同schema之间移动对象 ......................................................................................182
7.10　添加/删除表空间 .......................................................................................................183
7.11　在不同表空间之间移动对象 ....................................................................................186
7.12　访问其他PostgreSQL数据库中的对象 ....................................................................189
7.13　访问其他外部数据库中的对象 ................................................................................197
7.14　可更新视图 ................................................................................................................199
7.15　使用物化视图 ............................................................................................................204
第8章监控和诊断...............................................................................................................207
8.1　介绍 ..............................................................................................................................207
8.2　检查用户是否已经连接上来 ...................................................................................... 211
8.3　检查哪个查询在运行 ..................................................................................................213
8.4　检查哪个查询正在运行或被阻塞 ..............................................................................216
8.5　确定谁阻塞了一个查询 ..............................................................................................217
8.6　杀掉指定会话 ..............................................................................................................219
8.7　探测未决的准备事务 ..................................................................................................221
8.8　确定是否某人在使用某张表 ......................................................................................222
8.9　确定一张表最后被使用的时间 ..................................................................................223
8.10　临时数据使用的磁盘空间 ........................................................................................225
8.11　理解查询变慢的原因 ................................................................................................227
8.12　调查和报告一个缺陷 ................................................................................................230
8.13　生成每日的日志文件错误信息摘要 ........................................................................231
8.14　分析你的查询的实时性能 ........................................................................................234
目录
XXIII
第9章常规维护...................................................................................................................236
9.1　介绍 ..............................................................................................................................236
9.2　控制自动数据库维护 ..................................................................................................237
9.3　避免自动冻结和页损坏 ..............................................................................................241
9.4　避免事务重叠 ..............................................................................................................243
9.5　移除过期的准备事务 ..................................................................................................245
9.6　对于大量使用临时表时需要的操作 ..........................................................................247
9.7　识别和修复膨胀的表和索引 ......................................................................................249
9.8　维护索引 ......................................................................................................................253
9.9　添加约束而不检查已有行 ..........................................................................................256
9.10　寻找未使用的索引 ....................................................................................................258
9.11　谨慎删除不必要的索引 ............................................................................................260
9.12　维护规划 ....................................................................................................................261
第10章性能和并发.............................................................................................................263
10.1　介绍 ............................................................................................................................263
10.2　寻找执行缓慢的SQL语句 ........................................................................................264
10.3　从pg_stat* 视图收集常规统计信息 .........................................................................267
10.4　研究是什么让SQL缓慢 ............................................................................................269
10.5 　减少返回的行 ...........................................................................................................273
10.6　简化复杂的SQL ........................................................................................................274
10.7　加速查询的执行，而无须重写 ................................................................................281
10.8　为什么一个查询不使用索引 ....................................................................................284
10.9　强制让一个查询使用一个索引 ................................................................................285
10.10　使用乐观锁 ..............................................................................................................287
10.11　报告性能问题 ..........................................................................................................288
第11章备份和恢复.............................................................................................................290
11.1　介绍 ............................................................................................................................290
11.2　理解和控制崩溃恢复 ................................................................................................291
11.3　计划备份 ....................................................................................................................293
11.4　对一个数据库进行热逻辑备份 ................................................................................294
11.5　对所有数据库进行热逻辑备份 ................................................................................296
PostgreSQL 9 Administration Cookbook（第2版）中文版
XXIV
11.6　对一个表空间中的所有表进行热逻辑备份 ............................................................297
11.7　备份数据库对象的定义 ............................................................................................298
11.8　独立的数据库热物理备份 ........................................................................................299
11.9　在线物理备份和持续归档 ........................................................................................302
11.10　恢复所有的数据库 ..................................................................................................305
11.11　恢复到一个时间点 ..................................................................................................308
11.12　恢复一个被删除/损坏的表 .....................................................................................310
11.13　恢复一个被删除/损坏的表空间 .............................................................................312
11.14　恢复一个被删除/损坏的数据库 .............................................................................314
11.15　为备份/恢复提速 .....................................................................................................315
11.16　增量/差异备份和恢复 .............................................................................................317
11.17　使用Barman进行热物理备份 .................................................................................319
11.18　使用Barman进行恢复 .............................................................................................326
第12章复制和升级.............................................................................................................330
12.1　介绍 ............................................................................................................................330
12.2　复制最佳实践 ............................................................................................................337
12.3　基于日志文件传送的复制——不推荐使用 ............................................................338
12.4　建立流复制 ................................................................................................................341
12.5　建立流复制的安全机制 ............................................................................................346
12.6　热备机及读可扩展性 ................................................................................................347
12.7　管理流复制 ................................................................................................................351
12.8　使用repmgr ................................................................................................................353
12.9　使用复制槽位 ............................................................................................................355
12.10　监控复制 ..................................................................................................................357
12.11　性能和同步复制 ......................................................................................................360
12.12　延迟、暂停和同步复制 ..........................................................................................362
12.13　逻辑复制 ..................................................................................................................364
12.14　双向复制 ..................................................................................................................367
12.15　归档事务日志数据 ..................................................................................................370
12.16　升级——小版本 ......................................................................................................371
12.17　主版本原地升级 ......................................................................................................372
12.18　主版本在线升级 ......................................................................................................374