On Tue, Jun 18, 2024 at 5:03 AM Shaheed Haque <[email protected]>
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
> <https://www.postgresql.org/message-id/flat/CAAKRu_bw7Pgw8Mi9LJrBkFvPPHgvVjPphrT8ugbzs-2V0f%2B1Rw%40mail.gmail.com#8540282228634ecd061585867c6275ca>,
> 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)
