如何在postgresql中执行时间点恢复？

Question

我想在postgresql中做一个时间点恢复。这是我的recovery.conf

standby_mode = 'off'
primary_conninfo = 'host=localhost user=postgres port=5432 krbsrvname=postgres'
restore_command = 'cp /home/duc/backup/database/postgre_backupdb/%f %p'
recovery_target_time = '2017-06-16 9:00:00'

但是，当我启动服务器时，我总是在日志中看到这个错误。

2017-06-17 08:01:53.679 ICT [6984] LOG:  database system was shut down at 2017-06-16 16:27:53 ICT
2017-06-17 08:01:53.679 ICT [6984] LOG:  starting point-in-time recovery to 2017-06-16 09:00:00+07
cp: cannot stat '/home/duc/backup/database/postgre_backupdb/00000002.history': No such file or directory
cp: cannot stat '/home/duc/backup/database/postgre_backupdb/000000020000000C00000042': No such file or directory
2017-06-17 08:01:53.682 ICT [6984] LOG:  invalid checkpoint record
2017-06-17 08:01:53.682 ICT [6984] FATAL:  could not locate required checkpoint record
2017-06-17 08:01:53.682 ICT [6984] HINT:  If you are not restoring from a backup, try removing the file "/var/lib/postgresql/9.6/main/backup_label".
2017-06-17 08:01:53.683 ICT [6983] LOG:  startup process (PID 6984) exited with exit code 1
2017-06-17 08:01:53.683 ICT [6983] LOG:  aborting startup due to startup process failure
2017-06-17 08:01:53.684 ICT [6983] LOG:  database system is shut down

我已经尝试将recovery_target_time更改为其他时间，但仍然会出现错误。

我如何做一个时间点的恢复？

Answer 1

recovery.conf文件中的AFAIK仅在恢复过程中应用，此文件提供了PostgreSQL需要的参数

执行数据库的归档恢复，或充当日志流.

。

复制备用.

1. 你需要打开archive_log
2. 日志显示没有"cp：'/home/duc/backup/database/postgre_backupdb/000000020000000C00000042'：：cannot“，没有这样的文件或目录，没有那个文件，这意味着数据库无法使检查点对其进行归档。
3. 使用一些工具进行备份，例如PG_RMNA (例如: pg_rman还原-恢复-目标时间'2017-06-16 :00‘-B / backup /pgrman -D $PGDATA -U postgres)