This page in other versions: 1.0 / 1.0.3

7.3. Monitoring replication peers

As outlined in Why monitoring matters it is important to monitor the state of peer nodes in a BDR group. There are two main views used for this: pg_stat_replication to monitor for actively replicating nodes, and pg_replication_slots to monitor for replication slot progress.

7.3.1. Monitoring connected peers using pg_stat_replication

Administrators may query pg_catalog.pg_stat_replication to monitor actively replicating connections. It shows the pid of the local side of the connection (wal sender process), the application name sent by the peer (for BDR, this is bdr (sysid,timeline,dboid,)), and other status information:

    SELECT * FROM pg_stat_replication;
      pid  | usesysid | usename |              application_name              | client_addr | client_hostname | client_port |         backend_start         | backend_xmin |   state   | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
    -------+----------+---------+--------------------------------------------+-------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
     29045 |    16385 | myadmin   | bdr (6127682459268878512,1,16386,):receive |             |                 |          -1 | 2015-03-18 21:03:28.717175+00 |              | streaming | 0/189D3B8     | 0/189D3B8      | 0/189D3B8      | 0/189D3B8       |             0 | async
     29082 |    16385 | myadmin   | bdr (6127682494973391064,1,16386,):receive |             |                 |          -1 | 2015-03-18 21:03:44.665272+00 |              | streaming | 0/189D3B8     | 0/189D3B8      | 0/189D3B8      | 0/189D3B8       |             0 | async
    

This view shows all active replication connections, not just those used by BDR. You will see connections from physical streaming replicas, other logical decoding solutions, etc here as well.

To tell how far behind a given active connection is, compare its flush_location (the replay position up to which it has committed its work) with the sending server's pg_current_xlog_insert_location() using pg_xlog_location_diff, e.g:

     SELECT
       pg_xlog_location_diff(pg_current_xlog_insert_location(), flush_location) AS lag_bytes,
       pid, application_name
     FROM pg_stat_replication;
    

This query will show how much lag downstream servers have from the upstream server you run the query on. You can't use this to see, from the downstream server, how far it is behind an upstream it's receiving from. Also, the query will show lag for all replication consumers, including non-BDR ones. To show only BDR peers, append WHERE application_name LIKE 'bdr%'.

Warning

pg_stat_replication does not show peers that have a slot but are not currently connected, even though such peers are still making the server retain WAL. It is important to monitor pg_replication_slots too.

There is not currently any facility to report how far behind a given node is in elapsed seconds of wall-clock time. So you can't easily tell that node X currently has data that is n seconds older than the original data on node Y. If this is an application requirement the application should write periodic timestamp tick records to a table and check how old the newest tick for a given node is on other nodes.

7.3.2. Monitoring replication slots

Information about replication slots (both logical and physical) is available in the pg_catalog.pg_replication_slots view. This view shows all slots, whether or not there is an active replication connection using them. It looks like:

    SELECT * FROM pg_replication_slots;
                    slot_name                | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn
    -----------------------------------------+--------+-----------+--------+----------+--------+------+--------------+-------------
     bdr_16386_6127682459268878512_1_16386__ | bdr    | logical   |  16386 | bdrdemo  | t      |      |          749 | 0/191B130
     bdr_16386_6127682494973391064_1_16386__ | bdr    | logical   |  16386 | bdrdemo  | t      |      |          749 | 0/191B130
    (2 rows)
    

If a slot has active = t then there will be a corresponding pg_stat_replication entry for the walsender process connected to the slot.

This view shows only replication peers that use a slot. Physical streaming replication connections that don't use slots will not show up here, only in pg_stat_replication. BDR always uses slots so all BDR peers will appear here.

To see how much extra WAL BDR slot is asking the server to keep, in bytes, use a query like:

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

Retained WAL isn't additive; if you have three peers, who of which require 500KB of WAL to be retained and one that requires 8MB, only 8MB is retained. It's like a dynamic version of the wal_keep_segments setting (or, in 9.5, min_wal_size). So you need to monitor to make sure that the largest amount of retained WAL doens't exhaust the free space in pg_xlog on each node.

It is normal for pg_replication_slots.restart_lsn not to advance as soon as pg_stat_replication.flush_location advances on an active connection. The slot restat position does not indicate how old the data you will see on a peer node is.