搭建PostgreSQL主從架構 - Elastic Compute Service

PostgreSQL是一個開來源資料庫，以其強大的功能、標準合規性、可靠性和擴充性受到廣泛認可和採用。PostgreSQL遵循SQL標準，支援NoSQL資料類型（JSON/XML/hstore）遞迴查詢、全文檢索搜尋、GIS地理資訊處理等，能夠應對各種複雜資料分析需求。廣泛應用於互連網網站、企業級應用後端、資料倉儲與分析平台、位置應用系統、複雜資料對象處理等應用情境。本文檔介紹如何在CentOS 7.x作業系統的ECS執行個體上搭建PostgreSQL主從架構。

準備工作

本教程適用於熟悉Elastic Compute Service、Linux系統、PostgreSQL的阿里雲使用者。

搭建PostgreSQL主從複製架構，需要建立2台專用網路類型的ECS執行個體，一台ECS執行個體作為主節點，另一台ECS執行個體作為從節點。建立的ECS執行個體需要滿足以下條件，更多參數說明，請參見自訂購買執行個體。

作業系統：PostgreSQL支援多種作業系統，本文以CentOS 7.x為例。
Alibaba Cloud Linux 3/2不支援本文使用的YUM源安裝方式，建議您使用源碼安裝。更多其他動作系統安裝方式，請參見PostgreSQL官網。
執行個體規格：PostgreSQL主從架構並沒有嚴格的規格限制，具體的規格選擇應基於詳細的容量規劃、效能測試和業務增長預測。
公網IP：建議您不為ECS執行個體分配公網IP，按需購買Elastic IP Address綁定至ECS執行個體，後續您可以根據實際情況考慮升級配置或調優架構。詳情請參見申請Elastic IP Address。
安全性群組：已在安全性群組內添加入方向規則允許存取80、22、5432連接埠。具體操作，請參見添加安全性群組規則。

步驟一：配置PostgreSQL主節點

遠端連線PostgreSQL主節點。
具體操作，請參見通過密碼或密鑰認證登入Linux執行個體。
安裝PostgreSQL。
1. 運行以下命令，添加PostgreSQL官方YUM倉庫。
```
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
```
2. 安裝PostgreSQL 15。
```
sudo yum install -y postgresql15-server
```
  說明
  本教程以PostgreSQL 15版本為例。您可以根據業務需求選擇合適的版本。
初始化PostgreSQL資料庫。
```
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
```
回顯如下所示時，說明PostgreSQL資料庫已初始化完成。
依次運行以下命令，啟動服務並設定服務開機自啟動。
```
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15
```
在主節點上建立資料庫帳號replica（用於主從複製），並設定密碼及登入許可權和備份許可權。
1. 運行以下命令，登入postgres使用者。
```
sudo su - postgres
```
  回顯結果出現-bash-4.2$時表示成功登入。
2. 輸入以下命令，進入PostgreSQL互動終端。
```
psql
```
  回顯結果出現postgres=#時表示成功進入互動終端。
3. 運行以下命令，為使用者postgres設定密碼，增強安全性。
```
ALTER USER postgres WITH PASSWORD 'YourPassWord';
```
4. 輸入以下SQL語句建立資料庫帳號，並設定密碼、登入許可權和備份許可權。
```
CREATE ROLE user login replication encrypted password 'YourPassWord';
```
  本樣本中建立資料庫帳號replica，並將密碼設定為replica。
```
CREATE ROLE replica login replication encrypted password 'replica';
```
5. 查詢帳號是否建立成功。
```
SELECT usename from pg_user;
```
  返回結果如下，表示已建立成功。
6. 查詢許可權是否建立成功。
```
SELECT rolname from pg_roles;
```
  返回結果如下，表示已建立成功。
7. 輸入以下命令，按Enter鍵退出SQL終端。
```
\q
```
8. 輸入以下命令，按Enter鍵退出PostgreSQL。
```
exit
```

設定replica使用者白名單。

運行以下命令，開啟pg_hba.conf檔案。
```
sudo vim /var/lib/pgsql/15/data/pg_hba.conf
```

按i鍵進入編輯模式，在IPv4 local connections段添加下面兩行內容。

host    all             all             <從節點的VPC IPv4網段>          md5     #允許VPC網段中md5密碼認證串連
host    replication     replica         <從節點的VPC IPv4網段>          md5     #允許使用者從replication資料庫進行資料同步

按Esc鍵，輸入:wq，按下Enter鍵，儲存並退出pg_hba.conf檔案。

配置postgresql.conf檔案。

運行以下命令，開啟postgresql.conf檔案。
```
sudo vim /var/lib/pgsql/15/data/postgresql.conf
```

分別找到以下參數，並將參數修改為以下內容：

listen_addresses = '*'   #監聽的IP地址
max_connections = 100    #最大串連數，從庫的max_connections必須要大於主庫的
wal_level = replica      #記錄足夠的資訊以支援常見的備庫用途，包括流複製和熱備。
synchronous_commit = on  #開啟同步複製
max_wal_senders = 32     #同步最大的進程數量
wal_sender_timeout = 60s #流複製主機發送資料的逾時時間

按Esc鍵，輸入:wq，按下Enter鍵，儲存並退出postgresql.conf檔案。

運行以下命令，重啟PostgreSQL服務。
```
sudo systemctl restart postgresql-15
```

步驟二：配置PostgreSQL從節點

遠端連線PostgreSQL從節點。
具體操作，請參見通過密碼或密鑰認證登入Linux執行個體。
安裝PostgreSQL。
1. 運行以下命令，添加PostgreSQL官方YUM倉庫。
```
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
```
2. 安裝PostgreSQL 15。
```
sudo yum install -y postgresql15-server
```
  說明
  本教程以PostgreSQL 15版本為例。您可以根據業務需求選擇合適的版本。
運行以下命令，使用pg_basebackup基礎備份工具從主庫建立從庫的基礎備份。
```
pg_basebackup -D /var/lib/pgsql/15/data -h <主節點公網IP> -p 5432 -U replica -X stream -P
```
出現Password時，輸入主庫的密碼。出現如下所示時，表示已同步成功。

配置從庫的postgresql.conf檔案。

運行以下命令，開啟postgresql.conf檔案。
```
sudo vim /var/lib/pgsql/15/data/postgresql.conf
```

分別找到以下參數，並將參數修改為以下內容。

primary_conninfo = 'host=<主節點IP> port=5432 user=replica password=replica' #對應主庫的串連資訊
recovery_target_timeline = 'latest' #流複製同步到最新的資料
max_connections = 1000             # 最大串連數，從節點需設定比主節點大
hot_standby = on                   # 開啟熱備
max_standby_streaming_delay = 30s  # 資料流備份的最大延遲時間
wal_receiver_status_interval = 1s  # 從節點向主節點報告自身狀態的最長間隔時間
hot_standby_feedback = on          # 如果有錯誤的資料複製向主進行反饋

按Esc鍵，輸入:wq，按下Enter鍵，儲存並退出postgresql.conf檔案。

運行以下命令，修改資料目錄的屬組和屬主。
```
sudo chown -R postgres.postgres /var/lib/pgsql/15/data
```
依次運行以下命令，啟動服務並設定服務開機自啟動。
```
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15
```

步驟三：檢測驗證

檢測驗證需要主從節點之間存在資料互動，例如，從節點備份目錄時，進行檢測能夠得到預期的結果。

sudo pg_basebackup -D /var/lib/pgsql/15/data -h <主節點IP> -p 5432 -U replica -X stream -P

在主節點中運行以下命令，查看sender進程。

ps aux |grep sender

返回結果如下，表示可以成功查看到sender進程。

postgres  2916  0.0  0.3 340388  3220 ?        Ss   15:38   0:00 postgres: walsender  replica 192.168.**.**(49640) streaming 0/F01C1A8

在從節點中運行以下命令，查看receiver進程。
```
ps aux |grep receiver
```
返回結果類似如下，表示可以成功查看到receiver進程。
```
postgres 23284  0.0  0.3 387100  3444 ?        Ss   16:04   0:00 postgres: walreceiver   streaming 0/F01C1A8
```

在主節點中進入PostgreSQL互動終端，輸入以下SQL語句，在主庫中查看從庫狀態。

運行以下命令，登入postgres使用者。
```
sudo su - postgres
```
回顯結果出現-bash-4.2$時表示成功登入。
輸入以下命令，進入PostgreSQL互動終端。
```
psql
```
回顯結果出現postgres=#時表示成功進入互動終端。

運行以下命令，在主庫中查看從庫狀態。

select * from pg_stat_replication;

返回結果類似如下，表示可成功查看到從庫狀態。

pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_locati
on | flush_location | replay_location | sync_priority | sync_state 
------+----------+---------+------------------+---------------+-----------------+------------- +-------------------------------+--------------+-----------+---------------+-------------
---+----------------+-----------------+---------------+------------
2916 | 16393 | replica | walreceiver | 192.168.**.** | | 49640 | 2017-05-02 15:38:06.188988+08 | 1836 | streaming | 0/F01C0C8 | 0/F01C0C8 
| 0/F01C0C8 | 0/F01C0C8 | 0 | async
(1 rows)

輸入以下命令，並按Enter退出SQL終端。
```
\q
```
輸入以下命令，並按Enter退出PostgreSQL。
```
exit
```