因為工作的原因接觸到了pgsql數(shù)據(jù)庫，對PostgreSQL的體系和運維操作也有了一定的了解。PostgreSQL在官網(wǎng)上標(biāo)稱為世界上最先進的開源數(shù)據(jù)庫，而MySQL在官網(wǎng)上標(biāo)稱的是世界上最流行的開源數(shù)據(jù)庫，可見PostgresSQL還是比較高調(diào)的。

一、PostgreSQL初體驗

首先是數(shù)據(jù)庫的安裝，PostgreSQL官網(wǎng)上不像MySQL那樣提供了二進制包的下載，PostgreSQL主要提供了RPM包下載和源碼下載，通常使用源碼編譯安裝，安裝步驟相對比較簡單：

######postgres單實例安裝
1、官網(wǎng)下載源碼包：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的數(shù)據(jù)目錄
mkdir?/pgdata
chown?postgres:postgres?/pgdata

8、配置環(huán)境變量
su?-?postgres
vi?~/.bash_profile
export?PATH=$PATH:/usr/local/pgsql/bin

9、初始化數(shù)據(jù)庫
initdb?-D?/pgdata

10、啟動數(shù)據(jù)庫
pg_ctl?-D?/pgdata?start

11、驗證是否可登錄
psql

安裝完成后，會自動在數(shù)據(jù)目錄下面生成配置文件，根據(jù)實際情況首先需要修改配置文件postgresql.conf和訪問控制文件pg_hba.conf。修改完后通過pg_ctl命令重啟PG。

#####配置文件postgresql.conf
#connection?control
listen_addresses?=?'*'??#不限制連接ip
max_connections?=?1000
superuser_reserved_connections?=?10?#為超級用戶保留的連接數(shù)

#memory?management??????
shared_buffers?=?512MB????#推薦操作系統(tǒng)物理內(nèi)存的1/4??????????????????????????
work_mem?=?8MB????????#單個查詢操作(例如排序或哈希表)可使用的最大內(nèi)存?????????????????
maintenance_work_mem?=?512MB???????#維護性操作（例如VACUUM、CREATE?INDEX和ALTER?TABLE?ADD?FOREIGN?KEY）中使用的最大的內(nèi)存??
max_files_per_process?=?24800???????????
effective_cache_size?=?1GB???#推薦操作系統(tǒng)物理內(nèi)存的1/2

#log?optimization
log_destination?=?'csvlog'?????????????
logging_collector?=?on??????????
log_directory?=?'/pgdata/logs'????????#?日志存放路徑，提前規(guī)劃在系統(tǒng)上創(chuàng)建好?
log_truncate_on_rotation?=?on???????


#####訪問控制文件pg_hba.conf加上下面這行
host????all?????????????all?????????????0.0.0.0/0???????????????md5??

PostgreSQL通過WAL日志進行主從同步，不同于MySQL通過binlog進行邏輯復(fù)制。并且PostgreSQL 在9.x之后引入了主從的流復(fù)制機制，所謂流復(fù)制，就是備服務(wù)器通過tcp流從主服務(wù)器中同步相應(yīng)的數(shù)據(jù)，主服務(wù)器在WAL記錄產(chǎn)生時即將它們以流式傳送給備服務(wù)器，而不必等到WAL文件被填充。主從復(fù)制搭建的具體步驟可以參考如下：

#####主從同步配置
主庫創(chuàng)建同步賬號
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

清空從庫數(shù)據(jù)文件
rm?-rf??/pgdata/*

從庫拉取主庫數(shù)據(jù)文件
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??#檢查數(shù)據(jù)庫狀態(tài)，為in?production，說明備庫已提升為主庫

在PostgreSQL的數(shù)據(jù)庫邏輯存儲架構(gòu)中，采用的是database-schema-table這樣一個三層的架構(gòu)，和SQLServer一樣，SQLServer默認的模式是dbo，PostgresSQL中默認的模式是public。其實大多數(shù)應(yīng)用中，database-table這樣兩層的架構(gòu)足夠了，三層架構(gòu)感覺還是復(fù)雜了一些。每個database下面有兩個默認的系統(tǒng)schema：pg_catalog和information_schema，pg_catalog下面的表主要描述的是pg實例的配置信息，information_schema下面的表主要描述的當(dāng)前database的數(shù)據(jù)字典信息。比如要查詢當(dāng)前database下面所有的表可以通過information_schema.tables表查詢。在用戶管理方面，PostgreSQL中角色的概念影響較深，用戶即角色，創(chuàng)建角色的時候指定login屬性即代表創(chuàng)建同名的用戶。

二、PostgreSQL與MySQL對比

1. 開源協(xié)議

PostgreSQL采用的是寬松的BSD開源協(xié)議，基于開源PostgreSQL代碼封裝成的軟件可以不公開源代碼，它也不強制任何特定的版權(quán)聲明，這使得它與許多其他開源和專有許可證兼容?；谶@一點，很多國產(chǎn)數(shù)據(jù)庫廠商采用了基于開源PG二次開發(fā)的數(shù)據(jù)庫選型方案，華為的opengauss就是基于PG9版本，而vastbase、mogdb又是基于opengauss，也可以認為是PostgreSQL系列的產(chǎn)品。

MySQL采用的是較為嚴格的GPLv2開源協(xié)議，該協(xié)議具有強傳染性，這意味著任何基于GPLv2 許可的代碼進行修改或擴展，并且要分發(fā)的派生作品，也必須在GPLv2開源協(xié)議下發(fā)布，長期來看，具有傳染性的GPLv2開源協(xié)議更能把成果回饋社區(qū)，帶動社區(qū)的發(fā)展。國內(nèi)基于MySQL的幾款數(shù)據(jù)庫TDSQL、GoldenDB在目前的國內(nèi)的國產(chǎn)數(shù)據(jù)庫份額中占有相當(dāng)一部分比例，特別是在銀行業(yè)。但是好像從來沒有見過他們的開源版本，這個要較真起來很可能是違反開源協(xié)議的。

2. 表組織形式

PostgreSQL底層的表組織形式采用的是堆表（heap table），在堆表中數(shù)據(jù)的按數(shù)據(jù)插入的順序進行排序，索引指向堆中行的指針（CTID），而不是實際的行數(shù)據(jù)。MySQL底層的表組織形式采用的是索引組織表（IOT），索引組織表中數(shù)據(jù)按主鍵或唯一索引進行排序，數(shù)據(jù)存儲在主鍵索引的葉子節(jié)點中。對于基于主鍵索引查詢的SQL語句，索引組織表不需要回表，性能更佳。

可能大家覺得堆表對于寫入的性能會更高效，畢竟堆表中數(shù)據(jù)可以迅速地添加到表的末尾，不需要重新排序或調(diào)整數(shù)據(jù)，不需要像IOT那樣頻繁地對數(shù)據(jù)頁進行合并或分裂來維護B+樹結(jié)構(gòu)，但其實生產(chǎn)環(huán)境中一個表可能會有多個索引，對于PostgreSQL的B+樹索引的維護同樣會帶來很多開銷。所以那種表組織形式更好還需要看業(yè)務(wù)場景，通常來說索引組織表更適合于OLPT場景，堆表在OLAP場景中表現(xiàn)更好。

3. MVCC實現(xiàn)機制

MVCC實現(xiàn)機制和更新方式是一個問題，PostgreSQL采用的是異地更新（out-of-place update），它沒有undo表空間，PostgreSQL將歷史元組和最新元組都保存在Heap表中，這種方式的好處是無須做回滾操作，因此PostgreSQL的堆表需要存儲多個行版本數(shù)據(jù)。但是，假設(shè)事務(wù)不停地更新數(shù)據(jù)，那么一條元組就會產(chǎn)生大量的歷史版本。其他事務(wù)在訪問時需要查看這些元組是否滿足可見性要求，這會增加讀操作的時延，降低數(shù)據(jù)掃描的效率。為了防止數(shù)據(jù)膨脹，PostgreSQL數(shù)據(jù)庫采用Vacuum機制清理表中的無效元組，PostgreSQL默認會打開auto vacuum機制。

MySQL、ORACLE采用的都是原地更新（in-place update），如果事務(wù)更新了一條元組，它可以“原地”更新這條元組，歷史元組會以Undo日志記錄的形式保存到回滾段中，這樣就實現(xiàn)了元組的原地更新（Inplace Update）。當(dāng)有并發(fā)事務(wù)需要訪問歷史元組時，可以從回滾段中“回滾”出這條元組，如果事務(wù)異常終止，則可以利用Undo日志將數(shù)據(jù)恢復(fù)。當(dāng)所有可能訪問歷史元組的事務(wù)全部結(jié)束后，Undo日志中的歷史元組就可以被清理。由于Undo日志被集中存儲到某一個回滾段，所以清理也較為便捷。

4. 多進程VS多線程

PostgreSQL采用的是多進程架構(gòu)。優(yōu)點主要在穩(wěn)定性方面：在于每個連接都有自己的進程，一個進程崩潰不太會影響其他的進程，并且每個進程都有自己的內(nèi)存空間，這可以減少內(nèi)存泄漏或其他問題對整個系統(tǒng)的影響；缺點在于資源消耗更高：由于每個進程都有自己的內(nèi)存空間，這可能導(dǎo)致更高的內(nèi)存使用，并且進程間的上下文切換和進程間的通信開銷更大。

MySQL采用的是多線程架構(gòu)。優(yōu)點在于資源消耗更低：線程共享相同的內(nèi)存空間，這通常導(dǎo)致更低的內(nèi)存使用和更快的上下文切換。并且多線程可以更好的適用多核CPU架構(gòu)處理高并發(fā)問題。多線程架構(gòu)在穩(wěn)定性方面不如多進程，一個線程的問題可能會影響到同一進程中的其他線程。文章來源地址http://www.zghlxwxcb.cn/news/detail-837784.html

到了這里，關(guān)于PostgreSQL初體驗及其與MySQL的對比的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！