基础篇 2
第1章 安装与配置基础 2
1.1 初识PostgreSQL 2
1.1.1 PostgreSQL的特点 3
1.1.2 许可 3
1.1.3 邮件列表和讨论区 3
1.2 安装PostgreSQL 3
1.2.1 通过yurn源安装 4
1.2.2 通过源码编译安装 5
1.2.3 设置一个软链接 7
1.3 客户端程序和服务器程序 8
1.3.1 客户端程序 8
1.3.2 服务器程序 11
1.4 创建数据库实例 11
1.4.1 创建操作系统用户 11
1.4.2 创建数据目录 12
1.4.3 初始化数据目录 13
1.5 启动和停止数据库服务器 14
1.5.1 使用service方式 15
1.5.2 使用pg ctl进行管理 15
1.5.3 其他启动和关闭数据库服务器的方式 16
1.5.4 配置开机启动 16
1.6 数据库配置基础 17
1.6.1 配置文件的位置 17
1.6.2 pg＿hba.conf 17
1.6.3 postgresql.conf 19
1.6.4 允许远程访问数据库 20
1.7 本章小结 22
第2章 客户端工具 23
2.1 pgAdmin 4简介 23
2.1.1 pgAdmin 4安装 23
2.1.2 pgAdmin 4使用 23
2.2 psql功能及应用 26
2.2.1 使用psql连接数据库 26
2.2.2 psql元命令介绍 28
2.2.3 psql导入、导出表数据 31
2.2.4 psql的语法和选项介绍 34
2.2.5 psql执行sql脚本 36
2.2.6 psql如何传递变量到SQL 37
2.2.7 使用psql定制日常维护脚本 38
2.2.8 psql亮点功能 39
2.3 本章小结 43
第3章 数据类型 44
3.1 数字类型 44
3.1.1 数字类型列表 44
3.1.2 数字类型操作符和数学函数 46
3.2 字符类型 47
3.2.1 字符类型列表 47
3.2.2 字符类型函数 48
3.3 时间／日期类型 49
3.3.1 时间／日期类型列表 49
3.3.2 时间／日期类型操作符 50
3.3.3 时间／日期类型常用函数 51
3.4 布尔类型 52
3.5 网络地址类型 53
3.5.1 网络地址类型列表 54
3.5.2 网络地址操作符 55
3.5.3 网络地址函数 56
3.6 数组类型 56
3.6.1 数组类型定义 56
3.6.2 数组类型值输入 56
3.6.3 查询数组元素 57
3.6.4 数组元素的追加、删除、更新 58
3.6.5 数组操作符 58
3.6.6 数组函数 59
3.7 范围类型 60
3.7.1 范围类型列表 60
3.7.2 范围类型边界 61
3.7.3 范围类型操作符 62
3.7.4 范围类型函数 62
3.7.5 给范围类型创建索引 63
3.8 json／isonb类型 63
3.8.1 json类型简介 63
3.8.2 查询json数据 64
3.8.3 jsonb与j son差异 64
3.8.4 jsonb与json操作符 65
3.8.5 jsonb与json函数 66
3.8.6 jsonb键／值的追加、删除、更新 66
3.9 数据类型转换 68
3.9.1 通过格式化函数进行转换 68
3.9.2 通过CAST函数进行转换 68
3.9.3 通过∷操作符进行转换 69
3.10 本章小结 70
第4章 SQL高级特性 71
4.1 WITH查询 71
4.1.1 复杂查询使用CTE 71
4.1.2 递归查询使用CTE 72
4.2 批量插入 74
4.2.1 方式一：INSERT INTO.SELECT 74
4.2.2 方式二：INSERT INTO VALUES（），（），…（） 75
4.2.3 方式三：COPY或＼COPY元命令 75
4.3 RETURNING返回修改的数据 76
4.3.1 RETURNING返回插入的数据 76
4.3.2 RETURNING返回更新后数据 77
4.3.3 RETURNING返回删除的数据 77
4.4 UPSERT 78
4.4.1 UPSERT场景演示 78
4.4.2 UPSERT语法 79
4.5 数据抽样 80
4.5.1 SYSTEM抽样方式 81
4.5.2 BERNOULLI抽样方式 82
4.6 聚合函数 84
4.6.1 string＿agg函数 84
4.6.2 array＿agg函数 85
4.7 窗口函数 86
4.7.1 窗口函数语法 86
4.7.2 avg（） OVER（） 87
4.7.3 row＿number（） 88
4.7.4 rank（） 89
4.7.5 dense＿rank 89
4.7.6 lag（） 90
4.7.7 first＿value （） 91
4.7.8 last＿value （） 91
4.7.9 nth＿value（） 92
4.7.10 窗口函数别名的使用 92
4.8 本章小结 93
核心篇 96
第5章 体系结构 96
5.1 逻辑和物理存储结构 96
5.1.1 逻辑存储结构 96
5.1.2 物理存储结构 97
5.2 进程结构 105
5.2.1 守护进程与服务进程 105
5.2.2 辅助进程 105
5.3 内存结构 106
5.3.1 本地内存 106
5.3.2 共享内存 107
5.4 本章小结 107
第6章 并行查询 109
6.1 并行查询相关配置参数 109
6.2 并行扫描 111
6.2.1 并行顺序扫描 111
6.2.2 并行索引扫描 112
6.2.3 并行index-only扫描 114
6.2.4 并行bitmap heap扫描 115
6.3 并行聚合 117
6.4 多表关联 119
6.4.1 Nested loop多表关联 120
6.4.2 Merge join多表关联 121
6.4.3 Hash join多表关联 122
6.5 本章小结 124
第7章 事务与并发控制 125
7.1 事务和并发控制的概念 125
7.1.1 事务的基本概念和性质 125
7.1.2 并发引发的现象 126
7.1.3 ANSI SQL标准的事务隔离级别 129
7.2 PostgreSQL的事务隔离级别 130
7.2.1 查看和设置数据库的事务隔离级别 131
7.2.2 修改全局的事务隔离级别 132
7.2.3 查看当前会话的事务隔离级别 132
7.2.4 设置当前会话的事务隔离级别 132
7.2.5 设置当前事务的事务隔离级别 133
7.3 PostgreSQL的并发控制 133
7.3.1 基于锁的并发控制 134
7.3.2 基于多版本的并发控制 134
7.3.3 通过pageinspect观察MVCC 137
7.3.4 使用pg＿repack解决表膨胀问题 140
7.3.5 支持事务的DDL 140
7.4 本章小结 141
第8章 分区表 142
8.1 分区表的意义 142
8.2 传统分区表 143
8.2.1 继承表 143
8.2.2 创建分区表 145
8.2.3 使用分区表 146
8.2.4 查询父表还是子表 147
8.2.5 constraint＿exclusion参数 148
8.2.6 添加分区 150
8.2.7 删除分区 150
8.2.8 分区表相关查询 151
8.2.9 性能测试 152
8.2.10 传统分区表注意事项 155
8.3 内置分区表 155
8.3.1 创建分区表 155
8.3.2 使用分区表 157
8.3.3 内置分区表原理探索 157
8.3.4 添加分区 158
8.3.5 删除分区 158
8.3.6 性能测试 159
8.3.7 constraint＿exclusion参数 161
8.3.8 更新分区数据 162
8.3.9 内置分区表注意事项 162
8.4 本章小结 163
第9章 PostgreSQL的NoSQL特性 164
9.1 为jsonb类型创建索引 164
9.2 json、jsonb读写性能测试 165
9.2.1 创建json、jsonb测试表 165
9.2.2 json、jsonb表写性能测试 166
9.2.3 json、jsonb表读性能测试 166
9.3 全文检索对json和jsonb数据类型的支持 169
9.3.1 PostgreSQL全文检索简介 170
9.3.2 json、jsonb全文检索实践 173
9.4 本章小结 176
进阶篇 180
第10章 性能优化 180
10.1 服务器硬件 180
10.2 操作系统优化 181
10.2.1 常用Linux性能工具 181
10.2.2 Linux系统的I／O调度算法 189
10.2.3 预读参数调整 190
10.2.4 内存的优化 190
10.3 数据库调优 193
10.3.1 全局参数调整 193
10.3.2 统计信息和查询计划 194
10.3.3 索引管理与维护 201
10.4 本章小结 203
第11章 基准测试与pgbench 204
11.1 关于基准测试 204
11.1.1 基准测试的常见使用场景 205
11.1.2 基准测试衡量指标 205
11.1.3 基准测试的原则 205
11.2 使用pgbench进行测试 206
11.2.1 pgbench的测试结果报告 206
11.2.2 通过内置脚本进行测试 207
11.2.3 使用自定义脚本进行测试 210
11.2.4 其他选项 212
11.3 本章小结 214
第12章 物理复制和逻辑复制 215
12.1 异步流复制 216
12.1.1 以拷贝数据文件方式部署流复制 216
12.1.2 以pg＿basebackup方式部署流复制 222
12.1.3 查看流复制同步方式 223
12.2 同步流复制 224
12.2.1 synchronous＿commit参数详解 224
12.2.2 配置同步流复制 225
12.2.3 同步流复制的典型“陷阱” 226
12.3 单实例、异步流复制、同步流复制性能测试 227
12.3.1 读性能测试 228
12.3.2 写性能测试 230
12.4 流复制监控 231
12.4.1 pg＿stat＿replication 231
12.4.2 监控主备延迟 233
12.4.3 pg＿stat＿wal＿receiver 234
12.4.4 相关系统函数 235
12.5 流复制主备切换 236
12.5.1 判断主备角色的五种方法 236
12.5.2 主备切换之文件触发方式 238
12.5.3 主备切换之pg＿ctl promote方式 240
12.5.4 pg＿rewind 241
12.6 延迟备库 244
12.6.1 延迟备库的意义 244
12.6.2 延迟备库部署 244
12.6.3 recovery＿min＿apply＿delay参数对同步复制的影响 246
12.7 同步复制优选提交 247
12.7.1 synchronous standby＿names参数详解 248
12.7.2 基于优先级的同步备库 249
12.7.3 基于Quorum的同步备库 250
12.8 级联复制 251
12.8.1 级联复制物理架构 252
12.8.2 级联复制部署 253
12.9 流复制维护生产案例 255
12.9.1 案例一：主库上创建表空间时备库宕机 255
12.9.2 案例二：备库查询被中止 257
12.9.3 案例三：主库上的WAL被覆盖导致备库不可用 259
12.10 逻辑复制 265
12.10.1 逻辑解析 265
12.10.2 逻辑复制架构 267
12.10.3 逻辑复制部署 268
12.10.4 逻辑复制DML数据验证 273
12.10.5 逻辑复制添加表、删除表 274
12.10.6 逻辑复制启动、停止 276
12.10.7 逻辑复制配置注意事项和限制 277
12.10.8 逻辑复制延迟测试 278
12.11 本章小结 280
第13章 备份与恢复 281
13.1 备份与恢复概述 281
13.2 增量备份 283
13.2.1 开启WAL归档 284
13.2.2 创建基础备份 285
13.3 指定时间和还原点的恢复 288
13.3.1 恢复到最近时间点 289
13.3.2 恢复到指定时间点 291
13.3.3 恢复到指定还原点 292
13.3.4 恢复到指定事务 294
13.3.5 恢复到指定时间线 296
13.4 SQL转储和文件系统级别的备份 298
13.4.1 SQL转储 298
13.4.2 文件系统级别的备份 301
13.5 本章小结 301
第14章 高可用 302
14.1 Pgpool-Ⅱ＋异步流复制实现高可用 303
14.1.1 pgpool部署架构图 304
14.1.2 pgpool部署 305
14.1.3 PCP管理接口配置 312
14.1.4 pgpool方案高可用测试 313
14.1.5 pgpool方案常见错误处理 318
14.2 基于Keepalived＋异步流复制实现高可用 321
14.2.1 Keepalived＋异步流复制部署架构图 321
14.2.2 Keepalived＋异步流复制高可用方案部署 322
14.2.3 Keepalived配置 324
14.2.4 Keepalived方案高可用测试 329
14.3 本章小结 333
第15章 版本升级 334
15.1 版本介绍 334
15.2 小版本升级 335
15.3 大版本升级 336
15.3.1 通过pg＿dumpall进行大版本升级 336
15.3.2 通过pg＿upgrade进行大版本升级 339
15.3.3 使用pglogical升级大版本 346
15.4 本章小结 350
第16章 扩展模块 351
16.1 CREATE EXTENSION 351
16.2 pg＿stat＿atatements 353
16.3 auto＿explain 356
16.4 pg＿rewarm 357
16.5 file＿fdw 359
16.5.1 SQL／MED简介 359
16.5.2 file＿fdw部署 360
16.5.3 使用file＿fdw分析数据库日志 362
16.6 postgres＿fdw 364
16.6.1 postgres＿fdw部署 364
16.6.2 postgres＿fdw外部表支持写操作 366
16.6.3 postgres＿fdw支持聚合函数下推 367
16.7 Citus 369
16.7.1 Citus特性 370
16.7.2 Citus安装 370
16.7.3 Citus管理 372
16.7.4 创建分布表 373
16.7.5 Citus参数配置 373
16.7.6 Citus常用功能 374
16.8 本章小结 377
第17章 Oracle数据库迁移PostgreSQL实践 378
17.1 项目准备 378
17.2 数据库对象迁移 379
17.3 应用代码改造 380
17.4 数据迁移测试 384
17.5 功能测试和性能测试 388
17.6 生产割接 389
17.7 oracle＿fdw部署过程中的常见错误 389
17.8 本章小结 391
第18章 PostGIS 392
18.1 安装与配置 392
18.2 创建GIS数据库 393
18.3 几何对象 393
18.3.1 几何对象的输入 394
18.3.2 几何对象的存储 394
18.3.3 几何对象的输出 395
18.3.4 几何对象的运算 395
18.4 应用场景：圈人与地理围栏 397
18.4.1 空间索引 398
18.4.2 地理围栏 399
18.5 本章小结 399