diff options
| -rw-r--r-- | collectors/python.d.plugin/Makefile.am | 1 | ||||
| -rw-r--r-- | collectors/python.d.plugin/postgres/Makefile.inc | 13 | ||||
| -rw-r--r-- | collectors/python.d.plugin/postgres/README.md | 145 | ||||
| -rw-r--r-- | collectors/python.d.plugin/postgres/postgres.chart.py | 1436 | ||||
| -rw-r--r-- | collectors/python.d.plugin/postgres/postgres.conf | 134 | ||||
| -rw-r--r-- | collectors/python.d.plugin/python.d.conf | 1 | ||||
| -rw-r--r-- | docs/guides/python-collector.md | 10 | ||||
| -rwxr-xr-x | packaging/installer/install-required-packages.sh | 64 | ||||
| -rw-r--r-- | packaging/installer/methods/manual.md | 3 | ||||
| -rw-r--r-- | web/gui/dashboard_info.js | 84 |
10 files changed, 8 insertions, 1883 deletions
diff --git a/collectors/python.d.plugin/Makefile.am b/collectors/python.d.plugin/Makefile.am index 9377ebe8d6..60f56c973b 100644 --- a/collectors/python.d.plugin/Makefile.am +++ b/collectors/python.d.plugin/Makefile.am @@ -73,7 +73,6 @@ include ntpd/Makefile.inc include openldap/Makefile.inc include oracledb/Makefile.inc include postfix/Makefile.inc -include postgres/Makefile.inc include proxysql/Makefile.inc include puppet/Makefile.inc include rabbitmq/Makefile.inc diff --git a/collectors/python.d.plugin/postgres/Makefile.inc b/collectors/python.d.plugin/postgres/Makefile.inc deleted file mode 100644 index 91a185cb95..0000000000 --- a/collectors/python.d.plugin/postgres/Makefile.inc +++ /dev/null @@ -1,13 +0,0 @@ -# SPDX-License-Identifier: GPL-3.0-or-later - -# THIS IS NOT A COMPLETE Makefile -# IT IS INCLUDED BY ITS PARENT'S Makefile.am -# IT IS REQUIRED TO REFERENCE ALL FILES RELATIVE TO THE PARENT - -# install these files -dist_python_DATA += postgres/postgres.chart.py -dist_pythonconfig_DATA += postgres/postgres.conf - -# do not install these files, but include them in the distribution -dist_noinst_DATA += postgres/README.md postgres/Makefile.inc - diff --git a/collectors/python.d.plugin/postgres/README.md b/collectors/python.d.plugin/postgres/README.md deleted file mode 100644 index 7acb9a7a94..0000000000 --- a/collectors/python.d.plugin/postgres/README.md +++ /dev/null @@ -1,145 +0,0 @@ -<!-- -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" ---> - -# PostgreSQL monitoring with Netdata - -> **Warning**: This module is deprecated and will be deleted in v1.37.0. -> Use [go.d/postgres](https://learn.netdata.cloud/docs/agent/collectors/go.d.plugin/modules/postgres). - -Collects database health and performance metrics. - -## 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. - -- PostgreSQL v9.4+ - -Following charts are drawn: - -1. **Database size** MB - - - size - -2. **Current Backend Processes** processes - - - active - -3. **Current Backend Process Usage** percentage - - - used - - available - -4. **Write-Ahead Logging Statistics** files/s - - - total - - ready - - done - -5. **Checkpoints** writes/s - - - scheduled - - requested - -6. **Current connections to db** count - - - connections - -7. **Tuples returned from db** tuples/s - - - sequential - - bitmap - -8. **Tuple reads from db** reads/s - - - disk - - cache - -9. **Transactions on db** transactions/s - - - committed - - rolled back - -10. **Tuples written to db** writes/s - - - inserted - - updated - - deleted - - conflicts - -11. **Locks on db** count per type - - - locks - -12. **Standby delta** KB - - - sent delta - - write delta - - flush delta - - replay delta - -13. **Standby lag** seconds - - - write lag - - flush lag - - replay lag - -14. **Average number of blocking transactions in db** processes - - - blocking - -## Configuration - -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` with the -following collection jobs. - -```yaml -socket: - name : 'socket' - user : 'postgres' - database : 'postgres' - -tcp: - name : 'tcp' - user : 'postgres' - database : 'postgres' - host : 'localhost' - port : 5432 -``` - -**Note**: Every job collection must have a unique identifier. In cases that you monitor multiple DBs, every -job must have it's own name. Use a mnemonic of your preference (e.g us_east_db, us_east_tcp) - -## Troubleshooting - -To troubleshoot issues with the `postgres` collector, run the `python.d.plugin` with the debug option enabled. The output -should give you clues as to why the collector isn't working. - -First, navigate to your plugins directory, usually at `/usr/libexec/netdata/plugins.d/`. If that's not the case on your -system, open `netdata.conf` and look for the setting `plugins directory`. Once you're in the plugin's directory, switch -to the `netdata` user. - -```bash -cd /usr/libexec/netdata/plugins.d/ -sudo su -s /bin/bash netdata -``` - -You can now run the `python.d.plugin` to debug the collector: - -```bash -./python.d.plugin postgres debug trace -``` - ---- - - diff --git a/collectors/python.d.plugin/postgres/postgres.chart.py b/collectors/python.d.plugin/postgres/postgres.chart.py deleted file mode 100644 index bd8f71a666..0000000000 --- a/collectors/python.d.plugin/postgres/postgres.chart.py +++ /dev/null @@ -1,1436 +0,0 @@ -# -*- coding: utf-8 -*- -# Description: example netdata python.d module -# Authors: facetoe, dangtranhoang -# SPDX-License-Identifier: GPL-3.0-or-later - -from copy import deepcopy - -try: - import psycopg2 - from psycopg2 import extensions - from psycopg2.extras import DictCursor - from psycopg2 import OperationalError - - PSYCOPG2 = True -except ImportError: - PSYCOPG2 = False - -from bases.FrameworkServices.SimpleService import SimpleService - -DEFAULT_PORT = 5432 -DEFAULT_USER = 'postgres' -DEFAULT_CONNECT_TIMEOUT = 2 # seconds -DEFAULT_STATEMENT_TIMEOUT = 5000 # ms - -CONN_PARAM_DSN = 'dsn' -CONN_PARAM_HOST = 'host' -CONN_PARAM_PORT = 'port' -CONN_PARAM_DATABASE = 'database' -CONN_PARAM_USER = 'user' -CONN_PARAM_PASSWORD = 'password' -CONN_PARAM_CONN_TIMEOUT = 'connect_timeout' -CONN_PARAM_STATEMENT_TIMEOUT = 'statement_timeout' -CONN_PARAM_SSL_MODE = 'sslmode' -CONN_PARAM_SSL_ROOT_CERT = 'sslrootcert' -CONN_PARAM_SSL_CRL = 'sslcrl' -CONN_PARAM_SSL_CERT = 'sslcert' -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' -QUERY_NAME_BGWRITER = 'BGWRITER' -QUERY_NAME_LOCKS = 'LOCKS' -QUERY_NAME_BLOCKERS = 'BLOCKERS' -QUERY_NAME_DATABASES = 'DATABASES' -QUERY_NAME_STANDBY = 'STANDBY' -QUERY_NAME_REPLICATION_SLOT = 'REPLICATION_SLOT' -QUERY_NAME_STANDBY_DELTA = 'STANDBY_DELTA' -QUERY_NAME_STANDBY_LAG = 'STANDBY_LAG' -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' - -METRICS = { - QUERY_NAME_DATABASE: [ - 'connections', - 'xact_commit', - 'xact_rollback', - 'blks_read', - 'blks_hit', - 'tup_returned', - 'tup_fetched', - 'tup_inserted', - 'tup_updated', - 'tup_deleted', - 'conflicts', - 'temp_files', - 'temp_bytes', - 'size' - ], - QUERY_NAME_BACKENDS: [ - 'backends_active', - 'backends_idle' - ], - QUERY_NAME_BACKEND_USAGE: [ - 'available', - 'used' - ], - QUERY_NAME_INDEX_STATS: [ - 'index_count', - 'index_size' - ], - QUERY_NAME_TABLE_STATS: [ - 'table_size', - 'table_count' - ], - QUERY_NAME_WAL: [ - 'written_wal', - 'recycled_wal', - 'total_wal' - ], - QUERY_NAME_WAL_WRITES: [ - 'wal_writes' - ], - QUERY_NAME_ARCHIVE: [ - 'ready_count', - 'done_count', - 'file_count' - ], - QUERY_NAME_BGWRITER: [ - 'checkpoint_scheduled', - 'checkpoint_requested', - 'buffers_checkpoint', - 'buffers_clean', - 'maxwritten_clean', - 'buffers_backend', - 'buffers_alloc', - 'buffers_backend_fsync' - ], - QUERY_NAME_LOCKS: [ - 'ExclusiveLock', - 'RowShareLock', - 'SIReadLock', - 'ShareUpdateExclusiveLock', - 'AccessExclusiveLock', - 'AccessShareLock', - 'ShareRowExclusiveLock', - 'ShareLock', - 'RowExclusiveLock' - ], - QUERY_NAME_BLOCKERS: [ - 'blocking_pids_avg' - ], - QUERY_NAME_AUTOVACUUM: [ - 'analyze', - 'vacuum_analyze', - 'vacuum', - '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', - 'flush_delta', - 'replay_delta' - ], - QUERY_NAME_STANDBY_LAG: [ - 'write_lag', - 'flush_lag', - 'replay_lag' - ], - QUERY_NAME_REPSLOT_FILES: [ - 'replslot_wal_keep', - 'replslot_files' - ] -} - -NO_VERSION = 0 -DEFAULT = 'DEFAULT' -V72 = 'V72' -V82 = 'V82' -V91 = 'V91' -V92 = 'V92' -V96 = 'V96' -V10 = 'V10' -V11 = 'V11' - -QUERY_WAL = { - DEFAULT: """ -SELECT - count(*) as total_wal, - count(*) FILTER (WHERE type = 'recycled') AS recycled_wal, - count(*) FILTER (WHERE type = 'written') AS written_wal -FROM - (SELECT - wal.name, - pg_walfile_name( - CASE pg_is_in_recovery() - WHEN true THEN NULL - ELSE pg_current_wal_lsn() - END ), - CASE - WHEN wal.name > pg_walfile_name( - CASE pg_is_in_recovery() - WHEN true THEN NULL - ELSE pg_current_wal_lsn() - END ) THEN 'recycled' - ELSE 'written' - END AS type - FROM pg_catalog.pg_ls_dir('pg_wal') AS wal(name) - WHERE name ~ '^[0-9A-F]{24}$' - ORDER BY - (pg_stat_file('pg_wal/'||name, true)).modification, - wal.name DESC) sub; -""", - V96: """ -SELECT - count(*) as total_wal, - count(*) FILTER (WHERE type = 'recycled') AS recycled_wal, - count(*) FILTER (WHERE type = 'written') AS written_wal -FROM - (SELECT - wal.name, - pg_xlogfile_name( - CASE pg_is_in_recovery() - WHEN true THEN NULL - ELSE pg_current_xlog_location() - END ), - CASE - WHEN wal.name > pg_xlogfile_name( - CASE pg_is_in_recovery() - WHEN true THEN NULL - ELSE pg_current_xlog_location() - END ) THEN 'recycled' - ELSE 'written' - END AS type - FROM pg_catalog.pg_ls_dir('pg_xlog') AS wal(name) - WHERE name ~ '^[0-9A-F]{24}$' - ORDER BY - (pg_stat_file('pg_xlog/'||name, true)).modification, - wal.name DESC) sub; -""", -} - -QUERY_ARCHIVE = { - DEFAULT: """ -SELECT - CAST(COUNT(*) AS INT) AS file_count, - CAST(COALESCE(SUM(CAST(archive_file ~ $r$\.ready$$r$ as INT)),0) AS INT) AS ready_count, - CAST(COALESCE(SUM(CAST(archive_file ~ $r$\.done$$r$ AS INT)),0) AS INT) AS done_count -FROM - pg_catalog.pg_ls_dir('pg_wal/archive_status') AS archive_files (archive_file); -""", - V96: """ -SELECT - CAST(COUNT(*) AS INT) AS file_count, - CAST(COALESCE(SUM(CAST(archive_file ~ $r$\.ready$$r$ as INT)),0) AS INT) AS ready_count, - CAST(COALESCE(SUM(CAST(archive_file ~ $r$\.done$$r$ AS INT)),0) AS INT) AS done_count -FROM - pg_catalog.pg_ls_dir('pg_xlog/archive_status') AS archive_files (archive_file); - -""", -} - -QUERY_BACKEND = { - DEFAULT: """ -SELECT - count(*) - (SELECT count(*) - FROM pg_stat_activity - WHERE state = 'idle') - AS backends_active, - (SELECT count(*) - FROM pg_stat_activity - WHERE state = 'idle') - AS backends_idle -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 - sum(relpages) * current_setting('block_size')::numeric AS table_size, - count(1) AS table_count -FROM pg_class -WHERE relkind IN ('r', 't', 'm'); -""", -} - -QUERY_INDEX_STATS = { - DEFAULT: """ -SELECT - sum(relpages) * current_setting('block_size')::numeric AS index_size, - count(1) AS index_count -FROM pg_class -WHERE relkind = 'i'; -""", -} - -QUERY_DATABASE = { - DEFAULT: """ -SELECT - datname AS database_name, - numbackends AS connections, - xact_commit AS xact_commit, - xact_rollback AS xact_rollback, - blks_read AS blks_read, - blks_hit AS blks_hit, - tup_returned AS tup_returned, - tup_fetched AS tup_fetched, - tup_inserted AS tup_inserted, - tup_updated AS tup_updated, - tup_deleted AS tup_deleted, - conflicts AS conflicts, - pg_database_size(datname) AS size, - temp_files AS temp_files, - temp_bytes AS temp_bytes -FROM pg_stat_database -WHERE datname IN %(databases)s ; -""", -} - -QUERY_BGWRITER = { - DEFAULT: """ -SELECT - checkpoints_timed AS checkpoint_scheduled, - checkpoints_req AS checkpoint_requested, - buffers_checkpoint * current_setting('block_size')::numeric buffers_checkpoint, - buffers_clean * current_setting('block_size')::numeric buffers_clean, - maxwritten_clean, - buffers_backend * current_setting('block_size')::numeric buffers_backend, - buffers_alloc * current_setting('block_size')::numeric buffers_alloc, - buffers_backend_fsync -FROM pg_stat_bgwriter; -""", -} - -QUERY_LOCKS = { - DEFAULT: """ -SELECT - pg_database.datname as database_name, - mode, - count(mode) AS locks_count -FROM pg_locks -INNER JOIN pg_database - ON pg_database.oid = pg_locks.database -GROUP BY datname, mode -ORDER BY datname, mode; -""", -} - -QUERY_BLOCKERS = { - DEFAULT: """ -WITH B AS ( -SELECT DISTINCT - pg_database.datname as database_name, - pg_locks.pid, - cardinality(pg_blocking_pids(pg_locks.pid)) AS blocking_pids -FROM pg_locks -INNER JOIN pg_database ON pg_database.oid = pg_locks.database -WHERE NOT pg_locks.granted) -SELECT database_name, AVG(blocking_pids) AS blocking_pids_avg -FROM B -GROUP BY database_name -""", - V96: """ -WITH B AS ( -SELECT DISTINCT - pg_database.datname as database_name, - blocked_locks.pid AS blocked_pid, - COUNT(blocking_locks.pid) AS blocking_pids -FROM pg_catalog.pg_locks blocked_locks -INNER JOIN pg_database ON pg_database.oid = blocked_locks.database -JOIN pg_catalog.pg_locks blocking_locks - ON blocking_locks.locktype = blocked_locks.locktype - AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database - AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation - AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page - AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple - AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid - AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid - AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid - AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid - AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid - AND blocking_locks.pid != blocked_locks.pid -WHERE NOT blocked_locks.GRANTED -GROUP BY database_name, blocked_pid) -SELECT database_name, AVG(blocking_pids) AS blocking_pids_avg -FROM B -GROUP BY database_name -""" -} - -QUERY_DATABASES = { - DEFAULT: """ -SELECT - datname -FROM pg_stat_database -WHERE - has_database_privilege( - (SELECT current_user), datname, 'connect') - AND NOT datname ~* '^template\d' -ORDER BY datname; -""", -} - -QUERY_STANDBY = { - DEFAULT: """ -SELECT - 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; -""", -} - -QUERY_REPLICATION_SLOT = { - DEFAULT: """ -SELECT slot_name -FROM pg_replication_slots; -""" -} - -QUERY_STANDBY_DELTA = { - DEFAULT: """ -SELECT - 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() - ELSE pg_current_wal_lsn() - END, - sent_lsn) AS sent_delta, - pg_wal_lsn_diff( - CASE pg_is_in_recovery() - WHEN true THEN pg_last_wal_receive_lsn() - ELSE pg_current_wal_lsn() - END, - write_lsn) AS write_delta, - pg_wal_lsn_diff( - CASE pg_is_in_recovery() - WHEN true THEN pg_last_wal_receive_lsn() - ELSE pg_current_wal_lsn() - END, - flush_lsn) AS flush_delta, - pg_wal_lsn_diff( - CASE pg_is_in_recovery() - WHEN true THEN pg_last_wal_receive_lsn() - ELSE pg_current_wal_lsn() - END, - replay_lsn) AS replay_delta -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 - 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() - ELSE pg_current_xlog_location() - END, - sent_location) AS sent_delta, - pg_xlog_location_diff( - CASE pg_is_in_recovery() - WHEN true THEN pg_last_xlog_receive_location() - ELSE pg_current_xlog_location() - END, - write_location) AS write_delta, - pg_xlog_location_diff( - CASE pg_is_in_recovery() - WHEN true THEN pg_last_xlog_receive_location() - ELSE pg_current_xlog_location() - END, - flush_location) AS flush_delta, - pg_xlog_location_diff( - CASE pg_is_in_recovery() - WHEN true THEN pg_last_xlog_receive_location() - ELSE pg_current_xlog_location() - END, - replay_location) AS replay_delta -FROM pg_stat_replication psr -LEFT OUTER JOIN pg_replication_slots prs on psr.pid = prs.active_pid -WHERE application_name IS NOT NULL; -""", -} - -QUERY_STANDBY_LAG = { - DEFAULT: """ -SELECT - 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 psr -LEFT OUTER JOIN pg_replication_slots prs on psr.pid = prs.active_pid -WHERE application_name IS NOT NULL; -""" -} - -QUERY_REPSLOT_FILES = { - DEFAULT: """ -WITH wal_size AS ( - SELECT - setting::int AS val - FROM pg_settings - WHERE name = 'wal_segment_size' - ) -SELECT - slot_name, - slot_type, - replslot_wal_keep, - count(slot_file) AS replslot_files -FROM - (SELECT - slot.slot_name, - CASE - WHEN slot_file <> 'state' THEN 1 - END AS slot_file , - slot_type, - COALESCE ( - floor( - CASE WHEN pg_is_in_recovery() - THEN ( - pg_wal_lsn_diff(pg_last_wal_receive_lsn(), slot.restart_lsn) - -- this is needed to account for whole WAL retention and - -- not only size retention - + (pg_wal_lsn_diff(restart_lsn, '0/0') %% s.val) - ) / s.val - ELSE ( - pg_wal_lsn_diff(pg_current_wal_lsn(), slot.restart_lsn) - -- this is needed to account for whole WAL retention and - -- not only size retention - + (pg_walfile_name_offset(restart_lsn)).file_offset - ) / s.val - END - ),0) AS replslot_wal_keep - FROM pg_replication_slots slot - LEFT JOIN ( - SELECT - slot2.slot_name, - pg_ls_dir('pg_replslot/' || slot2.slot_name) AS slot_file - FROM pg_replication_slots slot2 - ) files (slot_name, slot_file) - ON slot.slot_name = files.slot_name - CROSS JOIN wal_size s - ) AS d -GROUP BY - slot_name, - slot_type, - replslot_wal_keep; -""", - V10: """ -WITH wal_size AS ( - SELECT - current_setting('wal_block_size')::INT * setting::INT AS val - FROM pg_settings - WHERE name = 'wal_segment_size' - ) -SELECT - slot_name, - slot_type, - replslot_wal_keep, - count(slot_file) AS replslot_files -FROM - (SELECT - slot.slot_name, - CASE - WHEN slot_file <> 'state' THEN 1 - END AS slot_file , - slot_type, - COALESCE ( - floor( - CASE WHEN pg_is_in_recovery() - THEN ( - pg_wal_lsn_diff(pg_last_wal_receive_lsn(), slot.restart_lsn) - -- this is needed to account for whole WAL retention and - -- not only size retention - + (pg_wal_lsn_diff(restart_lsn, '0/0') %% s.val) - ) / s.val - ELSE ( - pg_wal_lsn_diff(pg_current_wal_lsn(), slot.restart_lsn) - -- this is needed to account for whole WAL retention and - -- not only size retention - + (pg_walfile_name_offset(restart_lsn)).file_offset - ) / s.val - END - ),0) AS replslot_wal_keep - FROM pg_replication_slots slot - LEFT JOIN ( - SELECT - slot2.slot_name, - pg_ls_dir('pg_replslot/' || slot2.slot_name) AS slot_file - FROM pg_replication_slots slot2 - ) files (slot_name, slot_file) - ON slot.slot_name = files.slot_name - CROSS JOIN wal_size s - ) AS d -GROUP BY - slot_name, - slot_type, - replslot_wal_keep; -""", -} - -QUERY_SUPERUSER = { - DEFAULT: """ -SELECT current_setting('is_superuser') = 'on' AS is_superuser; -""", -} - -QUERY_SHOW_VERSION = { - DEFAULT: """ -SHOW server_version_num; -""", -} - -QUERY_AUTOVACUUM = { - DEFAULT: """ -SELECT - count(*) FILTER (WHERE query LIKE 'autovacuum: ANALYZE%%') AS analyze, - count(*) FILTER (WHERE query LIKE 'autovacuum: VACUUM ANALYZE%%') AS vacuum_analyze, - count(*) FILTER (WHERE query LIKE 'autovacuum: VACUUM%%' - AND query NOT LIKE 'autovacuum: VACUUM ANALYZE%%' - AND query NOT LIKE '%%to prevent wraparound%%') AS vacuum, - count(*) FILTER (WHERE query LIKE '%%to prevent wraparound%%') AS vacuum_freeze, - count(*) FILTER (WHERE query LIKE 'autovacuum: BRIN summarize%%') AS brin_summarize -FROM pg_stat_activity -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 - pg_wal_lsn_diff( - CASE pg_is_in_recovery() - WHEN true THEN pg_last_wal_receive_lsn() - ELSE pg_current_wal_lsn() - END, - '0/0') as wal_writes ; -""", - V96: """ -SELECT - pg_xlog_location_diff( - CASE pg_is_in_recovery() - WHEN true THEN pg_last_xlog_receive_location() - ELSE pg_current_xlog_location() - END, - '0/0') as wal_writes ; -""", -} - -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: - return QUERY_INDEX_STATS[DEFAULT] - elif name == QUERY_NAME_DATABASE: - return QUERY_DATABASE[DEFAULT] - elif name == QUERY_NAME_BGWRITER: - return QUERY_BGWRITER[DEFAULT] - elif name == QUERY_NAME_LOCKS: - return QUERY_LOCKS[DEFAULT] - elif name == QUERY_NAME_BLOCKERS: - if version < 90600: - return QUERY_BLOCKERS[V96] - return QUERY_BLOCKERS[DEFAULT] - elif name == QUERY_NAME_DATABASES: - return QUERY_DATABASES[DEFAULT] - elif name == QUERY_NAME_STANDBY: - return QUERY_STANDBY[DEFAULT] - elif name == QUERY_NAME_REPLICATION_SLOT: - return QUERY_REPLICATION_SLOT[DEFAULT] - elif name == QUERY_NAME_IF_SUPERUSER: - return QUERY_SUPERUSER[DEFAULT] - elif name == QUERY_NAME_SERVER_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] - return QUERY_WAL[DEFAULT] - elif name == QUERY_NAME_ARCHIVE: - if version < 100000: - return QUERY_ARCHIVE[V96] - return QUERY_ARCHIVE[DEFAULT] - elif name == QUERY_NAME_STANDBY_DELTA: - if version < 100000: - return QUERY_STANDBY_DELTA[V96] - return QUERY_STANDBY_DELTA[DEFAULT] - elif name == QUERY_NAME_STANDBY_LAG: - return QUERY_STANDBY_LAG[DEFAULT] - elif name == QUERY_NAME_REPSLOT_FILES: - if version < 110000: - return QUERY_REPSLOT_FILES[V10] - return QUERY_REPSLOT_FILES[DEFAULT] - elif name == QUERY_NAME_DIFF_LSN: - if version < 100000: - return QUERY_DIFF_LSN[V96] - return QUERY_DIFF_LSN[DEFAULT] - - raise ValueError('unknown query') - - -ORDER = [ - 'db_stat_temp_files', - 'db_stat_temp_bytes', - 'db_stat_blks', - 'db_stat_tuple_returned', - 'db_stat_tuple_write', - 'db_stat_transactions', - 'db_stat_connections', - 'db_stat_blocking_pids_avg', - 'database_size', - 'backend_process', - 'backend_usage', - 'index_count', - 'index_size', - 'table_count', - 'table_size', - 'wal', - 'wal_writes', - 'archive_wal', - 'checkpointer', - 'stat_bgwriter_alloc', - 'stat_bgwriter_checkpoint', - 'stat_bgwriter_backend', - 'stat_bgwriter_backend_fsync', - 'stat_bgwriter_bgwriter', - 'stat_bgwriter_maxwritten', - 'replication_slot', - 'standby_delta', - 'standby_lag', - 'autovacuum', - 'forced_autovacuum', - 'tx_wraparound_oldest_current_xid', - 'tx_wraparound_percent_towards_wraparound' -] - -CHARTS = { - 'db_stat_transactions': { - 'options': [None, 'Transactions on db', 'transactions/s', 'db statistics', 'postgres.db_stat_transactions', - 'line'], - 'lines': [ |