PostgreSql主從實現(xiàn)（異步流復制）

一、前期準備

1.1 環(huán)境準備：

1.2 主庫測試數(shù)據(jù)

# a. 顯示數(shù)據(jù)庫
postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

# b. 創(chuàng)建表
postgres=# create table t1(id int,name varchar(50));
CREATE TABLE
----
- id int：定義了一個名為id的字段，數(shù)據(jù)類型為int。
- name varchar(50)：定義了一個名為name的字段，數(shù)據(jù)類型為varchar，最大長度為50。
----

# c. 顯示表結構
postgres=# \d t1
                      Table "public.t1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 id     | integer               |           |          |
 name   | character varying(50) |           |          |

#d. 插入數(shù)據(jù)
postgres=# insert into t1 values (01,'kyle'),(02,'rocky');
INSERT 0 2
-----
- 在INSERT INTO語句中，每個字段值之間需要用逗號（,）分隔
- 在SQL中，字符串值需要用單引號（''）括起來
- 插入的值數(shù)量必須與字段數(shù)量一致，并且順序要匹配。
-----

#e. 查看表數(shù)據(jù)
postgres=# select * from t1 ;
 id | name
----+-------
  1 | kyle
  2 | rocky
(2 rows)

二、主庫操作

2.1 創(chuàng)建用于數(shù)據(jù)流的用戶

$ psql -h 127.0.0.1 -p 5432 -U postgres -W
Password: 
psql (14.10)
Type "help" for help.

postgres=# create role repl login replication encrypted password 'oLfex^5pfe';
CREATE ROLE
postgres=# \q

2.2 配置登錄權限

• 修改配置文件：$PGDATA/pg_hba.conf
  

2.3 配置postgresql.conf

wal_level = replica  # 開啟足夠級別的 WAL（Write Ahead Log）日志，用于支持復制。
    ? 可選值：
    minimal：最小化 WAL（不支持復制）
    replica（默認）：支持流復制
    logical：用于邏輯復制（發(fā)布訂閱）
fsync = on # 保證數(shù)據(jù)安全
max_wal_senders = 10  # 設置最大并發(fā) WAL 發(fā)送進程數(shù)，決定最多能有多少個從庫連接進行流復制。
   ?注意：如果設置，從庫這個值需要同主庫這個值相同或者比這個值大，否則會失敗?
hot_standby = on  # 參數(shù)用于啟用熱備功能，允許從服務器（standby）在同步過程中接受查詢請求。
wal_receiver_status_interval = 10s # 控制 walreceiver 進程向主服務器發(fā)送心跳消息的時間間隔
hot_standby_feedback = on # 控制備用服務器是否會向主服務器發(fā)送關于自己的復制狀態(tài)和進度的信息
archive_mode = on（可選,根據(jù)數(shù)據(jù)庫大小選擇，若太大，不建議配置）
    ? 作用：
    啟用 WAL 日志歸檔，用于災難恢復或從庫搭建失敗后的修復。
    ? 配合使用：
    需要設置 archive_command，如：
    archive_command = 'cp %p /var/lib/postgresql/archive/%f'

2.4 重啟數(shù)據(jù)庫

su - postgres
pg_ctl restart

或
systemctl restart postgresql

三、從庫操作(異步)

3.1 創(chuàng)建從庫數(shù)據(jù)目錄

su - postgres
# 根據(jù)自己數(shù)據(jù)目錄而定
sudo rm -rf /var/lib/postgresql/14/main
sudo mkdir -p /var/lib/postgresql/14/main/pg_wal && \
sudo chown -R postgres. /var/lib/postgresql/14/main/pg_wal && \
sudo chmod 700 /var/lib/postgresql/14/main/pg_wal

3.2 基礎備份

pg_basebackup -h 10.0.0.8  -p 5432 -U repl --pgdata=/var/lib/postgresql/14/main  --write-recovery-conf --progress --verbose

3.3 從庫相關配置

所有節(jié)點確認該參數(shù)，如沒有則添加

cat <<'EOF' | tee -a $PGDATA/postgresql.conf > /dev/null
# 備用服務器相關設置
hot_standby = on # 控制是否允許備用服務器在進行熱備份時同時處理讀取查詢
wal_receiver_status_interval = 10s # 控制 walreceiver 進程向主服務器發(fā)送心跳消息的時間間隔
hot_standby_feedback = on # 控制備用服務器是否會向主服務器發(fā)送關于自己的復制狀態(tài)和進度的信息

EOF

3.4 連接主庫配置

3.5 創(chuàng)建從庫標識文件

ls $PGDATA/standby.signal &> /dev/null || touch $PGDATA/standby.signal

3.6 啟動服務

從庫操作

pg_ctl start
或
 systemctl restart postgresql

3.7 查看日志

從庫操作

cd $PGDATA/logs/
tail -f postgresql-`date "+%Y-%m-%d"`.log

3.8 查看表數(shù)據(jù)

postgres@pgbak:~/14/main/logs$ psql
psql (14.18 (Ubuntu 14.18-1.pgdg22.04+1))
Type "help" for help.

postgres=# \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | t1   | table | postgres
 public | t2   | table | postgres
(2 rows)

postgres=# select * from t1;
 id | name
----+-------
  1 | kyle
  2 | rocky
(2 rows)

?? 對比主庫數(shù)據(jù)

四、從庫操作（同步）

4.1 基礎配置

按照上面異步流復制操作一遍

4.2 連接主庫配置

從庫操作

cat $PGDATA/postgresql.auto.conf
primary_conninfo = 'host=192.168.32.127 port=5432 user=repl application_name=pg01'

添加 application_name 參數(shù)

4.3 從庫相關配置

所有節(jié)點確認該參數(shù)，如沒有則添加

cat <<'EOF' | tee -a $PGDATA/postgresql.conf > /dev/null
# 同步(實時)復制配置
synchronous_commit = on # 控制事務提交的同步方式。該參數(shù)決定了在事務提交時是否等待數(shù)據(jù)同步到磁盤上
synchronous_standby_names = 'FIRST 2(pg01, pg03)' # 該參數(shù)指定了在主服務器提交事務時，需要等待哪些熱備服務器將數(shù)據(jù)同步到磁盤上

EOF

參數(shù) synchronous_standby_names 的值說明：

• FIRST num (name1, name2)：前面num臺為同步服務器，其他為異步服務器
• ANY num (name1, name2)：從括號中選擇num臺為同步服務器，其他為異步服務器

其中括號里面的 name1、name2 為 3.2步驟 對應的 application_name 值

4.4 重新加載配置

所有節(jié)點

pg_ctl reload

五、驗證

• 主庫執(zhí)行，打印從庫信息則說明搭建成功

psql -h localhost -p 5432 -U postgres -W postgres -c "select pid,usename,application_name,client_addr,state,sync_state,sync_priority from pg_stat_replication;"

• sync_state參數(shù)說明

  • async: 這臺服務器是異步的

  • sync: 這臺服務器是同步的

  • potential: 這臺服務器現(xiàn)在是異步的，但可能在當前的同步服務器失效時變成同步的

  • quorum: 這臺服務器被當做規(guī)定數(shù)量后備服務器的候選

六、故障問題

??若同步故障，最終主庫查詢時沒有出現(xiàn)async，可檢查配置文件postgresql.conf,檢查配置中主從不同之處，例如：

max_wal_senders = 10  # 設置最大并發(fā) WAL 發(fā)送進程數(shù)，決定最多能有多少個從庫連接進行流復制。
   ?注意：如果設置，從庫這個值需要同主庫這個值相同或者比這個值大，否則會失敗?

??關于一些sender數(shù)量，size大小配置上，遵從從庫比主庫相同或者更大