Thread: Log Apply Delay
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? Hypothetically, if I had a standby database with max_standby_*_delay set to -1, and there had been a long running query so log apply was an hour behind, could I use that database for point in time recovery if something went wrong on the primary? Say something bad happened on primary, and I rushed over to the standby (in this delayed situation) and shut it down. Could I then alter the recovery.conf and have it come up read/write at a point in time? Seems like I could.... - Ian
On Fri, 2011-09-16 at 08:02 -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? > IIRC, Robert Haas had a WIP patch to do that. Not sure what its status is now. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
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. Best regards, depesz
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. - Ian
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
On Fri, Sep 16, 2011 at 10:53 AM, Thom Brown <thom@linux.com> wrote:
> 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.
+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