前言

随着信息技术创新的推进，PostgreSQL培训中心（PostgreSQL Competence Cultivation Center，PGCCC）参与编写了认证标准，并积极推动中国PostgreSQL认证体系的制定和实践，旨在促进PostgreSQL开源数据库在我国的蓬勃发展。该认证体系的制定不仅规范了PostgreSQL技能培训、资格认证和考试测评等工作，更有利于PostgreSQL数据库相关人才的培养。这一认证体系的制定是PGCCC积极参与信息技术创新的体现，也是对中国PostgreSQL未来发展的积极响应。

本书由中国PostgreSQL资深专家编写，旨在为中级数据库管理人员（DBA）提供系统、高效的学习路径，这些专家来自不同领域，在数据库管理、性能优化、高可用解决方案等方面有很高的造诣。他们的经验不仅包括参与大规模数据库架构设计，还涉及关键业务系统的优化以及在各类行业应用中的成功实践。他们也积极地参与了中国PostgreSQL认证标准的编写工作。

本书聚焦于中级数据库管理人员在PostgreSQL学习过程中可能遇到的问题，通过精心设计的内容帮助读者快速适应PostgreSQL环境，提高工作效率。本书不仅是为中级DBA设计的学习路径，同时也是中级DBA了解PostgreSQL并解决过渡过程中困扰的有力工具。通过掌握本书的内容，中级DBA将更好地理解PostgreSQL的核心理念，成功地将其Oracle经验转化为PostgreSQL的价值。我们相信，通过学习本书，中级数据库管理人员将更加自信地应对实际工作中的挑战，为PostgreSQL数据库事业贡献一份力量。

在本书的编写过程中，人工智能领域迎来了以DeepSeek、OpenAI等为代表的大模型技术突破。这股技术浪潮迅速渗透至数据库运维领域，推动行业从依赖人工经验向AI自治演进，并已催生出像“LNXDB_OPS AI数据库故障诊断平台”这样的实践产物，标志着智能运维（AIOps）正式从概念走向落地。

为了便于读者更好地使用本书，读者可扫描右侧的二维码获取资源，其中包括书中涉及的各类软件和工具的下载地址以及作者的联系方式。

本书由PostgreSQL培训中心主编，由何秉卫校订，参与编写者包括赵振平、徐戟、林春、李海龙、熊灿灿、权宗亮、袁远松、卢洪棚、韩丹、王雪、杨栋、邓彪、王向华、刘金龙、张干、方白玉。

我们希望随着本书的推出，能为社会培养更多优秀的数据库管理人才，推动PostgreSQL在国内的繁荣发展。让我们共同努力，打造更加强大的数据库管理人才队伍，为国家大数据事业的发展贡献智慧和力量。

最后，谨向PostgreSQL中国认证体系的倡议者、支持者，以及众多为PostgreSQL社区无私贡献的成员致以诚挚感谢。希望本书为您的职业生涯带来新的启发与成就，祝您学有所得，业有所成！

编者

2025年5月

中国PostgreSQL认证体系

中国PostgreSQL认证体系是在甲骨文（Oracle）认证体系发生“崩塌”后，国内又缺少权威的数据库认证体系的情况下，为了推动中国数据库技术的发展，特别是国产数据库技术的普及和应用，由PGCCC参与了认证标准的编写，并协助相关部门设立了一套认证体系。该认证体系旨在通过权威、公正、全面的评估，确保数据库从业者在数据库领域具备相应的专业知识和技能。

一、认证分级

中国PostgreSQL认证体系分为三个等级：PCA认证专员、PCP认证专家、PCM认证大师。旨在帮助学员逐步提升其PostgreSQL技能，并获得相应的认可和资格。

1. PostgreSQL Certified Associate（PCA认证专员）

概述：PCA认证专员是PostgreSQL认证体系中的入门级别，旨在确认学员具备基本的PostgreSQL知识和技能。

要求：

熟悉PostgreSQL数据库的基本概念、架构和组件。

掌握数据库的安装方法。

能够执行基本的数据库管理任务，如创建、删除、修改数据库和表等。

熟练运用 SQL 语言进行数据的增、删、改、查等操作，以及了解其他常用的数据管理技术。

理解数据库安全性和权限管理的基本原则。

考试内容：涵盖PostgreSQL的基本概念、SQL查询、数据库管理和安全性等方面的知识。

2. PostgreSQL Certified Professional（PCP认证专家）

概述：PCP认证专家是PostgreSQL认证体系中的中级级别，旨在确认学员具备更丰富的PostgreSQL知识和技能，能够在复杂环境中管理和优化数据库。PCP认证专家在市场上极为紧缺，要求具备扎实的理论基础和卓越的动手能力。获得PCP认证专家资格的学员能够进入企业的生产系统完成运维工作。

要求：

具备PCA级别的知识和技能。

能够设计和优化复杂的数据库结构和查询。

熟悉高可用性、容灾和性能优化方面的最佳实践。

具备在生产环境中进行故障排除和性能调优的能力。

考试内容：涵盖数据库设计、性能优化、高可用性和故障排除等方面的深入知识。

3. PostgreSQL Certified Master（PCM认证大师）

概述：PCM认证大师是PostgreSQL认证体系中的最高级别，旨在确认学员在PostgreSQL领域具有卓越的技能和专业知识，是对数据库从业人员的技术、知识和操作技能最高级别的评定。PCM认证大师需要解决最复杂的技术难题和系统故障。在全球范围内，通过PCM认证大师考试的人员稀缺。拥有3年PostgreSQL实战经验的工程师，其薪酬已经达到了在Oracle工作8年的工程师水平。

要求：

具备PCP级别的知识和技能。

能够设计和实施复杂的数据库架构和解决方案。

精通PostgreSQL的内部工作原理，能够进行深入的性能优化和调整。

具备领导团队和指导其他数据库专业人员的能力。

考试内容：涵盖高级数据库架构设计、内部工作原理、领导能力和专业实践等方面的深入知识。

这3个级别的认证提供了一条完整的路径，让学员在PostgreSQL领域不断精进和成长，获得从基础的技能到高级的专业知识和领导能力的提升。

二、认证途径

要获得PostgreSQL Certified Associate（PCA认证专员）资格需要参加培训机构的培训并通过考试。

要获得PostgreSQL Certified Professional（PCP认证专家）资格必须参加培训机构的培训，然后方可申请考试，考试包括笔试和上机实验两部分。

要获得PostgreSQL Certified Master（PCM认证大师）资格需要通过以下步骤：

（1）必须获得PCP认证；

（2）必须参加培训机构的培训；

（3）笔试；

（4）上机实验；

（5）认证委员会面试。

汇集业内多位一线数据库专家的实战经验，讲清原理、教会方法。PostgreSQL中国社区专家委员会推荐读物。·深度剖析PostgreSQL的核心架构、高阶特性与关键技术，直面复杂应用场景。·涵盖丰富的案例、大量实用代码、有效的实践方法和性能优化技巧。·赋能数据库管理者与开发者，深入掌握PostgreSQL精髓，轻松应对技术挑战。·助力读者获取中国PostgreSQL认证专家（PCP）资格，赢得专业认可。

本书是PostgreSQL认证专家（PCP）教材。本书深度剖析了PostgreSQL数据库的核心概念和关键技术，涉及从体系结构到性能优化，再到高可用和灾难恢复的相关知识。本书共9章，主要内容包括PostgreSQL体系结构进阶、PostgreSQL性能优化、连接池与性能优化、数据库对象管理、PostgreSQL高可用解决方案、PostgreSQL灾难恢复、数据迁移与数据共享专题、PostgreSQL监控体系、PostgreSQL日常维护。书中内容突出实用性，为PostgreSQL学习者提供了全方位的学习路径。本书汇集了多位资深数据库专家在PostgreSQL领域多年积累的深厚经验，适合广大读者自学，尤其适合作为数据库管理者和开发者的学习指南，助力其实现更高效的数据库应用与管理。

PostgreSQL培训中心（PGCCC），是在国内权威数据库认证体系缺位的背景下成立的机构，核心工作是规范和组织PostgreSQL技能培训、考试测评等相关事务，助力推动中国数据库技术发展及国产数据库技术的普及应用。PGCCC不仅参与编写PostgreSQL标准，还组织领域内顶尖数据库专家团队开展能力水平认定；同时承担PostgreSQL教育内容标准制定、培训机构组织与管理职责，并依托PostgreSQL中国社区推进考试测评与能力认定工作，致力于打造具备国际水准的PCA、PCP、PCM数据库管理人才，通过权威、公正、全面的评估，确保数据库从业者具备相应的专业知识与技能。

目录

第1章 PostgreSQL体系结构进阶1

1.1 PostgreSQL体系结构概述1

1.2 实例、数据库集簇、数据库和模式2

1.3 内存结构3

1.3.1 本地内存4

1.3.2 共享内存6

练习题和答案12

1.4 进程结构12

1.4.1 主进程（Postmaster）14

1.4.2 后端进程15

1.4.3 后台写进程（Bg Writer）17

1.4.4 自动清理进程（Autovacuum）18

1.4.5 预写日志写进程（WAL Writer）21

1.4.6 统计数据收集进程（Pg Stat）22

1.4.7 系统日志进程（Logger）23

1.4.8 日志归档进程（Pg Arch）23

练习题和答案24

1.5 物理结构和逻辑结构25

1.5.1 数据库逻辑结构25

1.5.2 数据库物理结构26

练习题和答案36

1.6 建立会话（连接）的过程36

1.7 提交和检查点40

1.7.1 提交40

1.7.2 检查点42

练习题和答案43

1.8 数据字典43

1.8.1 pg_class44

1.8.2 pg_index45

1.8.3 pg_attribute47

1.9 多版本并发控制探索49

1.9.1 多版本并发控制的基本原理49

1.9.2 事务相关标识50

1.9.3 多版本并发控制的实现52

1.9.4 多版本并发控制实践案例54

1.10 客户端认证55

1.10.1 客户端配置文件55

1.10.2 认证方式58

第2章 PostgreSQL性能优化61

2.1 性能优化61

2.1.1 数据库性能优化62

2.1.2 操作系统优化71

2.1.3 数据库实例优化92

2.1.4 数据库I/O优化121

2.1.5 索引优化122

2.1.6 SQL优化136

2.2 性能基准测试155

2.2.1 pgbench155

2.2.2 BenchmarkSQL162

练习题和答案165

第3章 连接池与性能优化166

3.1 连接池应用场景166

3.2 连接池的优势167

3.3 连接池的工作原理168

3.4 连接池使用注意事项168

3.5 各种连接池的简单对比169

3.6 PgBouncer172

3.6.1 PgBouncer安装172

3.6.2 PgBouncer配置174

3.6.3 PgBouncer测试177

3.6.4 PgBouncer的优点与缺点177

第4章 数据库对象管理179

4.1 数据库管理179

4.1.1 新建数据库179

4.1.2 模板数据库181

4.1.3 数据库配置181

4.1.4 删除数据库184

4.1.5 复制数据库185

练习题和答案186

4.2 角色管理186

4.2.1 新建角色187

4.2.2 默认角色188

4.2.3 角色属性189

4.2.4 角色成员关系190

4.2.5 删除角色191

练习题和答案192

4.3 模式管理193

4.3.1 创建模式193

4.3.2 模式与权限194

4.3.3 修改模式195

4.3.4 删除模式195

4.3.5 模式搜索路径196

练习题和答案198

4.4 表空间管理198

4.4.1 创建表空间198

4.4.2 修改表空间199

4.4.3 删除表空间200

练习题和答案200

4.5 扩展管理201

4.5.1 什么是扩展201

4.5.2 安装扩展201

4.5.3 经典扩展201

4.6 环境变量管理213

4.7 表分区219

4.7.1 声明式分区219

4.7.2 声明式分区的维护222

4.7.3 继承式分区225

4.7.4 继承式分区的维护227

4.7.5 声明式分区与继承式分区228

练习题和答案229

4.8 物化视图230

4.8.1 物化视图的基本概念230

4.8.2 物化视图的使用230

第5章 PostgreSQL高可用解决方案233

5.1 高可用解决方案233

5.2 WAL基本原理和归档233

5.2.1 什么是WAL233

5.2.2 WAL归档234

5.2.3 归档清理235

练习题和答案236

5.3 流复制236

5.3.1 什么是流复制236

5.3.2 发展历史237

5.3.3 WAL日志简介237

5.3.4 流复制搭建238

5.3.5 热备库252

5.3.6 同步复制级别255

5.3.7 复制槽255

5.3.8 复制新特性256

5.3.9 常见报错256

5.4 逻辑复制257

5.4.1 介绍257

5.4.2 适用场景257

5.4.3 Output Plugins257

5.4.4 相关进程259

5.4.5 逻辑复制259

第6章 PostgreSQL灾难恢复275

6.1 备份的分类275

6.2 逻辑备份275

6.2.1 SQL dump方法和相关工具275

6.2.2 退出状态307

6.3 备份和恢复案例学习307

6.3.1 使用pg_dump备份307

6.3.2 使用.pgpass完成自动验证308

6.3.3 从远程机器初始化一个基础备份309

6.3.4 备份一个数据库并在使用不同数据库版本的另一台机器上恢复309

6.3.5 使用自定义格式完成备份与恢复310

6.3.6 理解自定义文件格式310

6.3.7 备份并恢复部分数据310

6.3.8 只备份指定表数据311

6.3.9 备份指定模式下的表结构，不备份数据311

6.3.10 备份整个数据库对象的转储模式结构312

6.3.11 备份数据库中的指定模式312

6.3.12 查看非纯文本格式备份文件的内容312

6.3.13 获取各种实用程序的版本信息312

6.3.14 执行备份实用程序时获取更多详细信息313

6.3.15 处理 tar 格式的备份和恢复314

6.3.16 遇到错误时停止恢复314

6.3.17 从自定义转储文件中恢复指定的对象314

6.3.18 并行备份和恢复314

6.3.19 转储数据库并恢复到另一个数据库315

6.3.20 pg_restore中exit on error 与 single transaction317

6.3.21 使用 pg_dumpall 仅转储全局对象318

6.3.22 仅转储跨数据库的表空间318

6.3.23 仅转储跨数据库的角色319

6.3.24 备份和恢复集簇中的所有数据库319

6.4 物理备份320

6.4.1 冷备份320

6.4.2 热备份（在线备份）321

练习题和答案334

第7章 数据迁移与数据共享专题335

7.1 数据装载335

7.1.1 COPY335

7.1.2 pgloader338

练习题和答案361

7.2 数据库升级362

7.2.1 版本变更规则362

7.2.2 是否需要升级363

7.2.3 升级计划363

7.2.4 使用pg_upgrade升级366

7.3 外部表372

7.3.1 外部表介绍372

7.3.2 外部表原理374

7.3.3 postgres_fdw实例377

第8章 PostgreSQL监控体系380

8.1 概述380

8.2 使用Prometheus十Grafana监控PostgreSQL380

8.2.1 安装Prometheus381

8.2.2 安装Grafana383

8.2.3 Prometheus十Grafana监控PostgreSQL386

8.3 使用Zabbix监控PostgreSQL389

8.3.1 安装Zabbix server390

8.3.2 Zabbix 监控PostgreSQL的配置393

8.4 总结396

第9章 PostgreSQL日常运维397

9.1 故障排查397

9.1.1 安装目录权限问题397

9.1.2 日志相关问题397

9.1.3 PostgreSQL连接问题400

9.1.4 WAL目录过大402

9.1.5 内存溢出，数据库崩溃403

9.1.6 备库查询被终止404

9.1.7 主库WAL被删除导致备库不可用404

9.2 日常维护405

9.2.1 统计信息优化405

9.2.2 数据碎片化408

9.2.3 VACUUM命令409

9.2.4 防止事务ID回卷失败410

9.2.5 VACUUM FREEZE411

9.2.6 Autovacuum412

9.2.7 数据库监控412

练习题和答案421