第1章 PostgreSQL基础 1
1.1 PostgreSQL简介 1
1.1.1 什么是PostgreSQL 1
1.1.2 PostgreSQL的功能特性 2
1.2 PostgreSQL的体系架构 3
1.2.1 逻辑存储结构 4
1.2.2 物理存储结构 9
1.2.3 进程结构 17
1.2.4 内存结构 23
第2章 安装与配置PostgreSQL 25
2.1 安装CentOS操作系统 25
2.2 安装PostgreSQL服务器 35
2.3 【实战】使用PostgreSQL客户端工具 37
2.3.1 命令行客户端psql 38
2.3.2 图形化客户端pgAdmin 4 41
2.4 PostgreSQL服务器端的主配置文件详解 45
2.4.1 基础文件 46
2.4.2 连接和认证 46
2.4.3 资源使用 47
2.4.4 预写日志 49
2.4.5 复制 50
2.4.6 查询调优 51
2.4.7 错误报告和日志 53
2.4.8 进程标题 54
2.4.9 运行时统计数据 55
2.4.10 自动清理 55
2.4.11 客户端连接默认值 56
2.4.12 锁管理 57
2.4.13 版本和平台兼容性 58
2.4.14 错误处理 58
2.4.15 预置选项 59
第3章 管理数据库与数据库实例 60
3.1 【实战】管理数据库和数据库模板 60
3.1.1 管理数据库的基本操作 60
3.1.2 管理数据库模板 64
3.2 【实战】管理数据库的扩展 65
3.2.1 使用扩展访问外部数据源 65
3.2.2 数据预热扩展pg_prewarm 75
3.2.3 共享缓冲区监控扩展pg_buffercache 79
3.2.4 预写日志解析扩展pg_walinspect 81
3.3 管理数据库实例 83
3.4 基于PostgreSQL的分布式数据库——Citus 85
3.4.1 Citus基础 85
3.4.2 安装与配置Citus 86
3.4.3 Citus中表的类型 91
3.4.4 Citus的配置参数 96
3.5 基于PostgreSQL的分布式数据库——Greenplum 96
3.5.1 Greenplum简介 96
3.5.2 安装与配置Greenplum 98
3.5.3 Greenplum中表的类型 101
第4章 管理数据库对象 105
4.1 数据库与模式 105
4.2 创建与管理表 107
4.2.1 PostgreSQL的数据类型 107
4.2.2 【实战】PostgreSQL表的基本操作 110
4.2.3 数据的约束条件 111
4.2.4 表中的碎片 115
4.2.5 统计信息 118
4.2.5.1 查看数据库的统计信息 118
4.2.5.2 使用analyze命令手动收集数据库的统计信息 120
4.2.5.3 了解统计信息收集器的配置项 121
4.2.6 【实战】使用PostgreSQL的临时表 122
4.3 在查询时使用索引 123
4.3.1 索引的基础知识 123
4.3.2 索引的类型 127
4.3.3 【实战】索引的维护 130
4.3.3.1 使用仅索引扫描 130
4.3.3.2 避免重复索引 131
4.3.3.3 清除未使用的索引 132
4.4 使用视图简化查询语句 133
4.4.1 什么是视图 133
4.4.2 视图的基本操作 133
4.4.3 【实战】在PostgreSQL中使用临时视图 135
4.4.4 物化视图 136
4.5 序列 138
第5章 并行查询 141
5.1 并行查询是如何工作的 141
5.2 何时会用到并行查询 142
5.3 【实战】查看并行查询的执行计划 143
5.3.1 并行扫描 144
5.3.2 并行连接 146
5.4 并行查询的限制 151
第6章 事务与并发控制 152
6.1 PostgreSQL的事务 152
6.1.1 事务简介 152
6.1.2 控制事务 154
6.1.2.1 事务的控制语句 154
6.1.2.2 【实战】使用事务的控制语句 154
6.1.3 事务的并发 157
6.1.3.1 事务隔离级别 157
6.1.3.2 【实战】事务的脏读 158
6.1.3.3 【实战】事务的不可重复读 159
6.2 PostgreSQL的锁 161
6.2.1 锁的类型 162
6.2.2 死锁 168
6.2.2.1 【实战】模拟死锁的产生 169
6.2.2.2 如何避免死锁 171
第7章 应用程序开发 173
7.1 PL/pgSQL基础 173
7.1.1 【实战】开发第一个PL/pgSQL程序 174
7.1.2 使用PL/pgSQL的基本数据类型 175
7.1.3 使用PL/pgSQL的高级数据类型 176
7.2 PL/pgSQL面向过程编程 177
7.2.1 在PL/pgSQL程序中使用条件判断 177
7.2.2 在PL/pgSQL程序中使用循环 178
7.2.3 在PL/pgSQL程序中使用游标 180
7.2.4 在PL/pgSQL程序中处理例外 182
7.2.4.1 处理系统预定义例外 182
7.2.4.2 处理用户自定义例外 183
7.3 【实战】综合案例——基于员工表统计各工资段的
员工人数及各部门的工资总额 184
7.4 使用PL/pgSQL开发存储过程与存储函数 187
7.4.1 存储过程与存储函数 187
7.4.2 【实战】创建和使用存储过程 187
7.4.3 【实战】创建和使用存储函数 189
7.4.4 【实战】设置存储过程中的inout参数 189
7.4.5 【实战】在inout参数中使用游标 191
7.5 使用PL/pgSQL开发触发器 192
7.5.1 常规触发器 192
7.5.1.1 常规触发器的定义 192
7.5.1.2 【实战】利用常规触发器实现安全性检查 193
7.5.1.3 【实战】利用常规触发器进行数据确认 194
7.5.1.4 【实战】利用常规触发器实现审计 195
7.5.2 事件触发器 197
第8章 管理数据库安全 199
8.1 用户管理 199
8.1.1 用户与角色 199
8.1.2 管理用户的密码 204
8.1.2.1 使用SQL语句管理用户的密码 205
8.1.2.2 使用passwordcheck插件检查密码的复杂度 206
8.1.3 预定义角色 207
8.2 权限管理 207
8.2.1 【实战】在创建用户时指定的权限 207
8.2.2 使用grant命令和revoke命令管理的权限 210
8.2.3 在授权时使用admin option选项和grant option选项 215
8.2.4 使用组角色管理权限 216
8.2.5 使用set role命令显示启用角色的权限 219
8.3 审计管理 222
8.3.1 PostgreSQL的审计日志功能 222
8.3.2 PostgreSQL的审计扩展插件pgaudit 224
第9章 备份与恢复 228
9.1 备份与恢复的基本概念 228
9.1.1 数据库的故障类型 228
9.1.2 备份的分类 231
9.2 设置PostgreSQL的日志归档 232
9.2.1 【实战】设置PostgreSQL的归档模式 232
9.2.2 【实战】管理过期的归档日志文件 235
9.2.2.1 通过配置脚本定期删除归档日志文件 235
9.2.2.2 通过手动方式删除归档日志文件 236
9.3 通过SQL转储实现逻辑备份与恢复 238
9.3.1 【实战】使用pg_dump完成SQL转储 238
9.3.2 【实战】使用pg_dumpall完成SQL转储 244
9.4 文件系统级别的备份与恢复 247
9.4.1 【实战】第一个PostgreSQL文件系统级别的备份与恢复 248
9.4.2 【实战】使用pg_basebackup完成热备份与恢复 250
9.4.2.1 在主数据库服务器上使用pg_basebackup 251
9.4.2.2 在从数据库服务器上使用pg_basebackup 253
9.4.2.3 使用pg_basebackup的备份进行恢复 254
9.5 【实战】连续归档与基于时间点的恢复 257
9.5.1 创建基础备份与连续归档 257
9.5.2 执行基于时间点的数据恢复 260
9.5.3 执行恢复到指定恢复点的数据恢复 261
9.5.4 恢复到指定事务上 262
9.5.5 恢复到最近时间点上 263
9.6 使用第三方备份恢复工具pg_rman 264
9.6.1 安装与配置pg_rman 264
9.6.2 初始化pg_rman 267
9.6.3 使用pg_rman进行备份 269
9.6.3.1 使用pg_rman进行全量备份 269
9.6.3.2 使用pg_rman进行增量备份 270
9.6.4 使用pg_rman进行恢复 272
9.6.4.1 原地覆盖式恢复 273
9.6.4.2 设置新的$PGDATA目录式恢复 275
第10章 监控、诊断与优化数据库 276
10.1 使用pgbench进行基准测试 276
10.1.1 数据库基准测试简介 276
10.1.2 【实战】使用pgbench进行数据库基准测试 276
10.1.2.1 使用pgbench进行基准测试 278
10.1.2.2 使用pgbench内置脚本进行基准测试 281
10.1.2.3 使用自定义脚本进行基准测试 284
10.2 使用扩展监控和诊断数据库 285
10.2.1 使用pg_top扩展监控数据库 285
10.2.1.1 pg_top扩展的安装与配置 285
10.2.1.2 【实战】使用pg_top扩展监控数据库 287
10.2.1.3 pg_top扩展的常用参数 289
10.2.2 使用pg_stat_statements扩展监控SQL运行 290
10.2.2.1 pg_stat_statements扩展的安装与配置 290
10.2.2.2 【实战】使用pg_stat_statements扩展监控SQL语句的执行 293
10.2.3 使用pg_stat_monitor扩展查询性能监控 296
10.2.3.1 pg_stat_monitor扩展的安装与配置 296
10.2.3.2 【实战】使用pg_stat_monitor扩展监控SQL语句的执行 298
10.2.3.3 pg_stat_monitor视图和pg_stat_statements视图的差异 301
10.2.4 使用auto_explain扩展监控慢查询 302
10.2.4.1 【实战】使用auto_explain扩展记录慢查询 302
10.2.4.2 auto_explain扩展的配置参数 304
10.2.5 使用pg_profile扩展生成数据库性能报告 306
10.2.5.1 pg_profile扩展的安装与配置 306
10.2.5.2 【实战】使用性能报告诊断数据库 308
10.2.5.3 分析数据库性能报告 310
10.3 【实战】使用PostgreSQL的分区 312
10.3.1 范围分区 313
10.3.2 列表分区 315
10.3.3 哈希分区 317
10.4 优化PostgreSQL服务器 319
10.4.1 数据库性能优化基础 320
10.4.1.1 数据库性能优化的三大问题 320
10.4.1.2 数据库的性能指标 320
10.4.1.3 影响数据库性能的外部因素 321
10.4.2 使用监控工具监控Linux操作系统的性能 321
10.4.2.1 磁盘监控工具 321
10.4.2.2 CPU监控工具 324
10.4.2.3 内存监控工具 325
10.4.2.4 网络监控工具 326
10.4.3 优化数据库存储性能 327
10.4.3.1 数据库的主从复制 327
10.4.3.2 数据库分片 328
10.4.4 优化PostgreSQL的配置参数 329
10.4.5 PostgreSQL的性能视图 333
第11章 PostgreSQL的高可用架构 335
11.1 基于Keepalived的高可用架构 335
11.1.1 基于Keepalived的PostgreSQL高可用架构 335
11.1.2 【实战】基于Keepalived部署PostgreSQL的高可用架构 336
11.1.2.1 部署基于流复制的主从复制集群 336
11.1.2.2 手动完成主从复制集群的主从切换 341
11.1.2.3 基于Keepalived自动完成主从复制集群的主从切换 343
11.2 基于pg_auto_failover扩展的高可用架构 349
11.2.1 pg_auto_failover扩展的体系架构 349
11.2.1.1 单备节点架构 350
11.2.1.2 双备节点架构 350
11.2.1.3 三备节点架构 350
11.2.1.4 异步节点多备架构 351
11.2.2 搭建基于pg_auto_failover扩展的高可用集群 352
11.2.2.1 安装与配置pg_auto_failover扩展 352
11.2.2.2 配置pg_auto_failover扩展的开机自启动 357
11.2.3 pg_auto_failover扩展的故障转移 359
11.2.3.1 集群的主从切换 359
11.2.3.2 客户端的故障转移 362
11.2.4 pg_auto_failover扩展故障转移的状态机 366
11.2.4.1 状态机中状态的转换 366
11.2.4.2 pg_auto_failover集群的调度过程 367
11.3 基于数据库中间件pgpool-II的高可用架构 368
11.3.1 pgpool-II简介 368
11.3.1.1 数据库中间件的定义 368
11.3.1.2 pgpool-II的功能特性 369
11.3.1.3 基于pgpool-II的PostgreSQL高可用集群 370
11.3.2 【实战】配置基于pgpool-II的PostgreSQL高可用集群 370
11.3.2.1 准备pgpool-II环境 371
11.3.2.2 配置基于pgpool-II的读/写分离 372
11.3.2.3 配置pgpool-II的高可用架构——看门狗 377
第12章 从Oracle迁移到PostgreSQL 381
12.1 从Oracle迁移到PostgreSQL基础 381
12.1.1 Oracle的体系架构 381
12.1.1.1 客户端组件和服务器端组件 381
12.1.1.2 Oracle的逻辑存储结构 382
12.1.1.3 Oracle的物理存储结构 382
12.1.1.4 Oracle的进程结构 383
12.1.1.5 Oracle的内存结构 385
12.1.2 对比Oracle和PostgreSQL的数据库对象的差异 385
12.1.2.1 数据库和数据库实例的差异 386
12.1.2.2 用户、方案与角色的差异 386
12.1.2.3 表空间的差异 387
12.1.2.4 表的差异 387
12.1.2.5 数据类型的差异 388
12.1.3 确定数据迁移方案 390
12.1.3.1 数据迁移前的准备 390
12.1.3.2 迁移数据库对象 390
12.1.3.3 改造系统代码 391
12.1.3.4 迁移工具的选择 391
12.1.3.5 功能测试和性能测试 392
12.1.3.6 生产割接 392
12.2 【实战】使用Ora2Pg完成数据迁移 392
12.2.1 安装Ora2Pg 393
12.2.2 数据迁移过程 395
12.2.2.1 Oracle表结构的迁移 395
12.2.2.2 Oracle表中数据的迁移 397
12.2.2.3 Oracle中视图的迁移 399
12.2.2.4 Oracle中存储过程的迁移 401