On Tue, Jun 18, 2024 at 5:03 AM Shaheed Haque <shaheedhaque@xxxxxxxxx> wrote:
Hi all,Is there an "official" pairing of LSN values on the publication and subscription sides that should be used to track the delta between the two systems? I ask because Google is full of different pairs being used. I tried to identify the highest level interface points exposed, i.e. what is documented on https://www.postgresql.org/docs/current/replication-origins.html, the pg_stat_subscription table, the pg_stat_publication table and the pg_current_wal_lsn() function on the publisher, but these seem to be barely used.
The attached scripts (whose guts I took from a Stack Exchange post) might be a good starting point. It certainly works for physical replication!
P.S. On a related note, I see a (stalled?) discussion on providing LSN -> timestamp conversion, I'd just like to say that something like that would be very useful.
Out of curiosity, how does that work? Is an instance's initial LSN really based on Epoch?
#!/bin/bash
readonly Prime=foo1.example.com
readonly Replica=foo2.example.com
wal_location_to_64bits()
{
local id="${1%%/*}"
local offset="${1##*/}"
echo $((0xFF000000 * 0x$id + 0x$offset))
}
declare Prime_SSE Prime_LSN
declare Second_SSE Second_Recv_LSN Second_Repl_LSN
IFS=$'\t' read -r Prime_LSN \
<<<$(psql --host=$Prime -XAt -F$'\t' \
-c "select pg_current_wal_lsn();")
IFS=$'\t' read -r Second_Timestamp Second_Recv_LSN Second_Repl_LSN \
<<<$(psql --host=$Replica -XAt -F$'\t' \
-c "select pg_last_wal_receive_lsn()
, pg_last_wal_replay_lsn();")
Prime_SSE=$(date +"%s.%N" -d "$Prime_Timestamp")
Second_SSE=$(date +"%s.%N" -d "$Second_Timestamp")
declare Query_Lag=$(echo "$Second_SSE - $Prime_SSE" | bc -l)
printf "Query Lag: %f\n" $Query_Lag
echo "LSN:"
printf " Prime: = %s\n" $Prime_LSN
printf " Replica Received: = %s\n" $Second_Recv_LSN
printf " Replica Replayed: = %s\n" $Second_Repl_LSN
declare -i Prime_Bytes=$(wal_location_to_64bits $Prime_LSN)
declare -i Second_Recv_Bytes=$(wal_location_to_64bits $Second_Recv_LSN)
declare -i Second_Repl_Bytes=$(wal_location_to_64bits $Second_Repl_LSN)
echo "Backlog Bytes:"
printf " Received = %'18d\n" $(echo "($Prime_Bytes - $Second_Recv_Bytes)" | bc -l)
printf " Replayed = %'18d\n" $(echo "($Prime_Bytes - $Second_Repl_Bytes)" | bc -l)
