一. PG體系結構
1.1 PG的體系結構概述
PostgreSQL的主要結構如下：
1.2 PostgreSQL進程概述
PostgreSQL進程結構圖譜和分類:
二. PG內存結構
內存結構分為共享內存、本地內存。類似于Oracle的SGA和PGA。
共享內存
是指數(shù)據(jù)庫服務器向操作系統(tǒng)申請的共享內存段，如數(shù)據(jù)共享緩沖區(qū)、日志緩沖區(qū)、事務提交日志內存區(qū)等，提供給PostgreSQL服務器的所有進程使用。
數(shù)據(jù)共享緩沖區(qū)：PostgreSQL把要操作和處理的表、index，讀入到內存中，放到該區(qū)域緩存。類似于Oracle的database buffer cache。其大小由shared_buffers參數(shù)決定。
日志緩沖區(qū)：用于緩存數(shù)據(jù)庫中對數(shù)據(jù)修改的日志記錄，如：update table test set id=1這條SQL語句，數(shù)據(jù)庫會把這個操作的信息記錄在該內存區(qū)，將來寫出到日志文件中，如果配置為歸檔模式，則最終寫出到歸檔日志文件中去，用于恢復使用。其大小由wal_buffers參數(shù)決定。類似于Oracle的log buffer。
提交日志緩沖區(qū)：該內存區(qū)域有別于wal buffer日志緩沖區(qū)。它用于記錄數(shù)據(jù)庫中所有事務的提交狀態(tài)，事務是否已經(jīng)提交，是否已經(jīng)終止，是否進行中，子事務等狀態(tài)信息。用于MVCC。
本地內存
當我們和數(shù)據(jù)庫建立一個連接請求時，數(shù)據(jù)庫幫我們創(chuàng)建1個后端進程。并給該后端進程分配的內存區(qū)域，該內存區(qū)域只屬于這一個后端進程使用，可以認為是私有的。用于處理和響應我們向數(shù)據(jù)庫發(fā)起的請求操作。通常包含：工作區(qū)work mem、維護工作區(qū)、臨時緩沖區(qū)。
工作區(qū)：該內存區(qū)用于處理客戶端SQL語句請求的order by排序、distinct過濾、表合并連接merge-join、哈希連接hash-join操作等。由work_mem參數(shù)決定大小。
維護工作區(qū)：該內存區(qū)域用于處理重建索引reindex、vacuum空間回收操作、給表添加外鍵約束等。由maintenance_work_mem參數(shù)決定大小。
臨時緩沖區(qū)：該內存區(qū)用于創(chuàng)建和訪問臨時表時，存放臨時表的數(shù)據(jù)。該內存區(qū)和因為SQL中因為大表排序或hash table而在服務器上建立的臨時文件(位于pgsql_tmp路徑下)沒有直接關系。由temp_buffers參數(shù)決定大小。
三. PostgreSQL進程
PostgreSQL數(shù)據(jù)庫的進程可以分為三類：后臺進程、后端進程或叫服務器進程、客戶端進程或用戶進程。
3.1 后臺進程
此處我的測試庫因為有些功能還未安裝，有些進程不存在。
/usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/是數(shù)據(jù)庫服務器的master進程，其它諸如checkpoint，background writer，walwrite，autovacuum launcher，stats collector，logical replication launcher都是由它fork的子進程。當然，數(shù)據(jù)庫運行模式不同，配置不同，也可能有其它后臺進程，如歸檔進程等。postgres：PostgreSQL數(shù)據(jù)庫的核心進程，也是整個cluster的父進程，該進程出現(xiàn)問題，整個cluster就over了。該進程由操作系統(tǒng)的守護進程1號進程派生。Linux下的1號進程是整個服務器的守護進程，類比于Java程序中的Object類，一切類的父類。checkpointer：檢查點進程，等價于Oracle的CKPT進程，負責完成數(shù)據(jù)庫的檢查點，通知數(shù)據(jù)庫的寫進程DBWR將內存中的臟數(shù)據(jù)寫出到磁盤。background writer：等價于Oracle的DBWR進程，負責將內存中的臟數(shù)據(jù)寫出到磁盤。walwriter：等價于Oracle的LGWR進程，負責將日志緩沖區(qū)中的記錄關于數(shù)據(jù)庫的修改的日志寫出到日志文件中去，確保數(shù)據(jù)的修改不會丟失，用于恢復使用。autovacuum launcher：自動清理工作進程。由于PostgreSQL不像Oracle那樣有undo的機制，將數(shù)據(jù)被修改前的信息寫入到undo，然后修改數(shù)據(jù)。PostgreSQL采取的是在原數(shù)據(jù)塊上進行保留舊的數(shù)據(jù)，并作標記，等到將來修改提交生效之后，舊的數(shù)據(jù)(dead tuple翻譯為死元組)不需要的話，就得清理，由該進程來完成。stats collector：統(tǒng)計信息收集進程。用于及時的更新數(shù)據(jù)庫中的統(tǒng)計信息，如表、index有多少條記錄，數(shù)據(jù)分布等，給優(yōu)化器提供最新的信息，便于優(yōu)化器選擇最優(yōu)的執(zhí)行計劃。避免統(tǒng)計信息不準確，導致優(yōu)化器選擇錯誤的執(zhí)行計劃，導致SQL性能下降或偏差。logical replication launcher:邏輯復制進程。用于完成邏輯復制的工作。archiver：歸檔進程，等價于Oracle的ARCH進程，用于完成數(shù)據(jù)庫日志文件的歸檔。當數(shù)據(jù)庫配置了歸檔模式之后，可以看到該進程。
3.2 后端進程(backend)或服務器進程
當我們的應用程序和圖形界面的客戶端工具，連接到PostgreSQL數(shù)據(jù)庫服務器時。master進程會為該應用程序創(chuàng)建1個服務器進程，用于處理和響應該客戶端應用程序的請求。
如上，可以看到服務器進程號是18499，通過select pg_backend_pid()查詢。同時，看到服務器上該進程的父進程是1164，由 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/這個主進程派生。
后端進程或服務器進程的數(shù)量由max_connections參數(shù)決定。
每一個后端進程一次只能訪問一個數(shù)據(jù)庫。它和客戶端進程進行TCP通信，開戶端斷開之后，該進程自動回收消失。客戶端重新連接或發(fā)起新連接時重新創(chuàng)建新的后端進程。
由于進程的創(chuàng)建或回收，比較消耗操作系統(tǒng)的資源，因此，多數(shù)情況下，應用系統(tǒng)都會通過連接池的方式和數(shù)據(jù)庫建立連接。
從PostgreSQL官方給后端進程的命名可以看到還是一脈相承的，比如，我們查看當前會話所在的后端進程號或者叫服務器進程的時候，我們調用的是pg_backend_pid()函數(shù)，殺會話所在進程時，調用的是pg_terminate_backend()，或者pg_cancel_backend()。
3.3 用戶進程或客戶端進程
指的是連接數(shù)據(jù)庫服務器的應用程序或者客戶端工具等。
每個用戶進程或者客戶端進程對應一個服務端進程。
這2張圖引自《PostgreSQL for DBA Architects》p21-22。
3.4 數(shù)據(jù)庫服務器啟動流程
當我們通過pg_ctl工具來啟動PostgreSQL數(shù)據(jù)庫時，先在操作系統(tǒng)上創(chuàng)建1個master進程，然后該進程派生出一系列的后臺進程，同時該進程監(jiān)聽$PGDATA/postgresql.conf配置文件中指定的端口。并且，向操作系統(tǒng)申請內存，用于數(shù)據(jù)庫的正常運行操作，處理客戶端的連接請求操作處理。最后，數(shù)據(jù)庫可以正常對外提供服務。
四. PG邏輯結構
PG邏輯結構圖解:
4.1 PostgreSQL cluster
當我們在一臺服務器上安裝部署并且初始化一個PostgreSQL數(shù)據(jù)庫之后，嚴格的講，其實是我們安裝部署了一套PostgreSQL數(shù)據(jù)庫軟件，然后初始化了一個PostgreSQL的database cluster。這里的cluster是什么概念呢？
首先，這里的cluster完全是個邏輯上的概念，它是指一系列的數(shù)據(jù)庫的集合。它所包含的數(shù)據(jù)庫就是指，當我們以postmaster -D /var/lib/pgsql/15/data/ start來啟動數(shù)據(jù)庫cluster時，由這個-D參數(shù)指定，或者是PGDATA環(huán)境變量指定的路徑下的所有的數(shù)據(jù)庫的集合。當然，這么說太抽象了，其實，從物理上，我們可以這么說明和解釋：
[root@sr-fe ~]# su - postgres
上一次登錄：日 6月 25 10:10:40 CST 2023pts/2 上
-bash-4.2$
-bash-4.2$ env | grep PGDATA
PGDATA=/var/lib/pgsql/15/data
-bash-4.2$
-bash-4.2$
-bash-4.2$ ll /var/lib/pgsql/15/data
total 68
drwx------ 5 postgres postgres 33 Jun 9 19:17 base
-rw------- 1 postgres postgres 30 Jun 25 00:00 current_logfiles
drwx------ 2 postgres postgres 4096 Jun 25 10:10 global
drwx------ 2 postgres postgres 188 Jun 15 00:00 log
drwx------ 2 postgres postgres 6 Jun 9 19:17 pg_commit_ts
drwx------ 2 postgres postgres 6 Jun 9 19:17 pg_dynshmem
-rw------- 1 postgres postgres 4574 Jun 12 10:28 pg_hba.conf
-rw------- 1 postgres postgres 1636 Jun 9 19:17 pg_ident.conf
drwx------ 4 postgres postgres 68 Jun 22 22:16 pg_logical
drwx------ 4 postgres postgres 36 Jun 9 19:17 pg_multixact
drwx------ 2 postgres postgres 6 Jun 9 19:17 pg_notify
drwx------ 2 postgres postgres 6 Jun 9 19:17 pg_replslot
drwx------ 2 postgres postgres 6 Jun 9 19:17 pg_serial
drwx------ 2 postgres postgres 6 Jun 9 19:17 pg_snapshots
drwx------ 2 postgres postgres 6 Jun 12 10:28 pg_stat
drwx------ 2 postgres postgres 6 Jun 9 19:17 pg_stat_tmp
drwx------ 2 postgres postgres 18 Jun 9 19:17 pg_subtrans
drwx------ 2 postgres postgres 6 Jun 9 19:17 pg_tblspc
drwx------ 2 postgres postgres 6 Jun 9 19:17 pg_twophase
-rw------- 1 postgres postgres 3 Jun 9 19:17 PG_VERSION
drwx------ 3 postgres postgres 60 Jun 9 19:17 pg_wal
drwx------ 2 postgres postgres 18 Jun 9 19:17 pg_xact
-rw------- 1 postgres postgres 88 Jun 9 19:17 postgresql.auto.conf
-rw------- 1 postgres postgres 29451 Jun 12 10:25 postgresql.conf
-rw------- 1 postgres postgres 58 Jun 22 22:16 postmaster.opts
-rw------- 1 postgres postgres 95 Jun 22 22:16 postmaster.pid
-bash-4.2$
-bash-4.2$ oid2name
All databases:
Oid Database Name Tablespace
--------------------------------
5 postgres pg_default
4 template0 pg_default
1 template1 pg_default
-bash-4.2$
-bash-4.2$ psql
psql (9.2.24, server 15.3)
WARNING: psql version 9.2, server version 15.0.
Some psql features might not work.
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=# select oid,datname from pg_database ;
oid | datname
-----+-----------
5 | postgres
1 | template1
4 | template0
(3 rows)
postgres=#
4.2 database和cluster的關系
數(shù)據(jù)庫是指一些列數(shù)據(jù)庫對象的集合，比如表，index，view，function等這些數(shù)據(jù)庫對象隸屬于一個特定的數(shù)據(jù)庫。
cluster指的是一些列數(shù)據(jù)庫的集合。比如：一個cluster初始化之后，包含3個默認數(shù)據(jù)庫：postgres，默認的管理數(shù)據(jù)庫；template0，默認的不可修改的空數(shù)據(jù)庫；template1，默認的模板數(shù)據(jù)庫，當我們創(chuàng)建數(shù)據(jù)庫時，會參照該數(shù)據(jù)庫來創(chuàng)建。
當我們在template1模板數(shù)據(jù)庫中創(chuàng)建和安裝1個數(shù)據(jù)庫插件extension，uuid之后，再去創(chuàng)建新的數(shù)據(jù)庫，那么新數(shù)據(jù)庫中就會自動包含該uuid這個extension。
1個cluster可以包含多個數(shù)據(jù)庫，反過來1個database只能隸屬于1個cluster。
4.3 數(shù)據(jù)庫實例和cluster的關系
我們定義數(shù)據(jù)庫實例是指一堆PostgreSQL的后臺進程和內存結構，cluster指的是我們在初始化數(shù)據(jù)庫時，指定的PGDATA環(huán)境變量指向的操作系統(tǒng)上的那個路徑下的一堆的文件。
一個數(shù)據(jù)庫實例在其一個生命周期內(從啟動到關閉)只能“掛載”一個數(shù)據(jù)庫cluster，反之，一個cluster也只能被一個實例掛載訪問。二者之間是嚴格的一對一關系。
但是，在一臺服務器上，我們可以安裝一套PostgreSQL數(shù)據(jù)庫軟件，用這個數(shù)據(jù)庫軟件可以創(chuàng)建多個實例和多個cluster。每個實例對應于一個cluster。只要每個cluster所指定的監(jiān)聽端口不同，我們就可以同時運行多個實例和cluster。
如：我們可以在這套環(huán)境上再初始化一個實例和cluster，將其指向另外一個監(jiān)聽端口即可。
4.4 PostgreSQL的tablespace
定義：
tablespace依然是一個邏輯概念，它是隸屬于cluster的。
查看：
通過pg_tablespace字典表來查看cluster下表空間的信息，或者是\db命令也可查看表空間的信息;
默認表空間：
每個cluster默認情況下有2個表空間，分別命名為pg_default用于存放各個數(shù)據(jù)庫私有的數(shù)據(jù)庫對象，pg_global用于存放cluster全局共享的數(shù)據(jù)庫對象信息，例如：cluster中數(shù)據(jù)庫本身的信息，表空間的信息，數(shù)據(jù)庫訂閱信息，數(shù)據(jù)庫復制信息，數(shù)據(jù)庫認證授權信息、控制文件等。
專用表空間：
pg_global表空間是專表空間專用的，只能存放全局共享的數(shù)據(jù)庫對象，不能存放用戶數(shù)據(jù)，否則報錯：
pg_default，表空間，默認存放cluster下所有數(shù)據(jù)庫的所有數(shù)據(jù)庫對象。這一點兒上，倒是有點兒類似于Oracle數(shù)據(jù)庫的專表空間專用。
表空間和數(shù)據(jù)庫的關系：一個表空間可以給多個數(shù)據(jù)庫使用，一個數(shù)據(jù)庫里的不同數(shù)據(jù)庫對象也可以存放在不同的表空間下。表空間和數(shù)據(jù)庫的關系，不嚴格的講，可以說是多對多的關系。不像Oracle數(shù)據(jù)庫中，一個數(shù)據(jù)庫可以包含多個表空間，且每個表空間只能屬于一個數(shù)據(jù)庫使用。
如何創(chuàng)建表空間：OS上路徑需提前創(chuàng)建，postgres用戶得有讀寫操作系統(tǒng)文件系統(tǒng)權限，數(shù)據(jù)庫管理員權限。
-- 不要把表空間目錄放在data目錄下
-bash-4.2$ psql
psql (9.2.24, server 15.3)
WARNING: psql version 9.2, server version 15.0.
Some psql features might not work.
Type "help" for help.
postgres=#
postgres=# create tablespace newtbs location '/var/lib/pgsql/15/data/tbs_dir';
WARNING: tablespace location should not be inside the data directory
CREATE TABLESPACE
postgres=#
創(chuàng)建庫表時指定表空間:
create database newdb tablespace newtbs;
create table test_tbs(id int) tablespace newtbs ;
修改表空間:
數(shù)據(jù)庫創(chuàng)建之后，或者數(shù)據(jù)庫對象(表、index等)創(chuàng)建之后，也可以分別通過alter database/table/index來修改表空間信息。
表空間作用:
主要用于邏輯上隔離數(shù)據(jù)庫對象，或者用于數(shù)據(jù)庫存儲空間規(guī)劃或遷移存儲。想要通過表空間的設置，進而對于數(shù)據(jù)庫性能提升？作用不大，畢竟現(xiàn)在基本上都是直接上SSD(Solid State Drive )存儲給數(shù)據(jù)庫使用。
4.5 Sgment
一個段是分配給一個邏輯結構（一個表、一個索引或其他對象）的一組區(qū)，是數(shù)據(jù)庫對象使用的空間的集合；段可以有表段、索引段、回滾段、臨時段和高速緩存段等。
4.6 Extent
區(qū)是數(shù)據(jù)庫存儲空間分配的一個邏輯單位，它由連續(xù)數(shù)據(jù)塊所組成。第一個段是由一個或多個盤區(qū)組成。當一段中間所有空間已完全使用，PostgreSQL為該段分配一個新的范圍。
4.7 Block
數(shù)據(jù)塊是PostgreSQL 管理數(shù)據(jù)文件中存儲空間的單位，為數(shù)據(jù)庫使用的I/O的最小單位，是最小的邏輯部件。默認值8K。
五. PG物理結構
5.1 cluster的物理結構圖譜
5.2 cluster在文件系統(tǒng)上的結構
目錄文件概述:
base --存放默認數(shù)據(jù)庫的目錄
global --存放的數(shù)據(jù)庫相關的字典視圖或者表文件
pg_commit_ts --事務存放的提交的時間戳數(shù)據(jù)
pg_dynshmem --動態(tài)內存分配存放的空間(dynamic share memeory)
pg_hba.conf --基于主機的配置文件
pg_ident.conf --基于對等認證的配置文件
pg_logical --存儲數(shù)據(jù)庫內部狀態(tài)的邏輯解碼數(shù)據(jù)
pg_multixact --存放多事務狀態(tài)的數(shù)據(jù)
pg_notify --消息通知目錄(LISTEN狀態(tài)目錄)
pg_replslot --存放復制槽的數(shù)據(jù)
pg_serial --提交的可串行化事務的狀態(tài)數(shù)據(jù)
pg_snapshots --執(zhí)行導出快照函數(shù)時的狀態(tài)信息數(shù)據(jù)
pg_stat --統(tǒng)計信息目錄
pg_stat_tmp --臨時統(tǒng)計信息目錄
pg_subtrans --子事務目錄
pg_tblspc --表空間映射目錄
pg_twophase --兩階段提交狀態(tài)的數(shù)據(jù)
PG_VERSION --存放主版本編號的文件
pg_wal --存儲 WAL 文件的目錄
pg_xact --事務提交的狀態(tài)數(shù)據(jù)
postgresql.auto.conf --存儲通過 ALTER SYSTEM 命令修改的參數(shù)文件（可以手動修改）
postgresql.conf --數(shù)據(jù)庫的參數(shù)配置文件
postmaster.opts --上一次數(shù)據(jù)庫啟動狀態(tài)的命令
postmaster.pid --存放當前數(shù)據(jù)庫的主進程編號及相關目錄及端口的信息
數(shù)據(jù)文件:
$PGDATA/base：用于存放當前cluster下所有的數(shù)據(jù)庫，數(shù)字化命名的路徑表示各個數(shù)據(jù)庫，每個數(shù)字表示數(shù)據(jù)庫的oid。一些創(chuàng)建數(shù)據(jù)庫時指定了表空間的庫會存在表空間目錄下，而不是這個目錄。
關于文件名中類似如1213_fsm、1213_vm的文件，表示空閑空間映射文件(free space map)，可見性映射文件(visibility map)。
表空間:
global:用于存放cluster級別共享的全局表，如pg_database，pg_tablespace表，其文件命名依舊采用oid的數(shù)字化格式。
我們可以從數(shù)據(jù)庫中驗證這些cluster級別的共享系統(tǒng)表的oid和global下的數(shù)字文件名匹配：
5.3 測試一個表空間及表的例子
代碼:
create tablespace testtbs location '/var/lib/pgsql/newtbs';
create database newdb tablespace testtbs;
\c newdb;
create table test_tbs(id bigint) tablespace newtbs ;
insert into test_tbs select * from generate_series(1,1000000000);
測試記錄:
postgres=# create tablespace testtbs location '/var/lib/pgsql/newtbs';
CREATE TABLESPACE
postgres=# create database newdb tablespace testtbs;
CREATE DATABASE
postgres=#
postgres=#
postgres=# \c newdb;
psql (9.2.24, server 15.3)
WARNING: psql version 9.2, server version 15.0.
Some psql features might not work.
You are now connected to database "newdb" as user "postgres".
newdb=# create table test_tbs(id bigint) tablespace newtbs ;
CREATE TABLE
newdb=# insert into test_tbs select * from generate_series(1,1000000000);
INSERT 0 1000000000
newdb=#
表的數(shù)據(jù)達到1G后會分割文件，所以會有這么多的文件
表大小達34GB
SELECT pg_size_pretty(pg_total_relation_size('test_tbs'));
SELECT datname, oid as database_id FROM pg_database WHERE datname = 'newdb';
SELECT nspname, oid as schema_id FROM pg_namespace WHERE nspname = 'public';
SELECT relname, oid as table_id FROM pg_class WHERE relname = 'test_tbs';
SELECT pg_relation_filepath('public.test_tbs');
