Skip to content

Instantly share code, notes, and snippets.

@victorpendleton

victorpendleton/View Postgres Replica Status

Last active March 17, 2017 14:07
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save victorpendleton/72222dd4b2edb1c8f7f3d2995faa607f to your computer and use it in GitHub Desktop.
Save victorpendleton/72222dd4b2edb1c8f7f3d2995faa607f to your computer and use it in GitHub Desktop.
Download ZIP
Postgres lag and wal file
Raw
View Postgres Replica Status
SELECT '00000001' || LPAD(SUBSTRING(trim(' ' || pg_last_xlog_replay_location() || ' ') FROM 1 FOR position('/' in trim(' ' || pg_last_xlog_replay_location() || ' '))-1), 8, '0') || LPAD(SUBSTRING(trim(' ' || pg_last_xlog_replay_location() || ' ') FROM position('/' in trim(' ' || pg_last_xlog_replay_location() || ' '))+1 FOR 2), 8, '0') as last_wal
, SUBSTRING(trim(' ' || pg_last_xlog_replay_location() || ' ') FROM position('/' in trim(' ' || pg_last_xlog_replay_location() || ' '))+3 ) as last_lsn
, '00000001' || LPAD(SUBSTRING(trim(' ' || pg_last_xlog_receive_location() || ' ') FROM 1 FOR position('/' in trim(' ' || pg_last_xlog_receive_location() || ' '))-1), 8, '0') || LPAD(SUBSTRING(trim(' ' || pg_last_xlog_receive_location() || ' ') FROM position('/' in trim(' ' || pg_last_xlog_receive_location() || ' '))+1 FOR 2), 8, '0') as last_wal_rev
, SUBSTRING(trim(' ' || pg_last_xlog_receive_location() || ' ') FROM position('/' in trim(' ' || pg_last_xlog_receive_location() || ' '))+3 ) as last_lsn_recv
, CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0
ELSE round( CAST(EXTRACT(EPOCH FROM Now() - pg_last_xact_replay_timestamp()) as NUMERIC), 3) END As Seconds_behind
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment