主页 / dba / 问题 / 162555
Accepted
Tombart
Asked: 2017-01-30 06:22:54 +0800 CST

如何从存档中恢复 PostgreSQL 备用服务器？

  • 772

我有一个 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 流式传输？是否可以自动执行此操作？

postgresql replication

1 个回答

  1. Best Answer

    不要指定一个recovery_target. 那就recovery_target_action无所谓了。

    • 1

相关问题