Re: [GENERAL] bdr replication latency monitoring
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 results from 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bdr replication latency monitoring
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 sbo...@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
[GENERAL] bdr replication latency monitoring
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
