2

In order to monitor replication delay from one PostgreSQL server to another, I am using a simple script which runs the query "SELECT pg_current_xlog_location()" on the master server and "SELECT pg_last_xlog_receive_location()" on the slave. Then I convert the results from hex into decimal and calculate the difference to get the replication delay.

My problem is I cannot figure out what units this xlog_location is returned in. Can anyone explain this?

2 Answers 2

2

"The units don't matter" (but if you're curious, the units are position in the transaction log -- there's a bit of a discussion here).

It's important to note that there is NO positive correlation between log position and time.
Big transactions may move the log forward a huge amount in a short period.
Lightly used databases may sit at the same log point for hours (or longer).

All you can know from this measurement is how far behind in the log you are (i.e. that you are or are not synchronized with the master, and roughly how much data needs to be sent/replayed).

There's some more discussion here in the Postgres Wiki, but judging by your question I think you've already read this page - It may be worth it to ask on the Postgres pgsql-admin mailing list for clarification (and you might turn up a better answer than what I've given you, and maybe be able to update the Postgres Wiki too :-)

2
  • Thanks voretaq7 that does make sense, and I understand the log would be a measure of data not time. The issue I am having is how could I know if we are at risk of filling the buffer on the master. To say another way, is it possible with this calculation of the replication delay to know if the slave is getting so far behind it will never be able to catch-up?
    – NickTX
    Jun 14, 2012 at 16:20
  • @NickTX It's not really possible to know if you'll never catch up (you could have a major burst of data all day, then a quiet night where it catches up as a regular occurrence). You can see if the gap keeps growing though, and possibly set an alarm point when the backlog is over a certain point. I want to say the log units are pages (8K), but don't hold me to that - I haven't looked at the transaction log system in a long time...
    – voretaq7
    Jun 14, 2012 at 16:24
1

The log unit are in bytes (though they are relative - so they aren't useful for measuring anything other than "offsets", and there sometimes can be "jumps" where there's a lot of skipped bytes), with the value being computed as:

If you take the output of: SELECT pg_current_xlog_location() you'll get something like:

70/A9002358

The part before the "/" is multiplied by 'ff000000' and added to the second part:

in python parlance (converting hex to into with the int('HEX',16) function) that might look like this:

int('ff000000',16)*int('70',16) + int('A9002358',16)

You could locate the current WAL file name in use using:

select pg_xlogfile_name(pg_current_xlog_insert_location());

Technically it's "possible" for a slave to catch up with the master if the log files that the slave needs are still available on the master. Of course, if the slave is replaying slowly it might never catch up - but you can measure if it is "catching up" or "falling behind" (sort of) using the query below:

On the slave you can get an idea of the time delay using the query:

SELECT extract(epoch from (now()-
              pg_last_xact_replay_timestamp())) AS time_lag;

However, the number returned there is actually the "time since the last replayed transaction from the master" - so if the master hasn't had a transaction in awhile that "time" might make it seem like the slave is falling behind (when it reality, it is caught up, but there have been no transactions on the master.)

You must log in to answer this question.

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