Postgres 流複製配置

wongchaofan發表於2024-07-22

主要 - 現有測試伺服器：
=======
主機名：nijam-1
IP 地址：53.99.198.11

次要 - 新測試伺服器
=========
主機名：nijam-2
IP 地址：53.99.198.12

步驟 1. 停止從屬伺服器

 /optt/10.5/bin/pg_ctl -D  /DATA stop

第 2 步，將從屬資料目錄移動到備份位置：

mv /DATA/* /tmp/backup

步驟 3. 在主 postgresql.conf 和 pg_hba.conf 檔案上更改以下引數。

listen_addresses = 'localhost,53.99.198.11'  
wal_level = replica                     # minimal, replica, or logical
archive_mode = on             #啟用歸檔
archive_command =  'cp %p /ARCHIVES/%f'   #歸檔命令
max_wal_senders = 10      
wal_keep_segments = 25

On pg_hba.conf:

host    replication     postgres        53.99.198.12/24          trust #信任主機
host    replication     postgres        53.99.198.11/24          trust

步驟4.重新啟動主伺服器：

/optt/10.5/bin/pg_ctl -D  /DATA restart

第五步、進行一致性備份：

psql -c "select pg_start_backup('initial_backup');"
rsync -cva --inplace /DATA/* postgres@53.99.198.12:/DATA/ #資料傳輸至從庫
psql -c "select pg_stop_backup();"

步驟 5. 在從屬伺服器上更改postgresql.conf 上的以下引數：

listen_addresses = 'localhost,53.99.198.12'  #監聽伺服器
wal_level = replica                     # minimal, replica, or logical
archive_mode = on 
archive_command = '/bin/cp -av %p /ARCHIVES/%f'
max_wal_senders = 10 
wal_keep_segments = 25
hot_standby = on        #熱備

on pg_hba.conf:

host    replication     postgres        53.99.198.12/24          trust
host    replication     postgres        53.99.198.11/24          trust

On recovery.conf :

standby_mode = 'on'      # to enable the standby (read-only) mode.     
primary_conninfo = 'host=53.99.198.11 port=5444 user=postgres'  
                         # to specify a connection info to the master node.
trigger_file = '/tmp/pg_failover_trigger'
                         # to specify a trigger file to recognize a fail over. 指定一個觸發器檔案來識別故障轉移。
restore_command = 'cp /ARCHIVES/%f "%p"'
archive_cleanup_command = '/optt/10.5/bin/pg_archivecleanup /ARCHIVES/%r'  #歸檔清除

步驟6.重新啟動從伺服器：

/optt/10.5/bin/pg_ctl -D  /DATA start

如果您遇到任何錯誤，例如存檔丟失或 wal 日誌序列丟失...請獲取丟失的存檔/wall 備份並將其移動到從伺服器，否則請對 postgres 主伺服器進行全新備份。

以下指令碼將幫助您獲取存檔和 xlog 備份

psql -c "select pg_start_backup('initial_backup');"   #打個開始備份的標籤
rsync -cva --inplace --exclude=pg_hba.conf --exclude=postgresql.conf --exclude=recovery.conf /DATA/* postgres@53.99.198.12:/DATA      #同步資料檔案到從庫
rsync -cva /DATA/xlog/* postgres@53.99.198.12:/DATA/xlog/
rsync -cva /ARCHIVES/* postgres@53.99.198.12:/ARCHIVES
psql -c "select pg_stop_backup();"    結束備份

步驟 7. 監控流複製：

在 MASTER 上：

1. 建立一些虛擬表，檢查從屬伺服器是否正確複製。

postgres=# create table t(id int);
CREATE TABLE

-- 檢查主伺服器是否處於恢復模式，

 postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)

主伺服器不會處於恢復模式，只有從伺服器才會處於恢復模式。

2.. 使用 pg_stat_replication 檢視

postgres=# select client_addr,client_hostname,client_port,state,sent_lsn,write_lsn,flush_lsn,replay_lsn,write_lag,replay_lag,flush_lag,sync_state from pg_stat_replication;
 client_addr | client_hostname | client_port |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | replay_lag | flush_lag | sync_state
-------------+-----------------+-------------+-----------+-----------+-----------+-----------+------------+-----------+------------+-----------+------------
 192.168.2.3 |                 |       60000 | streaming | 0/8017A28 | 0/8017A28 | 0/8017A28 | 0/8017A28  |           |            |           | async
(1 row)

3.使用linux命令檢查wal sender程序是否啟動：

[postgres@mster ~]$ ps -ef|grep postgres
root      82472  82437  0 08:34 pts/1    00:00:00 su postgres
postgres  82473  82472  0 08:34 pts/1    00:00:00 bash
root      94549  94494  0 20:36 pts/3    00:00:00 su postgres
postgres  94550  94549  0 20:36 pts/3    00:00:00 bash
postgres  94582      1  0 20:36 pts/3    00:00:00 /optt/10.5/bin/postgres -D /DATA/
postgres  94584  94582  0 20:36 ?        00:00:00 postgres: logger process
postgres  94586  94582  0 20:36 ?        00:00:00 postgres: checkpointer process
postgres  94587  94582  0 20:36 ?        00:00:00 postgres: writer process
postgres  94588  94582  0 20:36 ?        00:00:00 postgres: wal writer process
postgres  94589  94582  0 20:36 ?        00:00:00 postgres: autovacuum launcher process
postgres  94591  94582  0 20:36 ?        00:00:00 postgres: stats collector process
postgres  94592  94582  0 20:36 ?        00:00:00 postgres: bgworker: logical replication launcher
postgres  94741  94582  0 20:43 ?        00:00:00 postgres: wal sender process postgres 192.168.2.3(60000) streaming 0/8017B08
postgres  95178  94550  0 21:08 pts/3    00:00:00 ps -ef
postgres  95179  94550  0 21:08 pts/3    00:00:00 grep --color=auto postgres

-- 監控當前的牆壁序列。

$ ps -ef|grep sender
postgres   7585   3383  0 15:59 ?        00:00:00 postgres: wal sender process postgres 192.168.2.2(42586) streaming 0/18017CD8
postgres   7598   6564  0 15:59 pts/2    00:00:00 grep --color=auto sender

在從屬伺服器上：

1.檢查先前建立的表是否被複制

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

yes! it is successfully replicated the table.

-- Checking server mode.

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)

-- 嘗試在從屬伺服器上載入資料，從屬伺服器處於只讀模式，不允許載入資料。

postgres=# insert into t values(1);
ERROR:  cannot execute INSERT in a read-only transaction

2.使用 pg_stat_replication 檢視：

postgres=# select status,receive_start_lsn,received_lsn,last_msg_send_time,latest_end_lsn,latest_end_time,conninfo from pg_stat_wal_receiver ;
  status   | receive_start_lsn | received_lsn |        last_msg_send_time        | latest_end_lsn |         latest_end_time          |                                                           conninfo
-----------+-------------------+--------------+----------------------------------+----------------+----------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 streaming | 0/6000000         | 0/8017B08    | 2018-05-26 21:26:34.577733+05:30 | 0/8017B08      | 2018-05-26 21:06:32.309548+05:30 | user=postgres password=******** dbname=replication host=192.168.2.2 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any
(1 row)

3. 使用 linux 命令，您可以監視 wal 接收器是否已啟動。此 linux 命令將有助於找出 postgres 資料目錄以及 postgres 實用程式路徑（即 bin 路徑）

[postgres@slave data]$ ps -ef|grep postgres
root       3971   3573  0 10:27 pts/0    00:00:00 su postgres
postgres   3972   3971  0 10:27 pts/0    00:00:00 bash
root      45410  45321  0 20:36 pts/1    00:00:00 su postgres
postgres  45411  45410  0 20:36 pts/1    00:00:00 bash
postgres  45606      1  0 20:43 pts/1    00:00:00 /optt/10.5/bin/postgres -D /DATA/
postgres  45607  45606  0 20:43 ?        00:00:00 postgres: logger process
postgres  45608  45606  0 20:43 ?        00:00:00 postgres: startup process   recovering 000000010000000000000008
postgres  45610  45606  0 20:43 ?        00:00:00 postgres: checkpointer process
postgres  45611  45606  0 20:43 ?        00:00:00 postgres: writer process
postgres  45612  45606  0 20:43 ?        00:00:06 postgres: wal receiver process   streaming 0/8017B08
postgres  45613  45606  0 20:43 ?        00:00:00 postgres: stats collector process
postgres  45995  45411  0 21:16 pts/1    00:00:00 ps -ef
postgres  45996  45411  0 21:16 pts/1    00:00:00 grep --color=auto postgres

4.此命令將有助於獲取多少個 wal 段 postgres 接收器

[postgres@slave data]$ ps -ef|grep receiver;
postgres  45612  45606  0 20:43 ?        00:00:06 postgres: wal receiver process   streaming 0/8017B08
postgres  46018  45411  0 21:18 pts/1    00:00:00 grep --color=auto receiver

5. 如果從屬伺服器處於熱備用模式，則可以使用以下查詢瞭解在從屬伺服器上應用事務的延遲時間（以秒為單位）：

postgres = # select now() - pg_last_xact_replay_timestamp() AS replication_delay; 
 replication_delay 
------------------- 
 00：18：35.207663 
（1 行）

相關文章