Re: standby with a fixed lag behind the master

Hi there,

And why not shipping older WAL files to the target on a regular basis ?.
On the master you can control with a crontab job to ship the wanted WAL
files (n hours older than current time and clean the shipped up, check
rsync options up) in a regular basis.

A.A.



On 07/26/2012 02:24 AM, Alexey Klyukin wrote:
> Hello,
>
> I've recently come across the task of setting up a PostgreSQL 9.1 standby server that is N hours behind the master,
i.e.only transactions that finished N hours in the past or older should be replayed on a standby. The goal is to have a
known good state server to perform backups from and possibly revert to in case of unwanted changes on primary. It seems
thatthere is no mechanism in PostgreSQL to just ask the standby to keep a fixed distance (in terms of either WAL
segmentsor time) between the primary, so these are possible solutions: 
>
> 1. Use restore command on standby to fetch the current WAL segment only if it has been created not less than N hours
inthe past (according to ctime). 
> 2. Pause the restore process on standby if the lag * is less than N hours (with pg_xlog_replay_pause()) and resume if
itis more than that. 
> 3. Set recovery_target_time to current - 6 hours and pause_at_recovery_target to true, periodically check whether the
recoveryis paused, reset the recovery target time to a new value (and restart the standby) if it is. 
>
> * - the lag would be calculated as now() - pg_last_xact_replay_timestamp() on standby.
>
> Both 2 and 3 requires external cron job to pause/resume the recovery, and 1, while being the easiest of all, doesn't
workwith SR (unless it's combined with WAL shipping). I wonder if there are other well established approaches at
solvingthis problem and if there is an interest for adding such feature to the -core? 
>
> Thank you,
> --
> Alexey Klyukin        http://www.commandprompt.com
> The PostgreSQL Company – Command Prompt, Inc.
>
>
>
>
>