Checking PostgreSQL replication lag with pg_last_xact_replay_timestamp()
Recently we got alerted by our NetOps team about a replication lag in one PostgreSQL database. That by itself is not a major problem, nevertheless something which deserves investigation. NetOps is using Zabbix, and the replication lag in the database system in question is measured using a plpgsql function provided by an open source template. In the replica, not in the primary. DO LANGUAGE plpgsql $$ DECLARE ver integer; res text; BEGIN SELECT current_setting('server_version_num') INTO ver; IF (ver >= 100000) THEN SELECT * INTO res from ( SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0 ELSE COALESCE(EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp())::integer, 0) END ) T; ELSE SELECT * INTO res from ( SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE COALESCE(EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp())::integer, 0) END ) T; END IF; perform set_config('zbx_tmp....