主要 - 現有測試伺服器：
=======
主機名：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 行）