如何從存檔中恢復 PostgreSQL 備用伺服器？

我有一個 PostgreSQL 9.5 集群master和standby使用repmgr. Master 配置為保留4000WAL 文件：

wal_level = logical
hot_standby = on
archive_command = 'test ! -f /mnt/share/psql/archive/psql/%f && cp %p /mnt/share/psql/archive/psql/%f'
max_worker_processes = 10                        
max_replication_slots = 10
max_wal_senders = 10
wal_keep_segments = 4000

在某些時候，standby伺服器落後於主伺服器（我不知道為什麼會發生這種情況）：

2017-01-28 23:49:24 UTC ERROR:  current transaction is aborted, commands ignored until end of transaction block
2017-01-28 23:49:24 UTC STATEMENT:  
2017-01-28 23:51:10 UTC LOG:  invalid magic number 0000 in log segment 0000000200001E5A000000C8, offset 5201920
2017-01-28 23:51:10 UTC LOG:  started streaming WAL from primary at 1E5A/C8000000 on timeline 2
2017-01-29 00:04:59 UTC FATAL:  could not send data to WAL stream: server closed the connection unexpectedly
               This probably means the server terminated abnormally
               before or while processing the request.

2017-01-29 00:16:10 UTC LOG:  invalid magic number 0000 in log segment 0000000200001E5B000000C5, offset 5242880
2017-01-29 00:16:10 UTC LOG:  started streaming WAL from primary at 1E5B/C5000000 on timeline 2
2017-01-29 00:50:07 UTC FATAL:  could not send data to WAL stream: server closed the connection unexpectedly
               This probably means the server terminated abnormally
               before or while processing the request.

2017-01-29 00:50:07 UTC LOG:  invalid magic number 0000 in log segment 0000000200001E5D0000000A, offset 5373952
2017-01-29 00:50:22 UTC LOG:  started streaming WAL from primary at 1E5D/A000000 on timeline 2
2017-01-29 03:29:35 UTC FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 0000000200001E64000000B7 has already been removed

顯然 master 刪除了需要的 WAL 0000000200001E64000000B7，但該文件仍然存在於存檔中。無論如何，standby重新啟動數據庫後似乎達到一致狀態：

...
2017-01-29 13:45:35 UTC LOG:  restored log file "0000000200001E64000000B1" from archive
2017-01-29 13:45:36 UTC LOG:  restored log file "0000000200001E64000000B2" from archive
2017-01-29 13:45:36 UTC LOG:  restored log file "0000000200001E64000000B3" from archive
2017-01-29 13:45:36 UTC LOG:  restored log file "0000000200001E64000000B4" from archive
2017-01-29 13:45:37 UTC LOG:  restored log file "0000000200001E64000000B5" from archive
2017-01-29 13:45:37 UTC LOG:  restored log file "0000000200001E64000000B6" from archive
2017-01-29 13:45:37 UTC LOG:  restored log file "0000000200001E64000000B7" from archive
2017-01-29 13:45:38 UTC LOG:  consistent recovery state reached at 1E64/B7DFFD78
2017-01-29 13:45:38 UTC LOG:  recovery stopping after reaching consistency
2017-01-29 13:45:38 UTC LOG:  recovery has paused
2017-01-29 13:45:38 UTC HINT:  Execute pg_xlog_replay_resume() to continue.
2017-01-29 13:45:38 UTC LOG:  database system is ready to accept read only connections

PostgreSQL 建議執行pg_xlog_replay_resume()，但這會導致將standby 提升為master，從而出現腦裂的情況。

$ repmgr cluster show
Role      | Name                    | Upstream                | Connection String
----------+-------------------------|-------------------------|-----------------------------------------
* master  | psql01a                 |                         | host=psql01a user=repmgr
 standby | psql01b                 | psql01a                 | host=psql01b user=repmgr

recovery.conf:

restore_command = 'cp /mnt/share/psql/archive/psql/%f %p'
recovery_target_inclusive = true
recovery_target = 'immediate'
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'user=repmgr port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres host=psql01a application_name=psql01b password=ZDIzNjk2OTM2MWYyNjNiYzk5ZDVhMWIw'
recovery_min_apply_delay = 0

PostgreSQL 9.5 帶有一個新參數recovery_target_action，它可以有 3個值recovery.conf：

• pause（預設）恢復將被暫停
• shutdown達到恢復目標後將停止伺服器
• promote表示恢復過程將完成，伺服器將開始接受連接

promote根據文件，這似乎是合乎邏輯的選擇，但它再次導致腦裂情況：

2017-01-29 19:31:27 UTC LOG:  consistent recovery state reached at 1E64/B7DFFD78
2017-01-29 19:31:27 UTC LOG:  recovery stopping after reaching consistency
2017-01-29 19:31:27 UTC LOG:  redo done at 1E64/B7DFFD78
2017-01-29 19:31:27 UTC LOG:  last completed transaction was at log time 2017-01-29 00:32:06.442239+00
2017-01-29 19:31:27 UTC LOG:  database system is ready to accept read only connections
cp: cannot stat '/mnt/share/psql/archive/psql/00000003.history': No such file or directory
2017-01-29 19:31:27 UTC LOG:  selected new timeline ID: 3
cp: cannot stat '/mnt/share/psql/archive/psql/00000002.history': No such file or directory
2017-01-29 19:31:28 UTC LOG:  archive recovery complete

$ repmgr cluster show
Role      | Name                    | Upstream                | Connection String
----------+-------------------------|-------------------------|-----------------------------------------
* master  | psql01a                 |                         | host=psql01a user=repmgr
* master  | psql01b                 | psql01a                 | host=psql01b user=repmgr

問題是如何在不將備用提升為主控的情況下恢復 WAL 流式傳輸？是否可以自動執行此操作？

不要指定一個recovery_target. 那就recovery_target_action無所謂了。

引用自：https://dba.stackexchange.com/questions/162555