A query to get postgres streaming replication lag

postgres-replication-lag-functions.sql

BEGIN;
CREATE OR REPLACE FUNCTION primary_streaming_byte_lag() RETURNS TABLE (client_hostname text, client_addr inet, log_location_diff_bytes numeric, total_byte_lag double precision, total_byte_lag_pretty text, replay_byte_lag double precision, replay_byte_lag_pretty text)
  LANGUAGE SQL SECURITY DEFINER
AS $$
    SELECT
      client_hostname,
      client_addr,
      log_location_diff_bytes,
      ( (cur_xlog * 255 * 16 ^ 6) + cur_offset) - ((replay_xlog * 255 * 16 ^ 6) + replay_offset) AS total_byte_lag,
      pg_size_pretty((( (cur_xlog * 255 * 16 ^ 6) + cur_offset) - ((replay_xlog * 255 * 16 ^ 6) + replay_offset))::numeric) AS total_byte_lag_pretty,
      ((sent_xlog * 255 * 16 ^ 6) + sent_offset) - ((replay_xlog * 255 * 16 ^ 6) + replay_offset) AS replay_byte_lag,
      pg_size_pretty((( (sent_xlog * 255 * 16 ^ 6) + sent_offset) - ((replay_xlog * 255 * 16 ^ 6) + replay_offset))::numeric) AS replay_byte_lag_pretty
    FROM (
      SELECT
        client_hostname,
        client_addr,
        pg_xlog_location_diff(sent_location, replay_location) AS log_location_diff_bytes,
        ('x' || lpad(split_part(sent_location, '/', 1), 8, '0'))::bit(32)::bigint AS sent_xlog,
        ('x' || lpad(split_part(replay_location, '/', 1), 8, '0'))::bit(32)::bigint AS replay_xlog,
        ('x' || lpad(split_part(sent_location, '/', 2), 8, '0'))::bit(32)::bigint AS sent_offset,
        ('x' || lpad(split_part(replay_location, '/', 2), 8, '0'))::bit(32)::bigint AS replay_offset,
        ('x' || lpad(split_part(pg_current_xlog_location(), '/', 1), 8, '0'))::bit(32)::bigint AS cur_xlog,
        ('x' || lpad(split_part(pg_current_xlog_location(), '/', 2), 8, '0'))::bit(32)::bigint AS cur_offset
      FROM pg_stat_replication
    ) AS s;
$$;
REVOKE ALL ON FUNCTION primary_streaming_byte_lag() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION primary_streaming_byte_lag() to monitor_user;
COMMIT;

BEGIN;
CREATE OR REPLACE FUNCTION secondary_streaming_time_lag() RETURNS TABLE (secondary_replay_delay double precision)
  LANGUAGE SQL SECURITY DEFINER
AS $$
    SELECT
      CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()
        THEN 0
      ELSE
        EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
      END AS secondary_replay_delay;
$$;
REVOKE ALL ON FUNCTION secondary_streaming_time_lag() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION secondary_streaming_time_lag() to monitor_user;
COMMIT;

primary-output.txt

postgres=# select * from primary_streaming_byte_lag();
 client_hostname |  client_addr   | log_location_diff_bytes | total_byte_lag | total_byte_lag_pretty | replay_byte_lag | replay_byte_lag_pretty 
-----------------+----------------+-------------------------+----------------+-----------------------+-----------------+------------------------
                 | 192.168.56.105 |                    2784 |           2784 | 2784 bytes            |            2784 | 2784 bytes
                 | 192.168.56.106 |                     384 |           2784 | 2784 bytes            |             384 | 384 bytes
(2 rows)

secondary-output.txt

postgres=# select * from secondary_streaming_time_lag();
 secondary_replay_delay 
------------------------
                      0
(1 row)

Based on a comment on this post http://www.keithf4.com/monitoring_streaming_slave_lag.