【IT Notes】在Ubuntu20.04上安裝PostgreSQL資料庫可主從複寫與切換的Cluster

過年期間有研究一下如何使用PostgreSQL，聽說是個不錯用的資料庫，實際操作過後也確實好用，指令和核心都有公開資料可循，也算是很好理解，就把這花費幾天的研究成果記錄下來，也許將來有機會用到。

一‧事前準備

三台VM，基本配備：

4核心CPU、4GB RAM、50GB硬碟

node1:10.113.112.84   (Primary)

node2:10.113.112.85   (Standby)

node3:10.113.112.86   (Standby)

（要先確認彼此都能ping或ssh得到）

二．安裝軟體及套件

三台node都先安裝postgresql和repmgr

$ sudo apt update

$ sudo apt -y install vim bash-completion wget

$ sudo apt -y upgrade

$ sudo apt -y install postgresql-12 postgresql-client-12

$ sudo apt -y install postgresql-12-repmgr

$ service postgresql status

$ service repmgrd status

（repmgrd是主從切換的重要套件，必須安裝才能做failover測試）

三．node1(Primary)建立複寫用帳號及資料庫

--第一台主機先作為Primary node，主要擔任資料寫入工作。

$ sudo -i -u postgres                \\切換資料庫預設administrator:postgres

$ createuser --replication --createdb --createrole --superuser repmgr  \\建立一個複寫用途的user 

$ psql -c 'ALTER USER repmgr SET search_path TO repmgr_test, $user", public;'

$ createdb repmgr --owner=repmgr                                                        \\建立一個複寫用途的db

--(可以直接在外部用指令建立user和db)

四．各台node配置複寫用設定

1.修改基本設定檔的參數(用root)

# vim /etc/postgresql/12/main/postgresql.conf

listen_addresses = '*'                                                 \\星號是指全通，嚴謹的設定只需要設定彼此node的ip 

max_wal_senders = 10

max_replication_slots = 10

wal_level = 'replica'                                                   \\舊版的此設定參數是hot_standby，不建議再使用

wal_log_hints = on

hot_standby = on                                                       \\重要的設定！有此參數的node才有機會啟動複寫

archive_mode = on

archive_command = '/bin/true'

shared_preload_libraries = 'repmgr'

2.修改連線設定檔的參數(用root)

# vim /etc/postgresql/12/main/pg_hba.conf

--在最下面新增互連用的設定

local   replication   repmgr                                       trust

host    replication   repmgr      127.0.0.1/32             trust

host    replication   repmgr      10.113.112.0/24       trust

local   repmgr        repmgr                                       trust

host    repmgr        repmgr      127.0.0.1/32             trust

host    repmgr        repmgr      10.113.112.0/24       trust

3.測試各台node互連狀況

--上面設定完後要先重啟資料庫服務

# systemctl enable postgresql-12.service

# systemctl restart postgresql-12.service

$ su – postgres                                              \\用psql得要先切換回postgres

$ psql ‘host=10.113.112.84 dbname=repmgr user=repmgr’

$ psql ‘host=10.113.112.85 dbname=repmgr user=repmgr’

$ psql ‘host=10.113.112.86 dbname=repmgr user=repmgr’

--(正常的話各台node都能夠訪問彼此的資料庫)

五．各台node配置複寫用工具的參數

1.確認repmgrd工具已可以啟用（用root）

# vim /etc/default/repmgrd

REPMGRD_ENABLED=yes                                               \\啟用複寫套件

REPMGRD_CONF="/etc/repmgr.conf"                               \\重要！指定複寫工具配置檔的位置

2.自行新增並配置複寫工具（用root）

$vim /etc/repmgrd.conf                  \\預設沒有此檔案，自己新增

node_id=1                                      \\ node號碼，另兩台是2和3，不會隨便變化

node_name=node1                       \\ node 名稱，另兩台是node2和node3，主從複寫的參考值

conninfo='host=10.113.112.84 user=repmgr dbname=repmgr connect_timeout=2'

data_directory='/var/lib/postgresql/12/main'            \\資料庫所有參數的放置處

failover='automatic'                          \\ failover後自動切換

priority=100                                       \\ node的地位順序，node1設定100最高、node2設定80、node2設定40

connection_check_type=ping

reconnect_attempts=6

reconnect_interval=10

monitoring_history=yes

monitor_interval_secs=2

standby_disconnect_on_failover=true

primary_visibility_consensus=true

log_status_interval=60

promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'   \\ failover發生後，standby node要升等的指令

follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file'  \\ failover發生後，standby node要跟隨新primary的指令

log_file='/var/log/postgresql/repmgr.log'

log_level=NOTICE

service_start_command = 'systemctl start postgresql'  \\重要的參數，之前測試node沒有跟隨新primary，研究半天就是少了以下

service_stop_command = 'systemctl stop postgresql'

service_restart_command = 'systemctl restart postgresql'

service_reload_command = 'systemctl reload postgresql'

repmgrd_service_start_command = 'systemctl start repmgr'

repmgrd_service_stop_command = 'systemctl stop repmgr'

六．各node註冊身份

1.node1註冊primary（切換postgres）

$ repmgr primary register

--檢查是否註冊成功

$ repmgr cluster show

2.node2和3註冊standby（切換postgres）

$ cd /var/lib/postgresql/12/main

$ rm -rf *   \\先將本身的instance全部移除

$ repmgr -h 10.113.112.84 -U repmgr -d repmgr standby clone --dry-run \\先預演複製node1的instance

$ repmgr -h 10.113.112.84 -U repmgr -d repmgr standby clone           \\正式複製node1的instance

$ exit                                                                                                       \\換回root

# systemctl restart postgresql                                                                 \\重啟資料庫

# su - postgres                                                                                        \\換回postgres

$ repmgr standby register

--檢查是否註冊成功

$ repmgr cluster show

--接下來就會看到資料庫複寫狀態呈現這樣

(standby的資料庫都是read-only，不能夠寫入資料)

七．測試failover主從切換

1.關閉node1的資料庫

$ service postgresql stop

2.node2切換primary

--從node1觀看，這時候會看到三台node的狀態改變了，node2出現異動的驚嘆號，node2升等為primary，node3 follow的對象仍然還是node1

*(如果系統沒有自動切換時，用以下兩種指令)

$ repmgr standby promote           \\ repmgr primary register 直接升級為primary

--從node2查看狀態，發現node1變成驚嘆號、node2升等為primary

3.重啟node1並轉換成standy

$ sudo -i

# service postgresql start

# cd /var/lib/postgresql/12/main

# rm -rf * 

# repmgr -h 10.113.112.85 -U repmgr -d repmgr standby clone --dry-run

# repmgr -h 10.113.112.85 -U repmgr -d repmgr standby clone

#systemctl restart postgresql           \\複製instance後資料庫一定要再重啟，下一步註冊才會生效

$ su - postgres

$ repmgr standby register        \\node1不會自動降級為standby，必須重新手動註冊

$ repmgr standby follow       \\node1變成standby後，必須改跟隨node2

--任挑一台node查看狀態，node1變成standby了

4.將node3的follow對象轉換成node2

# su - postgres

$ repmgr standby follow

最終結果：node2變成新的primary，其他兩個standby node的upstream也都變成node2。

最後跟隨的部分我還沒有研究出更自動化的做法，正常來說應該要在failover發生後每台node都能自己去追隨新的primary，不過已經先達成我要的結果了，就先告一段落。