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.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

"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 :-)

share|improve this answer
    
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 '12 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 '12 at 16:24

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.