Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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())
share|improve this question
2  
What replication are you using? log shipping or the built in one? –  DerfK May 16 '13 at 15:28
add comment

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
    
I use the "hot standby" option. Many thanks DefK your analysis is correct. –  ilmerovingio May 17 '13 at 10:11
add comment

Your Answer

 
discard

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.