Re: Restore postgres to specific time

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.

I've tried pg_ctl promote, delete recovery.conf, it's also not work.

And, I've set hot_standby='on' in postgres.conf, but still can't connect
into this postgres ,why?

(sorry for the individual mail sent to you, I should send it to the list)

On Fri, Nov 23, 2012 at 5:02 PM, raghu ram <raghuchennuru(at)gmail(dot)com> wrote:

>
>
> On Fri, Nov 23, 2012 at 8:59 AM, wd <wd(at)wdicc(dot)com> wrote:
>
>> 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.175 CST 25744 50ae0334.6490 1 0]LOG: database
>> system was shut down in recovery at 2012-11-22 18:49:22 CST
>> [ 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
>> cp: cannot stat `/export/t/xlog/000000010000002800000024': No such file
>> or directory
>> cp: cannot stat `/export/t/xlog/000000010000002800000024': No such file
>> or directory
>> cp: cannot stat `/export/t/xlog/000000010000002800000024': No such file
>> or directory
>> cp: cannot stat `/export/t/xlog/000000010000002800000024': No such file
>> or directory
>> cp: cannot stat `/export/t/xlog/000000010000002800000024': No such file
>> or directory
>> cp: cannot stat `/export/t/xlog/000000010000002800000024': No such file
>> or directory
>> cp: cannot stat `/export/t/xlog/000000010000002800000024': No such file
>> or directory
>> cp: cannot stat `/export/t/xlog/000000010000002800000024': No such file
>> or directory
>> cp: cannot stat `/export/t/xlog/000000010000002800000024': No such file
>> or directory
>> cp: cannot stat `/export/t/xlog/000000010000002800000024': No such file
>> or directory
>> cp: cannot stat `/export/t/xlog/000000010000002800000024': No such file
>> or directory
>> cp: cannot stat `/export/t/xlog/000000010000002800000024': No such file
>> or directory
>> cp: cannot stat `/export/t/xlog/000000010000002800000024': No such file
>> or directory
>> cp: cannot stat `/export/t/xlog/000000010000002800000024': No such file
>> or directory
>> 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:50.291 CST 25744 50ae0334.6490 7 0]LOG: restored
>> log file "000000010000002800000025" from archive
>> [ 2012-11-22 18:50:50.759 CST 25744 50ae0334.6490 8 0]LOG: restored
>> log file "000000010000002800000026" from archive
>> [ 2012-11-22 18:50:51.220 CST 25744 50ae0334.6490 9 0]LOG: restored
>> log file "000000010000002800000027" from archive
>> [ 2012-11-22 18:50:51.686 CST 25744 50ae0334.6490 10 0]LOG: restored
>> log file "000000010000002800000028" from archive
>> [ 2012-11-22 18:50:52.145 CST 25744 50ae0334.6490 11 0]LOG: restored
>> log file "000000010000002800000029" from archive
>> [ 2012-11-22 18:50:52.613 CST 25744 50ae0334.6490 12 0]LOG: restored
>> log file "00000001000000280000002A" from archive
>> [ 2012-11-22 18:50:53.073 CST 25744 50ae0334.6490 13 0]LOG: restored
>> log file "00000001000000280000002B" from archive
>> [ 2012-11-22 18:50:53.538 CST 25744 50ae0334.6490 14 0]LOG: restored
>> log file "00000001000000280000002C" from archive
>> [ 2012-11-22 18:50:53.999 CST 25744 50ae0334.6490 15 0]LOG: restored
>> log file "00000001000000280000002D" from archive
>> [ 2012-11-22 18:50:54.459 CST 25744 50ae0334.6490 16 0]LOG: restored
>> log file "00000001000000280000002E" 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
>>
>>
>>
>> As per logs,it is working as a host-standby. For doing the point in time
> recovery,recovery.conf file requires only two settings:
>
> restore_command='cp /t/xlog/%f %p'
> recovery_target_time='2012-11-22 5:01:09 CST'
>
> --Raghu Ram