SQL queries to show Postgresql streaming replication delay

replication_delay.sql

/***
 * Secondary server commands.
 */
// Show the number of seconds between now and the last replayed transaction.
SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT;

// Show the number of minutes between now and the last replayed transaction.
SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT / 60;

// Get last transaction log location received and synced to disk by streaming replication.
SELECT pg_last_xlog_receive_location();

/***
 * Primary server commands.
 */
// Get current transaction log write location.
SELECT pg_current_xlog_location();