postgresql - Postgres pg_rewind 不适用于简单用例

Question

我们一直在努力让 pr_rewind 工作。无奈之下我们一直在努力让用例尽可能的简单

在正常的主/备用配置中，我们有两个数据库服务器在两台不同的机器上运行 Postgres 10。

两台机器都将它们的 WAL 存档日志写入同一个共享驱动器(称为/ice_dev/wal_archive)。配置有以下条款

archive_mode = always
archive_command = 'test ! -f /ice-dev/wal_archive/%f && cp %p /ice-dev/wal_archive/%f'
full_page_writes = on
wal_log_hints = on        

启用校验和

我们在机器A和B上运行的程序如下:

1. 在 A 和 B 上建立新的数据库，并将 A 配置为主数据库，将 B 配置为备用数据库。
2. 对 A(主)进行一些更改并检查它们是否已复制到 B(备用)
3. 将 B 提升为新的 primary
4. 关闭 A(原始主)
5. 将复制插槽添加到 B(新主)以供 A(即将成为备用)

6. 给A(即将备用)添加一个recovery.conf。文件包含

   recovery_target_timeline = 'latest' and restore_command = 'cp /ice-dev/wal_archive/%f "%p"
   

7. 在 A 上运行 pg_rewind——这似乎有效，因为它返回消息“源和目标集群在同一时间线上，不需要倒带”；
8. 启动服务器A(现在是slave)

此时A处于只读模式但不进行复制。它的日志包含以下重复消息

2018-08-01 20:30:58 UTC [7257]: [1] user=,db=,app=,client= FATAL:  could not start WAL streaming: ERROR:  requested starting point 0/6000000 on timeline 1 is not in this server's history
    DETAIL:  This server's history forked from timeline 1 at 0/57639D0.
cp: cannot stat ‘/ice-dev/wal_archive/00000002.history’: No such file or directory
cp: cannot stat ‘/ice-dev/wal_archive/00000003.history’: No such file or directory
cp: cannot stat ‘/ice-dev/wal_archive/00000002.history’: No such file or directory
2018-08-01 20:30:58 UTC [6840]: [48] user=,db=,app=,client= LOG:  new timeline 2 forked off current database system timeline 1 before current recovery point 0/6000098
cp: cannot stat ‘/ice-dev/wal_archive/000000010000000000000006’: No such file or directory

我们可以在 B 的 wal 目录中看到 00000002.history 文件……但它从未出现在 wal_archive 目录中——即使我们 checkout 甚至重新启动服务器也没有出现。

00000003.history 似乎不存在于任何一台机器上。

知道我们做错了什么吗？

谢谢。理查德

Answer 1

我目前正在调查一个类似的问题，这是我在 PostgreSQL official docs 中找到的内容:

Normally, recovery will proceed through all available WAL segments, thereby restoring the database to the current point in time (or as close as possible given the available WAL segments). Therefore, a normal recovery will end with a “file not found” message, the exact text of the error message depending upon your choice of restore_command. You may also see an error message at the start of recovery for a file named something like 00000001.history. This is also normal and does not indicate a problem in simple recovery situations; see Section 25.3.5 for discussion.

因此，答案可能是——您所做的一切都是正确的，这是设计好的行为，但时间线也可能存在问题:

The default behavior of recovery is to recover along the same timeline that was current when the base backup was taken. If you wish to recover into some child timeline (that is, you want to return to some state that was itself generated after a recovery attempt), you need to specify the target timeline ID in recovery.conf. You cannot recover into timelines that branched off earlier than the base backup.

因此您可以尝试在您的 recovery.conf 中明确指定时间线 ID。

此外，我建议您最好咨询https://dba.stackexchange.com/ ，因为您可以在那里更多地关注您的 DBA 特定问题。