Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Postgres has had streaming replication since 9.0 and since 9.1 it is possible to pause and resume the application of the streamed WAL records using pg_xlog_replay_pause() and pg_xlog_replay_resume(). As far as I can tell, there is no built-in way of keeping the application of WAL deliberately lagged by a specific time period (eg 24h).

Have I missed a built-in way of achieving this and if not, how should I approach doing it some other way?

share|improve this question
Streaming replication cannot do this, but if you make the standby getting the WAL files and apply them, you can apply an arbitrary delay. We push the WALs to a NFS volume and the restore command gets them from there to the local WAL dir, checking if the files are old enough. – dezso Mar 10 '14 at 15:37
You mean you push the WAL rather than using streaming replication? – Jack Douglas Mar 10 '14 at 17:36
Exactly. I don't see how this could be solved with streaming. – dezso Mar 10 '14 at 19:55
On one hand, I believe you cannot tell the standby where to stream the recieved WAL records. On the other hand, what you propose can possibly work. If you cannot archive the WAL on the master to a place where it is also available to the standby, this can be a solution. What you won't be able to influence is that, between two pauses, how much of the records will be applied. But then this is not that far from my suggestion where the delay is also like delay plusminus epsilon :) – dezso Mar 10 '14 at 21:26
There's been discussion about supporting delayed streaming replicas, and it's possible they may be supported in 9.4 or 9.5. With the slots feature added for logical changeset extraction in 9.4 the WAL retention is easier to manage, and commit timestamps (if committed) will tell it when WAL was generated. – Craig Ringer Mar 10 '14 at 23:01

Streaming replication cannot do this (but see Craig Ringer's comment above about a possibly upcoming feature).

The good news is, if you make the standby getting the WAL files and apply them, you can apply an arbitrary delay. We push the WALs to a NFS volume and the restore command copies them from there to the local WAL dir, checking if the files are old enough. Then the normal restore process applies them as usual.

Of course, the delay will match the preset value only approximately, as a single WAL file may contain changes from quite different time intervals, depending on the master's workload.

What you propose with pausing the restore process depending on the value returned by pg_last_xact_replay_timestamp() may work if run from a scheduled job (probably you want to schedule it to run quite frequently). So far I haven't seen such a setup and am wondering if it can be a production-grade solution.

share|improve this answer

Additionally, another configuration option that can help in "pausing" replication is max_standby_streaming_delay

You can either set it to -1 and open/commit, dummy queries in a transaction, based on the delay and granularity of time required or set max_standby_streaming_delay to 24hrs for the query to get cancelled and replication to resume till the next query!

(I had to isolate and herd DSS style queries within transactions to a dedicated standby - so no real life experience [yet] in lagging replication)

What is your reason for delaying replication? If resources permit, maintaining a pg_basebackup with WAL files shipped/copied regularly, might afford more control over "oops" :)

share|improve this answer
It's a peculiar system (at least to my mind). A database is replicated from master to a number of slaves (currently 2) and these slaves are hit by customer queries. The master database receives updates daily which are not time-critical: over the long term they need to be applied but day-to-day it doesn't matter if customers see yesterdays data or even last weeks data. The idea behind the 'lag' is that if a daily update or any other change to the master passes QA but breaks things subtly, only one of the slaves will initially be affected (as the lag would be set to different periods on each) – Jack Douglas Mar 11 '14 at 6:17
That slave can then be taken out of the loop relatively quickly and we can take steps to reverse the damage without customers continuing to suffer – Jack Douglas Mar 11 '14 at 6:18
This is similar to one of our mysql systems. (ans: percona utility :) ) In our case, the rare subtle break might not get noticed for a longer time period (hopefully your checks are more robust :) ), but the exact timestamp is known - which enables us to restore upto that point and reverse the bad data. – amacvar Mar 11 '14 at 19:28

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.