深入淺出 PostgreSQL

屠要峰

研究員、中興通訊數據庫技術專家委員會主任、研究院副院長、
CCF信息存儲委員會常委、中國開源軟件聯盟理事。
擁有二十多年大型軟件系統研發實踐經驗，主持過大數據、雲計算、
雲存儲及分佈式數據庫等多個大型分佈式系統的研發。
負責中興通訊數據智能方向的研發及應用，獲省級科技進步一等獎兩次。
主要研究方向為大數據、雲計算、數據庫及存儲。

陳河堆

現任中興通訊數據庫平台首席專家，PostgreSQL中文社區核心組成員，
擁有十幾年數據庫研發工作經驗，曾參與公司自研高性能內存數據庫、
分佈式數據庫和分佈式緩存等系統的設計和開發，近年來醉心於鑽研開源數據庫技術，
熱心推動PostgreSQL在中國地區的推廣和應用，積極參與PostgreSQL中文社區系列活動。
 

-
第1章初識PostgreSQL 2
1.1 PostgreSQL的起源2
1.2 PostgreSQL簡介3
-1.2.1基本概念3
-1.2 .2存儲體系結構4
-1.2.3進程體系架構6
1.3 PostgreSQL 10版本的新特性6
1.4小結7

第2章安裝和啟動PostgreSQL 8
2.1安裝PostgreSQL 8
-2.1.1在Windows中安裝PostgreSQL 8
-2.1. 2在Linux中安裝PostgreSQL 12
-2.1.3源碼安裝PostgreSQL 14
2.2初始化數據庫17
2.3數據庫的基本配置18
-2.3.1配置數據庫監聽IP和端口18
-2.3.2配置數據庫錯誤日誌18
2.4啟動、停止、查看數據庫18
-2.4.1啟動數據庫18
-2.4.2停止數據庫19
-2.4.3查看數據庫狀態19
2.5小結19
-
------------------第2篇入門
-
第3章創建數據庫和表21
3.1認識SQL語言21
-3.1.1 SQL語句類型21
-3.1.2 SQL數據類型21
3.2使用交互式終端psql 22
-3.2.1連接數據庫22
-3.2.2常用命令23
- 3.2.3使用技巧26
3.3表空間的創建、修改和刪除27
-3.3.1 【實例1】創建表空間27
-3.3.2 【實例2】修改表空間29
-3.3.3 【實例3】刪除表空間29
3.4數據庫的創建、修改和刪除30
-3.4.1 【實例4】創建數據庫30
-3.4.2 【實例5】修改數據庫31
-3.4.3 【實例6】刪除數據庫32
3.5創建數據表32
-3.5.1創建表的語法格式32
-3.5.2使用CHECK約束35
-3.5.3使用非空約束36
-3.5.4使用唯一性約束36
-3.5.5使用主鍵約束37
-3.5.6使用默認約束38
-3.5.7使用外鍵約束38
-3.5.8 【實例7】設置表的屬性值自動增加40
-3.5.9 【實例8 】查看表結構41
3.6修改數據表42
-3.6.1 【實例9】修改表名42
-3.6.2 【實例10】修改字段名43
-3.6.3 【實例11】添加字段43
-3.6.4 【實例12】刪除字段44
-3.6.5 【實例13】刪除表的外鍵約束44
3.7刪除數據表46
-3.7.1 【實例14】刪除沒有被關聯的表46
-3.7.2 【實例15】刪除被其他表關聯的主表46
3.8數據的插入、修改和刪除47
-3.8.1 【實例16】向表中插入數據47
-3.8.2 【實例17】修改表中的數據48
-3.8.3 【實例18】刪除表中的數據49
3.9數據的簡單查詢50
-3.9 .1 【實例19】創建最簡單的單表查詢50
-3.9.2 【實例20】創建帶過濾條件的查詢50
3.10小結51
-
第4章數據類型及其運算符52
4.1數值類型52
-4.1. 1整數類型52
-4.1.2任意精度數字類型53
-4.1.3浮點類型53
-4.1.4序列類型54
-4.1.5貨幣類型55
-4.1.6 【實例21】使用數學運算符55
4.2字符串類型56
-4.2.1字符串類型分類57
-4.2.2使用連接運算符57
-4.2.3使用模式匹配運算符58
4.3二進制數據類型60
-4.3.1二進制數據類型簡介60
-4.3.2二進制數據的十六進制格式61
-4.3.3二進制數據的轉義格式61
4.4日期類型和時間類型61
-4.4.1日期類型和時間類型分類61
-4.4.2日期類型和時間類型輸入格式62
-4.4.3日期類型和時間類型輸出格式64
-4.4.4時區64
-4.4.5使用日期類型和時間類型運算符65
4.5布爾類型66
-4.5.1布爾類型簡介66
-4.5. 2布爾類型運算符66
4.6位串類型68
-4.6.1位串類型簡介68
-4.6.2 【實例22】使用位串類型運算符68
4.7枚舉類型69
-4.7.1聲明枚舉類型69
- 4.7.2排序69
-4.7.3注意枚舉類型安全性70
4.8幾何類型71
-4.8.1點（point） 71
-4.8.2線（line） 72
-4.8.3線段（lseg） 72
-4.8.4矩形（box） 73
-4.8.5路徑（path） 74
-4.8.6多邊形（polygon） 75
-4.8.7圓（circle） 75
-4.8.8 【實例23】使用幾何類型運算符76
4.9 JSON數據類型77
-4.9.1 JSON數據類型簡介77
-4.9.2 JSON數據類型的輸入/輸出語法78
-4.9.3 【實例24】使用json運算符79
- 4.9.4 【實例25】使用jsonb運算符79
4.10範圍類型80
-4.10.1內建範圍類型80
-4.10.2範圍類型的邊界81
-4.10.3範圍值的輸入81
-4.10.4使用範圍類型運算符82
-4.10.5 【實例26】定義自己的範圍類型83
4.11數組類型83
-4.11.1定義數組類型84
-4.11.2輸入數組84
-4.11.3訪問數組86
-4.11.4修改數組87
-4.11.5查找數組中的內容88
-4.11.6數組運算符89
4.12小結89

第5章函數90
5.1數學函數90
-5.1.1認識數學函數90
-5.1.2 【實例27】用數學函數對小數取整93
5.2字符串函數94
-5.2.1認識字符串函數95
-5.2.2認識二進製字符串函數99
-5.2.3 【實例28】用format()函數格式化輸出101
5.3數據類型格式化函數103
5.4序列函數104
-5.4.1認識序列函數104
-5.4.2 【實例29】創建及使用序列函數104
5.5日期函數和時間函數107
- 5.5.1認識日期函數和時間函數107
-5.5.2 【實例30】用AT TIME ZONE進行時區轉換110
-5.5.3 【實例31】獲取當前的日期和時間111
-5.5.4 【實例32】設置延時執行112
5.6位串函數113
-5.6.1認識位串函數113
-5.6.2 【實例33】用bit()函數轉換整數和位串113
5.7枚舉函數114
-5.7.1認識枚舉函數114
-5.7.2 【實例34】用枚舉函數獲取枚舉類型中的值114
5.8幾何函數115
-5.8.1認識幾何函數115
-5.8.2 【實例35】用area()函數計算幾何類型的面積117
5.9 JSON函數117
-5.9.1 JSON創建函數117
-5.9.2 JSON處理函數118
-5.9.3 【實例36】用to_json()函數將字符串轉換成JSON格式122
-5.9.4 【實例37】用json_array_length()函數統計JSON數據類型中的元素個數122
5.10範圍函數123
-5.10.1認識範圍函數123
-5.10.2 【實例38】用range_merge()函數獲取一個包含給定範圍的最小範圍123
-5.10.3 【實例39】用upper_inc()函數判斷上界是否被包含在範圍內123
5.11數組函數124
-5.11.1認識數組函數124
-5.11.2 【實例40】用array_position()函數在數組中檢索數據125
-5.11.3 【實例41】用array_to_string()函數將數組轉為字符串126
5.12其他函數126
-5.12.1聚合函數126
-5.12.2集合返回函數131
-5.12.3系統信息函數131
-5.12.4系統管理函數133
5.13小結134

第6章查詢和修改數據135
6.1 SELECT語法135
6.2單表查詢136
-6.2.1 【實例42】簡單查詢136
-6.2.2 【實例43】等值查詢137
-6.2.3 【實例44】範圍查詢137
-6.2.4 【實例45】去重138
-6.2.5 【實例46】排序140
-6.2.6 【實例47】分組140
-6.2.7 【實例48】分頁141
6.3多表查詢142
-6.3.1 【實例49】內連接143
-6.3.2 【實例50】外連接144
-6.3. 3 【實例51】交叉連接146
-6.3.4 【實例52】多表關聯複合查詢146
6.4子查詢147
-6.4.1 【實例53】IN/NOT IN子查詢147
-6.4.2 【實例54】EXISTS /NOT EXISTS子查詢148
-6.4.3 【實例55】ANY/SOME/ALL子查詢149
6.5模糊匹配查詢150
-6.5.1 【實例56】LIKE查詢150
-6.5.2 【實例57】SIMILAR TO查詢152
6.6查詢運算152
-6.6.1 【實例58】聚集運算152
-6.6.2 【實例59】分組運算154
-6.6.3 【實例60】表達式運算156
-6.6.4 【實例61】遞歸查詢157
6.7窗口函數158
6.8高級操作162
-6.8.1 【實例62】歸併數據162
-6.8.2 【實例63】批量插入164
-6.8.3 【實例64】批量更新165
-6.8.4 【實例65】關聯更新166
-6.8.5 【實例66】批量刪除166
-6.8.6 【實例67】關聯刪除166
-6.8.7 【實例68】移動數據到歷史表167
-6.8.8 【實例69】清空表167
6.9小結168
-
------------------第3篇進階
-
第7章索引170
7.1索引簡介170
-7.1.1索引的含義和作用170
-7.1.2索引的分類171
-7.1.3索引的設計原則172
7.2索引操作172
-7.2.1創建索引172
-7.2.2修改索引174
-7.2.3刪除索引175
7.3常用索引方法176
-7.3.1多列索引176
-7.3.2組合索引177
-7.3.3唯一索引177
-7.3.4表達式索引178
-7.3.5部分索引178
- 7.3.6索引與排序180
7.4索引類型180
-7.4.1 B-tree索引180
-7.4.2 Hash索引181
-7.4.3 GiST索引182
-7.4.4 SP-GiST索引184
-7.4.5 GIN索引185
-7.4.6 BRIN索引186
7.5索引使用案例187
-7.5.1 【實例70】用GiST索引提升RANGE類型數據的查詢性能187
-7.5.2 【實例71】用SP-GiST索引快速查找幾何空間數據189
-7.5.3 【實例72】用GIN索引快速查找JSON數據190
-7.5.4 【實例73】用BRIN索引快速檢索時間類型數據192
7.6小結194
-
第8章視圖195
8.1為什麼使用視圖195
8.2普通視圖195
-8.2.1創建視圖196
-8.2.2使用視圖198
8.3物化視圖201
-8.3.1物化視圖和普通視圖的區別201
-8.3 .2創建和刷新物化視圖201
-8.3.3 【實例74】用物化視圖優化查詢性能203
8.4小結205
-
第9章觸發器206
9.1常規觸發器206
-9.1.1了解常規觸發器206
-9.1. 2 【實例75】創建觸發器208
-9.1.3 【實例76】修改觸發器211
-9.1.4 【實例77】刪除觸發器212
-9.1.5 【實例78】注意數據變更的可見性213
9.2事件觸發器214
-9.2.1事件觸發器支持的事件214
-9.2.2 【實例79】創建一個具有數據庫審計功能的事件觸發器215
9.3小結217

第10章表繼承和表分區218
10.1表繼承218
-10.1.1表繼承的使用場景218
-10.1.2 【實例80】創建表繼承219
-10.1.3 【實例81】使用表繼承221
-10.1.4確定表繼承數據來源228
-10.1.5表繼承的局限性和注意事項230
10.2表分區231
-10.2.1表分區的使用場景231
-10.2.2表分區的實現方式232
-10.2.3 【實例82】創建表分區233
-10.2.4 【實例83】使用表分區235
-10.2.5 【實例84】維護表分區237
-10.2.6使用約束排除提升查詢性能240
-10.2.7表分區注意事項243
10.3小結243

第11章管理用戶權限和模式244
11.1了解用戶、角色和權限244
11.2管理用戶、角色和權限245
-11.2.1 【實例85】創建用戶245
-11.2.2 【實例86】刪除用戶246
-11.2.3 【實例87】創建角色247
-11.2.4 【實例88】刪除角色249
-11.2.5 【實例89】給角色授予權限250
-11.2.6 【實例90】使用系統默認角色251
11.3管理數據庫模式252
-11.3.1 【實例91】使用數據庫模式252
-11.3.2 【實例92】設置模式的搜索路徑253
-11.3.3管理數據庫模式權限254
-11.3.4 pg_catalog模式254
11.4小結255

第12章配置和管理數據庫服務器256
12.1用不同方式進行參數設置256
-12.1.1了解參數格式256
-12.1.2 【實例93】通過配置文件設置參數257
-12.1.3 【實例94】通過SQL命令設置參數259
-12.1.4 【實例95】通過shell命令設置參數261
-12.1.5 【實例96】用include方式管理配置文件262
12.2配置postgresql.conf文件的參數262
-12.2.1連接和安全認證參數263
-12.2.2緩存和存儲參數264
-12.2.3 WAL日誌參數266
-12.2.4錯誤日誌參數267
-12.2.5 AUTOVACUUM參數274
12.3配置pg_hba.conf文件的參數274
-12.3.1了解不同客戶端認證方法275
-12.3.2 【實例97】用trust配置客戶端認證276
-12.3.3 【實例98】用md5配置客戶端認證277
-12.3.4 【實例99】用cert配置客戶端認證277
12.4服務端常用工具277
-12.4.1 【實例100】用initdb初始化數據庫277
-12.4.2 【實例101】用pg_ctl管理數據庫服務278
-12.4.3 【實例102】用pg_controldata查看控制信息280
-12.4.4 【實例103】用pg_rewind同步數據目錄282
12.5小結283

第13章備份和恢復數據284
13.1邏輯備份和恢復284
-13.1.1 【實例104】用pg_dump工具備份單庫單表數據284
-13.1.2 【實例105】用pg_restore工具恢復數據287
- 13.1.3 【實例106】用pg_dumpall工具備份所有庫表數據289
-13.1.4 【實例107】用psql工具恢復pg_dumpall備份的數據289
13.2物理備份和恢復289
-13.2.1 【實例108】用pg_basebackup工具熱備份290
-13.2.2 【實例109】用基礎備份恢復數據292
13.3增量備份和恢復293
-13.3.1 【實例110】用歸檔日誌進行增量備份293
-13.3.2 【實例111】將數據庫狀態恢復到指定時間點294
-13.3.3 【實例112】恢復到指定事務297
13.4第三方備份恢復工具299
-13.4.1認識pg_rman工具299
-13.4.2 【實例113】用pg_rman工具備份數據301
13.5小結304
-
------------------第4篇高階
-
第14章事務306
14.1事務的基本特性306
-14.1.1事務模型要解決的問題306
-14.1.2事務的四大特性307
-14.1.3 【實例114】設置合適的事務隔離級別308
14.2理解事務的實現原理310
-14.2.1 WAL機制310
-14.2.2 MVCC機制311
-14.2.3 Checkpoint機制314
-14.2.4 Crash Recovery機制315
-14.2.5事務並發與鎖機制316
14.3使用事務319
-14.3.1理解事務語法319
-14.3.2 【實例115】用ROLLBACK回滾事務320
-14.3. 3 【實例116】用COMMIT提交事務321
-14.3.4 【實例117】用SAVEPOINT回滾部分事務321
14.4小結323

第15章優化SQL語句324
15.1理解查詢優化器的工作原理324
-15.1.1 SQL語句執行過程324
-15.1.2了解查詢樹325
-15.1.3了解邏輯優化326
-15.1.4邏輯優化：對子查詢進行優化326
-15.1.5邏輯優化：條件表達式優化及等價謂詞重寫優化327
-15.1.6邏輯優化：外連接優化328
-15.1.7了解物理優化329
15.1.8代價估算332
15.2查看並分析執行計劃334
-15.2.1 EXPLAIN命令介紹334
-15.2.2 EXPLAIN輸出解讀336
-15.2.3 【實例118】用EXPLAIN分析執行計劃338
15.3常見SQL語句優化343
-15.3.1 【實例119】用索引避免表掃描343
-15.3.2 【實例120】子查詢優化345
15.3.3 【實例121】表連接優化347
-15.3.4 【實例122】用UNION ALL代替UNION 349
-15.3.5 【實例123】避免使用“SELECT *” 350
-15.3.6 【實例124】為jsonb字段建立合適的索引351
15.4小結352

第16章優化數據庫服務端性能353
16.1了解數據庫性能指標353
-16.1.1衡量數據庫性能的指標353
-16.1.2影響數據庫性能的硬件因素353
-16.1.3 【實例125】用nmon工具監控Linux性能356
16.2優化存儲性能359
-16.2.1物理存儲介質簡介359
-16.2.2存儲路徑隔離原則361
16.3優化數據庫配置參數366
-16.3.1優化內存資源類參數366
-16.3.2優化臟頁刷寫類參數368
-16.3.3優化空間回收類參數369
-16.3.4優化WAL日誌文件相關參數372
-16.3.5數據庫性能視圖376
16.4小結383

第17章PL/pgSQL服務端編程384
17.1 PL/pgSQL編程基礎384
-17.1.1 PL/pgSQL的結構384
-17.1.2 PL/pgSQL的聲明386
-17.1.3 PL/pgSQL的表達式389
-17.1 .4 PL/pgSQL的基本語句389
-17.1.5 PL/pgSQL的控制結構392
-17.1.6 PL/pgSQL的游標399
-17.1.7 PL/pgSQL的錯誤和消息401
-17.1.8 PL/pgSQL的觸發器函數402
17.2 PL/pgSQL編程實戰405
-17.2.1 PL/pgSQL開發建議405
-17.2.2 【實例126】編寫一個自定義用戶函數407
-17.2.3 【實例127】從PL/SQL移植到PL/pgSQL 409
17.3小結411

第18章高可用方案412
18.1如何實現高可用方案412
-18.1.1基本概念412
-18.1.2實現自動故障接管（Failover） 414
-18.1.3 WAL歸檔與流複製415
-18.1.4防止WAL日誌文件被過早刪除418
-18.1.5巧用級聯複製419
-18.1.6同步複製與讀寫分離420
18.2 【實例128】基於Corosync+Pacemaker的方案424
-18.2.1初識Corosync和Pacemaker 424
-18.2.2 Corosync和Pacemaker的安裝426
-18.2.3基於Corosync+Pacemaker的高可用方案及實現430
18.3 【實例129】基於Stolon的方案436
-18.3.1 Stolon方案架構及特性436
-18.3.2 Stolon方案安裝配置及【實例437
18.4小結443