How to determine Single Master XDB replication lag

I recently had a few customers ask me about using a query for monitoring SMR XDB lag, so they can keep tabs on replication progress and set up notifications.

 

Unfortunately, because of the way WAL works, it's a bit tedious to try to quantify lag in terms of rows (You'd have to take each WAL file, scan, and filter for unrelated WAL entries, based on sync_ids in the XDB control database).  Additionally, tracking WAL-based SMR replication lag cannot be done with an  earlier solution.  Fortunately, it is possible to calculate the time lag with a query:

 

WITH src_db (current_xid_commit_timestamp) AS (SELECT timestamp FROM pg_last_committed_xact()), -- last replicated timestamp for target databases target_db (target_db_id, last_repl_xid_timestamp) AS (SELECT sub_db_id AS target_db_id, MAX(last_repl_xid_timestamp) AS last_repl_xid_timestamp FROM _edb_replicator_pub.rrep_txset rt JOIN _edb_replicator_sub.xdb_subscriptions xs ON xs.sub_id=rt.sub_id WHERE status = 'C' GROUP BY sub_db_id), -- identity of target databases target_db_identity (target_db_id, target_db_identity) AS (SELECT sub_db_id, db_host || ':' || db_port || ':' || db_name FROM _edb_replicator_sub.xdb_sub_database) -- replication lag for each of the target databases SELECT c.target_db_id, target_db_identity, AGE(current_xid_commit_timestamp, last_repl_xid_timestamp) repl_time_lag FROM src_db a, target_db b, target_db_identity c WHERE b.target_db_id = c.target_db_id ORDER BY target_db_id;

By examining the replication timestamps, we are able to get a good handle on the time delay between the associated servers.  Additionally, with some more tweaking, we can use a similar query to calculate time lag for Multi-Master Replication (MMR) clusters:

 

WITH src_db (current_xid_commit_timestamp) AS (SELECT timestamp FROM pg_last_committed_xact()), -- last replicated timestamp for target databases target_db (target_db_id, last_repl_xid_timestamp) AS (SELECT db_id AS target_db_id, MAX(last_repl_xid_timestamp) AS last_repl_xid_timestamp FROM _edb_replicator_pub.rrep_mmr_txset WHERE status = 'C' GROUP BY db_id), -- identity of target databases target_db_identity (target_db_id, target_db_identity) AS (SELECT pub_db_id, db_host || ':' || db_port || ':' || db_name FROM _edb_replicator_pub.xdb_pub_database) -- replication lag for each of the target databases SELECT c.target_db_id, target_db_identity, AGE(current_xid_commit_timestamp, last_repl_xid_timestamp) repl_time_lag FROM src_db a, target_db b, target_db_identity c WHERE b.target_db_id = c.target_db_id ORDER BY target_db_id;

With these queries, DBAs and sysadmins can easily set up notification and monitoring systems for XDB performance.