Re: Log Apply Delay

On 16 September 2011 16:41, Ian Harding <harding.ian@gmail.com> wrote:
> On Fri, Sep 16, 2011 at 8:35 AM, hubert depesz lubaczewski
> <depesz@depesz.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.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company