PostgreSQL实战

本书由数据库专业开发人员撰写，系统介绍PostgreSQL10的丰富特性，及其在生产实践运维中的技巧，全书分为基础篇、核心篇、进阶篇，共18章。基础篇包括靠前~4章，主要介绍PostgreSQL基础知识，例如安装与配置、客户端工具、数据类型、SQL不错特性等，为读者阅读核心篇和进阶篇做好准备；核心篇包括第5-9章，主要介绍PostgreSQL核心内容，例如体系结构、并行查询、事务与并发控制、分区表等；进阶篇包括靠前0~18章，主要介绍PostgreSQL不错内容，例如性能优化、物理复制、逻辑复制、备份与恢复、高可用、版本升级、扩展模块、Oracle数据库迁移PostgreSQL实战、PostGIS等。

谭峰，网名francs，中国开源软件推进联盟PostgreSQL分会特聘专家，《PostgreSQL 9 Administration Cookbook》译者之一，《PostgreSQL High Performance Cookbook》英文版技术审校者之一，曾在杭州斯凯网络科技有限公司从事PostgreSQL DBA一职六年。热忠于博客分享PostgreSQL经验，分享技术博客500余篇。现就职于浙江移动负责应用上云架构管控、资源分配以及私有云建设工作。
张文升，中国开源软件推进联盟PostgreSQL分会核心成员之一。常年活跃于PostgreSQL、MySQL、Redis等开源技术社区，坚持推动PostgreSQL在中国地区的发展，多次参与组织PostgreSQL全国用户大会。近年来致力于推动PostgreSQL在互联网企业的应用以及企业PostgreSQL培训与技术支持。

前言
基础篇
第1章安装与配置基础2
1.1初识PostgreSQL2
1.1.1PostgreSQL的特点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.2pg_hba.conf17
1.6.3postgresql.conf19
1.6.4允许远程访问数据库20
1.7本章小结22
第2章客户端工具23
2.1pgAdmin4简介23
2.1.1pgAdmin4安装23
2.1.2pgAdmin4使用23
2.2psql功能及应用26
2.2.1使用psql连接数据库26
2.2.2psql元命令介绍28
2.2.3psql导入、导出表数据31
2.2.4psql的语法和选项介绍34
2.2.5psql执行sql脚本36
2.2.6psql如何传递变量到SQL37
2.2.7使用psql定制日常维护脚本38
2.2.8psql亮点功能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.8json/jsonb类型63
3.8.1json类型简介63
3.8.2查询json数据64
3.8.3jsonb与json差异64
3.8.4jsonb与json操作符65
3.8.5jsonb与json函数66
3.8.6jsonb键/值的追加、删除、更新66
3.9数据类型转换68
3.9.1通过格式化函数进行转换68
3.9.2通过CAST函数进行转换68
3.9.3通过::操作符进行转换69
3.10本章小结70
第4章SQL高级特性71
4.1WITH查询71
4.1.1复杂查询使用CTE71
4.1.2递归查询使用CTE72
4.2批量插入74
4.2.1方式一：INSERTINTO...SELECT...74
4.2.2方式二：INSERTINTOVALUES(),(),...()75
4.2.3方式三：COPY或\COPY元命令75
4.3RETURNING返回修改的数据76
4.3.1RETURNING返回插入的数据76
4.3.2RETURNING返回更新后数据77
4.3.3RETURNING返回删除的数据77
4.4UPSERT78
4.4.1UPSERT场景演示78
4.4.2UPSERT语法79
4.5数据抽样80
4.5.1SYSTEM抽样方式81
4.5.2BERNOULLI抽样方式82
4.6聚合函数84
4.6.1string_agg函数84
4.6.2array_agg函数85
4.7窗口函数86
4.7.1窗口函数语法86
4.7.2avg()OVER()87
4.7.3row_number()88
4.7.4rank()89
4.7.5dense_rank()89
4.7.6lag()90
4.7.7first_value()91
4.7.8last_value()91
4.7.9nth_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并行bitmapheap扫描115
6.3并行聚合117
6.4多表关联119
6.4.1Nestedloop多表关联120
6.4.2Mergejoin多表关联121
6.4.3Hashjoin多表关联122
6.5本章小结124
第7章事务与并发控制125
7.1事务和并发控制的概念125
7.1.1事务的基本概念和性质125
7.1.2并发引发的现象126
7.1.3ANSISQL标准的事务隔离级别129
7.2PostgreSQL的事务隔离级别130
7.2.1查看和设置数据库的事务隔离级别131
7.2.2修改全局的事务隔离级别132
7.2.3查看当前会话的事务隔离级别132
7.2.4设置当前会话的事务隔离级别132
7.2.5设置当前事务的事务隔离级别133
7.3PostgreSQL的并发控制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
……

Preface  前  言PostgreSQL拥有近三十年的历史，是目前最先进的开源数据库，PostgreSQL具备丰富的企业级特性，尽管在欧美、日本使用非常广泛，但在国内并没有得到广泛使用，产生这种情形的原因是多样的，其中与PostgreSQL中文资料匮乏有较大关系，目前市场上PostgreSQL中文书籍非常少。    笔者从2010年开始从事PostgreSQL DBA工作，在PostgreSQL数据库运维工程中积累了一些经验，因此想系统编写一本PostgreSQL书籍，一方面总结自己在PostgreSQL数据库运维方面的经验，另一方面希望对PostgreSQL从业者有所帮助，同时希望给PostgreSQL在国内的发展贡献一份力量；本书的另一位作者张文升拥有丰富的PostgreSQL运维经验，目前就职于探探科技任首席PostgreSQL DBA，他的加入极大地丰富了此书的内容。    近几年PostgreSQL在国内得到较快的发展，平安科技、去哪儿网、探探科技、斯凯网络等公司都在逐步使用PostgreSQL，目前阿里云、腾讯云、华为云等主流云服务提供商也提供了基于PostgreSQL数据库的云服务，相信PostgreSQL在国内将有更广阔的发展。    本书主要内容本书系统介绍PostgreSQL的丰富特性，以及生产实践运维中的技巧，全书分为基础篇、核心篇、进阶篇。基础篇包括第1～4章，主要介绍PostgreSQL基础知识，例如安装与配置、客户端工具、数据类型、SQL高级特性等，为读者阅读核心篇和进阶篇做好准备；核心篇包括第5～9章，主要介绍PostgreSQL核心内容，例如体系结构、并行查询、事务与并发控制、分区表等；进阶篇包括第10～18章，主要介绍PostgreSQL进阶内容，相比前两篇进阶篇的难度有一定程度增加，例如性能优化、物理复制、逻辑复制、备份与恢复、高可用、版本升级、扩展模块、Oracle数据库迁移PostgreSQL实战、PostGIS等。本书18章主要内容如下。    第1章：介绍PostgreSQL起源、安装、数据库实例创建、数据库配置、数据库的启动和停止等。    第2章：介绍psql命令行客户端工具的使用和特性，例如psql元命令、psql导入导出数据、使用psql执行脚本、psql的亮点功能等。    第3章：介绍PostgreSQL各种数据类型，包括字符类型、时间/日期类型、布尔类型、网络地址类型、数组类型、范围类型、json/jsonb类型等；同时介绍了数据类型相关函数、操作符、数据类型转换。    第4章：主要介绍PostgreSQL支持的一些高级SQL特性，例如WITH查询、批量插入、RETURNING返回DML修改的数据、UPSERT、数据抽样、聚合函数、窗口函数等。    第5章：简单介绍PostgreSQL的逻辑结构和物理结构，以及PostgreSQL的守护进程、服务进程和辅助进程。    第6章：介绍PostgreSQL并行查询相关配置与应用，以及多表关联中并行的使用。    第7章：介绍事务的基本概念、性质和事务隔离级别，以及PostgreSQL多版本并发控制的原理和机制。    第8章：介绍传统分区表和内置分区表的部署、分区维护和性能测试。    第9章：介绍PostgreSQL的NoSQL特性，以及PostgreSQL全文检索。    第10章：简单介绍了服务器硬件、操作系统配置对性能的影响，介绍了一些常用的Linux性能监控工具，并着重介绍了对性能影响较大的几个方面，以及性能优化方案。    第11章：着重介绍PostgreSQL内置的测试工具pgbench，以及如何使用pgbench的内置脚本和自定义脚本进行基准测试。    第12章：主要介绍PostgreSQL物理复制和逻辑复制，并结合笔者在数据库维护过程中的实践经验分享了三个典型的流复制维护生产案例。    第13章：重点介绍PostgreSQL物理备份、增量备份，同时演示了数据库恢复的几种场景。    第14章：介绍两种高可用方案，一种是基于Pgpool-II和异步流复制的高可用方案，另一种是基于Keepalived和异步流复制的高可用方案。    第15章：介绍PostgreSQL版本命名规则、支撑策略、历史版本演进，介绍了小版本升级，最后重点介绍了大版本升级的三种方式。    第16章：主要介绍一些常见的外部扩展，例如file_fdw、pg_stat_statements、auto_explain、postgres_fdw，并重点介绍Citus外部扩展。    第17章：从实际案例出发，分享了一个Oracle数据库迁移到PostgreSQL数据库的实际项目。    第18章：简单介绍PostGIS部署、几何对象的输入、输出、存储、运算，最后介绍了PostGIS的一个典型应用场景：圈人与地理围栏。    本书特点本书不是PostgreSQL入门书籍，不会介绍PostgreSQL每个基础知识点，本书从PostgreSQL生产实践运维出发，对PostgreSQL重点内容进行详细讲解并给出演示示例，是一本PostgreSQL数据库运维实战书籍。    本书基于PostgreSQL 10编写，书中涵盖了大量PostgreSQL 10重量级新特性，例如内置分区表、逻辑复制、并行查询增强、同步复制优选提交等，通过阅读此书，读者能够学习到PostgreSQL 10重量级新特性。    本书共18章，如果你对PostgreSQL有一定的运维经验，完全可以不按章节顺序，而是选择比较关注的章节进行阅读。如果你完全没有PostgreSQL数据库基础，建议先通过其他资料大致掌握PostgreSQL基础知识，再来阅读本书，相信你在此书的阅读过程中能有收获。    读者对象本书适合有一定