本書基于PostgreSQL 10編寫，重點介紹PostgreSQL的豐富特性以及生產(chǎn)實踐運維技巧。全書共18章，分為基礎篇、核心篇、進階篇三部分?；A篇包括第1到4章，主要介紹PostgreSQL基礎知識，例如安裝、客戶端工具、數(shù)據(jù)類型、SQL高級特性等，為讀者閱讀核心篇和進階篇做好準備；核心篇包括第5到9章，主要介紹PostgreSQL核心內(nèi)容，例如體系結(jié)構(gòu)、并行查詢、事務與并發(fā)控制、分區(qū)表、NoSQL特性等；進階篇包括第10到18章，主要介紹PostgreSQL進階內(nèi)容，例如性能優(yōu)化、物理復制、邏輯復制、備份與恢復、高可用、版本升級、Oracle數(shù)據(jù)庫遷移PostgreSQL實戰(zhàn)、PostGIS等。

目錄

Contents 目錄
序言
前言
基　礎　篇
第1章　安裝與配置基礎2
1.1　初識PostgreSQL2
1.1.1　PostgreSQL的特點3
1.1.2　許可3
1.1.3　郵件列表和討論區(qū)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　創(chuàng)建數(shù)據(jù)庫實例11
1.4.1　創(chuàng)建操作系統(tǒng)用戶11
1.4.2　創(chuàng)建數(shù)據(jù)目錄12
1.4.3　初始化數(shù)據(jù)目錄13
1.5　啟動和停止數(shù)據(jù)庫服務器14
1.5.1　使用service方式15
1.5.2　使用pg_ctl進行管理15
1.5.3　其他啟動和關閉數(shù)據(jù)庫服務器的方式16
1.5.4　配置開機啟動16
1.6　數(shù)據(jù)庫配置基礎17
1.6.1　配置文件的位置17
1.6.2　pg_hba.conf17
1.6.3　postgresql.conf19
1.6.4　允許遠程訪問數(shù)據(jù)庫20
1.7　本章小結(jié)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連接數(shù)據(jù)庫26
2.2.2　psql元命令介紹28
2.2.3　psql導入、導出表數(shù)據(jù)31
2.2.4　psql的語法和選項介紹34
2.2.5　psql執(zhí)行sql腳本36
2.2.6　psql如何傳遞變量到SQL37
2.2.7　使用psql定制日常維護腳本38
2.2.8　psql亮點功能39
2.3　本章小結(jié)43
第3章　數(shù)據(jù)類型44
3.1　數(shù)字類型44
3.1.1　數(shù)字類型列表44
3.1.2　數(shù)字類型操作符和數(shù)學函數(shù)46
3.2　字符類型47
3.2.1　字符類型列表47
3.2.2　字符類型函數(shù)48
3.3　時間/日期類型49
3.3.1　時間/日期類型列表49
3.3.2　時間/日期類型操作符50
3.3.3　時間/日期類型常用函數(shù)51
3.4　布爾類型52
3.5　網(wǎng)絡地址類型53
3.5.1　網(wǎng)絡地址類型列表54
3.5.2　網(wǎng)絡地址操作符55
3.5.3　網(wǎng)絡地址函數(shù)56
3.6　數(shù)組類型56
3.6.1　數(shù)組類型定義56
3.6.2　數(shù)組類型值輸入56
3.6.3　查詢數(shù)組元素57
3.6.4　數(shù)組元素的追加、刪除、更新58
3.6.5　數(shù)組操作符58
3.6.6　數(shù)組函數(shù)59
3.7　范圍類型60
3.7.1　范圍類型列表60
3.7.2　范圍類型邊界61
3.7.3　范圍類型操作符62
3.7.4　范圍類型函數(shù)62
3.7.5　給范圍類型創(chuàng)建索引63
3.8　json/jsonb類型63
3.8.1　json類型簡介63
3.8.2　查詢json數(shù)據(jù)64
3.8.3　jsonb與json差異64
3.8.4　jsonb與json操作符65
3.8.5　jsonb與json函數(shù)66
3.8.6　jsonb鍵/值的追加、刪除、更新66
3.9　數(shù)據(jù)類型轉(zhuǎn)換68
3.9.1　通過格式化函數(shù)進行轉(zhuǎn)換68
3.9.2　通過CAST函數(shù)進行轉(zhuǎn)換68
3.9.3　通過::操作符進行轉(zhuǎn)換69
3.10　本章小結(jié)70
第4章　SQL高級特性71
4.1　WITH查詢71
4.1.1　復雜查詢使用CTE71
4.1.2　遞歸查詢使用CTE72
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返回修改的數(shù)據(jù)76
4.3.1　RETURNING返回插入的數(shù)據(jù)76
4.3.2　RETURNING返回更新后數(shù)據(jù)77
4.3.3　RETURNING返回刪除的數(shù)據(jù)77
4.4　UPSERT78
4.4.1　UPSERT場景演示78
4.4.2　UPSERT語法79
4.5　數(shù)據(jù)抽樣80
4.5.1　SYSTEM抽樣方式81
4.5.2　BERNOULLI抽樣方式82
4.6　聚合函數(shù)84
4.6.1　string_agg函數(shù)84
4.6.2　array_agg函數(shù)85
4.7　窗口函數(shù)86
4.7.1　窗口函數(shù)語法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　窗口函數(shù)別名的使用92
4.8　本章小結(jié)93
核　心　篇
第5章　體系結(jié)構(gòu)96
5.1　邏輯和物理存儲結(jié)構(gòu)96
5.1.1　邏輯存儲結(jié)構(gòu)96
5.1.2　物理存儲結(jié)構(gòu)97
5.2　進程結(jié)構(gòu)105
5.2.1　守護進程與服務進程105
5.2.2　輔助進程105
5.3　內(nèi)存結(jié)構(gòu)106
5.3.1　本地內(nèi)存106
5.3.2　共享內(nèi)存107
5.4　本章小結(jié)107
第6章　并行查詢109
6.1　并行查詢相關配置參數(shù)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　多表關聯(lián)119
6.4.1　Nested loop多表關聯(lián)120
6.4.2　Merge join多表關聯(lián)121
6.4.3　Hash join多表關聯(lián)122
6.5　本章小結(jié)124
第7章　事務與并發(fā)控制125
7.1　事務和并發(fā)控制的概念125
7.1.1　事務的基本概念和性質(zhì)125
7.1.2　并發(fā)引發(fā)的現(xiàn)象126
7.1.3　ANSI SQL標準的事務隔離級別129
7.2　PostgreSQL的事務隔離級別130
7.2.1　查看和設置數(shù)據(jù)庫的事務隔離級別131
7.2.2　修改全局的事務隔離級別132
7.2.3　查看當前會話的事務隔離級別132
7.2.4　設置當前會話的事務隔離級別132
7.2.5　設置當前事務的事務隔離級別133
7.3　PostgreSQL的并發(fā)控制133
7.3.1　基于鎖的并發(fā)控制134
7.3.2　基于多版本的并發(fā)控制134
7.3.3　通過pageinspect觀察MVCC137
7.3.4　使用pg_repack解決表膨脹問題140
7.3.5　支持事務的DDL140
7.4　本章小結(jié)141
第8章　分區(qū)表142
8.1　分區(qū)表的意義142
8.2　傳統(tǒng)分區(qū)表143
8.2.1　繼承表143
8.2.2　創(chuàng)建分區(qū)表145
8.2.3　使用分區(qū)表146
8.2.4　查詢父表還是子表147
8.2.5　constraint_exclusion參數(shù)148
8.2.6　添加分區(qū)150
8.2.7　刪除分區(qū)150
8.2.8　分區(qū)表相關查詢151
8.2.9　性能測試152
8.2.10　傳統(tǒng)分區(qū)表注意事項155
8.3　內(nèi)置分區(qū)表155
8.3.1　創(chuàng)建分區(qū)表155
8.3.2　使用分區(qū)表157
8.3.3　內(nèi)置分區(qū)表原理探索157
8.3.4　添加分區(qū)158
8.3.5　刪除分區(qū)158
8.3.6　性能測試159
8.3.7　constraint_exclusion參數(shù)161
8.3.8　更新分區(qū)數(shù)據(jù)162
8.3.9　內(nèi)置分區(qū)表注意事項162
8.4　本章小結(jié)163
第9章　PostgreSQL的NoSQL特性164
9.1　為jsonb類型創(chuàng)建索引164
9.2　json、jsonb讀寫性能測試165
9.2.1　創(chuàng)建json、jsonb測試表165
9.2.2　json、jsonb表寫性能測試166
9.2.3　json、jsonb表讀性能測試166
9.3　全文檢索對json和jsonb數(shù)據(jù)類型的支持169
9.3.1　PostgreSQL全文檢索簡介170
9.3.2　json、jsonb全文檢索實踐173
9.4　本章小結(jié)176
進　階　篇
第10章　性能優(yōu)化180
10.1　服務器硬件180
10.2　操作系統(tǒng)優(yōu)化181
10.2.1　常用Linux性能工具181
10.2.2　Linux系統(tǒng)的I/O調(diào)度算法189
10.2.3　預讀參數(shù)調(diào)整190
10.2.4　內(nèi)存的優(yōu)化190
10.3　數(shù)據(jù)庫調(diào)優(yōu)193
10.3.1　全局參數(shù)調(diào)整193
10.3.2　統(tǒng)計信息和查詢計劃194
10.3.3　索引管理與維護201
10.4　本章小結(jié)203
第11章　基準測試與pgbench204
11.1　關于基準測試204
11.1.1　基準測試的常見使用場景205
11.1.2　基準測試衡量指標205
11.1.3　基準測試的原則205
11.2　使用pgbench進行測試206
11.2.1　pgbench的測試結(jié)果報告206
11.2.2　通過內(nèi)置腳本進行測試207
11.2.3　使用自定義腳本進行測試210
11.2.4　其他選項212
11.3　本章小結(jié)214
第12章　物理復制和邏輯復制215
12.1　異步流復制216
12.1.1　以拷貝數(shù)據(jù)文件方式部署流復制216
12.1.2　以pg_basebackup方式部署流復制222
12.1.3　查看流復制同步方式223
12.2　同步流復制224
12.2.1　synchronous_commit參數(shù)詳解224
12.2.2　配置同步流復制225
12.2.3　同步流復制的典型“陷阱”226
12.3　單實例、異步流復制、同步流復制性能測試227
12.3.1　讀性能測試228
12.3.2　寫性能測試230
12.4　流復制監(jiān)控231
12.4.1　pg_stat_replication231
12.4.2　監(jiān)控主備延遲233
12.4.3　pg_stat_wal_receiver234
12.4.4　相關系統(tǒng)函數(shù)235
12.5　流復制主備切換236
12.5.1　判斷主備角色的五種方法236
12.5.2　主備切換之文件觸發(fā)方式238
12.5.3　主備切換之pg_ctl promote方式240
12.5.4　pg_rewind241
12.6　延遲備庫244
12.6.1　延遲備庫的意義244
12.6.2　延遲備庫部署244
12.6.3　recovery_min_apply_delay參數(shù)對同步復制的影響246
12.7　同步復制優(yōu)選提交247
12.7.1　synchronous_standby_names參數(shù)詳解248
12.7.2　基于優(yōu)先級的同步備庫249
12.7.3　基于Quorum的同步備庫250
12.8　級聯(lián)復制251
12.8.1　級聯(lián)復制物理架構(gòu)252
12.8.2　級聯(lián)復制部署253
12.9　流復制維護生產(chǎn)案例255
12.9.1　案例一：主庫上創(chuàng)建表空間時備庫宕機255
12.9.2　案例二：備庫查詢被中止257
12.9.3　案例三：主庫上的WAL被覆蓋導致備庫不可用259
12.10　邏輯復制265
12.10.1　邏輯解析265
12.10.2　邏輯復制架構(gòu)267
12.10.3　邏輯復制部署268
12.10.4　邏輯復制DML數(shù)據(jù)驗證273
12.10.5　邏輯復制添加表、刪除表274
12.10.6　邏輯復制啟動、停止276
12.10.7　邏輯復制配置注意事項和限制277
12.10.8　邏輯復制延遲測試278
12.11　本章小結(jié)280
第13章　備份與恢復281
13.1　備份與恢復概述281
13.2　增量備份283
13.2.1　開啟WAL歸檔284
13.2.2　創(chuàng)建基礎備份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轉(zhuǎn)儲和文件系統(tǒng)級別的備份298
13.4.1　SQL轉(zhuǎn)儲298
13.4.2　文件系統(tǒng)級別的備份301
13.5　本章小結(jié)301
第14章　高可用302
14.1　Pgpool-II+異步流復制實現(xiàn)高可用303
14.1.1　pgpool部署架構(gòu)圖304
14.1.2　pgpool部署305
14.1.3　PCP管理接口配置312
14.1.4　pgpool方案高可用測試313
14.1.5　pgpool方案常見錯誤處理318
14.2　基于Keepalived+異步流復制實現(xiàn)高可用321
14.2.1　Keepalived+異步流復制部署架構(gòu)圖321
14.2.2　Keepalived+異步流復制高可用方案部署322
14.2.3　Keepalived配置324
14.2.4　Keepalived方案高可用測試329
14.3　本章小結(jié)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　本章小結(jié)350
第16章　擴展模塊351
16.1　CREATE EXTENSION351
16.2　pg_stat_statements353
16.3　auto_explain356
16.4　pg_prewarm357
16.5　file_fdw359
16.5.1　SQL/MED簡介359
16.5.2　file_fdw部署360
16.5.3　使用file_fdw分析數(shù)據(jù)庫日志362
16.6　postgres_fdw364
16.6.1　postgres_fdw部署364
16.6.2　postgres_fdw外部表支持寫操作366
16.6.3　postgres_fdw支持聚合函數(shù)下推367
16.7　Citus369
16.7.1　Citus特性370
16.7.2　Citus安裝370
16.7.3　Citus管理372
16.7.4　創(chuàng)建分布表373
16.7.5　Citus參數(shù)配置373
16.7.6　Citus常用功能374
16.8　本章小結(jié)377
第17章　Oracle數(shù)據(jù)庫遷移PostgreSQL實踐378
17.1　項目準備378
17.2　數(shù)據(jù)庫對象遷移379
17.3　應用代碼改造380
17.4　數(shù)據(jù)遷移測試384
17.5　功能測試和性能測試388
17.6　生產(chǎn)割接389
17.7　oracle_fdw部署過程中的常見錯誤389
17.8　本章小結(jié)391
第18章　PostGIS392
18.1　安裝與配置392
18.2　創(chuàng)建GIS數(shù)據(jù)庫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　本章小結(jié)399