PostgreSQL數(shù)據(jù)庫實(shí)戰(zhàn)派

目錄

=
第1章 PostgreSQL基礎(chǔ) 1
1.1 PostgreSQL簡介 1
1.1.1 什么是PostgreSQL 1
1.1.2 PostgreSQL的功能特性 2
1.2 PostgreSQL的體系架構(gòu) 3
1.2.1 邏輯存儲(chǔ)結(jié)構(gòu) 4
1.2.2 物理存儲(chǔ)結(jié)構(gòu) 9
1.2.3 進(jìn)程結(jié)構(gòu) 17
1.2.4 內(nèi)存結(jié)構(gòu) 23
=
第2章 安裝與配置PostgreSQL 25
2.1 安裝CentOS操作系統(tǒng) 25
2.2 安裝PostgreSQL服務(wù)器 35
2.3 【實(shí)戰(zhàn)】使用PostgreSQL客戶端工具 37
2.3.1 命令行客戶端psql 38
2.3.2 圖形化客戶端pgAdmin 4 41
2.4 PostgreSQL服務(wù)器端的主配置文件詳解 45
2.4.1 基礎(chǔ)文件 46
2.4.2 連接和認(rèn)證 46
2.4.3 資源使用 47
2.4.4 預(yù)寫日志 49
2.4.5 復(fù)制 50
2.4.6 查詢調(diào)優(yōu) 51
2.4.7 錯(cuò)誤報(bào)告和日志 53
2.4.8 進(jìn)程標(biāo)題 54
2.4.9 運(yùn)行時(shí)統(tǒng)計(jì)數(shù)據(jù) 55
2.4.10 自動(dòng)清理 55
2.4.11 客戶端連接默認(rèn)值 56
2.4.12 鎖管理 57
2.4.13 版本和平臺(tái)兼容性 58
2.4.14 錯(cuò)誤處理 58
2.4.15 預(yù)置選項(xiàng) 59
=
第3章 管理數(shù)據(jù)庫與數(shù)據(jù)庫實(shí)例 60
3.1 【實(shí)戰(zhàn)】管理數(shù)據(jù)庫和數(shù)據(jù)庫模板 60
3.1.1 管理數(shù)據(jù)庫的基本操作 60
3.1.2 管理數(shù)據(jù)庫模板 64
3.2 【實(shí)戰(zhàn)】管理數(shù)據(jù)庫的擴(kuò)展 65
3.2.1 使用擴(kuò)展訪問外部數(shù)據(jù)源 65
3.2.2 數(shù)據(jù)預(yù)熱擴(kuò)展pg_prewarm 75
3.2.3 共享緩沖區(qū)監(jiān)控?cái)U(kuò)展pg_buffercache 79
3.2.4 預(yù)寫日志解析擴(kuò)展pg_walinspect 81
3.3 管理數(shù)據(jù)庫實(shí)例 83
3.4 基于PostgreSQL的分布式數(shù)據(jù)庫——Citus 85
3.4.1 Citus基礎(chǔ) 85
3.4.2 安裝與配置Citus 86
3.4.3 Citus中表的類型 91
3.4.4 Citus的配置參數(shù) 96
3.5 基于PostgreSQL的分布式數(shù)據(jù)庫——Greenplum 96
3.5.1 Greenplum簡介 96
3.5.2 安裝與配置Greenplum 98
3.5.3 Greenplum中表的類型 101
=
第4章 管理數(shù)據(jù)庫對(duì)象 105
4.1 數(shù)據(jù)庫與模式 105
4.2 創(chuàng)建與管理表 107
4.2.1 PostgreSQL的數(shù)據(jù)類型 107
4.2.2 【實(shí)戰(zhàn)】PostgreSQL表的基本操作 110
4.2.3 數(shù)據(jù)的約束條件 111
4.2.4 表中的碎片 115
4.2.5 統(tǒng)計(jì)信息 118
4.2.5.1 查看數(shù)據(jù)庫的統(tǒng)計(jì)信息 118
4.2.5.2 使用analyze命令手動(dòng)收集數(shù)據(jù)庫的統(tǒng)計(jì)信息 120
4.2.5.3 了解統(tǒng)計(jì)信息收集器的配置項(xiàng) 121
4.2.6 【實(shí)戰(zhàn)】使用PostgreSQL的臨時(shí)表 122
4.3 在查詢時(shí)使用索引 123
4.3.1 索引的基礎(chǔ)知識(shí) 123
4.3.2 索引的類型 127
4.3.3 【實(shí)戰(zhàn)】索引的維護(hù) 130
4.3.3.1 使用僅索引掃描 130
4.3.3.2 避免重復(fù)索引 131
4.3.3.3 清除未使用的索引 132
4.4 使用視圖簡化查詢語句 133
4.4.1 什么是視圖 133
4.4.2 視圖的基本操作 133
4.4.3 【實(shí)戰(zhàn)】在PostgreSQL中使用臨時(shí)視圖 135
4.4.4 物化視圖 136
4.5 序列 138
=
第5章 并行查詢 141
5.1 并行查詢是如何工作的 141
5.2 何時(shí)會(huì)用到并行查詢 142
5.3 【實(shí)戰(zhàn)】查看并行查詢的執(zhí)行計(jì)劃 143
5.3.1 并行掃描 144
5.3.2 并行連接 146
5.4 并行查詢的限制 151
=
第6章 事務(wù)與并發(fā)控制 152
6.1 PostgreSQL的事務(wù) 152
6.1.1 事務(wù)簡介 152
6.1.2 控制事務(wù) 154
6.1.2.1 事務(wù)的控制語句 154
6.1.2.2 【實(shí)戰(zhàn)】使用事務(wù)的控制語句 154
6.1.3 事務(wù)的并發(fā) 157
6.1.3.1 事務(wù)隔離級(jí)別 157
6.1.3.2 【實(shí)戰(zhàn)】事務(wù)的臟讀 158
6.1.3.3 【實(shí)戰(zhàn)】事務(wù)的不可重復(fù)讀 159
6.2 PostgreSQL的鎖 161
6.2.1 鎖的類型 162
6.2.2 死鎖 168
6.2.2.1 【實(shí)戰(zhàn)】模擬死鎖的產(chǎn)生 169
6.2.2.2 如何避免死鎖 171
=
第7章 應(yīng)用程序開發(fā) 173
7.1 PL/pgSQL基礎(chǔ) 173
7.1.1 【實(shí)戰(zhàn)】開發(fā)第一個(gè)PL/pgSQL程序 174
7.1.2 使用PL/pgSQL的基本數(shù)據(jù)類型 175
7.1.3 使用PL/pgSQL的高級(jí)數(shù)據(jù)類型 176
7.2 PL/pgSQL面向過程編程 177
7.2.1 在PL/pgSQL程序中使用條件判斷 177
7.2.2 在PL/pgSQL程序中使用循環(huán) 178
7.2.3 在PL/pgSQL程序中使用游標(biāo) 180
7.2.4 在PL/pgSQL程序中處理例外 182
7.2.4.1 處理系統(tǒng)預(yù)定義例外 182
7.2.4.2 處理用戶自定義例外 183
7.3 【實(shí)戰(zhàn)】綜合案例——基于員工表統(tǒng)計(jì)各工資段的
員工人數(shù)及各部門的工資總額 184
7.4 使用PL/pgSQL開發(fā)存儲(chǔ)過程與存儲(chǔ)函數(shù) 187
7.4.1 存儲(chǔ)過程與存儲(chǔ)函數(shù) 187
7.4.2 【實(shí)戰(zhàn)】創(chuàng)建和使用存儲(chǔ)過程 187
7.4.3 【實(shí)戰(zhàn)】創(chuàng)建和使用存儲(chǔ)函數(shù) 189
7.4.4 【實(shí)戰(zhàn)】設(shè)置存儲(chǔ)過程中的inout參數(shù) 189
7.4.5 【實(shí)戰(zhàn)】在inout參數(shù)中使用游標(biāo) 191
7.5 使用PL/pgSQL開發(fā)觸發(fā)器 192
7.5.1 常規(guī)觸發(fā)器 192
7.5.1.1 常規(guī)觸發(fā)器的定義 192
7.5.1.2 【實(shí)戰(zhàn)】利用常規(guī)觸發(fā)器實(shí)現(xiàn)安全性檢查 193
7.5.1.3 【實(shí)戰(zhàn)】利用常規(guī)觸發(fā)器進(jìn)行數(shù)據(jù)確認(rèn) 194
7.5.1.4 【實(shí)戰(zhàn)】利用常規(guī)觸發(fā)器實(shí)現(xiàn)審計(jì) 195
7.5.2 事件觸發(fā)器 197
=
第8章 管理數(shù)據(jù)庫安全 199
8.1 用戶管理 199
8.1.1 用戶與角色 199
8.1.2 管理用戶的密碼 204
8.1.2.1 使用SQL語句管理用戶的密碼 205
8.1.2.2 使用passwordcheck插件檢查密碼的復(fù)雜度 206
8.1.3 預(yù)定義角色 207
8.2 權(quán)限管理 207
8.2.1 【實(shí)戰(zhàn)】在創(chuàng)建用戶時(shí)指定的權(quán)限 207
8.2.2 使用grant命令和revoke命令管理的權(quán)限 210
8.2.3 在授權(quán)時(shí)使用admin option選項(xiàng)和grant option選項(xiàng) 215
8.2.4 使用組角色管理權(quán)限 216
8.2.5 使用set role命令顯示啟用角色的權(quán)限 219
8.3 審計(jì)管理 222
8.3.1 PostgreSQL的審計(jì)日志功能 222
8.3.2 PostgreSQL的審計(jì)擴(kuò)展插件pgaudit 224
=
第9章 備份與恢復(fù) 228
9.1 備份與恢復(fù)的基本概念 228
9.1.1 數(shù)據(jù)庫的故障類型 228
9.1.2 備份的分類 231
9.2 設(shè)置PostgreSQL的日志歸檔 232
9.2.1 【實(shí)戰(zhàn)】設(shè)置PostgreSQL的歸檔模式 232
9.2.2 【實(shí)戰(zhàn)】管理過期的歸檔日志文件 235
9.2.2.1 通過配置腳本定期刪除歸檔日志文件 235
9.2.2.2 通過手動(dòng)方式刪除歸檔日志文件 236
9.3 通過SQL轉(zhuǎn)儲(chǔ)實(shí)現(xiàn)邏輯備份與恢復(fù) 238
9.3.1 【實(shí)戰(zhàn)】使用pg_dump完成SQL轉(zhuǎn)儲(chǔ) 238
9.3.2 【實(shí)戰(zhàn)】使用pg_dumpall完成SQL轉(zhuǎn)儲(chǔ) 244
9.4 文件系統(tǒng)級(jí)別的備份與恢復(fù) 247
9.4.1 【實(shí)戰(zhàn)】第一個(gè)PostgreSQL文件系統(tǒng)級(jí)別的備份與恢復(fù) 248
9.4.2 【實(shí)戰(zhàn)】使用pg_basebackup完成熱備份與恢復(fù) 250
9.4.2.1 在主數(shù)據(jù)庫服務(wù)器上使用pg_basebackup 251
9.4.2.2 在從數(shù)據(jù)庫服務(wù)器上使用pg_basebackup 253
9.4.2.3 使用pg_basebackup的備份進(jìn)行恢復(fù) 254
9.5 【實(shí)戰(zhàn)】連續(xù)歸檔與基于時(shí)間點(diǎn)的恢復(fù) 257
9.5.1 創(chuàng)建基礎(chǔ)備份與連續(xù)歸檔 257
9.5.2 執(zhí)行基于時(shí)間點(diǎn)的數(shù)據(jù)恢復(fù) 260
9.5.3 執(zhí)行恢復(fù)到指定恢復(fù)點(diǎn)的數(shù)據(jù)恢復(fù) 261
9.5.4 恢復(fù)到指定事務(wù)上 262
9.5.5 恢復(fù)到最近時(shí)間點(diǎn)上 263
9.6 使用第三方備份恢復(fù)工具pg_rman 264
9.6.1 安裝與配置pg_rman 264
9.6.2 初始化pg_rman 267
9.6.3 使用pg_rman進(jìn)行備份 269
9.6.3.1 使用pg_rman進(jìn)行全量備份 269
9.6.3.2 使用pg_rman進(jìn)行增量備份 270
9.6.4 使用pg_rman進(jìn)行恢復(fù) 272
9.6.4.1 原地覆蓋式恢復(fù) 273
9.6.4.2 設(shè)置新的$PGDATA目錄式恢復(fù) 275
=
第10章 監(jiān)控、診斷與優(yōu)化數(shù)據(jù)庫 276
10.1 使用pgbench進(jìn)行基準(zhǔn)測試 276
10.1.1 數(shù)據(jù)庫基準(zhǔn)測試簡介 276
10.1.2 【實(shí)戰(zhàn)】使用pgbench進(jìn)行數(shù)據(jù)庫基準(zhǔn)測試 276
10.1.2.1 使用pgbench進(jìn)行基準(zhǔn)測試 278
10.1.2.2 使用pgbench內(nèi)置腳本進(jìn)行基準(zhǔn)測試 281
10.1.2.3 使用自定義腳本進(jìn)行基準(zhǔn)測試 284
10.2 使用擴(kuò)展監(jiān)控和診斷數(shù)據(jù)庫 285
10.2.1 使用pg_top擴(kuò)展監(jiān)控?cái)?shù)據(jù)庫 285
10.2.1.1 pg_top擴(kuò)展的安裝與配置 285
10.2.1.2 【實(shí)戰(zhàn)】使用pg_top擴(kuò)展監(jiān)控?cái)?shù)據(jù)庫 287
10.2.1.3 pg_top擴(kuò)展的常用參數(shù) 289
10.2.2 使用pg_stat_statements擴(kuò)展監(jiān)控SQL運(yùn)行 290
10.2.2.1 pg_stat_statements擴(kuò)展的安裝與配置 290
10.2.2.2 【實(shí)戰(zhàn)】使用pg_stat_statements擴(kuò)展監(jiān)控SQL語句的執(zhí)行 293
10.2.3 使用pg_stat_monitor擴(kuò)展查詢性能監(jiān)控 296
10.2.3.1 pg_stat_monitor擴(kuò)展的安裝與配置 296
10.2.3.2 【實(shí)戰(zhàn)】使用pg_stat_monitor擴(kuò)展監(jiān)控SQL語句的執(zhí)行 298
10.2.3.3 pg_stat_monitor視圖和pg_stat_statements視圖的差異 301
10.2.4 使用auto_explain擴(kuò)展監(jiān)控慢查詢 302
10.2.4.1 【實(shí)戰(zhàn)】使用auto_explain擴(kuò)展記錄慢查詢 302
10.2.4.2 auto_explain擴(kuò)展的配置參數(shù) 304
10.2.5 使用pg_profile擴(kuò)展生成數(shù)據(jù)庫性能報(bào)告 306
10.2.5.1 pg_profile擴(kuò)展的安裝與配置 306
10.2.5.2 【實(shí)戰(zhàn)】使用性能報(bào)告診斷數(shù)據(jù)庫 308
10.2.5.3 分析數(shù)據(jù)庫性能報(bào)告 310
10.3 【實(shí)戰(zhàn)】使用PostgreSQL的分區(qū) 312
10.3.1 范圍分區(qū) 313
10.3.2 列表分區(qū) 315
10.3.3 哈希分區(qū) 317
10.4 優(yōu)化PostgreSQL服務(wù)器 319
10.4.1 數(shù)據(jù)庫性能優(yōu)化基礎(chǔ) 320
10.4.1.1 數(shù)據(jù)庫性能優(yōu)化的三大問題 320
10.4.1.2 數(shù)據(jù)庫的性能指標(biāo) 320
10.4.1.3 影響數(shù)據(jù)庫性能的外部因素 321
10.4.2 使用監(jiān)控工具監(jiān)控Linux操作系統(tǒng)的性能 321
10.4.2.1 磁盤監(jiān)控工具 321
10.4.2.2 CPU監(jiān)控工具 324
10.4.2.3 內(nèi)存監(jiān)控工具 325
10.4.2.4 網(wǎng)絡(luò)監(jiān)控工具 326
10.4.3 優(yōu)化數(shù)據(jù)庫存儲(chǔ)性能 327
10.4.3.1 數(shù)據(jù)庫的主從復(fù)制