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

On Mon, Mar 28, 2011 at 9:19 PM, hubert depesz lubaczewski
<depesz(at)depesz(dot)com> wrote:
> On Mon, Mar 28, 2011 at 01:48:13PM +0900, Fujii Masao wrote:
>> 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
>
> Is it intentional and/or does it serve some greater good?

Yes, it's intentional. In streaming replication, at first the master must stream
a backup history file to the standby in order to let it know the recovery ending
position. But streaming replication doesn't have ability to send a text file, so
we changed the code so that the recovery ending position was also written as
WAL record which can be streamed.

IIRC another reason is that it's more reliable to write down the important
information like the recovery ending position to WAL record than a backup
history file.

> I mean -
> ability to make backups on slave without ever bothering master was
> pretty interesting.

Me, too. We would need to implement that in 9.2.

BTW, in my system, I use another trick to take a base backup from the
standby:

(All of these operations are expected to be performed on the standby)
(1) Run CHECKPOINT
(2) Copy pg_control to temporary area
(3) Take a base backup of $PGDATA
(4) Copy back pg_control from temporary area to the backup taken in (2).
(5) Calculate the recovery ending position from current pg_control in
$PGDATA by using pg_controldata

When recovery starts from that backup, it doesn't automatically check
whether it has reached the ending position or not. So the user needs to
check that manually.

Yeah, this trick is very fragile and complicated. I'd like to improve the way
in 9.2.

Regards,

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