Message-ID: <5022A33E.6080500@d2.com>
Date: Wed, 08 Aug 2012 10:34:54 -0700
From: Amador Alvarez <aalvarez@d2.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686 (x86_64); en-US;
	rv:1.9.2.27) Gecko/20120216 Lightning/1.0b2 Thunderbird/3.1.19
MIME-Version: 1.0
To: Alexey Klyukin <alexk@commandprompt.com>
CC: pgsql-admin@postgresql.org
Subject: Re: standby with a fixed lag behind the master
References: <43742AE6-5297-428F-A04A-6A91BF68B54F@commandprompt.com>
In-Reply-To: <43742AE6-5297-428F-A04A-6A91BF68B54F@commandprompt.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: quoted-printable

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=20
files (n hours older than current time and clean the shipped up, check=20
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 fini=
shed 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 that there is n=
o mechanism in PostgreSQL to just ask the standby to keep a fixed distance =
(in terms of either WAL segments or time) between the primary, so these are=
 possible solutions:
>
> 1. Use restore command on standby to fetch the current WAL segment only i=
f it has been created not less than N hours in the 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 it is more than that.
> 3. Set recovery_target_time to current - 6 hours and pause_at_recovery_ta=
rget to true, periodically check whether the recovery is 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 work with SR (unless it's combi=
ned with WAL shipping). I wonder if there are other well established approa=
ches at solving this problem and if there is an interest for adding such fe=
ature to the -core?
>
> Thank you,
> --
> Alexey Klyukin        http://www.commandprompt.com
> The PostgreSQL Company =96 Command Prompt, Inc.
>
>
>
>
>