基於streaming replication搭建的PostgreSQL HA環境,如出現網路訪問/硬體故障等原因導致Standby節點升級為Master節點,但舊Master節點資料庫並未損壞,在排除故障後舊Master節點是否不需要透過備份方式重新搭建成為Standby節點呢?答案是肯定的,PG提供了pg_rewind這個工具實現.

**原理**
在PostgreSQL HA環境中,Standby節點升級為Master節點後,時間線會切換為新的時間線,比如從1變為2.而舊Master節點的時間線仍然為原來的時間線,比如仍為1,那麼使用pg_rewind工具,舊Master節點如何從新Master節點讀取相關的資料成為新的Standby節點?
簡單來說,有以下幾步:
1.確定新Master和舊Master資料一致性的Checkpoint位置.在該位置上,新Master和舊Master資料完全一致.這可以透過讀取新舊Master節點時間線歷史檔案可以獲得,該檔案位於$PGDATA/pg_wal/目錄下,檔名稱為XX.history
2.舊Master節點根據上一步獲取的Checkpoint讀取本機日誌檔案WAL Record,獲取在此Checkpoint之後出現變化的Block,並以連結串列的方式儲存Block編號等資訊
3.根據第2步獲取的Block資訊從新Master節點複製相應的Block,替換舊Master節點相應的Block
4.複製新Master節點上除資料檔案外的所有其他檔案,包括配置檔案等(如果複製資料檔案,與備份方式搭建區別不大)
5.舊Master啟動資料庫,應用從Checkpoint開始後的WAL Record.

testdb=# 
testdb=# select pg_is_in_recovery();
-[ RECORD 1 ]-----+--
pg_is_in_recovery | f

```
插入測試資料
```
testdb=# create table t_oldmaster(id int);
CREATE TABLE
testdb=# insert into t_oldmaster select generate_series(1,1000000);
INSERT 0 1000000
testdb=# 
```
*切換前26.26節點資訊,資料已複製,角色為Standby*
```
testdb=# select count(*) from t_oldmaster;
  count  
---------
 1000000
(1 row)

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

```
*模擬Master節點當機*
```
[xdb@localhost testdb]$ ps -ef|grep postgres
xdb       1353     1  0 14:46 pts/1    00:00:00 /appdb/xdb/pg11.2/bin/postgres
xdb       1354  1353  0 14:46 ?        00:00:00 postgres: logger   
xdb       1356  1353  0 14:46 ?        00:00:00 postgres: checkpointer   
xdb       1357  1353  0 14:46 ?        00:00:00 postgres: background writer   
xdb       1358  1353  0 14:46 ?        00:00:00 postgres: walwriter   
xdb       1359  1353  0 14:46 ?        00:00:00 postgres: autovacuum launcher  
xdb       1360  1353  0 14:46 ?        00:00:00 postgres: archiver   last was 00000010000000000000006D
xdb       1361  1353  0 14:46 ?        00:00:00 postgres: stats collector   
xdb       1362  1353  0 14:46 ?        00:00:00 postgres: logical replication launcher  
xdb       1418  1353  0 15:11 ?        00:00:02 postgres: xdb testdb [local] idle
xdb       1537  1353  0 15:19 ?        00:00:00 postgres: walsender replicator 192.168.26.26(53164) streaming 0/6ED4FDA8
xdb       1555  1317  0 15:25 pts/1    00:00:00 grep --color=auto postgres
[xdb@localhost testdb]$ kill -9 1353
[xdb@localhost testdb]$ ps -ef|grep postgres
xdb       1557  1317  0 15:25 pts/1    00:00:00 grep --color=auto postgres
[xdb@localhost testdb]$ 
```

*Standby節點執行切換*
編輯recovery.conf檔案,新增trigger_file和recovery_target_timeline
```
[xdb@localhost testdb]$ cat recovery.conf 
standby_mode = 'on'
primary_conninfo = 'host=192.168.26.25 port=5432 user=replicator password=replicator application_name=standby_26'
restore_command = 'cp /data/archivelog/%f %p'
archive_cleanup_command = 'pg_archivecleanup /data/archivelog %r'
trigger_file = '/tmp/trigger_file'
recovery_target_timeline = 'latest'

#生成trigger檔案
[xdb@localhost testdb]$ touch /tmp/trigger_file

```
重啟Standby節點
```
[xdb@localhost testdb]$ pg_ctl restart
pg_ctl: PID file "/data/pgsql/testdb/postmaster.pid" does not exist
Is server running?
trying to start server anyway
waiting for server to start....2019-03-27 15:33:28.218 CST [1901] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-03-27 15:33:28.219 CST [1901] LOG:  listening on IPv6 address "::", port 5432
2019-03-27 15:33:28.223 CST [1901] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-03-27 15:33:28.253 CST [1901] LOG:  redirecting log output to logging collector process
2019-03-27 15:33:28.253 CST [1901] HINT:  Future log output will appear in directory "pg_log".
. done
server started
[xdb@localhost testdb]$ 
```
日誌輸出
```
2019-03-27 15:33:28.259 CST,,,1903,,5c9b2748.76f,2,,2019-03-27 15:33:28 CST,,0,LOG,00000,"entering standby mode",,,,,,,,,""
2019-03-27 15:33:28.264 CST,,,1903,,5c9b2748.76f,3,,2019-03-27 15:33:28 CST,1/0,0,LOG,00000,"redo starts at 0/6B0164C0",,,,,,,,,""
2019-03-27 15:33:29.416 CST,,,1903,,5c9b2748.76f,4,,2019-03-27 15:33:28 CST,1/0,0,LOG,00000,"consistent recovery state reached at 0/6ED4FDA8",,,,,,,,,""
2019-03-27 15:33:29.416 CST,,,1903,,5c9b2748.76f,5,,2019-03-27 15:33:28 CST,1/0,0,LOG,00000,"invalid record length at 0/6ED4FDA8: wanted 24, got 0",,,,,,,,,""
2019-03-27 15:33:29.416 CST,,,1903,,5c9b2748.76f,6,,2019-03-27 15:33:28 CST,1/0,0,LOG,00000,"trigger file found: /tmp/trigger_file",,,,,,,,,""
2019-03-27 15:33:29.416 CST,,,1903,,5c9b2748.76f,7,,2019-03-27 15:33:28 CST,1/0,0,LOG,00000,"redo done at 0/6ED4FD70",,,,,,,,,""
2019-03-27 15:33:29.416 CST,,,1903,,5c9b2748.76f,8,,2019-03-27 15:33:28 CST,1/0,0,LOG,00000,"last completed transaction was at log time 2019-03-27 15:21:57.519456+08",,,,,,,,,""
2019-03-27 15:33:29.416 CST,,,1901,,5c9b2748.76d,2,,2019-03-27 15:33:28 CST,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,,""
2019-03-27 15:33:29.421 CST,,,1903,,5c9b2748.76f,9,,2019-03-27 15:33:28 CST,1/0,0,LOG,00000,"selected new timeline ID: 17",,,,,,,,,""
```
Standby節點已升級為新Master節點
recovery.conf 檔案重新命名為 recovery.done
/tmp/trigger_file檔案被刪除
pg_is_in_recovery返回false
```
[xdb@localhost testdb]$ cat recovery.done 
standby_mode = 'on'
primary_conninfo = 'host=192.168.26.25 port=5432 user=replicator password=replicator application_name=standby_26'
restore_command = 'cp /data/archivelog/%f %p'
archive_cleanup_command = 'pg_archivecleanup /data/archivelog %r'
trigger_file = '/tmp/trigger_file'
recovery_target_timeline = 'latest'
[root@localhost testdb]# ls -l /tmp/trigger_file
ls: cannot access /tmp/trigger_file: No such file or directory
[xdb@localhost testdb]$ psql -d testdb
psql (11.2)
Type "help" for help.

testdb=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)
```
在pg_wal目錄下生成了時間線history檔案
```
[xdb@localhost testdb]$ ls ./pg_wal/
00000010000000000000006E.partial  00000011000000000000006F  000000110000000000000072
00000010.history                  000000110000000000000070  00000011.history
00000011000000000000006E          000000110000000000000071  archive_status
[xdb@localhost testdb]$ cat ./pg_wal/00000011.history 
7	0/27000000	no recovery target specified

8	0/2A0012E8	no recovery target specified

12	0/32000000	no recovery target specified

14	0/39A63BD0	no recovery target specified

15	0/40A63B08	no recovery target specified

16	0/6ED4FDA8	no recovery target specified
[xdb@localhost testdb]$ cat ./pg_wal/00000010.history 
7	0/27000000	no recovery target specified

8	0/2A0012E8	no recovery target specified

12	0/32000000	no recovery target specified

14	0/39A63BD0	no recovery target specified

15	0/40A63B08	no recovery target specified
```
生成測試資料
```
testdb=# create table t_new_master(id int);
CREATE TABLE
testdb=# insert into t_new_master select generate_series(1,1000000);
INSERT 0 1000000
testdb=# 
```

*舊Master節點降級為Standby節點*
重新啟動舊master節點,插入測試資料,用於模擬時間線分叉後的資料變動
```
[xdb@localhost testdb]$ pg_ctl start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2019-03-27 15:39:07.989 CST [1561] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-03-27 15:39:07.989 CST [1561] LOG:  listening on IPv6 address "::", port 5432
2019-03-27 15:39:07.991 CST [1561] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-03-27 15:39:08.015 CST [1561] LOG:  redirecting log output to logging collector process
2019-03-27 15:39:08.015 CST [1561] HINT:  Future log output will appear in directory "pg_log".
. done
server started
[xdb@localhost testdb]$ psql -d testdb
psql (11.2)
Type "help" for help.

testdb=# 
testdb=# create table t_fork(id int);
CREATE TABLE
testdb=# insert into t_fork select generate_series(1,100000);
INSERT 0 100000
testdb=# 
testdb=# \q
[xdb@localhost testdb]$ 
```
關閉舊主庫,執行pg_rewind
```
[xdb@localhost testdb]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[xdb@localhost testdb]$ pg_rewind --target-pgdata=$PGDATA --source-server="host=192.168.26.26 port=5432 dbname=testdb" --progress --debug -n
connected to server
fetched file "global/pg_control", length 8192

target server needs to use either data checksums or "wal_log_hints = on"
Failure, exiting
```
主從設定postgres.conf中的wal_log_hints引數為on,重啟資料庫
```
[xdb@localhost testdb]$ pg_ctl start
waiting for server to start....2019-03-27 15:54:56.038 CST [1603] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-03-27 15:54:56.038 CST [1603] LOG:  listening on IPv6 address "::", port 5432
2019-03-27 15:54:56.042 CST [1603] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-03-27 15:54:56.079 CST [1603] LOG:  redirecting log output to logging collector process
2019-03-27 15:54:56.079 CST [1603] HINT:  Future log output will appear in directory "pg_log".
 done
server started
[xdb@localhost testdb]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
```
再次執行pg_rewind
```
[xdb@localhost testdb]$ pg_rewind --target-pgdata=$PGDATA --source-server="host=192.168.26.26 port=5432 dbname=testdb" --progress --debug -n
connected to server
fetched file "global/pg_control", length 8192
fetched file "pg_wal/00000011.history", length 261
Source timeline history:
Target timeline history:
7: 0/0 - 0/27000000
8: 0/27000000 - 0/2A0012E8
12: 0/2A0012E8 - 0/32000000
14: 0/32000000 - 0/39A63BD0
15: 0/39A63BD0 - 0/40A63B08
16: 0/40A63B08 - 0/0
servers diverged at WAL location 0/6ED4FDA8 on timeline 16
could not open file "/data/pgsql/testdb/pg_wal/00000010000000000000006E": No such file or directory

could not find previous WAL record at 0/6ED4FDA8
Failure, exiting
[xdb@localhost testdb]$ 
```
複製本機和新Master節點的歸檔wal檔案
```
[xdb@localhost testdb]$ scp -P 22 root@192.168.26.26:/data/archivelog/* $PGDATA/pg_wal/
The authenticity of host '192.168.26.26 (192.168.26.26)' can't be established.
ECDSA key fingerprint is SHA256:wT+B26a2wJvmsOz8QH2UK6QUqodwBYvDu/WMrmqZ5fU.
ECDSA key fingerprint is MD5:79:63:4d:55:83:d9:d6:78:21:d1:b6:a1:e8:74:0f:b0.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.26.26' (ECDSA) to the list of known hosts.
root@192.168.26.26's password: 
00000010000000000000006E.partial                                                           100%   16MB  44.1MB/s   00:00    
00000011000000000000006E                                                                   100%   16MB  71.5MB/s   00:00    
00000011000000000000006F                                                                   100%   16MB  47.0MB/s   00:00    
000000110000000000000070                                                                   100%   16MB  26.7MB/s   00:00    
000000110000000000000071                                                                   100%   16MB  26.7MB/s   00:00    
000000110000000000000072                                                                   100%   16MB  31.6MB/s   00:00    
000000110000000000000073                                                                   100%   16MB  37.1MB/s   00:00    
00000011.history                                                                           100%  261   323.1KB/s   00:00    
[xdb@localhost testdb]$ ls ./pg_wal/
00000010000000000000006A.00000028.backup  000000100000000000000074  00000010.history          000000110000000000000073
00000010000000000000006E.partial          000000100000000000000075  00000011000000000000006E  00000011.history
000000100000000000000070                  000000100000000000000076  00000011000000000000006F  archive_status
000000100000000000000071                  000000100000000000000077  000000110000000000000070  RECOVERYHISTORY
000000100000000000000072                  000000100000000000000078  000000110000000000000071
000000100000000000000073                  000000100000000000000079  000000110000000000000072
[xdb@localhost testdb]$ 
[xdb@localhost testdb]$ cp /data/archivelog/20190327/00000010000000000000006* ./pg_wal
```
重新執行pg_rewind
```
[xdb@localhost testdb]$ pg_rewind --target-pgdata=$PGDATA --source-server="host=192.168.26.26 port=5432 dbname=testdb" --progress -n
connected to server
servers diverged at WAL location 0/6ED4FDA8 on timeline 16
rewinding from last common checkpoint at 0/6B0164F8 on timeline 16
reading source file list
reading target file list
reading WAL in target
need to copy 296 MB (total source directory size is 437 MB)
303659/303659 kB (100%) copied
creating backup label and updating control file
syncing target data directory
Done!
```
修改配置檔案recovery.conf
```
[xdb@localhost testdb]$ scp -P 22 root@192.168.26.26:$PGDATA/recovery.done $PGDATA
root@192.168.26.26's password: 
recovery.done                                                                              100%  317   312.7KB/s   00:00    
[xdb@localhost testdb]$ mv recovery.done recovery.conf
[xdb@localhost testdb]$ vim recovery.conf 
[xdb@localhost testdb]$ cat recovery.conf 
standby_mode = 'on'
primary_conninfo = 'host=192.168.26.26 port=5432 user=replicator password=replicator application_name=standby_25'
restore_command = 'cp /data/archivelog/%f %p'
archive_cleanup_command = 'pg_archivecleanup /data/archivelog %r'
[xdb@localhost testdb]$ 
```
重啟原主庫

*新Master節點*
日誌提示有誤,搭建出現問題
```
2019-03-27 16:22:19.800 CST,"replicator","",2360,"192.168.26.25:32974",5c9b32bb.938,1,"idle",2019-03-27 16:22:19 CST,4/0,0,ERROR,XX000,"requested starting point 0/71000000 on timeline 16 is not in this server's history","This server's history forked from timeline 16 at 0/6ED4FDA8.",,,,,,,,"standby_25"
2019-03-27 16:22:24.813 CST,"replicator","",2361,"192.168.26.25:32976",5c9b32c0.939,1,"idle",2019-03-27 16:22:24 CST,4/0,0,ERROR,XX000,"requested starting point 0/71000000 on timeline 16 is not in this server's history","This server's history forked from timeline 16 at 0/6ED4FDA8.",,,,,,,,"standby_25"
```

作為失敗的案例,供參考.

**參考資料**
[神奇的pg_rewind](http://mysql.taobao.org/monthly/2018/05/05/)

PostgreSQL DBA(32) - HA#1(pg_rewind切換)

相關文章