PostgreSQL修煉之道：從小工到專(zhuān)家（第2版）

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