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

目錄

Contents 目錄
序言
前言
基　礎(chǔ)　篇
第1章　安裝與配置基礎(chǔ)2
1.1　初識(shí)PostgreSQL2
1.1.1　PostgreSQL的特點(diǎn)3
1.1.2　許可3
1.1.3　郵件列表和討論區(qū)3
1.2　安裝PostgreSQL3
1.2.1　通過(guò)yum源安裝4
1.2.2　通過(guò)源碼編譯安裝5
1.2.3　設(shè)置一個(gè)軟鏈接7
1.3　客戶(hù)端程序和服務(wù)器程序8
1.3.1　客戶(hù)端程序8
1.3.2　服務(wù)器程序11
1.4　創(chuàng)建數(shù)據(jù)庫(kù)實(shí)例11
1.4.1　創(chuàng)建操作系統(tǒng)用戶(hù)11
1.4.2　創(chuàng)建數(shù)據(jù)目錄12
1.4.3　初始化數(shù)據(jù)目錄13
1.5　啟動(dòng)和停止數(shù)據(jù)庫(kù)服務(wù)器14
1.5.1　使用service方式15
1.5.2　使用pg_ctl進(jìn)行管理15
1.5.3　其他啟動(dòng)和關(guān)閉數(shù)據(jù)庫(kù)服務(wù)器的方式16
1.5.4　配置開(kāi)機(jī)啟動(dòng)16
1.6　數(shù)據(jù)庫(kù)配置基礎(chǔ)17
1.6.1　配置文件的位置17
1.6.2　pg_hba.conf17
1.6.3　postgresql.conf19
1.6.4　允許遠(yuǎn)程訪問(wèn)數(shù)據(jù)庫(kù)20
1.7　本章小結(jié)22
第2章　客戶(hù)端工具23
2.1　pgAdmin 4簡(jiǎn)介23
2.1.1　pgAdmin 4 安裝23
2.1.2　pgAdmin 4 使用23
2.2　psql功能及應(yīng)用26
2.2.1　使用psql連接數(shù)據(jù)庫(kù)26
2.2.2　psql元命令介紹28
2.2.3　psql導(dǎo)入、導(dǎo)出表數(shù)據(jù)31
2.2.4　psql的語(yǔ)法和選項(xiàng)介紹34
2.2.5　psql執(zhí)行sql腳本36
2.2.6　psql如何傳遞變量到SQL37
2.2.7　使用psql定制日常維護(hù)腳本38
2.2.8　psql亮點(diǎn)功能39
2.3　本章小結(jié)43
第3章　數(shù)據(jù)類(lèi)型44
3.1　數(shù)字類(lèi)型44
3.1.1　數(shù)字類(lèi)型列表44
3.1.2　數(shù)字類(lèi)型操作符和數(shù)學(xué)函數(shù)46
3.2　字符類(lèi)型47
3.2.1　字符類(lèi)型列表47
3.2.2　字符類(lèi)型函數(shù)48
3.3　時(shí)間/日期類(lèi)型49
3.3.1　時(shí)間/日期類(lèi)型列表49
3.3.2　時(shí)間/日期類(lèi)型操作符50
3.3.3　時(shí)間/日期類(lèi)型常用函數(shù)51
3.4　布爾類(lèi)型52
3.5　網(wǎng)絡(luò)地址類(lèi)型53
3.5.1　網(wǎng)絡(luò)地址類(lèi)型列表54
3.5.2　網(wǎng)絡(luò)地址操作符55
3.5.3　網(wǎng)絡(luò)地址函數(shù)56
3.6　數(shù)組類(lèi)型56
3.6.1　數(shù)組類(lèi)型定義56
3.6.2　數(shù)組類(lèi)型值輸入56
3.6.3　查詢(xún)數(shù)組元素57
3.6.4　數(shù)組元素的追加、刪除、更新58
3.6.5　數(shù)組操作符58
3.6.6　數(shù)組函數(shù)59
3.7　范圍類(lèi)型60
3.7.1　范圍類(lèi)型列表60
3.7.2　范圍類(lèi)型邊界61
3.7.3　范圍類(lèi)型操作符62
3.7.4　范圍類(lèi)型函數(shù)62
3.7.5　給范圍類(lèi)型創(chuàng)建索引63
3.8　json/jsonb類(lèi)型63
3.8.1　json類(lèi)型簡(jiǎn)介63
3.8.2　查詢(xún)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ù)類(lèi)型轉(zhuǎn)換68
3.9.1　通過(guò)格式化函數(shù)進(jìn)行轉(zhuǎn)換68
3.9.2　通過(guò)CAST函數(shù)進(jìn)行轉(zhuǎn)換68
3.9.3　通過(guò)::操作符進(jìn)行轉(zhuǎn)換69
3.10　本章小結(jié)70
第4章　SQL高級(jí)特性71
4.1　WITH查詢(xún)71
4.1.1　復(fù)雜查詢(xún)使用CTE71
4.1.2　遞歸查詢(xún)使用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場(chǎng)景演示78
4.4.2　UPSERT語(yǔ)法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ù)語(yǔ)法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　邏輯和物理存儲(chǔ)結(jié)構(gòu)96
5.1.1　邏輯存儲(chǔ)結(jié)構(gòu)96
5.1.2　物理存儲(chǔ)結(jié)構(gòu)97
5.2　進(jìn)程結(jié)構(gòu)105
5.2.1　守護(hù)進(jìn)程與服務(wù)進(jìn)程105
5.2.2　輔助進(jìn)程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章　并行查詢(xún)109
6.1　并行查詢(xún)相關(guān)配置參數(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　多表關(guān)聯(lián)119
6.4.1　Nested loop多表關(guān)聯(lián)120
6.4.2　Merge join多表關(guān)聯(lián)121
6.4.3　Hash join多表關(guān)聯(lián)122
6.5　本章小結(jié)124
第7章　事務(wù)與并發(fā)控制125
7.1　事務(wù)和并發(fā)控制的概念125
7.1.1　事務(wù)的基本概念和性質(zhì)125
7.1.2　并發(fā)引發(fā)的現(xiàn)象126
7.1.3　ANSI SQL標(biāo)準(zhǔn)的事務(wù)隔離級(jí)別129
7.2　PostgreSQL的事務(wù)隔離級(jí)別130
7.2.1　查看和設(shè)置數(shù)據(jù)庫(kù)的事務(wù)隔離級(jí)別131
7.2.2　修改全局的事務(wù)隔離級(jí)別132
7.2.3　查看當(dāng)前會(huì)話(huà)的事務(wù)隔離級(jí)別132
7.2.4　設(shè)置當(dāng)前會(huì)話(huà)的事務(wù)隔離級(jí)別132
7.2.5　設(shè)置當(dāng)前事務(wù)的事務(wù)隔離級(jí)別133
7.3　PostgreSQL的并發(fā)控制133
7.3.1　基于鎖的并發(fā)控制134
7.3.2　基于多版本的并發(fā)控制134
7.3.3　通過(guò)pageinspect觀察MVCC137
7.3.4　使用pg_repack解決表膨脹問(wèn)題140
7.3.5　支持事務(wù)的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　查詢(xún)父表還是子表147
8.2.5　constraint_exclusion參數(shù)148
8.2.6　添加分區(qū)150
8.2.7　刪除分區(qū)150
8.2.8　分區(qū)表相關(guān)查詢(xún)151
8.2.9　性能測(cè)試152
8.2.10　傳統(tǒng)分區(qū)表注意事項(xiàng)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　性能測(cè)試159
8.3.7　constraint_exclusion參數(shù)161
8.3.8　更新分區(qū)數(shù)據(jù)162
8.3.9　內(nèi)置分區(qū)表注意事項(xiàng)162
8.4　本章小結(jié)163
第9章　PostgreSQL的NoSQL特性164
9.1　為jsonb類(lèi)型創(chuàng)建索引164
9.2　json、jsonb讀寫(xiě)性能測(cè)試165
9.2.1　創(chuàng)建json、jsonb測(cè)試表165
9.2.2　json、jsonb表寫(xiě)性能測(cè)試166
9.2.3　json、jsonb表讀性能測(cè)試166
9.3　全文檢索對(duì)json和jsonb數(shù)據(jù)類(lèi)型的支持169
9.3.1　PostgreSQL全文檢索簡(jiǎn)介170
9.3.2　json、jsonb全文檢索實(shí)踐173
9.4　本章小結(jié)176
進(jìn)　階　篇
第10章　性能優(yōu)化180
10.1　服務(wù)器硬件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　預(yù)讀參數(shù)調(diào)整190
10.2.4　內(nèi)存的優(yōu)化190
10.3　數(shù)據(jù)庫(kù)調(diào)優(yōu)193
10.3.1　全局參數(shù)調(diào)整193
10.3.2　統(tǒng)計(jì)信息和查詢(xún)計(jì)劃194
10.3.3　索引管理與維護(hù)201
10.4　本章小結(jié)203
第11章　基準(zhǔn)測(cè)試與pgbench204
11.1　關(guān)于基準(zhǔn)測(cè)試204
11.1.1　基準(zhǔn)測(cè)試的常見(jiàn)使用場(chǎng)景205
11.1.2　基準(zhǔn)測(cè)試衡量指標(biāo)205
11.1.3　基準(zhǔn)測(cè)試的原則205
11.2　使用pgbench進(jìn)行測(cè)試206
11.2.1　pgbench的測(cè)試結(jié)果報(bào)告206
11.2.2　通過(guò)內(nèi)置腳本進(jìn)行測(cè)試207
11.2.3　使用自定義腳本進(jìn)行測(cè)試210
11.2.4　其他選項(xiàng)212
11.3　本章小結(jié)214
第12章　物理復(fù)制和邏輯復(fù)制215
12.1　異步流復(fù)制216
12.1.1　以拷貝數(shù)據(jù)文件方式部署流復(fù)制216
12.1.2　以pg_basebackup方式部署流復(fù)制222
12.1.3　查看流復(fù)制同步方式223
12.2　同步流復(fù)制224
12.2.1　synchronous_commit參數(shù)詳解224
12.2.2　配置同步流復(fù)制225
12.2.3　同步流復(fù)制的典型“陷阱”226
12.3　單實(shí)例、異步流復(fù)制、同步流復(fù)制性能測(cè)試227
12.3.1　讀性能測(cè)試228
12.3.2　寫(xiě)性能測(cè)試230
12.4　流復(fù)制監(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　相關(guān)系統(tǒng)函數(shù)235
12.5　流復(fù)制主備切換236
12.5.1　判斷主備角色的五種方法236
12.5.2　主備切換之文件觸發(fā)方式238
12.5.3　主備切換之pg_ctl promote方式240
12.5.4　pg_rewind241
12.6　延遲備庫(kù)244
12.6.1　延遲備庫(kù)的意義244
12.6.2　延遲備庫(kù)部署244
12.6.3　recovery_min_apply_delay參數(shù)對(duì)同步復(fù)制的影響246
12.7　同步復(fù)制優(yōu)選提交247
12.7.1　synchronous_standby_names參數(shù)詳解248
12.7.2　基于優(yōu)先級(jí)的同步備庫(kù)249
12.7.3　基于Quorum的同步備庫(kù)250
12.8　級(jí)聯(lián)復(fù)制251
12.8.1　級(jí)聯(lián)復(fù)制物理架構(gòu)252
12.8.2　級(jí)聯(lián)復(fù)制部署253
12.9　流復(fù)制維護(hù)生產(chǎn)案例255
12.9.1　案例一：主庫(kù)上創(chuàng)建表空間時(shí)備庫(kù)宕機(jī)255
12.9.2　案例二：備庫(kù)查詢(xún)被中止257
12.9.3　案例三：主庫(kù)上的WAL被覆蓋導(dǎo)致備庫(kù)不可用259
12.10　邏輯復(fù)制265
12.10.1　邏輯解析265
12.10.2　邏輯復(fù)制架構(gòu)267
12.10.3　邏輯復(fù)制部署268
12.10.4　邏輯復(fù)制DML數(shù)據(jù)驗(yàn)證273
12.10.5　邏輯復(fù)制添加表、刪除表274
12.10.6　邏輯復(fù)制啟動(dòng)、停止276
12.10.7　邏輯復(fù)制配置注意事項(xiàng)和限制277
12.10.8　邏輯復(fù)制延遲測(cè)試278
12.11　本章小結(jié)280
第13章　備份與恢復(fù)281
13.1　備份與恢復(fù)概述281
13.2　增量備份283
13.2.1　開(kāi)啟WAL歸檔284
13.2.2　創(chuàng)建基礎(chǔ)備份285
13.3　指定時(shí)間和還原點(diǎn)的恢復(fù)288
13.3.1　恢復(fù)到最近時(shí)間點(diǎn)289
13.3.2　恢復(fù)到指定時(shí)間點(diǎn)291
13.3.3　恢復(fù)到指定還原點(diǎn)292
13.3.4　恢復(fù)到指定事務(wù)294
13.3.5　恢復(fù)到指定時(shí)間線(xiàn)296
13.4　SQL轉(zhuǎn)儲(chǔ)和文件系統(tǒng)級(jí)別的備份298
13.4.1　SQL轉(zhuǎn)儲(chǔ)298
13.4.2　文件系統(tǒng)級(jí)別的備份301
13.5　本章小結(jié)301
第14章　高可用302
14.1　Pgpool-II+異步流復(fù)制實(shí)現(xiàn)高可用303
14.1.1　pgpool部署架構(gòu)圖304
14.1.2　pgpool部署305
14.1.3　PCP管理接口配置312
14.1.4　pgpool方案高可用測(cè)試313
14.1.5　pgpool方案常見(jiàn)錯(cuò)誤處理318
14.2　基于Keepalived+異步流復(fù)制實(shí)現(xiàn)高可用321
14.2.1　Keepalived+異步流復(fù)制部署架構(gòu)圖321
14.2.2　Keepalived+異步流復(fù)制高可用方案部署322
14.2.3　Keepalived配置324
14.2.4　Keepalived方案高可用測(cè)試329
14.3　本章小結(jié)333
第15章　版本升級(jí)334
15.1　版本介紹334
15.2　小版本升級(jí)335
15.3　大版本升級(jí)336
15.3.1　通過(guò)pg_dumpall進(jìn)行大版本升級(jí)336
15.3.2　通過(guò)pg_upgrade進(jìn)行大版本升級(jí)339
15.3.3　使用pglogical升級(jí)大版本346
15.4　本章小結(jié)350
第16章　擴(kuò)展模塊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簡(jiǎn)介359
16.5.2　file_fdw部署360
16.5.3　使用file_fdw分析數(shù)據(jù)庫(kù)日志362
16.6　postgres_fdw364
16.6.1　postgres_fdw部署364
16.6.2　postgres_fdw外部表支持寫(xiě)操作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ù)庫(kù)遷移PostgreSQL實(shí)踐378
17.1　項(xiàng)目準(zhǔn)備378
17.2　數(shù)據(jù)庫(kù)對(duì)象遷移379
17.3　應(yīng)用代碼改造380
17.4　數(shù)據(jù)遷移測(cè)試384
17.5　功能測(cè)試和性能測(cè)試388
17.6　生產(chǎn)割接389
17.7　oracle_fdw部署過(guò)程中的常見(jiàn)錯(cuò)誤389
17.8　本章小結(jié)391
第18章　PostGIS392
18.1　安裝與配置392
18.2　創(chuàng)建GIS數(shù)據(jù)庫(kù)393
18.3　幾何對(duì)象393
18.3.1　幾何對(duì)象的輸入394
18.3.2　幾何對(duì)象的存儲(chǔ)394
18.3.3　幾何對(duì)象的輸出395
18.3.4　幾何對(duì)象的運(yùn)算395
18.4　應(yīng)用場(chǎng)景：圈人與地理圍欄397
18.4.1　空間索引398
18.4.2　地理圍欄399
18.5　本章小結(jié)399