Logical replication lag in seconds

From: Klaus Darilion <klaus(dot)mailinglists(at)pernau(dot)at>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Logical replication lag in seconds
Date: 2020-02-21 16:12:44
Message-ID: be7464e5-1859-8da2-1419-55b648af4f2d@pernau.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello!

I currently use Slony for replication and want to switch to logical
replication. With Slony I was aware of the replication lag of each slave
in seconds. This info was available regardless if a slave was online or
offline.

For my application I need to know the "delay" of all replicas, wether
they are currently active or not. I.e. a replica may be on the other
side of the world, serving queries there, but has problems to connect to
the master. Even in this case I need to know the replication status of
the replica.

With logical replication, it seems the only available data, eve in case
a replica is offline, is in the pg_replication_slots table, for example:

-[ RECORD 1 ]-------+---------------
slot_name | test_6
plugin | pgoutput
slot_type | logical
datoid | 16402
database | mydns
temporary | f
active | f
active_pid |
xmin |
catalog_xmin | 1116191193
restart_lsn | 2EA/E61E7FA8
confirmed_flush_lsn | 2EA/E626F0B0

So I guess, the replication status of this replica is the
"confirmed_flush_lsn", ie: 2EA/E626F0B0

But how to I get from 2EA/E626F0B0 to a timestamp when this transaction
was added to the WAL files?

I would appreciate any hints, or other methods to get the delay in some
time format.

thanks
Klaus

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Patrick FICHE 2020-02-21 16:24:28 RE: Can we have multiple tablespaces with in a database.
Previous Message David G. Johnston 2020-02-21 14:27:08 Re: How to get the name of the table taht fired a triigger?