From: Steve Crawford <scrawford@pinpointresearch.com>
To: Alexey Klyukin <alexk@commandprompt.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: standby with a fixed lag behind the master
Date: Thu, 26 Jul 2012 09:18:14 -0700
Message-ID: <50116DC6.8050704@pinpointresearch.com> (raw)
In-Reply-To: <43742AE6-5297-428F-A04A-6A91BF68B54F@commandprompt.com>
References: <43742AE6-5297-428F-A04A-6A91BF68B54F@commandprompt.com>
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 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?
>
This sounds like a weird way to approach this problem. If you want a
snapshot n-hours ago why not just schedule a dump or base-backup to run
at the desired times? Or continuously archive your WAL so you can
recover up to any point you want rather than just a pre-defined n-hours?
I'm currently looking at using the recently announced pgbarman to
simplify the overall process. (http://www.pgbarman.org/)
Cheers,
Steve
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scrawford@pinpointresearch.com, alexk@commandprompt.com
Subject: Re: standby with a fixed lag behind the master
In-Reply-To: <50116DC6.8050704@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox