I've a Postgres 9.2.1 server master (geolocalized in EU) and 2 slave replica (one in EU and one in US). The EU slave is used also for read queries, the US only for failover. Is it possible to have a replication lag > in EU than the US replica due to the select queries?

I use this query to extract the replication lag:

SELECT EXTRACT(MILLISECONDS FROM now() - pg_last_xact_replay_timestamp())
  • 2
    What replication are you using? log shipping or the built in one?
    – DerfK
    Commented May 16, 2013 at 15:28

1 Answer 1


I'm guessing you're using postgresql's own streaming replication with the "hot standby" option rather than an add-on replication system like slony or bucardo.

With streaming replication, replication is paused whenever a transaction (including a single query) begins on the standby server in order to prevent replication from overwriting the data the query may be trying to use. Therefore it is possible that the replication lag would be higher in the EU. This is documented here.

The max_standby_streaming_delay configuration parameter on the slave replica controls how long postgresql will wait for a transaction to finish. If the transaction does not finish, postgresql will abort the transaction so that it can catch up on replication. By adjusting this setting you can reduce the amount of lag time queries can cause at the cost of having queries canceled when the server needs to catch up.

  • I use the "hot standby" option. Many thanks DefK your analysis is correct. Commented May 17, 2013 at 10:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .