PostgreSQL数据库实战派

目录

=
第1章 PostgreSQL基础 1
1.1 PostgreSQL简介 1
1.1.1 什么是PostgreSQL 1
1.1.2 PostgreSQL的功能特性 2
1.2 PostgreSQL的体系架构 3
1.2.1 逻辑存储结构 4
1.2.2 物理存储结构 9
1.2.3 进程结构 17
1.2.4 内存结构 23
=
第2章 安装与配置PostgreSQL 25
2.1 安装CentOS操作系统 25
2.2 安装PostgreSQL服务器 35
2.3 【实战】使用PostgreSQL客户端工具 37
2.3.1 命令行客户端psql 38
2.3.2 图形化客户端pgAdmin 4 41
2.4 PostgreSQL服务器端的主配置文件详解 45
2.4.1 基础文件 46
2.4.2 连接和认证 46
2.4.3 资源使用 47
2.4.4 预写日志 49
2.4.5 复制 50
2.4.6 查询调优 51
2.4.7 错误报告和日志 53
2.4.8 进程标题 54
2.4.9 运行时统计数据 55
2.4.10 自动清理 55
2.4.11 客户端连接默认值 56
2.4.12 锁管理 57
2.4.13 版本和平台兼容性 58
2.4.14 错误处理 58
2.4.15 预置选项 59
=
第3章 管理数据库与数据库实例 60
3.1 【实战】管理数据库和数据库模板 60
3.1.1 管理数据库的基本操作 60
3.1.2 管理数据库模板 64
3.2 【实战】管理数据库的扩展 65
3.2.1 使用扩展访问外部数据源 65
3.2.2 数据预热扩展pg_prewarm 75
3.2.3 共享缓冲区监控扩展pg_buffercache 79
3.2.4 预写日志解析扩展pg_walinspect 81
3.3 管理数据库实例 83
3.4 基于PostgreSQL的分布式数据库——Citus 85
3.4.1 Citus基础 85
3.4.2 安装与配置Citus 86
3.4.3 Citus中表的类型 91
3.4.4 Citus的配置参数 96
3.5 基于PostgreSQL的分布式数据库——Greenplum 96
3.5.1 Greenplum简介 96
3.5.2 安装与配置Greenplum 98
3.5.3 Greenplum中表的类型 101
=
第4章 管理数据库对象 105
4.1 数据库与模式 105
4.2 创建与管理表 107
4.2.1 PostgreSQL的数据类型 107
4.2.2 【实战】PostgreSQL表的基本操作 110
4.2.3 数据的约束条件 111
4.2.4 表中的碎片 115
4.2.5 统计信息 118
4.2.5.1 查看数据库的统计信息 118
4.2.5.2 使用analyze命令手动收集数据库的统计信息 120
4.2.5.3 了解统计信息收集器的配置项 121
4.2.6 【实战】使用PostgreSQL的临时表 122
4.3 在查询时使用索引 123
4.3.1 索引的基础知识 123
4.3.2 索引的类型 127
4.3.3 【实战】索引的维护 130
4.3.3.1 使用仅索引扫描 130
4.3.3.2 避免重复索引 131
4.3.3.3 清除未使用的索引 132
4.4 使用视图简化查询语句 133
4.4.1 什么是视图 133
4.4.2 视图的基本操作 133
4.4.3 【实战】在PostgreSQL中使用临时视图 135
4.4.4 物化视图 136
4.5 序列 138
=
第5章 并行查询 141
5.1 并行查询是如何工作的 141
5.2 何时会用到并行查询 142
5.3 【实战】查看并行查询的执行计划 143
5.3.1 并行扫描 144
5.3.2 并行连接 146
5.4 并行查询的限制 151
=
第6章 事务与并发控制 152
6.1 PostgreSQL的事务 152
6.1.1 事务简介 152
6.1.2 控制事务 154
6.1.2.1 事务的控制语句 154
6.1.2.2 【实战】使用事务的控制语句 154
6.1.3 事务的并发 157
6.1.3.1 事务隔离级别 157
6.1.3.2 【实战】事务的脏读 158
6.1.3.3 【实战】事务的不可重复读 159
6.2 PostgreSQL的锁 161
6.2.1 锁的类型 162
6.2.2 死锁 168
6.2.2.1 【实战】模拟死锁的产生 169
6.2.2.2 如何避免死锁 171
=
第7章 应用程序开发 173
7.1 PL/pgSQL基础 173
7.1.1 【实战】开发第一个PL/pgSQL程序 174
7.1.2 使用PL/pgSQL的基本数据类型 175
7.1.3 使用PL/pgSQL的高级数据类型 176
7.2 PL/pgSQL面向过程编程 177
7.2.1 在PL/pgSQL程序中使用条件判断 177
7.2.2 在PL/pgSQL程序中使用循环 178
7.2.3 在PL/pgSQL程序中使用游标 180
7.2.4 在PL/pgSQL程序中处理例外 182
7.2.4.1 处理系统预定义例外 182
7.2.4.2 处理用户自定义例外 183
7.3 【实战】综合案例——基于员工表统计各工资段的
员工人数及各部门的工资总额 184
7.4 使用PL/pgSQL开发存储过程与存储函数 187
7.4.1 存储过程与存储函数 187
7.4.2 【实战】创建和使用存储过程 187
7.4.3 【实战】创建和使用存储函数 189
7.4.4 【实战】设置存储过程中的inout参数 189
7.4.5 【实战】在inout参数中使用游标 191
7.5 使用PL/pgSQL开发触发器 192
7.5.1 常规触发器 192
7.5.1.1 常规触发器的定义 192
7.5.1.2 【实战】利用常规触发器实现安全性检查 193
7.5.1.3 【实战】利用常规触发器进行数据确认 194
7.5.1.4 【实战】利用常规触发器实现审计 195
7.5.2 事件触发器 197
=
第8章 管理数据库安全 199
8.1 用户管理 199
8.1.1 用户与角色 199
8.1.2 管理用户的密码 204
8.1.2.1 使用SQL语句管理用户的密码 205
8.1.2.2 使用passwordcheck插件检查密码的复杂度 206
8.1.3 预定义角色 207
8.2 权限管理 207
8.2.1 【实战】在创建用户时指定的权限 207
8.2.2 使用grant命令和revoke命令管理的权限 210
8.2.3 在授权时使用admin option选项和grant option选项 215
8.2.4 使用组角色管理权限 216
8.2.5 使用set role命令显示启用角色的权限 219
8.3 审计管理 222
8.3.1 PostgreSQL的审计日志功能 222
8.3.2 PostgreSQL的审计扩展插件pgaudit 224
=
第9章 备份与恢复 228
9.1 备份与恢复的基本概念 228
9.1.1 数据库的故障类型 228
9.1.2 备份的分类 231
9.2 设置PostgreSQL的日志归档 232
9.2.1 【实战】设置PostgreSQL的归档模式 232
9.2.2 【实战】管理过期的归档日志文件 235
9.2.2.1 通过配置脚本定期删除归档日志文件 235
9.2.2.2 通过手动方式删除归档日志文件 236
9.3 通过SQL转储实现逻辑备份与恢复 238
9.3.1 【实战】使用pg_dump完成SQL转储 238
9.3.2 【实战】使用pg_dumpall完成SQL转储 244
9.4 文件系统级别的备份与恢复 247
9.4.1 【实战】第一个PostgreSQL文件系统级别的备份与恢复 248
9.4.2 【实战】使用pg_basebackup完成热备份与恢复 250
9.4.2.1 在主数据库服务器上使用pg_basebackup 251
9.4.2.2 在从数据库服务器上使用pg_basebackup 253
9.4.2.3 使用pg_basebackup的备份进行恢复 254
9.5 【实战】连续归档与基于时间点的恢复 257
9.5.1 创建基础备份与连续归档 257
9.5.2 执行基于时间点的数据恢复 260
9.5.3 执行恢复到指定恢复点的数据恢复 261
9.5.4 恢复到指定事务上 262
9.5.5 恢复到最近时间点上 263
9.6 使用第三方备份恢复工具pg_rman 264
9.6.1 安装与配置pg_rman 264
9.6.2 初始化pg_rman 267
9.6.3 使用pg_rman进行备份 269
9.6.3.1 使用pg_rman进行全量备份 269
9.6.3.2 使用pg_rman进行增量备份 270
9.6.4 使用pg_rman进行恢复 272
9.6.4.1 原地覆盖式恢复 273
9.6.4.2 设置新的$PGDATA目录式恢复 275
=
第10章 监控、诊断与优化数据库 276
10.1 使用pgbench进行基准测试 276
10.1.1 数据库基准测试简介 276
10.1.2 【实战】使用pgbench进行数据库基准测试 276
10.1.2.1 使用pgbench进行基准测试 278
10.1.2.2 使用pgbench内置脚本进行基准测试 281
10.1.2.3 使用自定义脚本进行基准测试 284
10.2 使用扩展监控和诊断数据库 285
10.2.1 使用pg_top扩展监控数据库 285
10.2.1.1 pg_top扩展的安装与配置 285
10.2.1.2 【实战】使用pg_top扩展监控数据库 287
10.2.1.3 pg_top扩展的常用参数 289
10.2.2 使用pg_stat_statements扩展监控SQL运行 290
10.2.2.1 pg_stat_statements扩展的安装与配置 290
10.2.2.2 【实战】使用pg_stat_statements扩展监控SQL语句的执行 293
10.2.3 使用pg_stat_monitor扩展查询性能监控 296
10.2.3.1 pg_stat_monitor扩展的安装与配置 296
10.2.3.2 【实战】使用pg_stat_monitor扩展监控SQL语句的执行 298
10.2.3.3 pg_stat_monitor视图和pg_stat_statements视图的差异 301
10.2.4 使用auto_explain扩展监控慢查询 302
10.2.4.1 【实战】使用auto_explain扩展记录慢查询 302
10.2.4.2 auto_explain扩展的配置参数 304
10.2.5 使用pg_profile扩展生成数据库性能报告 306
10.2.5.1 pg_profile扩展的安装与配置 306
10.2.5.2 【实战】使用性能报告诊断数据库 308
10.2.5.3 分析数据库性能报告 310
10.3 【实战】使用PostgreSQL的分区 312
10.3.1 范围分区 313
10.3.2 列表分区 315
10.3.3 哈希分区 317
10.4 优化PostgreSQL服务器 319
10.4.1 数据库性能优化基础 320
10.4.1.1 数据库性能优化的三大问题 320
10.4.1.2 数据库的性能指标 320
10.4.1.3 影响数据库性能的外部因素 321
10.4.2 使用监控工具监控Linux操作系统的性能 321
10.4.2.1 磁盘监控工具 321
10.4.2.2 CPU监控工具 324
10.4.2.3 内存监控工具 325
10.4.2.4 网络监控工具 326
10.4.3 优化数据库存储性能 327
10.4.3.1 数据库的主从复制