summaryrefslogtreecommitdiffstats
path: root/collectors/python.d.plugin/postgres
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2021-02-07 11:45:55 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2021-02-07 11:45:55 +0000
commita8220ab2d293bb7f4b014b79d16b2fb05090fa93 (patch)
tree77f0a30f016c0925cf7ee9292e644bba183c2774 /collectors/python.d.plugin/postgres
parentAdding upstream version 1.19.0. (diff)
downloadnetdata-a8220ab2d293bb7f4b014b79d16b2fb05090fa93.tar.xz
netdata-a8220ab2d293bb7f4b014b79d16b2fb05090fa93.zip
Adding upstream version 1.29.0.upstream/1.29.0
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to '')
-rw-r--r--collectors/python.d.plugin/postgres/README.md49
-rw-r--r--collectors/python.d.plugin/postgres/postgres.chart.py105
-rw-r--r--collectors/python.d.plugin/postgres/postgres.conf9
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