Re: Restore postgres to specific time

wd wrote:
>>> I've try to restore Postgres to a specific time but failed.
>>>
>>> The recovery.conf as bellow
>>> restore_command='cp /t/xlog/%f %p'
>>> recovery_target_time='2012-11-22 5:01:09 CST'
>>> pause_at_recovery_target=true
>>> recovery_target_inclusive=false
>>>
>>> The basebackup was made at 2012-11-22 3:10 CST, I've copy wal log files at 3:00 to 5:10 into /t/xlog,
>>> but the recovery will not stop at 2012-11-22 5:01:09。
>>>
>>> How to do this?
>
> Thanks for your reply, the logs are something like bellow，postgres will restore every wal log I put
> in the xlog directory，and then continues waiting for next wal log. The postgres version is 9.1.6.
>
> [ 2012-11-22 18:49:24.176 CST 25744 50ae0334.6490 2 0]LOG: entering standby mode
> [ 2012-11-22 18:49:24.210 CST 25744 50ae0334.6490 3 0]LOG: restored log file
> "000000010000002800000023" from archive
> [ 2012-11-22 18:49:24.213 CST 25744 50ae0334.6490 4 0]LOG: redo starts at 28/23001900
> [ 2012-11-22 18:49:24.619 CST 25744 50ae0334.6490 5 0]LOG: consistent recovery state reached at
> 28/23FFFFC8
> [ 2012-11-22 18:49:24.620 CST 25736 50ae0333.6488 2 0]LOG: database system is ready to accept read
> only connections
> cp: cannot stat `/export/t/xlog/000000010000002800000024': No such file or directory
[...]
> cp: cannot stat `/export/t/xlog/000000010000002800000024': No such file or directory
> [ 2012-11-22 18:50:49.828 CST 25744 50ae0334.6490 6 0]LOG: restored log file
> "000000010000002800000024" from archive
[...]
> [ 2012-11-22 18:50:54.920 CST 25744 50ae0334.6490 17 0]LOG: restored log file
> "00000001000000280000002F" from archive
> cp: cannot stat `/export/t/xlog/000000010000002800000030': No such file or directory
> cp: cannot stat `/export/t/xlog/000000010000002800000030': No such file or directory

Can you also show us postgresql.conf from the standby?

Try to find out where you are in log replay.

Connect to the standby and run the following query:

SELECT pg_last_xact_replay_timestamp();

It would be interesting what time that is.

Yours,
Laurenz Albe