PostgreSQL实战PDF电子书下载

基础篇 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