Re: Restore postgres to specific time

On Sun, Nov 25, 2012 at 4:25 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Sat, Nov 24, 2012 at 6:00 AM, wd <wd(at)wdicc(dot)com> 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
>