Adding upstream version 1.29.0.upstream/1.29.0

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

3 files changed, 143 insertions, 20 deletions

diff --git a/collectors/python.d.plugin/postgres/README.md b/collectors/python.d.plugin/postgres/README.md
index 29dd85a5c..dc9b18467 100644
--- a/collectors/python.d.plugin/postgres/README.md
+++ b/collectors/python.d.plugin/postgres/README.md
@@ -1,10 +1,16 @@
-# postgres
+<!--
+title: "PostgreSQL monitoring with Netdata"
+custom_edit_url: https://github.com/netdata/netdata/edit/master/collectors/python.d.plugin/postgres/README.md
+sidebar_label: "PostgreSQL"
+-->
 
-Module monitors one or more postgres servers.
+# PostgreSQL monitoring with Netdata
 
-**Requirements:**
+Collects database health and performance metrics.
 
--   `python-psycopg2` package. You have to install it manually.
+## Requirements
+
+-   `python-psycopg2` package. You have to install it manually and make sure that it is available to the `netdata` user, either using `pip`, the package manager of your Linux distribution, or any other method you prefer.
 
 Following charts are drawn:
 
@@ -16,50 +22,63 @@ Following charts are drawn:
 
     -   active
 
-3.  **Write-Ahead Logging Statistics** files/s
+3.  **Current Backend Process 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
 
-## configuration
+## Configuration
 
-For all available options please see module [configuration file](postgres.conf).
+Edit the `python.d/postgres.conf` configuration file using `edit-config` from the Netdata [config
+directory](/docs/configure/nodes.md), which is typically at `/etc/netdata`.
+
+```bash
+cd /etc/netdata   # Replace this path with your Netdata config directory, if different
+sudo ./edit-config python.d/postgres.conf
+```
+
+When no configuration file is found, the module tries to connect to TCP/IP socket: `localhost:5432`.
 
 ```yaml
 socket:
@@ -75,8 +94,6 @@ tcp:
   port         : 5432
 ```
 
-When no configuration file is found, module tries to connect to TCP/IP socket: `localhost:5432`.
-
 ---
 
 [![analytics](https://www.google-analytics.com/collect?v=1&aip=1&t=pageview&_s=1&ds=github&dr=https%3A%2F%2Fgithub.com%2Fnetdata%2Fnetdata&dl=https%3A%2F%2Fmy-netdata.io%2Fgithub%2Fcollectors%2Fpython.d.plugin%2Fpostgres%2FREADME&_u=MAC~&cid=5792dfd7-8dc4-476b-af31-da2fdb9f93d2&tid=UA-64295674-3)](<>)
diff --git a/collectors/python.d.plugin/postgres/postgres.chart.py b/collectors/python.d.plugin/postgres/postgres.chart.py
index 9e3020358..bd28dd9b7 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
@@ -315,7 +394,7 @@ FROM pg_stat_database
 WHERE
     has_database_privilege(
       (SELECT current_user), datname, 'connect')
-    AND NOT datname ~* '^template\d ';
+    AND NOT datname ~* '^template\d';
 """,
 }
 
@@ -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]
@@ -1063,7 +1162,7 @@ def zero_lock_types(databases):
 
 
 def hide_password(config):
-    return dict((k, v if k != 'password' else '*****') for k, v in config.items())
+    return dict((k, v if k != 'password' or not v else '*****') for k, v in config.items())
 
 
 def add_database_lock_chart(order, definitions, database_name):
diff --git a/collectors/python.d.plugin/postgres/postgres.conf b/collectors/python.d.plugin/postgres/postgres.conf
index 3dd461408..1970a7a27 100644
--- a/collectors/python.d.plugin/postgres/postgres.conf
+++ b/collectors/python.d.plugin/postgres/postgres.conf
@@ -81,7 +81,7 @@
 #     sslkey              : path/to/key        # the location of the client key file
 #
 # SSL connection parameters description: https://www.postgresql.org/docs/current/libpq-ssl.html
-#
+# 
 # Additionally, the following options allow selective disabling of charts
 #
 #     table_stats : false
@@ -93,6 +93,10 @@
 # a postgres user for netdata and add its password below to allow
 # netdata connect.
 #
+# Please note that when running Postgres from inside the container, 
+# the client (Netdata) is not considered local, unless it runs from inside
+# the same container. 
+#
 # Postgres supported versions are : 
 # - 9.3 (without autovacuum)
 # - 9.4
@@ -116,6 +120,7 @@ tcp:
     name     : 'local'
     database : 'postgres'
     user     : 'postgres'
+    password : 'postgres'
     host     : 'localhost'
     port     : 5432
 
@@ -123,6 +128,7 @@ tcpipv4:
     name     : 'local'
     database : 'postgres'
     user     : 'postgres'
+    password : 'postgres'
     host     : '127.0.0.1'
     port     : 5432
 
@@ -130,5 +136,6 @@ tcpipv6:
     name     : 'local'
     database : 'postgres'
     user     : 'postgres'
+    password : 'postgres'
     host     : '::1'
     port     : 5432