第一篇 准备篇 2
第1章 PostgreSQL简介 2
1.1 什么是PostgreSQL 2
1.1.1 PostgreSQL概述 2
1.1.2 PostgreSQL的发展历史 2
1.1.3 PostgreSQL数据库的优势 3
1.1.4 PostgreSQL应用现状和发展趋势 4
1.2 PostgreSQL数据库与其他数据库的对比 4
1.2.1 PostgreSQL与MySQL数据库的对比 4
1.2.2 PostgreSQL与Oracle数据库的对比 6
1.3 小结 6
第2章 PostgreSQL安装与配置 7
2.1 从发行版本安装 7
2.1.1 在Debian或Ubuntu下的安装 7
2.1.2 在Redhat、CentOS或Fedora下的安装 9
2.1.3 在Windows下的安装 12
2.1.4 发行版安装总结 16
2.2 从源码安装 16
2.2.1 编译安装过程介绍 16
2.2.2 下载源代码 17
2.2.3 编译及安装 18
2.2.4 安装后的配置 20
2.2.5 创建数据库簇 21
2.2.6 安装contrib目录下的工具 21
2.2.7 启动和停止数据库 21
2.2.8 编译安装时的常见问题及解决方法 22
2.3 安装技巧介绍 24
2.3.1 在Redhat、CentOS下使用二进制包安装较新版本的方法 24
2.3.2 如何使用较大的数据块提高I/O性能 25
2.4 PostgreSQL的简单配置 25
2.4.1 修改监听的IP和端口 25
2.4.2 与数据库log相关的参数 25
2.4.3 内存参数的设置 26
2.5 小结 26
第3章 SQL语言入门 27
3.1 SQL语句语法简介 27
3.1.1 语句的分类 27
3.1.2 词法结构 27
3.2 DDL语句 28
3.2.1 建表语句 28
3.2.2 删除表语句 30
3.3 DML语句 30
3.3.1 插入语句 30
3.3.2 更新语句 31
3.3.3 删除语句 31
3.4 查询语句 31
3.4.1 单表查询语句 31
3.4.2 过滤条件的查询 32
3.4.3 排序 32
3.4.4 分组查询 33
3.4.5 表join 34
3.5 其他SQL语句 36
3.5.1 INSERT INTO...SELECT语句 36
3.5.2 UNION语句 36
3.5.3 TRUNCATE TABLE语句 37
3.6 小结 37
第二篇 基础篇 40
第4章 psql工具的使用介绍 40
4.1 psql介绍 40
4.2 psql的简单使用 40
4.3 psql的常用命令 42
4.3.1 \d命令 42
4.3.2 指定字符集编译的命令 45
4.3.3 \pset命令 46
4.3.4 命令 46
4.3.5 执行存储在外部文件中的SQL命令 47
4.3.6 显示信息的命令 48
4.3.7 更多的命令 49
4.4 psql的使用技巧和注意事项 50
4.4.1 历史命令与补全的功能 50
4.4.2 自动提交方面的技巧 50
4.4.3 如何得到psql中命令实际执行的SQL 51
4.5 小结 53
第5章 数据类型 54
5.1 类型介绍 54
5.1.1 类型的分类 54
5.1.2 类型输入与转换 55
5.2 布尔类型 56
5.2.1 布尔类型解释 56
5.2.2 布尔类型的操作符 58
5.3 数值类型 59
5.3.1 数值类型解释 59
5.3.2 整数类型 59
5.3.3 精确的小数类型 59
5.3.4 浮点数类型 60
5.3.5 序列类型 61
5.3.6 货币类型 61
5.3.7 数学函数和操作符 62
5.4 字符串类型 64
5.4.1 类型解释 64
5.4.2 字符串函数和操作符 65
5.5 二进制数据类型 67
5.5.1 二进制数据类型解释 67
5.5.2 二进制数据类型转义表示 67
5.5.3 二进制数据类型的函数 68
5.6 位串类型 69
5.6.1 位串类型解释 69
5.6.2 位串类型的使用 69
5.6.3 位串的操作符及函数 70
5.7 日期／时间类型 71
5.7.1 日期／时间类型详解 71
5.7.2 日期输入 72
5.7.3 时间输入 73
5.7.4 特殊值 75
5.7.5 函数和操作符列表 75
5.7.6 时间函数 77
5.7.7 extract和date_part函数 80
5.8 枚举类型 81
5.8.1 枚举类型的使用 81
5.8.2 枚举类型的说明 82
5.8.3 枚举类型的函数 83
5.9 几何类型 84
5.9.1 几何类型概况 84
5.9.2 几何类型的输入 84
5.9.3 几何类型的操作符 89
5.9.4 几何类型的函数 97
5.10 网络地址类型 98
5.10.1 网络地址类型概况 98
5.10.2 inet与cidr类型 98
5.10.3 macaddr类型 101
5.10.4 网络地址类型的操作符 101
5.10.5 网络地址类型的函数 102
5.11 复合类型 103
5.11.1 复合类型的定义 103
5.11.2 复合类型的输入 104
5.11.3 访问复合类型 105
5.11.4 修改复合类型 105
5.11.5 复合类型的输入与输出 106
5.12 XML类型 107
5.12.1 XML类型的输入 107
5.12.2 字符集的问题 108
5.12.3 XML类型的函数 109
5.13 JSON类型 114
5.13.1 JSON类型简介 115
5.13.2 JSON类型的输入与输出 115
5.13.3 JSON类型的操作符 116
5.13.4 JSON类型的函数 118
5.13.5 JSON类型的索引 121
5.14 Range类型 125
5.14.1 Range类型简介 125
5.14.2 创建Range类型 126
5.14.3 Range类型的输入与输出 127
5.14.4 Range类型的操作符 130
5.14.5 Range类型的函数 130
5.14.6 Range类型的索引和约束 131
5.15 数组类型 132
5.15.1 数组类型的声明 132
5.15.2 如何输入数组值 133
5.15.3 访问数组 135
5.15.4 修改数组 137
5.15.5 数组的操作符 138
5.15.6 数组的函数 139
5.16 伪类型 142
5.17 其他类型 143
5.17.1 UUID类型 143
5.17.2 pg_lsn类型 143
第6章 逻辑结构管理 145
6.1 数据库逻辑结构介绍 145
6.2 数据库基本操作 145
6.2.1 创建数据库 145
6.2.2 修改数据库 146
6.2.3 删除数据库 147
6.2.4 常见问题及解答 147
6.3 模式 148
6.3.1 模式的定义 148
6.3.2 模式的使用 148
6.3.3 公共模式 150
6.3.4 模式的搜索路径 150
6.3.5 模式的权限 151
6.3.6 模式的移植性 151
6.4 表 152
6.4.1 创建表 152
6.4.2 表的存储属性 154
6.4.3 临时表 156
6.4.4 默认值 158
6.4.5 约束 159
6.4.6 修改表 163
6.4.7 表继承及分区表 167
6.4.8 分区表 168
6.5 触发器 173
6.5.1 创建触发器 173
6.5.2 语句级触发器与行级触发器 175
6.5.3 BEFORE触发器与AFTER触发器 177
6.5.4 删除触发器 178
6.5.5 触发器的行为 179
6.5.6 触发器函数中的特殊变量 180
6.6 事件触发器 180
6.6.1 创建事件触发器 183
6.6.2 修改事件触发器 186
6.7 表空间 186
6.7.1 表空间的定义 186
6.7.2 表空间的使用 186
6.8 视图 187
6.8.1 视图的定义 187
6.8.2 创建视图 188
6.8.3 可更新视图 189
6.9 索引 191
6.9.1 索引简介 191
6.9.2 索引的分类 192
6.9.3 创建索引 192
6.9.4 并发创建索引 193
6.9.5 修改索引 196
6.9.6 删除索引 196
6.10 用户及权限管理 197
6.10.1 用户和角色 197
6.10.2 创建用户和角色 198
6.10.3 权限的管理 199
6.10.4 函数和触发器的权限 202
6.10.5 权限的总结 202
6.10.6 权限的示例 202
6.11 事务、并发、锁 203
6.11.1 ACID 203
6.11.2 DDL事务 204
6.11.3 事务的使用 204
6.11.4 SAVEPOINT 205
6.11.5 事务隔离级别 206
6.11.6 两阶段提交 207
6.11.7 锁机制 209
6.11.8 死锁及防范 212
6.11.9 表级锁命令LOCKTABLE 213
6.11.10 行级锁命令 213
6.11.11 锁的查看 214
第7章 PostgreSQL的核心架构 221
7.1 应用程序的访问接口 221
7.1.1 访问接口总体图 221
7.1.2 不同编辑语言的PostgreSQL驱动介绍 222
7.2 进程及内存结构 223
7.2.1 进程和内存架构图 223
7.2.2 主进程Postmaster 224
7.2.3 SysLogger（系统日志）进程 224
7.2.4 BgWriter（后台写）进程 225
7.2.5 WalWriter（预写式日志写）进程 225
7.2.6 PgArch（归档）进程 225
7.2.7 AutoVacuum（自动清理）进程 225
7.2.8 PgStat（统计数据收集）进程 226
7.2.9 共享内存 226
7.2.10 本地内存 226
7.3 目录结构 227
7.3.1 安装目录的结构 227
7.3.2 数据目录的结构 227
7.3.3 表空间的目录 228
第8章 服务管理 229
8.1 服务的启停和创建 229
8.1.1 启停方法 229
8.1.2 pg_ctl 230
8.1.3 信号 234
8.1.4 postgres及单用户模式 234
8.2 服务配置介绍 235
8.2.1 配置参数 235
8.2.2 连接配置项 237
8.2.3 内存配置项 240
8.2.4 预写式日志的配置项 241
8.2.5 错误报告和日志项 243
8.3 访问控制配置文件 246
8.3.1 pg_hba.conf文件 247
8.3.2 认证方法介绍 248
8.3.3 认证方法实战 249
8.4 备份和还原 249
8.4.1 逻辑备份 249
8.4.2 pg_dump命令 250
8.4.3 pg_restore命令 254
8.4.4 pg_dump和pg_restore使用举例 257
8.4.5 物理备份 258
8.4.6 使用LVM快照进行热备份 259
8.5 常用的管理命令 261
8.5.1 查看系统信息的常用命令 261
8.5.2 系统维护常用命令 267
第三篇 提高篇 270
第9章 PostgreSQL中执行计划 270
9.1 执行计划的解释 270
9.1.1 EXPLAIN命令 270
9.1.2 EXPLAIN输出结果解释 271
9.1.3 EXPLAIN使用示例 272
9.1.4 全表扫描 275
9.1.5 索引扫描 275
9.1.6 位图扫描 275
9.1.7 条件过滤 276
9.1.8 Nestloop Join 277
9.1.9 Hash Join 277
9.1.10 Merge Join 278
9.2 与执行计划相关的配置项 279
9.2.1 ENABLE_＊参数 279
9.2.2 COST基准值参数 279
9.2.3 基因查询优化的参数 280
9.2.4 其他执行计划配置项 281
9.3 统计信息的收集 282
9.3.1 统计信息收集器的配置项 282
9.3.2 SQL执行的统计信息输出 283
9.3.3 手工收集统计信息 283
第10章 PostgreSQL中的技术内幕 285
10.1 表中的系统字段 285
10.1.1 oid 286
10.1.2 ctid 288
10.1.3 xmin、xmax、cmin、cmax 289
10.2 多版本并发控制 290
10.2.1 多版本并发控制的原理 290
10.2.2 PostgreSQL中的多版本并发控制 291
10.2.3 PostgreSQL多版本的优劣分析 293
10.3 物理存储结构 293
10.3.1 PostgreSQL中的术语 293
10.3.2 数据块结构 293
10.3.3 Tuple结构 294
10.3.4 数据块空闲空间管理 296
10.3.5 可见性映射表文件 298
10.4 技术解密 298
10.4.1 Index-only scans 298
10.4.2 Heap-Only Tuples 300
第11章 PostgreSQL的特色功能 302
11.1 规则系统 302
11.1.1 SELECT规则 302
11.1.2 更新规则 303
11.1.3 规则和权限 306
11.1.4 规则和命令状态 307
11.1.5 规则与触发器的比较 308
11.2 模式匹配和正则表达式 308
11.2.1 PostgreSQL中的模式匹配和正则表达式介绍 308
11.2.2 传统SQL的LIKE操作符 309
11.2.3 SIMILARTO正则表达式 310
11.2.4 POSIX正则表达式 312
11.2.5 模式匹配函数substring 313
11.3 listen与notify 315
11.3.1 listen与notify的简单示例 315
11.3.2 listen与notify的相关命令 316
11.3.3 listen与notify的使用详解 317
11.4 索引的特色 320
11.4.1 表达式上的索引 320
11.4.2 部分索引 320
11.4.3 GiST索引 323
11.4.4 SP-GiST索引 325
11.4.5 GIN索引 326
11.5 序列的使用 328
11.5.1 序列的创建 328
11.5.2 序列的使用及相关的函数 329
11.5.3 常见问题及解答 331
11.6 咨询锁的使用 333
11.6.1 咨询锁的定义 333
11.6.2 咨询锁的函数及使用 333
11.6.3 常见问题及解答 337
11.7 SQL/MED 338
11.7.1 SQL/MED的介绍 338
11.7.2 外部数据包装器对象 339
11.7.3 外部服务器对象 340
11.7.4 用户映射对象 341
11.7.5 外部表对象 341
11.7.6 file_fdw使用实例 342
11.7.7 postgres_fdw使用实例 345
第12章 数据库优化 347
12.1 优化准则和方法 347
12.1.1 优化准则 347
12.1.2 优化方法 348
12.2 硬件知识 348
12.2.1 CPU及服务器体系结构 348
12.2.2 内存 349
12.2.3 硬盘 350
12.3 文件系统及I/O调优 352
12.3.1 文件系统的崩溃恢复 352
12.3.2 Ext2文件系统 353
12.3.3 Ext3文件系统 353
12.3.4 Ext4文件系统 354
12.3.5 XFS文件系统 355
12.3.6 Barriers I/O 355
12.3.7 I/O调优的方法 356
12.4 性能监控 359
12.4.1 数据库性能视图 359
12.4.2 Linux监控工具 362
12.5 数据库配置优化 364
12.5.1 内存配置优化 364
12.5.2 关于双缓存的优化 366
12.5.3 vacuum中的优化 367
12.5.4 预写式日志写优化 369
第13章 Standby数据库的搭建 371
13.1 Standby数据库原理 371
13.1.1 PITR原理 371
13.1.2 WAL日志归档 372
13.1.3 流复制 372
13.1.4 Standby的运行原理 373
13.1.5 创建Standby的步骤 373
13.2 pg_basebackup命令行工具 374
13.2.1 pg_basebackup介绍 374
13.2.2 pg_basebackup的命令行参数 375
13.2.3 pg_basebackup使用示例 376
13.3 异步流复制Hot Standby的示例 377
13.3.1 配置环境 377
13.3.2 主数据库的配置 378
13.3.3 在Standby上生成基础备份 378
13.3.4 启动Standby 379
13.4 同步流复制的Standby数据库 380
13.4.1 同步流复制的架构 380
13.4.2 同步复制的配置 381
13.4.3 配置实例 381
13.5 检查备库及流复制情况 383
13.5.1 检查异步流复制的情况 383
13.5.2 检查同步流复制的情况 384
13.5.3 视图pg_stat_replication详解 385
13.5.4 查看备库的状态 385
13.6 Hot Standby的限制 387
13.6.1 Hot Standby的查询限制 387
13.6.2 Hot Standby的查询冲突处理 389
13.7 恢复配置详解 390
13.7.1 归档恢复配置的配置项 390
13.7.2 Recovery Target配置 391
13.7.3 Standby Server配置 391
13.8 流复制的注意事项 392
13.8.1 wal_keep_segments参数的配置 392
13.8.2 vacuum_defer_cleanup_age参数的配置 392
第四篇 第三方开源软件及架构篇 394
第14章 PgBouncer 394
14.1 PgBouncer介绍 394
14.2 PgBouncer中的概念 395
14.3 PgBouncer的安装方法 395
14.4 PgBouncer的简单使用 395
14.4.1 简单配置方法 395
14.4.2 启动PgBouncer 396
14.4.3 停止PgBouncer 397
14.4.4 查看连接池信息 397
14.5 PgBouncer的配置文件详解 399
14.5.1 “［databases］”部分的配置项 399
14.5.2 “［pgbouncer］”部分的配置项 399
14.5.3 用户密码文件 403
第15章 Slony-Ⅰ的使用 404
15.1 Slony-Ⅰ中的概念 404
15.1.1 集群 404
15.1.2 节点 405
15.1.3 复制集合 405
15.1.4 数据原始生产者、数据提供者和数据订阅者 405
15.1.5 slon守护程序 405
15.1.6 slonik配置程序 405
15.2 Slony-Ⅰ复制的限制 405
15.3 在Windows下使用pgAdminⅢ安装配置Slony-Ⅰ 406
15.3.1 Windows下安装Slony-Ⅰ 406
15.3.2 Windows配置Slony-Ⅰ同步实例 407
15.4 在Linux下安装配置Slony-Ⅰ 419
15.4.1 编译安装Slony-Ⅰ 419
15.4.2 配置Slony-Ⅰ复制 421
第16章 Bucardo的使用 426
16.1 Bucardo中的概念 426
16.1.1 Bucardo介绍 426
16.1.2 Bucardo FAQ 426
16.1.3 Bucardo同步中定义的概念 427
16.2 Bucardo的安装方法 427
16.2.1 Bucardo的安装步骤 427
16.2.2 安装Test-Simple、ExtUtils-MakeMaker version 428
16.2.3 安装DBI及DBD∷Pg 428
16.2.4 安装DBIx-Safe 429
16.2.5 安装Bucardo源码包 429
16.3 Bucardo同步配置 429
16.3.1 示例环境 429
16.3.2 配置同步的简要过程 430
16.3.3 bucardo_ctl install 431
16.3.4 bucardo_ctl add db 433
16.3.5 bucardo_ctl add table 433
16.3.6 bucardo_ctl add herd 433
16.3.7 bucardo_ctl add sync 433
16.3.8 bucardo_ctl start 434
16.4 Bucardo的日常维护 434
16.4.1 Bucardo的触发器日志清理 434
16.4.2 临时停止和启动同步的方法 435
16.4.3 新增表到同步的方法 435
16.4.4 移除某个表或序列的方法 435
第17章 PL/Proxy的使用 436
17.1 PL/Proxy中的概念 436
17.1.1 PL/Proxy的定义 436
17.1.2 PL/Proxy的特性说明 438
17.2 PL/Proxy安装及配置 438
17.2.1 编译安装 438
17.2.2 安装规划 439
17.2.3 配置过程 439
17.3 PL/Proxy的集群配置详解 443
17.3.1 Cluster configuration API方式 444
17.3.2 SQL/MED方式配置集群 446
17.4 PL/Proxy语言详解 446
17.4.1 CONNECT 447
17.4.2 CLUSTER 447
17.4.3 RUN ON 447
17.4.4 SPLIT 448
17.4.5 TARGET 448
17.5 PL/Proxy的一个高可用方案 448
17.5.1 方案介绍 448
17.5.2 方案架构 449
17.5.3 具体实施步骤 449
第18章 pgpool-Ⅱ的使用 458
18.1 pgpool-Ⅱ中的概念 458
18.1.1 pgpool-Ⅱ的定义 458
18.1.2 pgpool-Ⅱ的架构 459
18.1.3 pgpool-Ⅱ的工作模式 460
18.1.4 pgpool-Ⅱ的程序模块 461
18.2 pgpool-Ⅱ安装方法 462
18.2.1 源码安装 462
18.2.2 安装pgpool_regclass 463
18.2.3 建立insert_lock表 463
18.2.4 安装C语言函数 463
18.3 pgpool-Ⅱ配置快速入门 463
18.3.1 pgpool-Ⅱ的配置文件及启停方法 464
18.3.2 复制和负载均衡的示例 466
18.3.3 使用流复制的主备模式的示例 467
18.3.4 show命令 468
18.4 pgpool-Ⅱ高可用配置方法 471
18.4.1 pgpool-Ⅱ高可用切换及恢复的原理 471
18.4.2 pgpool-Ⅱ的健康检查 473
18.4.3 复制和负载均衡模式的高可用示例 473
18.4.4 使用流复制的主备模式下的高可用示例 488
18.5 pgpool-Ⅱ的总结 492
第19章 Postgres-XC的使用 493
19.1 Postgres-XC中的概念 493
19.1.1 Postgres-XC的定义 493
19.1.2 Postgres-XC的特点 493
19.1.3 Postgres-XC的性能 494
19.1.4 Postgres-XC的组件 494
19.2 Postgres-XC的安装 495
19.2.1 源码安装方法 495
19.2.2 Postgres-XC目录及程序说明 496
19.3 配置Postgres-XC集群 497
19.3.1 集群规划 497
19.3.2 初始化GTM 498
19.3.3 初始化GTM的备库 498
19.3.4 初始化GTM Proxy 499
19.3.5 初始化Coordinators、数据节点 499
19.3.6 启动集群 500
19.3.7 停止集群 501
19.3.8 配置集群节点信息 502
19.4 Postgres-XC的使用 503
19.4.1 建表详解 503
19.4.2 使用限制 509
19.4.3 重新分布数据 510
19.4.4 增加Coordinator节点的方法 512
19.4.5 移除Coordinator节点的方法 513
19.4.6 增加Datanode节点的方法 513
19.4.7 移除Datanode节点的方法 514
第20章 高可用性方案设计 516
20.1 高可用架构基础 516
20.1.1 各种高可用架构介绍 516
20.1.2 服务的可靠性设计 517
20.1.3 数据可靠性设计 517
20.2 基于共享存储的高可用方案 517
20.2.1 SAN存储的方案 517
20.2.2 DRBD的方案 518
20.3 WAL日志同步或流复制同步的方案 519
20.3.1 持续复制归档的standby的方法 519
20.3.2 异步流复制的方案 519
20.3.3 基于同步流复制方案 519
20.4 基于触发器的同步方案 520
20.4.1 方案的特点 520
20.4.2 基于触发器方案的同步软件介绍 520
20.5 基于语句中间件的高可用方案 520
20.5.1 方案的特点 520
20.5.2 基于语句中间件的开源软件介绍 521