前言

在国内，PostgreSQL在数据库市场的影响力与日俱增，正逐步迈向行业前沿。PostgreSQL作为一款强大而灵活的开源数据库管理系统，受到越来越多的国内企业和开发者的青睐。然而，相较于其他商业数据库，PostgreSQL在中国缺乏一个完善的认证体系，这成为了一个值得关注和需要解决的问题。

数据库认证体系的建立对于数据库技术的推广和行业的规范化至关重要。在当前技术迅猛发展的时代，数据库不仅是信息存储的关键基础，更是支撑企业业务、数据分析等关键应用的重要组成部分。因此，建立和完善PostgreSQL认证体系不仅有助于推动PostgreSQL在我国的应用，也将为数据库行业注入新的活力。PostgreSQL培训中心（PostgreSQL Competence ?Cultivation Center，PGCCC）是PostgreSQL的倡导者，积极参与并推动着标准的制定和实践，为国内数据库事业的繁荣发展贡献了力量。

PostgreSQL作为开源数据库中的佼佼者，拥有强大的功能与出色的性能。要想真正地驾驭它，考取PCM认证是很好的选择。但这条路也充满荆棘—复杂的内部机制，繁杂的参数调优，实战场景中的各种异常情况，都需要应对自如。如何在短时间内抓住备考的重点，建立完整的知识框架和思维逻辑，是每位考生面临的难题。

为此，我们精心设计了本书的内容结构，由浅入深地介绍各知识模块，并辅以大量案例分析与考题进行解读。通过本书，读者可以系统地学习优化器的启发式算法、MVCC的实现机制、串行化异常的处理方法等高阶内容，还能了解复制、故障转移、逻辑解码等实战考点，并通过模拟试题检验学习效果。本书不仅传授了PostgreSQL的技术精髓，还汇集了多位行业顶尖专家多年积累的经验心得，帮助读者避开PostgreSQL数据库应用过程中的常见误区。

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

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

本书由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认证大师（PCM）资格。

本书是PostgreSQL认证大师（PCM）教材，旨在帮助读者深入理解和掌握PostgreSQL数据库管理及性能优化的相关知识，为PCM考试提供全面准备。全书共9章，主要内容包括PostgreSQL体系结构、高可用解决方案（Pgpool与Patroni）、数据库安全、PostgreSQL与容器、高级PL/PGSQL、数据库迁移、分布式数据库、数据库内核开发等。本书通过对PostgreSQL高级特性的深入探讨，包括SQL执行过程、解析器、重写器、规划器、执行器、可见性映射、空闲空间映射、时间线、锁、防暴力破解、数据库安全加固、Hash索引、GiST索引、SP_GiST索引等，使读者能够理解并应用PostgreSQL的先进特性。本书汇集了我国多位资深数据库专家多年来在PostgreSQL领域积累的深厚经验，适合广大读者自学，尤其适合转型的开发人员、运维工程师和数据库专业学生学习。

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

目录

第 1 章 PostgreSQL体系结构1

1.1 SQL语句执行过程1

1.1.1 概述1

1.1.2 执行过程1

1.1.3 Parser2

1.1.4 Analyzer2

1.1.5 Rewriter2

1.1.6 Planner3

1.1.7 Executor10

1.2 事务号10

1.2.1 可见性映射11

1.2.2 空闲空间映射12

1.2.3 时间线12

1.3 多版本并发控制深度解析13

1.3.1 概述13

1.3.2 事务的基本概念14

1.3.3 事务隔离级别15

1.3.4 PostgreSQL中的多版本并发控制实现19

1.3.5 小结34

1.4 锁原理深度解析35

1.4.1 锁35

1.4.2 表级锁35

1.4.3 行级锁36

1.4.4 页级锁38

1.4.5 咨询锁39

1.4.6 死锁40

1.4.7 观察锁40

1.4.8 小结42

第 2 章 高可用解决方案——Pgpool篇43

2.1 高可用方案简介43

2.1.1 相关指标43

2.1.2 常见高可用方案44

2.2 认识Pgpool47

2.2.1 Pgpool_II简史47

2.2.2 Pgpool_II的功能47

2.2.3 Pgpool_II的架构48

2.2.4 Pgpool_II的集群模式51

2.3 Pgpool_II的安装52

2.3.1 部署建议52

2.3.2 连接访问设置53

2.3.3 源代码安装54

2.3.4 RPM安装57

2.4 Pgpool_II 的配置57

2.4.1 连接和身份认证58

2.4.2 后端信息60

2.4.3 连接池62

2.4.4 负载均衡63

2.4.5 查询缓存70

2.4.6 健康检查72

2.4.7 数据库故障转移和故障恢复73

2.4.8 流复制检查77

2.4.9 Watchdog78

2.4.10 Pgpool_II错误报告和日志记录80

2.5 Pgpool_II 的高可用配置示例81

2.5.1 环境介绍81

2.5.2 准备工作82

2.5.3 Pgpool_II配置示例87

2.5.4 启动、停止Pgpool_II90

2.5.5 故障转移与故障恢复示范90

2.6 Pgpool_II管理93

2.6.1 二进制管理命令93

2.6.2 SQL管理命令95

练习题和答案96

第 3 章 高可用解决方案——Patroni篇97

3.1 Patroni介绍97

3.2 Patroni实现97

3.3 Patroni的配置98

3.3.1 Patroni的配置类型98

3.3.2 YAML配置100

3.4 Patroni部署实战112

3.4.1 部署规划112

3.4.2 部署架构图112

3.4.3 组件部署113

3.5 Patroni日常管理122

3.5.1 查看集群状态122

3.5.2 重启集群123

3.5.3 重新加载配置文件125

3.5.4 查看动态配置参数126

3.5.5 修改参数126

3.5.6 故障转移130

3.5.7 switchover131

3.5.8 pause/resume132

第 4 章 数据库安全134

4.1 数据库安全威胁134

4.2 防火墙134

4.3 监听地址134

4.4 认证安全135

4.4.1 身份认证135

4.4.2 密码复杂度137

4.4.3 防暴力破解138

4.4.4 验证失败自动锁定138

4.4.5 密码更换周期138

4.4.6 密码加密方式140

4.4.7 密码设置方式141

4.5 数据传输通道安全加固141

4.5.1 OpenSSL简介141

4.5.2 配置SSL安全连接142

4.5.3 服务端证书配置142

4.5.4 服务端配置143

4.5.5 对比SSL和非SSL的抓包信息145

4.5.6 小结146

4.6 数据库安全加固146

4.6.1 字段存储加密146

4.6.2 函数加密148

4.7 透明加密（TDE）152

4.7.1 基本概念152

4.7.2 加密等级152

4.7.3 加密算法154

4.7.4 密钥管理158

练习题和答案160

4.8 防恶意攻击160

4.8.1 视图攻击160

4.8.2 SQL注入164

4.8.3 函数陷阱164

4.8.4 防止恶意占用连接166

4.9 权限控制167

4.10 版本升级168

4.11 审计168

4.11.1 审计的概念168

4.11.2 审计的目的168

4.11.3 审计的分类168

第 5 章 PostgreSQL与容器176

5.1 Docker简介176

5.2 Docker与虚拟机的区别176

5.3 安装Docker177

5.4 安装PostgreSQL179

5.5 验证与测试183

5.6 自定义Dockfile183

5.7 小结184

第6章 高级PL/PGSQL185

6.1 基于PL/PGSQL的存储过程185

6.1.1 CREATE FUNCTION命令的语法说明185

6.1.2 稳定性说明186

6.2 块结构188

6.2.1 匿名块189

6.2.2 子块189

6.3 注释190

6.4 变量的声明190

6.5 参数的别名192

6.6 默认值192

6.7 输出参数194

6.7.1 out参数194

6.7.2 inout参数195

6.8 复制类型196

6.9 行类型197

6.10 记录类型199

6.11 赋值语句201

6.11.1 定义时赋值201

6.11.2 select into201

6.12 动态执行命令202

6.13 流程控制语句204

6.13.1 if和case条件语句204

6.13.2 if条件语句205

6.13.3 case条件语句206

6.14 循环207

6.14.1 loop简单循环207

6.14.2 while循环208

6.14.3 for循环208

6.14.4 continue语句209

6.14.5 exit语句210

6.15 获取返回结果210

6.15.1 return语句210

6.15.2 return命令210

6.15.3 return next命令211

6.15.4 return query命令212

6.15.5 return query execute命令212

6.16 游标213

6.16.1 游标介绍213

6.16.2 游标的声明214

6.16.3 游标的打开214

6.16.4 游标的使用214

6.16.5 使用示例215

6.17 strict221

6.17.1 修饰函数222

6.17.2 修饰变量223

6.18 found223

6.19 get diagnostics224

6.20 异常的处理224

6.20.1 处理异常224

6.20.2 自定义异常227

6.21 错误调试228

第7章 数据库迁移230

7.1 数据库迁移的难点与痛点230

7.2 从Oracle迁移到PostgreSQL230

7.2.1 orafce230

7.2.2 数据对象迁移230

7.2.3 数据迁移233

7.2.4 应用代码迁移235

7.3 从MySQL迁移到PostgreSQL236

7.3.1 基础数据迁移236

7.3.2 存储过程迁移238

7.4 小结239

第8章 分布式数据库240

8.1 概述240

8.2 Greenplum240

8.3 Citus242

8.4 LNXDB_HTAP242

8.5 Yugabyte243

8.6 Postgres_XL244

8.6.1 Postgres_XL简介244

8.6.2 整体架构245

8.6.3 衍生数据库245

第9章 数据库内核开发246

9.1 基本操作符实现原理246

9.1.1 join操作符246

9.1.2 aggregate操作符255

9.1.3 sort操作符260

练习题和答案265

9.2 索引原理与实现266

9.2.1 概述266

9.2.2 B_Tree索引267

9.2.3 Hash索引274

9.2.4 GiST索引276

9.2.5 SP_GiST索引279

9.2.6 GIN索引282

9.2.7 BRIN索引286

参考文献290