Calculate the lag in WAL transmission for postgres databases

db_lag.rb

# math based on http://eulerto.blogspot.com/2011/11/understanding-wal-nomenclature.html

dbname = ""
dbuser = ""
host_master = ""
host_slave  = ""

previous_recieved_lags = []
previous_replated_lags = []

previous_recieved_lag  = 0
previous_replated_lag  = 0


while true
  xlog_master,  offset_master    = `psql -h #{host_master} -U #{dbuser} #{dbname} -c "SELECT pg_current_xlog_location()"      `.split[2].split('/')
  xlog_recieve, offset_recieve   = `psql -h #{host_slave}  -U #{dbuser} #{dbname} -c "SELECT pg_last_xlog_receive_location()" `.split[2].split('/')
  xlog_replayed, offset_replayed = `psql -h #{host_slave}  -U #{dbuser} #{dbname} -c "SELECT pg_last_xlog_replay_location()"  `.split[2].split('/')
  system "clear"

  puts "Current:  #{xlog_master}/#{offset_master}"
  puts "Received: #{xlog_recieve}/#{offset_recieve}"
  puts "Replayed: #{xlog_replayed}/#{offset_replayed}"
  puts

  raise "fuck" if xlog_master.hex != "0x#{xlog_master}".hex

  recieved_lag = ("0xFF000000".hex * xlog_master.hex + offset_master.hex) - ("0xFF000000".hex * xlog_recieve.hex + offset_recieve.hex)
  replayed_lag = ("0xFF000000".hex * xlog_master.hex + offset_master.hex) - ("0xFF000000".hex * xlog_replayed.hex + offset_replayed.hex)

  recieved_delta = recieved_lag - previous_recieved_lag
  replayed_delta = replayed_lag - previous_replated_lag


  puts "Received Lag:   #{recieved_lag/1024.0/1024.0/1024.0} GB" 
  puts "Received Delta: #{recieved_delta/1024.0/1024.0} MB"  
  puts
  
  puts "Replayed Lag:   #{replayed_lag/1024.0/1024.0/1024.0} GB"
  puts "Replayed Delta: #{replayed_delta/1024.0/1024.0} MB"
  puts
  
  previous_recieved_lag = recieved_lag
  previous_replated_lag = replayed_lag

  sleep 1
end