數據庫

PostgreSQL DBA(31) – Backup&Recovery#4(搭建流復制)

PostgreSQL通過流復制Streaming Replication可輕松實現高可用HA環境的搭建.本節簡單介紹了搭建流復制環境的基本步驟.

Step 1 主庫:創建用戶 
創建復制用戶replicator

testdb=# CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'replicator';CREATE ROLE 

Step 2 主庫:參數配置 
配置archive_mode等參數

archive_mode = ONwal_level = replicamax_wal_senders = 10archive_command = '/home/xdb/archive.sh %p %f'listen_addresses = '*' 

也可用alter system命令修改

ALTER SYSTEM SET wal_level TO 'replica';ALTER SYSTEM SET archive_mode TO 'ON';ALTER SYSTEM SET max_wal_senders TO '10';ALTER SYSTEM SET listen_addresses TO '*'; 

重啟數據庫

pg_ctl -D $PGDATA restart -mf

Step 3 主庫:訪問配置 
修改pg_hba.conf文件

host replication replicator 192.168.26.26/32 md5

生效配置

pg_ctl -D $PGDATA reload

Step 4 從庫:從主庫備份中恢復 
在從庫上使用pg_basebackup創建備庫 
192.168.26.25是主庫IP,192.168.26.26是從庫IP

pg_basebackup -h 192.168.26.25 -U replicator -p 5432 -D $PGDATA -P -Xs -R

配置從庫postgres.conf

hot_standby = ONhot_standby_feedback = ONALTER SYSTEM SET hot_standby TO 'ON';ALTER SYSTEM SET hot_standby_feedback TO 'ON'; 

配置從庫recovery.conf

$ cat $PGDATA/recovery.confstandby_mode = 'on'primary_conninfo = 'host=192.168.26.25 port=5432 user=replicator password=replicator'restore_command = 'cp /data/archivelog/%f %p'archive_cleanup_command = 'pg_archivecleanup /data/archivelog %r' 

Step 5 從庫:啟動數據庫

[[email protected] testdb]$ pg_ctl -D $PGDATA startwaiting for server to start....2019-03-13 12:13:30.239 CST [1870] LOG:  listening on IPv4 address "0.0.0.0", port 54322019-03-13 12:13:30.239 CST [1870] LOG:  listening on IPv6 address "::", port 54322019-03-13 12:13:30.252 CST [1870] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"2019-03-13 12:13:30.379 CST [1870] LOG:  redirecting log output to logging collector process2019-03-13 12:13:30.379 CST [1870] HINT:  Future log output will appear in directory "pg_log". doneserver started 

Step 6 驗證復制環境 
確認相關進程是否已啟動

#主庫[[email protected] testdb]$ ps -ef|grep senderxdb       1646  1532  0 12:13 ?        00:00:00 postgres: walsender replicator 192.168.26.26(35294) streaming 0/43000140xdb       1659  1440  0 12:17 pts/1    00:00:00 grep --color=auto sender[[email protected] testdb]$ #從庫[[email protected] testdb]$ ps -ef|grep receiverxdb       1879  1870  0 12:13 ?        00:00:00 postgres: walreceiver   streaming 0/43000140xdb       1884  1799  0 12:18 pts/0    00:00:00 grep --color=auto receiver[[email protected] testdb]$ ps -ef|grep startupxdb       1872  1870  0 12:13 ?        00:00:00 postgres: startup   recovering 000000100000000000000043xdb       1887  1799  0 12:18 pts/0    00:00:00 grep --color=auto startup[[email protected] testdb]$ 

Step 7 監控 
查詢pg_stat_replication數據字典表

testdb=# SELECT * FROM pg_stat_replication;-[ RECORD 1 ]----+------------------------------pid              | 1646usesysid         | 90113usename          | replicatorapplication_name | walreceiverclient_addr      | 192.168.26.26client_hostname  | client_port      | 35294backend_start    | 2019-03-13 12:13:30.852269+08backend_xmin     | state            | streamingsent_lsn         | 0/43000140write_lsn        | 0/43000140flush_lsn        | 0/43000140replay_lsn       | 0/43000140write_lag        | flush_lag        | replay_lag       | sync_priority    | 0sync_state       | asynctestdb=# 

同步復制 
從庫配置參數recovery.conf,在primary_conninfo中添加application_name

primary_conninfo = 'user=replicator password=replicator host=192.168.26.25 port=5432 application_name = standby_26'

主庫配置參數

synchronous_standby_names = 'standby_26'synchronous_commit = on 

重啟數據庫,驗證是否配置成功

testdb=# \xExpanded display is on.testdb=# SELECT * FROM pg_stat_replication;-[ RECORD 1 ]----+------------------------------pid              | 2257usesysid         | 90113usename          | replicatorapplication_name | standby_26client_addr      | 192.168.26.26client_hostname  | client_port      | 35418backend_start    | 2019-03-13 15:17:57.330573+08backend_xmin     | 634state            | streamingsent_lsn         | 0/54D4DBD0write_lsn        | 0/54D4DBD0flush_lsn        | 0/54D4DBD0replay_lsn       | 0/54D4DBD0write_lag        | 00:00:00.00101flush_lag        | 00:00:00.001954replay_lag       | 00:00:00.002145sync_priority    | 1sync_state       | sync 

參考資料 
pgbasebackup 
Setting up Streaming Replication in PostgreSQL

我還沒有學會寫個人說明!

我當測試總監的那幾年

上一篇

MySQL Batched Key Access (BKA)原理和設置使用方法舉例

下一篇

你也可能喜歡

PostgreSQL DBA(31) – Backup&Recovery#4(搭建流復制)

長按儲存圖像,分享給朋友

ITPUB 每周精要將以郵件的形式發放至您的郵箱


微信掃一掃

微信掃一掃
重庆百变王牌开奖结果