Did it help? If yes - maybe you can help me?
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.