PostgreSQL修煉之道：從小工到專家（第2版）

前言
第一篇　準備篇
第1章　PostgreSQL簡介 2
1.1　什么是PostgreSQL 2
1.1.1　PostgreSQL的發(fā)展歷史 2
1.1.2　PostgreSQL數(shù)據(jù)庫的優(yōu)勢 4
1.1.3　PostgreSQL應用現(xiàn)狀和發(fā)展趨勢 5
1.2　PostgreSQL數(shù)據(jù)庫與其他數(shù)據(jù)庫的對比 6
1.2.1　PostgreSQL與MySQL數(shù)據(jù)庫的對比 6
1.2.2　PostgreSQL與Oracle數(shù)據(jù)庫的對比 8
1.3　小結 8
第2章　PostgreSQL的安裝與配置 9
2.1　從發(fā)行版本安裝 9
2.1.1　Red Hat/CentOS下的安裝方法 10
2.1.2　Windows下的安裝方法 12
2.1.3　從發(fā)行版本安裝總結 18
2.2　從源碼安裝 18
2.2.1　編譯安裝過程簡介 18
2.2.2　下載源代碼 18
2.2.3　編譯及安裝 20
2.2.4　PostgreSQL的配置 22
2.2.5　創(chuàng)建數(shù)據(jù)庫實例 23
2.2.6　安裝contrib目錄下的工具 23
2.2.7　啟動和停止數(shù)據(jù)庫 23
2.2.8　編譯安裝過程中的常見問題及解決方法 24
2.3　PostgreSQL的簡單配置 25
2.3.1　pg_hba.conf的配置 26
2.3.2　修改監(jiān)聽的IP和端口 26
2.3.3　數(shù)據(jù)庫日志相關參數(shù) 26
2.3.4　內存參數(shù)的設置 27
2.4　PostgreSQL的安裝技巧 27
2.4.1　不想把數(shù)據(jù)庫實例創(chuàng)建到“/var/lib/pgsql”目錄下 27
2.4.2　如何使用較大的數(shù)據(jù)塊提高I/O性能 29
2.4.3　打開數(shù)據(jù)塊的checksum功能 29
2.5　小結 30
第3章　SQL入門 31
3.1　SQL語句語法簡介 31
3.1.1　語句的分類 31
3.1.2　詞法結構 31
3.2　DDL語句 32
3.2.1　建表語句 32
3.2.2　刪除表語句 34
3.3　DML語句 34
3.3.1　插入語句 34
3.3.2　更新語句 35
3.3.3　刪除語句 35
3.4　查詢語句 36
3.4.1　單表查詢語句 36
3.4.2　過濾條件的查詢 36
3.4.3　排序 37
3.4.4　分組查詢 38
3.4.5　多表關聯(lián)查詢 38
3.4.6　子查詢 40
3.5　其他SQL語句 42
3.5.1　INSERT ... SELECT語句 42
3.5.2　UNION語句 42
3.5.3　TRUNCATE TABLE語句 43
3.6　小結 43
第二篇　基礎篇
第4章　psql工具 46
4.1　psql介紹 46
4.2　psql的簡單使用 46
4.3　psql的常用命令 48
4.3.1　“\\h”命令 49
4.3.2　“\\d”命令 49
4.3.3　指定客戶端字符集的命令 52
4.3.4　格式化輸出的\\pset命令 52
4.3.5　“\\x”命令 55
4.3.6　執(zhí)行存儲在外部文件中的SQL命令 56
4.3.7　編輯命令 56
4.3.8　輸出信息的“\\echo”命令 57
4.3.9　其他命令 58
4.4　psql的使用技巧 59
4.4.1　歷史命令與補全功能 59
4.4.2　自動提交技巧 59
4.4.3　如何得到psql中快捷命令執(zhí)行的實際SQL 60
4.5　小結 62
第5章　數(shù)據(jù)類型 63
5.1　數(shù)據(jù)類型介紹 63
5.1.1　數(shù)據(jù)類型的分類 63
5.1.2　數(shù)據(jù)類型的輸入與轉換 65
5.2　布爾類型 65
5.2.1　布爾類型介紹 65
5.2.2　布爾類型的操作符 67
5.3　數(shù)值類型 68
5.3.1　數(shù)值類型介紹 68
5.3.2　整數(shù)類型 68
5.3.3　精確的小數(shù)類型 69
5.3.4　浮點數(shù)類型 70
5.3.5　序列類型 70
5.3.6　貨幣類型 70
5.3.7　數(shù)學函數(shù)和操作符 71
5.4　字符串類型 73
5.4.1　字符串類型介紹 73
5.4.2　字符串函數(shù)和操作符 73
5.5　二進制數(shù)據(jù)類型 76
5.5.1　二進制數(shù)據(jù)類型介紹 76
5.5.2　二進制數(shù)據(jù)類型轉義表示 76
5.5.3　二進制數(shù)據(jù)類型的函數(shù) 77
5.6　位串類型 78
5.6.1　位串類型介紹 78
5.6.2　位串類型的使用方法 78
5.6.3　位串的操作符及函數(shù) 79
5.7　日期/時間類型 80
5.7.1　日期/時間類型介紹 80
5.7.2　日期輸入 81
5.7.3　時間輸入 82
5.7.4　特殊值 84
5.7.5　函數(shù)和操作符列表 84
5.7.6　時間函數(shù) 85
5.7.7　extract和date_part函數(shù) 89
5.8　枚舉類型 90
5.8.1　枚舉類型的使用 90
5.8.2　枚舉類型說明 91
5.8.3　枚舉類型的函數(shù) 92
5.9　幾何類型 92
5.9.1　幾何類型概況 93
5.9.2　幾何類型的輸入 93
5.9.3　幾何類型的操作符 98
5.9.4　幾何類型的函數(shù) 106
5.10　網(wǎng)絡地址類型 107
5.10.1　網(wǎng)絡地址類型概況 107
5.10.2　inet與cidr類型 107
5.10.3　macaddr類型 110
5.10.4　網(wǎng)絡地址類型的操作符 110
5.10.5　網(wǎng)絡地址類型的函數(shù) 111
5.11　復合類型 112
5.11.1　復合類型的定義 112
5.11.2　復合類型的輸入 113
5.11.3　訪問復合類型 114
5.11.4　修改復合類型 114
5.11.5　復合類型的輸入輸出 115
5.12　xml類型 116
5.12.1　xml類型的輸入 116
5.12.2　字符集的問題 117
5.12.3　xml類型函數(shù) 118
5.13　JSON類型 124
5.13.1　JSON類型簡介 124
5.13.2　JSON類型的輸入與輸出 125
5.13.3　JSON類型的操作符 126
5.13.4　JSON類型的函數(shù) 127
5.13.5　JSON類型的索引 132
5.14　Range類型 136
5.14.1　Range類型簡介 136
5.14.2　創(chuàng)建Range類型 138
5.14.3　Range類型的輸入與輸出 138
5.14.4　Range類型的操作符 141
5.14.5　Range類型的函數(shù) 142
5.14.6　Range類型的索引和約束 142
5.15　數(shù)組類型 143
5.15.1　數(shù)組類型的聲明 143
5.15.2　如何輸入數(shù)組值 144
5.15.3　訪問數(shù)組 146
5.15.4　修改數(shù)組 148
5.15.5　數(shù)組的操作符 149
5.15.6　數(shù)組的函數(shù) 151
5.16　偽類型 153
5.17　其他類型 154
5.17.1　UUID類型 154
5.17.2　pg_lsn 類型 154
5.18　小結 155
第6章　邏輯結構管理 156
6.1　數(shù)據(jù)庫邏輯結構介紹 156
6.2　數(shù)據(jù)庫基本操作 156
6.2.1　創(chuàng)建數(shù)據(jù)庫 156
6.2.2　修改數(shù)據(jù)庫 157
6.2.3　刪除數(shù)據(jù)庫 158
6.2.4　常見問題及解答 158
6.3　模式 159
6.3.1　什么是模式 159
6.3.2　模式的使用 159
6.3.3　公共模式 161
6.3.4　模式的搜索路徑 161
6.3.5　模式的權限 162
6.3.6　模式的可移植性 162
6.4　表 163
6.4.1　創(chuàng)建表 163
6.4.2　表的存儲屬性 165
6.4.3　臨時表 167
6.4.4　UNLOGGED表 170
6.4.5　默認值 170
6.4.6　約束 171
6.4.7　修改表 175
6.4.8　表繼承 179
6.4.9　通過表繼承實現(xiàn)分區(qū)表 181
6.4.10　聲明式分區(qū) 186
6.5　觸發(fā)器 188
6.5.1　創(chuàng)建觸發(fā)器 189
6.5.2　語句級觸發(fā)器與行級觸發(fā)器 190
6.5.3　BEFORE觸發(fā)器與AFTER觸發(fā)器 193
6.5.4　刪除觸發(fā)器 194
6.5.5　觸發(fā)器的行為 194
6.5.6　觸發(fā)器函數(shù)中的特殊變量 195
6.6　事件觸發(fā)器 196
6.6.1　創(chuàng)建事件觸發(fā)器 198
6.6.2　修改事件觸發(fā)器 201
6.7　表空間 201
6.7.1　什么是表空間 201
6.7.2　表空間的使用方法 201
6.8　視圖 202
6.8.1　什么是視圖 202
6.8.2　創(chuàng)建視圖 203
6.8.3　可更新視圖 204
6.9　索引 207
6.9.1　索引簡介 207
6.9.2　索引的分類 207
6.9.3　創(chuàng)建索引 208
6.9.4　并發(fā)創(chuàng)建索引 209
6.9.5　修改索引 211
6.9.6　刪除索引 212
6.10　用戶及權限管理 213
6.10.1　用戶和角色 213
6.10.2　創(chuàng)建用戶和角色 213
6.10.3　權限的管理 214
6.10.4　函數(shù)和觸發(fā)器的權限 219
6.10.5　權限的總結 220
6.10.6　權限的示例 220
6.11　事務、并發(fā)和鎖 220
6.11.1　什么是ACID 221
6.11.2　DDL事務 221
6.11.3　事務的使用方法 221
6.11.4　SAVEPOINT 222
6.11.5　事務隔離級別 224
6.11.6　兩階段提交 225
6.11.7　鎖機制 226
6.11.8　死鎖及防范 230
6.11.9　表級鎖命令LOCK TABLE 230
6.11.10　行級鎖命令 231
6.11.11　鎖的查看 231
6.12　小結 238
第7章　PostgreSQL的核心架構 239
7.1　進程及內存結構 239
7.1.1　進程和內存架構圖 239
7.1.2　主進程Postmaster介紹 240
7.1.3　Logger系統(tǒng)日志進程介紹 241
7.1.4　BgWriter后臺寫進程介紹 241
7.1.5　WalWriter預寫式日志寫進程介紹 241
7.1.6　PgArch歸檔進程 242
7.1.7　AutoVacuum自動清理進程 242
7.1.8　PgStat統(tǒng)計數(shù)據(jù)收集進程 242
7.1.9　共享內存 242
7.1.10　本地內存 243
7.2　存儲結構 243
7.2.1　邏輯存儲結構 243
7.2.2　軟件目錄結構 244
7.2.3　物理存儲結構 244
7.2.4　表空間的目錄 246
7.3　應用程序訪問接口 247
7.3.1　訪問接口總體圖 247
7.3.2　不同編程語言的PostgreSQL驅動介紹 248
7.4　小結 249
第8章　服務管理 250
8.1　服務的啟停及原理 250
8.1.1　服務的啟停方法 250
8.1.2　pg_ctl工具 251
8.1.3　信號 255
8.1.4　postgres及單用戶模式 255
8.2　服務配置介紹 256
8.2.1　配置參數(shù) 256
8.2.2　連接配置項 259
8.2.3　內存配置項 261
8.2.4　預寫式日志的配置項 262
8.2.5　錯誤報告和日志項 264
8.3　訪問控制配置文件 268
8.3.1　pg_hba.conf 文件 268
8.3.2　認證方法介紹 269
8.3.3　認證方法實戰(zhàn) 270
8.4　備份和還原 271
8.4.1　邏輯備份 271
8.4.2　pg_dump命令 272
8.4.3　pg_restore命令 275
8.4.4　pg_dump和pg_restore應用示例 278
8.4.5　物理備份 279
8.4.6　使用LVM快照進行熱備份 279
8.5　常用的管理命令 282
8.5.1　查看系統(tǒng)信息的常用命令 282
8.5.2　系統(tǒng)維護常用命令 288
8.6　小結 289
第三篇　提高篇
第9章　PostgreSQL執(zhí)行計劃 292
9.1　執(zhí)行計劃的解釋 292
9.1.1　EXPLAIN命令 292
9.1.2　EXPLAIN輸出結果解釋 293
9.1.3　EXPLAIN使用示例 294
9.1.4　全表掃描 297
9.1.5　索引掃描 297
9.1.6　位圖掃描 297
9.1.7　條件過濾 298
9.1.8　嵌套循環(huán)連接 299
9.1.9　散列連接 299
9.1.10　合并連接 300
9.2　與執(zhí)行計劃相關的配置項 301
9.2.1　ENABLE_*參數(shù) 301
9.2.2　COST基準值參數(shù) 301
9.2.3　基因查詢優(yōu)化的參數(shù) 302
9.2.4　其他執(zhí)行計劃配置項 303
9.3　統(tǒng)計信息的收集 304
9.3.1　統(tǒng)計信息收集器的配置項 304
9.3.2　SQL執(zhí)行的統(tǒng)計信息輸出 304
9.3.3　手動收集統(tǒng)計信息 305
9.4　小結 306
第10章　PostgreSQL中的技術內幕 307
10.1　表中的系統(tǒng)字段 307
10.1.1　oid 308
10.1.2　ctid 310
10.1.3　xmin、xmax、cmin、cmax 311
10.2　多版本并發(fā)控制 312
10.2.1　多版本并發(fā)控制的原理 312
10.2.2　Postg