PostgreSQL修炼之道

内容概要

唐成，拥有十几年数据库、操作系统、存储领域的工作经验。目前任沃趣科技首席数据库架构师，从事数据库高可用、高性能以及数据库云化等方面的研究工作。历任阿里巴巴数据库专家、高级数据库专家，从事过阿里巴巴的线上Oracle、Greenplum、PostgreSQL数据库的架构设计和运维工作，为阿里巴巴的PostgreSQL数据库的布道者，推动阿里云的关系型数据库服务（RDS）中提供PostgreSQL数据库服务，为阿里巴巴PostgreSQL数据库方面的技术带头人。也曾任网易杭州研究院开发专家，主导了网易云计算中的云硬盘产品（类似amazon有EBS）的设计和开发。

书籍目录

前言

第一篇 准备篇

第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

第二篇 基础篇

第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 \x命令  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 表级锁命令LOCK TABLE  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

第三篇 提高篇

第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 SIMILAR TO 正则表达式  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.1PITR原理  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

第四篇 第三方开源软件及架构篇

第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-I的使用  404

15.1 Slony-I中的概念  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-I复制的限制  405

15.3 在Windows下使用pgAdminIII安装配置Slony-I  406

15.3.1 Windows下安装Slony-I  406

15.3.2 Windows配置 Slony-I同步实例  407

15.4 在Linux下安装配置Slony-I  419

15.4.1 编译安装Slony-I  419

15.4.2 配置Slony-I复制  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-II的使用  458

18.1 pgpool-II中的概念  458

18.1.1 pgpool-II的定义  458

18.1.2 pgpool-II的架构  459

18.1.3 pgpool-II的工作模式  460

18.1.4 pgpool-II的程序模块  461

18.2 pgpool-II安装方法  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-II配置快速入门  463

18.3.1 pgpool-II的配置文件及启停方法  464

18.3.2 复制和负载均衡的示例  466

18.3.3 使用流复制的主备模式的示例  467

18.3.4 show命令  468

18.4 pgpool-II高可用配置方法  471

18.4.1 pgpool-II高可用切换及恢复的原理  471

18.4.2 pgpool-II的健康检查  473

18.4.3 复制和负载均衡模式的高可用示例  473

18.4.4 使用流复制的主备模式下的高可用示例  488

18.5 pgpool-II的总结  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

作者简介

《PostgreSQL修炼之道：从小工到专家》是目前有关PostgreSQL数据库领域比较全面和系统的中文著作，内容十分丰富。全书共20章，分为四个部分。

准备篇（1～3章）针对PostgreSQL数据库的总体概况、安装配置步骤及SQL基础知识进行了介绍。基础篇（4～8章）的内容是为了帮助对PostgreSQL数据库不是很了解的读者打好基础。这部分介绍了psql工具的使用方法， PostgreSQL数据库的数据类型和数据库对象的操作方法，以及PostgreSQL数据库的架构和数据库服务的管理等知识。提高篇（9～13章）讲解PostgreSQL的执行计划、技术内幕及特色功能，最后还介绍了解数据库优化方法和PostgreSQL Standby数据库的搭建过程。若想早日成为PostgreSQL数据库高手，请仔细阅读这一部分内容。第三方开源软件及架构篇（14～20章）介绍PostgreSQL中最常用开源连接池软件PgBouncer、逻辑复制软件Slony-I、逻辑同步软件Bucardo、数据水平拆分功能的开源软件 PLProxy、数据库中间件软件pgpool-II、集群数据库软件Postgres-XC的使用方法。最后还介绍了与PostgreSQL数据库配套使用的一些开源软件和搭建各种实用的高可用方案方面的知识。

 PostgreSQL修炼之道下载 精选章节试读 更多精彩书评