0

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())
j0k
  • 411
  • 9
  • 16
ilmerovingio
  • 3
  • 2

1 Answers1

0

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.

DerfK
  • 19,493
  • 2
  • 38
  • 54