The problem with this is that it’s not an atomic unit of work and is GUARANTEED to be inaccurate (it can be done quickly, minimizing the margin of error). Also, it involves remote connections to multiple hosts, which I’m not interested in for my use case. If we want to calculate this difference from the master we can simply issue the following command:

and compare pg_current_xlog_location with the write location. This approach works, but will become a bit more interesting in 9.2, with the addition of the pg_xlog_location_diff function, which calculates the difference between two transaction log locations.

Getting the replication delay from only the slave, though, is a bit more circuitous. We’ll need to use the pg_last_xact_replay_timestamp() function, which gets time stamp of last transaction replayed during recovery. This by itself won’t be terribly interesting, but we can approximate the delay as follows:

Here, the replica is less than one second behind the master. In a very busy system, with many writes per second, this number will remain fairly accurate. However, in a system where there are few writes, the “replication_delay” will continually grow because the last replayed transaction timestamp isn’t increasing (this is generally the same limitation as MySQL’s SHOW SLAVE STATUS output). We can get around this, though, by introducing an external heartbeat, which will minimize the drift that will manifest with this approach.