This extension provide useful functions to compute Log Sequence Number in PostgreSQL
and show it in byte size.
These functions translate LSN in bigint :
create or replace function CalculateNumericalOffset(text) returns bigintcreate or replace function CalculateNumericalOffset(pg_lsn) returns bigint
These functions calculate difference between LSN :
create or replace function pg_streaming_lag( text, text ) returns bigintcreate or replace function pg_streaming_lag( pg_lsn, pg_lsn ) returns bigint
Before PostgreSQL 9.4, LSN data are stored as text.
These function can be used with the view pg_stat_replication, as shown in the
following queries :
select pid, backend_start, sync_state,
pg_size_pretty(pg_streaming_lag(sent_location,write_location)) as write_lag,
pg_size_pretty(pg_streaming_lag(sent_location,flush_location)) as flush_lag,
pg_size_pretty(pg_streaming_lag(sent_location,replay_location) ) as replay_lag
from pg_stat_replication ;
Differents Lags are calculate from sent_location on the master to write, flush
and replay on a standby.
Just use the makefile :
sudo make install
Then create the extension :
psql -Upostgres
CREATE EXTENSION streaminglag ;
And use it !
These functions come from these source :