Waiting for 9.4 – Allow time delayed standbys and recovery

On 12th of December, Simon Riggs committed patch:

Allow time delayed standbys and recovery
 
Set min_recovery_apply_delay to force a delay in recovery apply for commit and
restore point WAL records. Other records are replayed immediately. Delay is
measured between WAL record time and local standby time.
 
Robert Haas, Fabrízio de Royes Mello and Simon Riggs
Detailed review by Mitsumasa Kondo

When we're talking about security of data, we can mean replicas or backups. Both are good for their respective use cases, but there is one usecase that is troubling even when you have replica and backup. That is – admin mistake.

Superfluous “;", missing “WHERE", erroneous “enter" after jokingly entering “drop table users cascade;". These things happen.

Replication – doesn't really help. Backups are good, but how fast you can recover depends on when you made the backup, how large it is, and how fast you can get it up.

The best solution seems to be delayed slave. One that is running continuously, but always some time behind master. This is the grace time to allow for noticing of the error, stopping dbs, and fixing what can be fixed.

So far such feature was part of OmniPITR (not sure if any other replication engines did support it, but it was definitely not part of normal Pg replication.

Now, with this new patch – you can setup slave that is always, purposely, couple of “steps" behind its master.

How to do it?

That's simple. In recovery.conf, you simply add new line:

min_recovery_apply_delay = ...

And you're done.

The value is integer, but it supports these units:

  • ms (obviously)
  • s (seconds)
  • min (minutes)
  • h (hours)
  • d (days)

So you can have:

min_recovery_apply_delay = 2h

and it will give you 2 hours to notice the problem and stop replication before data on slave will get damaged.

Great. Thanks Robert, Fabrízio, Simon and Mitsumasa.

7 thoughts on “Waiting for 9.4 – Allow time delayed standbys and recovery”

  1. While I do not see myself using this particular one, many great features are landing in 9.4.

  2. 3 notes (the first of which will be added to the documentation shortly):

    1) Delaying the application of WAL will cause build-up of WAL files in the pg_xlog directory on the standby, as they have to be kept around until they’re ready to be replayed once the delay has elapsed.

    2) The technical upper limit one can delay a standby to is 2,147,483,647 milliseconds (or 24 days, 20 hours, 31 minutes, 23 seconds and 647 milliseconds) due to the parameter being represented by a 32-bit integer.

    3) If you’re using cascading replication, the delay is still between the primary and any standby, so if your setup is [P]->[S1]->[S2]->[S3] (where S2 subscribes to S1, S3 subscribes to S2), and if the delay were 10 minutes, the standbys would all apply at the same time after 10 minutes rather than be 10 minutes apart from one another. In order to achieve a staggered effect, each standby (whether in chain configuration or not) would have to have separate values. So a standby’s delay does not affect it passing on the WAL records immediately to the next standby in the chain. There’s nothing to stop a standby further down the chain (e.g. S3) from applying before any standby further up the chain if the replication delay is less than the others.

  4. the parameter renamed from min_recovery_apply_delay to recovery_min_apply_delay

Comments are closed.