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

From Craig Ringer
Subject Re: bdr replication latency monitoring
Date
Msg-id CAMsr+YEqeFXDS=AmqBZMopQDFnt+hO50uV-+s854KW9oA-XKkA@mail.gmail.com
Whole thread Raw
In response to bdr replication latency monitoring  (Steve Boyle <sboyle@connexity.com>)
List pgsql-general
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@connexity.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

pgsql-general by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: pitr archive_command cp fsync
Next
From: Alvaro Herrera
Date:
Subject: Re: How does one make the following psql statement sql-injection resilient?