Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 9125C1FA2A4B for ; Thu, 26 Jul 2012 06:24:22 -0300 (ADT) Received: from 30.55.colo.spiretech.net ([69.168.55.30] helo=lists.commandprompt.com) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SuKIv-0008RY-7R for pgsql-admin@postgresql.org; Thu, 26 Jul 2012 09:24:21 +0000 Received: from [10.0.0.100] (spinlock.commandprompt.com [69.168.55.30]) by lists.commandprompt.com (Postfix) with ESMTPA id 96EE834D for ; Thu, 26 Jul 2012 02:24:07 -0700 (PDT) Received: from [10.0.0.100] ([46.173.85.53] helo=[10.0.0.100]) by assp.commandprompt.com with ESMTPS(AES128-SHA) (2.1.1); 26 Jul 2012 02:24:04 -0700 From: Alexey Klyukin Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable Subject: standby with a fixed lag behind the master Message-Id: <43742AE6-5297-428F-A04A-6A91BF68B54F@commandprompt.com> Date: Thu, 26 Jul 2012 12:24:04 +0300 To: pgsql-admin@postgresql.org Mime-Version: 1.0 (Mac OS X Mail 6.0 \(1485\)) X-Mailer: Apple Mail (2.1485) X-Assp-Version: 2.1.1(11364) on assp.commandprompt.com X-Assp-Client-TLS: yes X-Assp-ID: assp.commandprompt.com m1-94647-07232 X-Assp-Envelope-From: alexk@commandprompt.com X-Assp-Intended-For: pgsql-admin@postgresql.org X-Assp-Original-Subject: standby with a fixed lag behind the master X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/87 X-Sequence-Number: 39804 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 that there is no 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 = if 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_target 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 = combined with WAL shipping). I wonder if there are other well = established approaches at solving this 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 =96 Command Prompt, Inc.