PostgreSQL 时间点恢复不起作用

Question

我们有 2 台 PostgreSQL 服务器设置为主从。

我想测试下面的场景

我在 master 上进行了基本备份，然后使用命令 select pg_switch_xlog(); 删除了一个表并切换了 xlog 文件。然后我阻止了主人。将 slave 提升为 master 并删除了一张表并切换了日志文件。

现在我已经在新服务器上恢复了两个服务器的基本备份和 wal 文件，并使用 recovery_target_time 来测试 PITR。我能够恢复到我没有删除任何表或从旧主人那里删除 1 个表的时间。但是我无法恢复到从新主控中删除表时的时间点。

下面是相同的日志输出

2018-08-06 14:49:39.564 UTC  [19772] LOG:  starting point-in-time recovery to 2018-08-06 14:20:00+00
cp: cannot stat ‘/var/lib/pgsql/pg_log_archive/00000005.history’: No such file or directory
2018-08-06 14:49:39.579 UTC  [19772] LOG:  restored log file "000000050000000200000046" from archive
2018-08-06 14:49:39.630 UTC  [19772] LOG:  redo starts at 2/46000028
2018-08-06 14:49:39.635 UTC  [19772] LOG:  consistent recovery state reached at 2/460ABCE8
2018-08-06 14:49:39.636 UTC  [19770] LOG:  database system is ready to accept read only connections
2018-08-06 14:49:39.648 UTC  [19772] LOG:  restored log file "000000050000000200000047" from archive
2018-08-06 14:49:39.732 UTC  [19772] LOG:  restored log file "000000050000000200000048" from archive
cp: cannot stat ‘/var/lib/pgsql/pg_log_archive/000000050000000200000049’: No such file or directory
2018-08-06 14:49:39.780 UTC  [19772] LOG:  redo done at 2/48003410
2018-08-06 14:49:39.780 UTC  [19772] LOG:  last completed transaction was at log time 2018-08-06 13:27:00.442816+00
2018-08-06 14:49:39.796 UTC  [19772] LOG:  restored log file "000000050000000200000048" from archive
2018-08-06 14:49:39.843 UTC  [19772] LOG:  restored log file "00000006.history" from archive
cp: cannot stat ‘/var/lib/pgsql/pg_log_archive/00000007.history’: No such file or directory
2018-08-06 14:49:39.845 UTC  [19772] LOG:  selected new timeline ID: 7
cp: cannot stat ‘/var/lib/pgsql/pg_log_archive/00000005.history’: No such file or directory
2018-08-06 14:49:39.903 UTC  [19772] LOG:  archive recovery complete
2018-08-06 14:49:40.006 UTC  [19772] LOG:  MultiXact member wraparound protections are now enabled
2018-08-06 14:49:40.008 UTC  [19770] LOG:  database system is ready to accept connections
2018-08-06 14:49:40.009 UTC  [19786] LOG:  autovacuum launcher started

recovery.conf 文件的内容如下:

restore_command = 'cp /var/lib/pgsql/pg_log_archive/%f %p'
recovery_target_time = '2018-08-06 14:20:00.0'
recovery_target_inclusive = 'true'
recovery_target_action = 'promote'

我在这里做错了什么？

Answer 1

恢复不会进行到时间线 6，因为你没有添加

recovery_target_timeline = 'latest'

到 recovery.conf。作为the documentation说:

The default is to recover along the same timeline that was current when the base backup was taken.