Added new postgres charts and updated standby charts to include slot_… (#11267)

2 files changed, 107 insertions, 11 deletions

diff --git a/collectors/python.d.plugin/postgres/postgres.chart.py b/collectors/python.d.plugin/postgres/postgres.chart.py
index 09c30f7956..cab8f6161f 100644
--- a/collectors/python.d.plugin/postgres/postgres.chart.py
+++ b/collectors/python.d.plugin/postgres/postgres.chart.py
@@ -55,6 +55,8 @@ QUERY_NAME_REPSLOT_FILES = 'REPSLOT_FILES'
 QUERY_NAME_IF_SUPERUSER = 'IF_SUPERUSER'
 QUERY_NAME_SERVER_VERSION = 'SERVER_VERSION'
 QUERY_NAME_AUTOVACUUM = 'AUTOVACUUM'
+QUERY_NAME_FORCED_AUTOVACUUM = 'FORCED_AUTOVACUUM'
+QUERY_NAME_TX_WRAPAROUND = 'TX_WRAPAROUND'
 QUERY_NAME_DIFF_LSN = 'DIFF_LSN'
 QUERY_NAME_WAL_WRITES = 'WAL_WRITES'
 
@@ -135,6 +137,13 @@ METRICS = {
         'vacuum_freeze',
         'brin_summarize'
     ],
+    QUERY_NAME_FORCED_AUTOVACUUM: [
+        'percent_towards_forced_vacuum'
+    ],
+    QUERY_NAME_TX_WRAPAROUND: [
+        'oldest_current_xid',
+        'percent_towards_wraparound'
+    ],
     QUERY_NAME_STANDBY_DELTA: [
         'sent_delta',
         'write_delta',
@@ -453,10 +462,10 @@ WHERE
 QUERY_STANDBY = {
     DEFAULT: """
 SELECT
-    application_name
-FROM pg_stat_replication
-WHERE application_name IS NOT NULL
-GROUP BY application_name;
+    COALESCE(prs.slot_name, psr.application_name) application_name
+FROM pg_stat_replication psr
+LEFT OUTER JOIN pg_replication_slots prs on psr.pid = prs.active_pid
+WHERE application_name IS NOT NULL;
 """,
 }
 
@@ -470,7 +479,7 @@ FROM pg_replication_slots;
 QUERY_STANDBY_DELTA = {
     DEFAULT: """
 SELECT
-    application_name,
+    COALESCE(prs.slot_name, psr.application_name) application_name,
     pg_wal_lsn_diff(
       CASE pg_is_in_recovery()
         WHEN true THEN pg_last_wal_receive_lsn()
@@ -495,12 +504,13 @@ SELECT
         ELSE pg_current_wal_lsn()
       END,
     replay_lsn) AS replay_delta
-FROM pg_stat_replication
+FROM pg_stat_replication psr
+LEFT OUTER JOIN pg_replication_slots prs on psr.pid = prs.active_pid
 WHERE application_name IS NOT NULL;
 """,
     V96: """
 SELECT
-    application_name,
+    COALESCE(prs.slot_name, psr.application_name) application_name,
     pg_xlog_location_diff(
       CASE pg_is_in_recovery()
         WHEN true THEN pg_last_xlog_receive_location()
@@ -525,7 +535,8 @@ SELECT
         ELSE pg_current_xlog_location()
       END,
     replay_location) AS replay_delta
-FROM pg_stat_replication
+FROM pg_stat_replication psr
+LEFT OUTER JOIN pg_replication_slots prs on psr.pid = prs.active_pid
 WHERE application_name IS NOT NULL;
 """,
 }
@@ -533,11 +544,12 @@ WHERE application_name IS NOT NULL;
 QUERY_STANDBY_LAG = {
     DEFAULT: """
 SELECT
-    application_name,
+    COALESCE(prs.slot_name, psr.application_name) application_name,
     COALESCE(EXTRACT(EPOCH FROM write_lag)::bigint, 0) AS write_lag,
     COALESCE(EXTRACT(EPOCH FROM flush_lag)::bigint, 0) AS flush_lag,
     COALESCE(EXTRACT(EPOCH FROM replay_lag)::bigint, 0) AS replay_lag
-FROM pg_stat_replication
+FROM pg_stat_replication psr
+LEFT OUTER JOIN pg_replication_slots prs on psr.pid = prs.active_pid
 WHERE application_name IS NOT NULL;
 """
 }
@@ -650,6 +662,43 @@ WHERE query NOT LIKE '%%pg_stat_activity%%';
 """,
 }
 
+QUERY_FORCED_AUTOVACUUM = {
+    DEFAULT: """
+WITH max_age AS (
+    SELECT setting AS autovacuum_freeze_max_age
+        FROM pg_catalog.pg_settings
+        WHERE name = 'autovacuum_freeze_max_age' )
+, per_database_stats AS (
+    SELECT datname
+        , m.autovacuum_freeze_max_age::int
+        , age(d.datfrozenxid) AS oldest_current_xid
+    FROM pg_catalog.pg_database d
+    JOIN max_age m ON (true)
+    WHERE d.datallowconn )
+SELECT max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_forced_autovacuum
+FROM per_database_stats;
+""",
+}
+
+QUERY_TX_WRAPAROUND = {
+    DEFAULT: """
+WITH max_age AS (
+    SELECT 2000000000 as max_old_xid
+        FROM pg_catalog.pg_settings
+        WHERE name = 'autovacuum_freeze_max_age' )
+, per_database_stats AS (
+    SELECT datname
+        , m.max_old_xid::int
+        , age(d.datfrozenxid) AS oldest_current_xid
+    FROM pg_catalog.pg_database d
+    JOIN max_age m ON (true)
+    WHERE d.datallowconn )
+SELECT max(oldest_current_xid) AS oldest_current_xid
+    , max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound
+FROM per_database_stats;
+""",
+}
+
 QUERY_DIFF_LSN = {
     DEFAULT: """
 SELECT
@@ -712,6 +761,10 @@ def query_factory(name, version=NO_VERSION):
         return QUERY_SHOW_VERSION[DEFAULT]
     elif name == QUERY_NAME_AUTOVACUUM:
         return QUERY_AUTOVACUUM[DEFAULT]
+    elif name == QUERY_NAME_FORCED_AUTOVACUUM:
+        return QUERY_FORCED_AUTOVACUUM[DEFAULT]
+    elif name == QUERY_NAME_TX_WRAPAROUND:
+        return QUERY_TX_WRAPAROUND[DEFAULT]
     elif name == QUERY_NAME_WAL:
         if version < 100000:
             return QUERY_WAL[V96]
@@ -767,7 +820,10 @@ ORDER = [
     'replication_slot',
     'standby_delta',
     'standby_lag',
-    'autovacuum'
+    'autovacuum',
+    'forced_autovacuum',
+    'tx_wraparound_oldest_current_xid',
+    'tx_wraparound_percent_towards_wraparound'
 ]
 
 CHARTS = {
@@ -954,6 +1010,24 @@ CHARTS = {
             ['brin_summarize', 'brin summarize', 'absolute']
         ]
     },
+    'forced_autovacuum': {
+        'options': [None, 'Percent towards forced autovacuum', 'percent', 'autovacuum', 'postgres.forced_autovacuum', 'line'],
+        'lines': [
+            ['percent_towards_forced_autovacuum', 'percent', 'absolute']
+        ]
+    },
+    'tx_wraparound_oldest_current_xid': {
+        'options': [None, 'Oldest current XID', 'xid', 'tx_wraparound', 'postgres.tx_wraparound_oldest_current_xid', 'line'],
+        'lines': [
+            ['oldest_current_xid', 'xid', 'absolute']
+        ]
+    },
+    'tx_wraparound_percent_towards_wraparound': {
+        'options': [None, 'Percent towards wraparound', 'percent', 'tx_wraparound', 'postgres.percent_towards_wraparound', 'line'],
+        'lines': [
+            ['percent_towards_wraparound', 'percent', 'absolute']
+        ]
+    },
     'standby_delta': {
         'options': [None, 'Standby delta', 'KiB', 'replication delta', 'postgres.standby_delta', 'line'],
         'lines': [
@@ -1180,6 +1254,9 @@ class Service(SimpleService):
         if self.server_version >= 90400:
             self.queries[query_factory(QUERY_NAME_AUTOVACUUM)] = METRICS[QUERY_NAME_AUTOVACUUM]
 
+        self.queries[query_factory(QUERY_NAME_FORCED_AUTOVACUUM)] = METRICS[QUERY_NAME_FORCED_AUTOVACUUM]
+        self.queries[query_factory(QUERY_NAME_TX_WRAPAROUND)] = METRICS[QUERY_NAME_TX_WRAPAROUND]
+
         if self.server_version >= 100000:
             self.queries[query_factory(QUERY_NAME_STANDBY_LAG)] = METRICS[QUERY_NAME_STANDBY_LAG]
 
diff --git a/web/gui/dashboard_info.js b/web/gui/dashboard_info.js
index 8807548bd4..a1188e332d 100644
--- a/web/gui/dashboard_info.js
+++ b/web/gui/dashboard_info.js
@@ -1684,6 +1684,25 @@ netdataDashboard.context = {
             'Assuming non-superuser accounts are being used to connect to Postgres (so <i>superuser_reserved_connections</i> are subtracted from <i>max_connections</i>).<br/>' +
             'For more information see <a href="https://www.postgresql.org/docs/current/runtime-config-connection.html" target="_blank">Connections and Authentication</a>.'
     },
+    'postgres.forced_autovacuum': {
+        info: 'Percent towards forced autovacuum for one or more tables.<ul>' +
+            '<li><strong>percent_towards_forced_autovacuum:</strong> a forced autovacuum will run once this value reaches 100.</li>' +
+            '</ul>' +
+            'For more information see <a href="https://www.postgresql.org/docs/current/routine-vacuuming.html" target="_blank">Preventing Transaction ID Wraparound Failures</a>.'
+    },
+    'postgres.tx_wraparound_oldest_current_xid': {
+        info: 'The oldest current transaction id (xid).<ul>' +
+            '<li><strong>oldest_current_xid:</strong> oldest current transaction id.</li>' +
+            '</ul>' +
+            'If for some reason autovacuum fails to clear old XIDs from a table, the system will begin to emit warning messages when the database\'s oldest XIDs reach eleven million transactions from the wraparound point.<br/>' +
+            'For more information see <a href="https://www.postgresql.org/docs/current/routine-vacuuming.html" target="_blank">Preventing Transaction ID Wraparound Failures</a>.'
+    },
+    'postgres.percent_towards_wraparound': {
+        info: 'Percent towards transaction wraparound.<ul>' +
+            '<li><strong>percent_towards_wraparound:</strong> transaction wraparound may occur when this value reaches 100.</li>' +
+            '</ul>' +
+            'For more information see <a href="https://www.postgresql.org/docs/current/routine-vacuuming.html" target="_blank">Preventing Transaction ID Wraparound Failures</a>.'
+    },
 
 
     // ------------------------------------------------------------------------