Re: Restore postgres to specific time

[wd <wd@xxxxxxxxx>]

On Sun, Nov 25, 2012 at 4:25 AM, Jeff Janes <jeff.janes@xxxxxxxxx> wrote:

On Sat, Nov 24, 2012 at 6:00 AM, wd <wd@xxxxxxxxx> wrote:
> Yes,  you are right, after set the two command, the recovery will stop at
> that time.
>
> But there is an other question, how to make this recovered Postgres can be
> read and write? According to the manual, Postgres should be rename
> recovery.conf to recovery.done, but it didn't.

This is after you removed "standby_mode=on"?

Yes, the recovery file like this:

recovery_target_time='2012-11-24 03:35:09+8'
recovery_target_inclusive=true
restore_command='cp /export/t/xlog/%f %p'
 

It should happen automatically.

What entries are you getting in the log file?

Logs are something like this:
 

[    2012-11-24 21:51:33.374 CST 583 50b0d0e5.247 1 0]LOG:  database system was shut down in recovery at 2012-11-24 21:51:32 CST
[    2012-11-24 21:51:33.375 CST 583 50b0d0e5.247 2 0]LOG:  starting point-in-time recovery to 2012-11-24 03:35:09+08
[    2012-11-24 21:51:33.415 CST 583 50b0d0e5.247 3 0]LOG:  restored log file "000000010000002B00000085" from archive
[    2012-11-24 21:51:33.457 CST 583 50b0d0e5.247 4 0]LOG:  restored log file "000000010000002B00000084" from archive
[    2012-11-24 21:51:33.458 CST 583 50b0d0e5.247 5 0]LOG:  redo starts at 2B/8401D6E8
[    2012-11-24 21:51:33.498 CST 583 50b0d0e5.247 6 0]LOG:  restored log file "000000010000002B00000085" from archive
[    2012-11-24 21:51:33.591 CST 583 50b0d0e5.247 7 0]LOG:  restored log file "000000010000002B00000086" from archive
[    2012-11-24 21:51:33.591 CST 583 50b0d0e5.247 8 0]LOG:  recovery stopping before commit of transaction 1573829, time 2012-11-24 03:35:17.704521+08
[    2012-11-24 21:51:33.591 CST 583 50b0d0e5.247 9 0]LOG:  recovery has paused
[    2012-11-24 21:51:33.591 CST 583 50b0d0e5.247 10 0]HINT:  Execute pg_xlog_replay_resume() to continue.
 

Cheers,

Jeff