因為工作的原因接觸到了pgsql資料庫，對PostgreSQL的體系和運維操作也有了一定的瞭解。PostgreSQL在官網上標稱為世界上最先進的開源資料庫，而MySQL在官網上標稱的是世界上最流行的開源資料庫，可見PostgresSQL還是比較高調的。
一、PostgreSQL初體驗
首先是資料庫的安裝，PostgreSQL官網上不像MySQL那樣提供了二進位制包的下載，PostgreSQL主要提供了RPM包下載和原始碼下載，通常使用原始碼編譯安裝，安裝步驟相對比較簡單：
######postgres單例項安裝 1、官網下載原始碼包：https://www.postgresql.org/ftp/source/v14.8/ 2、解壓 tar -xvf postgresql-14.0.tar.gz 3、新建postgres使用者 groupadd postgres useradd -g postgres postgres 4、安裝依賴包 yum install *zlib* yum install *libreadline* 5、編譯安裝 ./configure make && make install 6、修改安裝目錄所屬使用者組 chown -R postgres:postgres /usr/local/pgsql 7、新建postgresql的資料目錄 mkdir /pgdata chown postgres:postgres /pgdata 8、配置環境變數 su - postgres vi ~/.bash_profile export PATH=$PATH:/usr/local/pgsql/bin 9、初始化資料庫 initdb -D /pgdata 10、啟動資料庫 pg_ctl -D /pgdata start 11、驗證是否可登入 psql
安裝完成後，會自動在資料目錄下面生成配置檔案，根據實際情況首先需要修改配置檔案postgresql.conf和訪問控制檔案pg_hba.conf。修改完後透過pg_ctl命令重啟PG。
#####配置檔案postgresql.conf #connection control listen_addresses = '*' #不限制連線ip max_connections = 1000 superuser_reserved_connections = 10 #為超級使用者保留的連線數 #memory management shared_buffers = 512MB #推薦作業系統實體記憶體的1/4 work_mem = 8MB #單個查詢操作(例如排序或雜湊表)可使用的最大記憶體 maintenance_work_mem = 512MB #維護性操作（例如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY）中使用的最大的記憶體 max_files_per_process = 24800 effective_cache_size = 1GB #推薦作業系統實體記憶體的1/2 #log optimization log_destination = 'csvlog' logging_collector = on log_directory = '/pgdata/logs' # 日誌存放路徑，提前規劃在系統上建立好 log_truncate_on_rotation = on #####訪問控制檔案pg_hba.conf加上下面這行 host all all 0.0.0.0/0 md5
PostgreSQL透過WAL日誌進行主從同步，不同於MySQL透過binlog進行邏輯複製。並且PostgreSQL 在9.x之後引入了主從的流複製機制，所謂流複製，就是備伺服器透過tcp流從主伺服器中同步相應的資料，主伺服器在WAL記錄產生時即將它們以流式傳送給備伺服器，而不必等到WAL檔案被填充。主從複製搭建的具體步驟可以參考如下：
#####主從同步配置 主庫建立同步賬號 CREATE ROLE replica login replication encrypted password 'Temp##2022'; 主庫修改pg_hba.conf增加從庫訪問控制 host replication replica 10.2.111.192/32 md5 主庫重啟 pg_ctl -D /pgdata restart 停止從庫 pg_ctl stop -D /pgdata 清空從庫資料檔案 rm -rf /pgdata/* 從庫拉取主庫資料檔案 pg_basebackup -h 10.2.111.192 -D /pgdata -p 5432 -U replica -Fp -Xs -Pv -R --checkpoint=fast 從庫postgresql.conf檔案新增主庫資訊 primary_conninfo = 'host=10.2.111.193 port=5432 user=replica password=Temp##2022' 啟動從庫 pg_ctl start -D /pgdata 主庫驗證主從同步正常 select client_addr,usename,backend_start,application_name,sync_state,sync_priority FROM pg_stat_replication; 備庫提升為主庫 pg_ctl promote -D /pgdata pg_controldata -D /pgdata | grep cluster #檢查資料庫狀態，為in production，說明備庫已提升為主庫
在PostgreSQL的資料庫邏輯儲存架構中，採用的是database-schema-table這樣一個三層的架構，和SQLServer一樣，SQLServer預設的模式是dbo，PostgresSQL中預設的模式是public。其實大多數應用中，database-table這樣兩層的架構足夠了，三層架構感覺還是複雜了一些。每個database下面有兩個預設的系統schema：pg_catalog和information_schema，pg_catalog下面的表主要描述的是pg例項的配置資訊，information_schema下面的表主要描述的當前database的資料字典資訊。比如要查詢當前database下面所有的表可以透過information_schema.tables表查詢。在使用者管理方面，PostgreSQL中角色的概念影響較深，使用者即角色，建立角色的時候指定login屬性即代表建立同名的使用者。
二、PostgreSQL與MySQL對比
1. 開源協議
PostgreSQL採用的是寬鬆的BSD開源協議，基於開源PostgreSQL程式碼封裝成的軟體可以不公開原始碼，它也不強制任何特定的版權宣告，這使得它與許多其他開源和專有許可證相容。基於這一點，很多國產資料庫廠商採用了基於開源PG二次開發的資料庫選型方案，華為的opengauss就是基於PG9版本，而vastbase、mogdb又是基於opengauss，也可以認為是PostgreSQL系列的產品。
MySQL採用的是較為嚴格的GPLv2開源協議，該協議具有強傳染性，這意味著任何基於GPLv2 許可的程式碼進行修改或擴充套件，並且要分發的派生作品，也必須在GPLv2開源協議下發布，長期來看，具有傳染性的GPLv2開源協議更能把成果回饋社群，帶動社群的發展。國內基於MySQL的幾款資料庫TDSQL、GoldenDB在目前的國內的國產資料庫份額中佔有相當一部分比例，特別是在銀行業。但是好像從來沒有見過他們的開源版本，這個要較真起來很可能是違反開源協議的。
2. 表組織形式
PostgreSQL底層的表組織形式採用的是堆表（heap table），在堆表中資料的按資料插入的順序進行排序，索引指向堆中行的指標（CTID），而不是實際的行資料。MySQL底層的表組織形式採用的是索引組織表（IOT），索引組織表中資料按主鍵或唯一索引進行排序，資料儲存在主鍵索引的葉子節點中。對於基於主鍵索引查詢的SQL語句，索引組織表不需要回表，效能更佳。
可能大家覺得堆表對於寫入的效能會更高效，畢竟堆表中資料可以迅速地新增到表的末尾，不需要重新排序或調整資料，不需要像IOT那樣頻繁地對資料頁進行合併或分裂來維護B+樹結構，但其實生產環境中一個表可能會有多個索引，對於PostgreSQL的B+樹索引的維護同樣會帶來很多開銷。所以那種表組織形式更好還需要看業務場景，通常來說索引組織表更適合於OLPT場景，堆表在OLAP場景中表現更好。
3. MVCC實現機制
MVCC實現機制和更新方式是一個問題，PostgreSQL採用的是異地更新（out-of-place update），它沒有undo表空間，PostgreSQL將歷史元組和最新元組都儲存在Heap表中，這種方式的好處是無須做回滾操作，因此PostgreSQL的堆表需要儲存多個行版本資料。但是，假設事務不停地更新資料，那麼一條元組就會產生大量的歷史版本。其他事務在訪問時需要檢視這些元組是否滿足可見性要求，這會增加讀操作的時延，降低資料掃描的效率。為了防止資料膨脹，PostgreSQL資料庫採用Vacuum機制清理表中的無效元組，PostgreSQL預設會開啟auto vacuum機制。
MySQL、ORACLE採用的都是原地更新（in-place update），如果事務更新了一條元組，它可以“原地”更新這條元組，歷史元組會以Undo日誌記錄的形式儲存到回滾段中，這樣就實現了元組的原地更新（Inplace Update）。當有併發事務需要訪問歷史元組時，可以從回滾段中“回滾”出這條元組，如果事務異常終止，則可以利用Undo日誌將資料恢復。當所有可能訪問歷史元組的事務全部結束後，Undo日誌中的歷史元組就可以被清理。由於Undo日誌被集中儲存到某一個回滾段，所以清理也較為便捷。
4. 多程序VS多執行緒
PostgreSQL採用的是多程序架構。優點主要在穩定性方面：在於每個連線都有自己的程序，一個程序崩潰不太會影響其他的程序，並且每個程序都有自己的記憶體空間，這可以減少記憶體洩漏或其他問題對整個系統的影響；缺點在於資源消耗更高：由於每個程序都有自己的記憶體空間，這可能導致更高的記憶體使用，並且程序間的上下文切換和程序間的通訊開銷更大。
MySQL採用的是多執行緒架構。優點在於資源消耗更低：執行緒共享相同的記憶體空間，這通常導致更低的記憶體使用和更快的上下文切換。並且多執行緒可以更好的適用多核CPU架構處理高併發問題。多執行緒架構在穩定性方面不如多程序，一個執行緒的問題可能會影響到同一程序中的其他執行緒。