Added connections (backend) usage to postgres monitoring (#8126)

* Added connections (backend) usage to postgres monitoring

* Adjusted PostgreSQL connection usage against server version

* Changed PostgreSQL connection usage against server version

* Fixed chart PG Backend Usage family and improved documentation

2 files changed, 113 insertions, 9 deletions

diff --git a/collectors/python.d.plugin/postgres/README.md b/collectors/python.d.plugin/postgres/README.md
index ce0c3ac5e2..6ab1211ee1 100644
--- a/collectors/python.d.plugin/postgres/README.md
+++ b/collectors/python.d.plugin/postgres/README.md
@@ -16,44 +16,49 @@ Following charts are drawn:
 
     -   active
 
-3.  **Write-Ahead Logging Statistics** files/s
+3.  **Current Backend Processe Usage** percentage
+
+    -   used
+    -   available
+
+4.  **Write-Ahead Logging Statistics** files/s
 
     -   total
     -   ready
     -   done
 
-4.  **Checkpoints** writes/s
+5.  **Checkpoints** writes/s
 
     -   scheduled
     -   requested
 
-5.  **Current connections to db** count
+6.  **Current connections to db** count
 
     -   connections
 
-6.  **Tuples returned from db** tuples/s
+7.  **Tuples returned from db** tuples/s
 
     -   sequential
     -   bitmap
 
-7.  **Tuple reads from db** reads/s
+8.  **Tuple reads from db** reads/s
 
     -   disk
     -   cache
 
-8.  **Transactions on db** transactions/s
+9.  **Transactions on db** transactions/s
 
     -   committed
     -   rolled back
 
-9.  **Tuples written to db** writes/s
+10.  **Tuples written to db** writes/s
 
     -   inserted
     -   updated
     -   deleted
     -   conflicts
 
-10. **Locks on db** count per type
+11. **Locks on db** count per type
 
     -   locks
 
diff --git a/collectors/python.d.plugin/postgres/postgres.chart.py b/collectors/python.d.plugin/postgres/postgres.chart.py
index 9e30203589..ed2e2de583 100644
--- a/collectors/python.d.plugin/postgres/postgres.chart.py
+++ b/collectors/python.d.plugin/postgres/postgres.chart.py
@@ -39,6 +39,7 @@ CONN_PARAM_SSL_KEY = 'sslkey'
 QUERY_NAME_WAL = 'WAL'
 QUERY_NAME_ARCHIVE = 'ARCHIVE'
 QUERY_NAME_BACKENDS = 'BACKENDS'
+QUERY_NAME_BACKEND_USAGE = 'BACKEND_USAGE'
 QUERY_NAME_TABLE_STATS = 'TABLE_STATS'
 QUERY_NAME_INDEX_STATS = 'INDEX_STATS'
 QUERY_NAME_DATABASE = 'DATABASE'
@@ -76,6 +77,10 @@ METRICS = {
         'backends_active',
         'backends_idle'
     ],
+    QUERY_NAME_BACKEND_USAGE: [
+        'available',
+        'used'
+    ],
     QUERY_NAME_INDEX_STATS: [
         'index_count',
         'index_size'
@@ -139,6 +144,10 @@ METRICS = {
 
 NO_VERSION = 0
 DEFAULT = 'DEFAULT'
+V72 = 'V72'
+V82 = 'V82'
+V91 = 'V91'
+V92 = 'V92'
 V96 = 'V96'
 V10 = 'V10'
 V11 = 'V11'
@@ -235,6 +244,76 @@ FROM pg_stat_activity;
 """,
 }
 
+QUERY_BACKEND_USAGE = {
+    DEFAULT: """
+SELECT
+    COUNT(1) as used,
+    current_setting('max_connections')::int - current_setting('superuser_reserved_connections')::int
+    - COUNT(1) AS available
+FROM pg_catalog.pg_stat_activity
+WHERE backend_type IN ('client backend', 'background worker');
+""",
+    V10: """
+SELECT
+    SUM(s.conn) as used,
+    current_setting('max_connections')::int - current_setting('superuser_reserved_connections')::int
+    - SUM(s.conn) AS available
+FROM (
+    SELECT 's' as type, COUNT(1) as conn
+    FROM pg_catalog.pg_stat_activity
+    WHERE backend_type IN ('client backend', 'background worker')
+    UNION ALL
+    SELECT 'r', COUNT(1) 
+    FROM pg_catalog.pg_stat_replication
+) as s;
+""",
+    V92: """
+SELECT
+    SUM(s.conn) as used,
+    current_setting('max_connections')::int - current_setting('superuser_reserved_connections')::int
+    - SUM(s.conn) AS available
+FROM (
+    SELECT 's' as type, COUNT(1) as conn
+    FROM pg_catalog.pg_stat_activity
+    WHERE query NOT LIKE 'autovacuum: %%'
+    UNION ALL
+    SELECT 'r', COUNT(1) 
+    FROM pg_catalog.pg_stat_replication
+) as s;
+""",
+    V91: """
+SELECT
+    SUM(s.conn) as used,
+    current_setting('max_connections')::int - current_setting('superuser_reserved_connections')::int
+    - SUM(s.conn) AS available
+FROM (
+    SELECT 's' as type, COUNT(1) as conn
+    FROM pg_catalog.pg_stat_activity
+    WHERE current_query NOT LIKE 'autovacuum: %%'
+    UNION ALL
+    SELECT 'r', COUNT(1) 
+    FROM pg_catalog.pg_stat_replication
+) as s;
+""",
+    V82: """
+SELECT
+    COUNT(1) as used,
+    current_setting('max_connections')::int - current_setting('superuser_reserved_connections')::int
+    - COUNT(1) AS available
+FROM pg_catalog.pg_stat_activity
+WHERE current_query NOT LIKE 'autovacuum: %%';
+""",
+    V72: """
+SELECT
+    COUNT(1) as used,
+    current_setting('max_connections')::int - current_setting('superuser_reserved_connections')::int
+    - COUNT(1) AS available
+FROM pg_catalog.pg_stat_activity s
+JOIN pg_catalog.pg_database d ON d.oid = s.datid
+WHERE d.datallowconn;
+""",
+}
+
 QUERY_TABLE_STATS = {
     DEFAULT: """
 SELECT
@@ -528,10 +607,21 @@ SELECT
 """,
 }
 
-
 def query_factory(name, version=NO_VERSION):
     if name == QUERY_NAME_BACKENDS:
         return QUERY_BACKEND[DEFAULT]
+    elif name == QUERY_NAME_BACKEND_USAGE:
+        if version < 80200:
+            return QUERY_BACKEND_USAGE[V72]
+        if version < 90100:
+            return QUERY_BACKEND_USAGE[V82]
+        if version < 90200:
+            return QUERY_BACKEND_USAGE[V91]
+        if version < 100000:
+            return QUERY_BACKEND_USAGE[V92]
+        elif version < 120000:
+            return QUERY_BACKEND_USAGE[V10]
+        return QUERY_BACKEND_USAGE[DEFAULT]
     elif name == QUERY_NAME_TABLE_STATS:
         return QUERY_TABLE_STATS[DEFAULT]
     elif name == QUERY_NAME_INDEX_STATS:
@@ -588,6 +678,7 @@ ORDER = [
     'db_stat_connections',
     'database_size',
     'backend_process',
+    'backend_usage',
     'index_count',
     'index_size',
     'table_count',
@@ -674,6 +765,13 @@ CHARTS = {
             ['backends_idle', 'idle', 'absolute']
         ]
     },
+    'backend_usage': {
+        'options': [None, '% of Connections in use', 'percentage', 'backend processes', 'postgres.backend_usage', 'stacked'],
+        'lines': [
+            ['available', 'available', 'percentage-of-absolute-row'],
+            ['used', 'used', 'percentage-of-absolute-row']
+        ]
+    },
     'index_count': {
         'options': [None, 'Total indexes', 'index', 'indexes', 'postgres.index_count', 'line'],
         'lines': [
@@ -970,6 +1068,7 @@ class Service(SimpleService):
     def populate_queries(self):
         self.queries[query_factory(QUERY_NAME_DATABASE)] = METRICS[QUERY_NAME_DATABASE]
         self.queries[query_factory(QUERY_NAME_BACKENDS)] = METRICS[QUERY_NAME_BACKENDS]
+        self.queries[query_factory(QUERY_NAME_BACKEND_USAGE, self.server_version)] = METRICS[QUERY_NAME_BACKEND_USAGE]
         self.queries[query_factory(QUERY_NAME_LOCKS)] = METRICS[QUERY_NAME_LOCKS]
         self.queries[query_factory(QUERY_NAME_BGWRITER)] = METRICS[QUERY_NAME_BGWRITER]
         self.queries[query_factory(QUERY_NAME_DIFF_LSN, self.server_version)] = METRICS[QUERY_NAME_WAL_WRITES]