Re: bdr replication latency monitoring - Mailing list pgsql-general

Craig,

Your response was very helpful, thank you.

I was looking at some of the standard bits in Postgres like txid_current_snapshot() and txid_snapshot_xmin().  Can the
resultsfrom txid_snapshot_xmin be used with pg_get_transaction_committime() to get the latency? 

Thanks again,
Steve Boyle

____________________________________________________________________

Steve,

The relevant change was made during the commit of logical decoding to
PostgreSQL 9.4, where the field of interest was renamed from 'xmin' to
'catalog_xmin'. It's around then that pg_stat_logical_decoding was renamed
to pg_replication_slots too.

To get lag in bytes, use:

SELECT slot_name, database, active,
pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn)
FROM pg_replication_slots
WHERE plugin = 'bdr';

The catalog_xmin doesn't really reflect lag at all. Replay may have
continued past that xid and fully caught up. Additionally, the commit
timestamp records for the catalog xmin may be truncated away, rendering its
commit time unknown and causing pg_get_transaction_committime(...) to
report the epoch 2000-01-01 as the commit time. So using
pg_get_transaction_committime on the catalog xmin isn't as useful as it was
in earlier versions of BDR. I don't currently have a good way to get you a
sense of replay lag in wall-clock time and will need to get back to you on
that one.


Note that we're in the process of updating all that documentation, moving
it into the same SGML format used for PostgreSQL's official documentation
and putting it in the BDR source tree. Some of the documentation on the
wiki has become outdated since 0.7.x as a result. The coming 0.9.x release
will bundle the documentation in the source tree and make the wiki docs
obsolete.

Thanks for your patience in the mean time. Please bring up any other issues
you encounter, as it'll help make sure I and the rest of the team don't
miss anything.





On 14 March 2015 at 03:06, Steve Boyle <sboyle(at)connexity(dot)com> wrote:

> I'm trying to follow the BDR monitoring docs:
> https://wiki.postgresql.org/wiki/BDR_Monitoring
>
> My postgres version string is (its from the 2nd Quadrant repo):
> PostgreSQL 9.4.0 on x86_64-unknown-linux-gnu, compiled by gcc
> (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
>
> My BDR plugin is version 0.8.0beta1
>
> From the docs, I've come up with this query:
> select slot_name, plugin, database, active, xmin,
>             pg_get_transaction_committime(xmin)
>             FROM pg_replication_slots ;
>
> BDR is working.  When I run that query, the 'xmin' value is always null,
> even though there is activity on the database.  I do/can get a catalog_xmin
> value.  Should I expect the 'xmin' value to be null?  Is there another way
> to monitor the replication latency when using BDR?
>
> Thanks,
> Steve Boyle
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>