public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-general list <[email protected]>
Subject: Re: Monitoring logical replication
Date: Tue, 18 Jun 2024 09:32:43 -0400
Message-ID: <CANzqJaA-s+y7dPUWKkbUXKBu3_WmvU4+ZOmWEzH7su83AT8ohg@mail.gmail.com> (raw)
In-Reply-To: <CAHAc2jccg964PHjxkmyD6XgknS2umxGsuNtVULo2P76N+uvvHQ@mail.gmail.com>
References: <CAHAc2jdAHvp7tFZBP37awcth=T3h5WXCN9KjZOvuTNJaAAC_hg@mail.gmail.com>
<[email protected]>
<CAHAc2jeDbVLz4UON4e-UdYA+5JCzrVZYN99E0SDk9FRgZWXu+Q@mail.gmail.com>
<CAHAc2jccg964PHjxkmyD6XgknS2umxGsuNtVULo2P76N+uvvHQ@mail.gmail.com>
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%40m...;,
> 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?
Attachments:
[text/x-sh] physical_backlog.sh (1.4K, 3-physical_backlog.sh)
download | inline:
#!/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)
view thread (4+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Monitoring logical replication
In-Reply-To: <CANzqJaA-s+y7dPUWKkbUXKBu3_WmvU4+ZOmWEzH7su83AT8ohg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox