Re: Log Apply Delay

On Fri, Sep 16, 2011 at 10:53 AM, Thom Brown <thom(at)linux(dot)com> wrote:
> On 16 September 2011 16:41, Ian Harding <harding(dot)ian(at)gmail(dot)com> wrote:
>> On Fri, Sep 16, 2011 at 8:35 AM, hubert depesz lubaczewski
>> <depesz(at)depesz(dot)com> wrote:
>>> On Fri, Sep 16, 2011 at 08:02:31AM -0700, Ian Harding wrote:
>>>> Oracle has a configuration option for its version of hot standby
>>>> (DataGuard) that lets you specify a time based delay in applying logs.
>>>>  They get transferred right away, but changes in them are only applied
>>>> as they reach a certain age.  The idea is that if something horrible
>>>> happens on the master, you can keep it from propagating to one or more
>>>> of your standby databases (or keep from having to reinstate one in the
>>>> case of a failover)
>>>>
>>>> Anyway, Is there any plan to add a knob like that to the streaming
>>>> replication in Postgres?
>>>
>>> In streaming - no. But if you want delay, perhaps normal WAL-files based
>>> approach would be good enough? OmniPITR, for one, has a option to delay
>>> applying wal segments.
>>>
>>
>> The file based approach is pretty close, unless the Bad Thing happens
>> right before a file gets transferred.  This is not a super important
>> feature to me but It's a nice security blanket and almost takes the
>> place of a PITR plan including big file transfers of the data
>> directory at regular intervals.
>
> You could always ship the log to a waiting directory on the
> destination server, then run a command like this every few mins:
>
> find /holding/dir -maxdepth 1 -mtime +1 -exec mv '{}' /actual/dir/ ';'
>
> That particular command would move all files over a day old to the
> directory the standby is looking at.
>
> Or change +1 to +1h to leave a gap of an hour instead of a day.

+1 on this approach -- there's a tremendous amount of flexibility that
you can utilize using with a non-SR hot standby if you can handle a
little scripting. another nifty trick is to multiplex the log file to
multiple receiving standbys so you only have to pay the network
bandwidth getting the file off the server once...

with non-SR hot standby, don't forget you can set archive_timeout to a
small number of minutes if the server is lightly loaded and you wand
to keep the data loss window down.

merlin