Contents 序言
前言
基　础　篇
第1章　安装与配置基础2
1.1　初识PostgreSQL2
1.1.1　PostgreSQL的特点3
1.1.2　许可3
1.1.3　邮件列表和讨论区3
1.2　安装PostgreSQL3
1.2.1　通过yum源安装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.conf17
1.6.3　postgresql.conf19
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如何传递变量到SQL37
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/jsonb类型63
3.8.1　json类型简介63
3.8.2　查询json数据64
3.8.3　jsonb与json差异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　复杂查询使用CTE71
4.1.2　递归查询使用CTE72
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　UPSERT78
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
核　心　篇
第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观察MVCC137
7.3.4　使用pg_repack解决表膨胀问题140
7.3.5　支持事务的DDL140
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
进　阶　篇
第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章　基准测试与pgbench204
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_replication231
12.4.2　监控主备延迟233
12.4.3　pg_stat_wal_receiver234
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_rewind241
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-II+异步流复制实现高可用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 EXTENSION351
16.2　pg_stat_statements353
16.3　auto_explain356
16.4　pg_prewarm357
16.5　file_fdw359
16.5.1　SQL/MED简介359
16.5.2　file_fdw部署360
16.5.3　使用file_fdw分析数据库日志362
16.6　postgres_fdw364
16.6.1　postgres_fdw部署364
16.6.2　postgres_fdw外部表支持写操作366
16.6.3　postgres_fdw支持聚合函数下推367
16.7　Citus369
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章　PostGIS392
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