Re: Restore postgres to specific time

hi,

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?

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?

Looks ok.
What does the server log say during the restore?

Yours,
Laurenz Albe

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

On Thu, Nov 22, 2012 at 10:39 PM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>wrote:

> 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?
>
> Looks ok.
> What does the server log say during the restore?
>
> Yours,
> Laurenz Albe
>

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

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

On Thu, Nov 22, 2012 at 7:29 PM, 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

Do you have standby_mode=on in your recovery.conf? If so, remove it.

It is meaningless to specify both standy_mode, and recovery_target_time.

Cheers,

Jeff

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

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"?

It should happen automatically.

What entries are you getting in the log file?

Cheers,

Jeff

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
>

wd wrote:
> Logs are something like this:
> [ 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.

Well, try

SELECT pg_xlog_replay_resume();

Yours,
Laurenz Albe

I can't connect to postgres at that time.

On Mon, Nov 26, 2012 at 4:33 PM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>wrote:

> wd wrote:
> > Logs are something like this:
> > [ 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.
>
> Well, try
>
> SELECT pg_xlog_replay_resume();
>
> Yours,
> Laurenz Albe
>

wd wrote:
>>> Logs are something like this:
>>>
>>> [ 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.
>>
>> Well, try
>>
>> SELECT pg_xlog_replay_resume();
>
> I can't connect to postgres at that time.

Duh.
Then the trick is not to pause at all.
Make sure that pause_at_recovery_target is not set in
recovery.conf.
Stop the recovering server, then start it again.
That should do the trick.

Yours,
Laurenz Albe

On Sat, Nov 24, 2012 at 3:44 PM, wd <wd(at)wdicc(dot)com> wrote:
>>
>> 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.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.
>

Something is strange here. There is no message about reaching a
consistent recovery point (which is why it doesn't start in hot
standby mode I guess). But when I arrange for that to happen myself,
by specifying a time before consistent recovery would be reached, I
get this error:

FATAL: requested recovery stop point is before consistent recovery point

I don't understand why are you not getting this message. I wonder if
the earlier attempts you made at doing this left the system in some
inconsistent state. If you start over again with a fresh restore of
the base backup and a recovery.conf which is correctly configured the
first time you try to use it, do you still see this?

Cheers,

Jeff

On Mon, Nov 26, 2012 at 11:32 PM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>wrote:

> wd wrote:
> >>> Logs are something like this:
> >>>
> >>> [ 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.
> >>
> >> Well, try
> >>
> >> SELECT pg_xlog_replay_resume();
> >
> > I can't connect to postgres at that time.
>
> Duh.
> Then the trick is not to pause at all.
> Make sure that pause_at_recovery_target is not set in
> recovery.conf.
> Stop the recovering server, then start it again.
> That should do the trick.
>
> I've tried set pause_at_recovery_target to true, it's not work. and also
tried comment out this setting it's the same.

Restart have no effect, the recovery.conf is always there, I think it's
should be rename to recovery.done if it's work.

>
> Yours,
> Laurenz Albe
>