Re: Problem with streaming replication, backups, and recovery (9.0.x)

On Sat, Mar 26, 2011 at 5:31 AM, hubert depesz lubaczewski
<depesz(at)depesz(dot)com> wrote:
> I can also setup streaming slave, and it also works, but when I create
> trigger file to promote this slave to master it fails with error:
> 2011-03-24 21:01:58.051 CET @ 9680  LOG:  trigger file found: /home/depesz/slave2/finish.recovery
> 2011-03-24 21:01:58.051 CET @ 9930  FATAL:  terminating walreceiver process due to administrator command
> 2011-03-24 21:01:58.151 CET @ 9680  LOG:  redo done at 0/1F000058
> 2011-03-24 21:01:58.151 CET @ 9680  LOG:  last completed transaction was at log time 2011-03-24 20:58:25.836333+01
> 2011-03-24 21:01:58.238 CET @ 9680  FATAL:  WAL ends before consistent recovery point
>
> Which is interesting, because this particular backup was done using .backup file containing:
>
> START WAL LOCATION: 0/A000020 (file 00000001000000000000000A)
> STOP WAL LOCATION: 0/12C9D7E8 (file 000000010000000000000012)
> CHECKPOINT LOCATION: 0/B803050
> START TIME: 2011-03-24 20:52:46 CET
> STOP TIME: 2011-03-24 20:53:41 CET
> LABEL: OmniPITR_Slave_Hot_Backup
>
> Which means that minimum recovery ending location was in fact reached (it was
> on 0/12C9D7E8, and recovery continued till 0/1F000058).

In 9.0, recovery doesn't read a backup history file. That FATAL error happens
if recovery ends before it reads the WAL record which was generated by
pg_stop_backup(). IOW, recovery gets the recovery ending location from WAL
record not backup history file. Since you didn't run pg_stop_backup() and there
is no WAL record containing the recovery ending location, you got that error.

If you want to take hot backup from the standby, you need to do the procedure
explained in
http://wiki.postgresql.org/wiki/Incrementally_Updated_Backups

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center