代码之家 › 专栏 › 技术社区 › YogeshR

PostgreSQL时间点恢复不起作用

pitr postgresql-9.6 postgresql

YogeshR · 技术社区 · 6 年前

我们有两个PostgreSQL服务器作为主从服务器。

我想测试下面的场景

我在master上做了一个基本备份,然后删除了一个表并使用命令切换xlog文件 select pg_switch_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'

我在这里做错什么了?

1 回复 | 直到 6 年前

Laurenz Albe 6 年前

恢复无法继续到时间线6,因为您没有添加

recovery_target_timeline = 'latest'

到 recovery.conf . 作为 the documentation 说:

默认情况下,恢复时间与执行基本备份时的当前时间相同。