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.

Assuming that I've got it hot server replication all set up nicely, what's the easiest or most convenient steps I can take periodically to ensure that the hot server replication is still working fine and as it should be.

I thought of comparing SELECT txid_current_snapshot(); on both servers. Is that good and safe enough to assume that replication is working fine if the 2 txids match?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

PostgreSQL's wiki has this information on its page on streaming replication:

You can calculate the replication lag by comparing the current WAL write location on the primary with the last WAL location received/replayed by the standby. They can be retrieved using pg_current_xlog_location on the primary and the pg_last_xlog_receive_location / pg_last_xlog_replay_location on the standby, respectively.

These functions return a string of the form id/offset where id and offset are hexadecimal numbers. To convert them to 64 bit numbers for comparison purposes, you may use this formula, taken from the check_postgres Nagios plugin:

0xff000000 * from_hex(id) + from_hex(offset)

In a bash script, it can be done with this function:

xlog_location_to_64bits()
{
  id="${1%%/*}"
  offset="${1##*/}"
  echo $((0xFF000000 * 0x$id + 0x$offset))
}

Subtracting the value on the slave from the value on the master gives the replication lag in bytes.

share|improve this answer
    
Thanks Daniel. That's very helpful. I'll give that a try. –  uzyn Jan 29 '13 at 6:22
add comment

For me, the most convenient method would be what is mentioned at step 12 of this page:

# The displayed LSNs indicate the byte position that the standby server has
# written up to in the xlogs.
[primary] $ ps -ef | grep sender
postgres  6879  6831  0 10:31 ?        00:00:00 postgres: wal sender process postgres    127.0.0.1(44663) streaming 0/2000000

[standby] $ ps -ef | grep receiver
postgres  6878  6872  1 10:31 ?        00:00:01 postgres: wal receiver process   streaming 0/2000000
share|improve this answer
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.