# -*- 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
WAL = 'WAL'
ARCHIVE = 'ARCHIVE'
BACKENDS = 'BACKENDS'
TABLE_STATS = 'TABLE_STATS'
INDEX_STATS = 'INDEX_STATS'
DATABASE = 'DATABASE'
BGWRITER = 'BGWRITER'
LOCKS = 'LOCKS'
DATABASES = 'DATABASES'
STANDBY = 'STANDBY'
REPLICATION_SLOT = 'REPLICATION_SLOT'
STANDBY_DELTA = 'STANDBY_DELTA'
REPSLOT_FILES = 'REPSLOT_FILES'
IF_SUPERUSER = 'IF_SUPERUSER'
SERVER_VERSION = 'SERVER_VERSION'
AUTOVACUUM = 'AUTOVACUUM'
DIFF_LSN = 'DIFF_LSN'
WAL_WRITES = 'WAL_WRITES'
METRICS = {
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'
],
BACKENDS: [
'backends_active',
'backends_idle'
],
INDEX_STATS: [
'index_count',
'index_size'
],
TABLE_STATS: [
'table_size',
'table_count'
],
WAL: [
'written_wal',
'recycled_wal',
'total_wal'
],
WAL_WRITES: [
'wal_writes'
],
ARCHIVE: [
'ready_count',
'done_count',
'file_count'
],
BGWRITER: [
'checkpoint_scheduled',
'checkpoint_requested',
'buffers_checkpoint',
'buffers_clean',
'maxwritten_clean',
'buffers_backend',
'buffers_alloc',
'buffers_backend_fsync'
],
LOCKS: [
'ExclusiveLock',
'RowShareLock',
'SIReadLock',
'ShareUpdateExclusiveLock',
'AccessExclusiveLock',
'AccessShareLock',
'ShareRowExclusiveLock',
'ShareLock',
'RowExclusiveLock'
],
AUTOVACUUM: [
'analyze',
'vacuum_analyze',
'vacuum',
'vacuum_freeze',
'brin_summarize'
],
STANDBY_DELTA: [
'sent_delta',
'write_delta',
'flush_delta',
'replay_delta'
],
REPSLOT_FILES: [
'replslot_wal_keep',
'replslot_files'
]
}
NO_VERSION = 0
DEFAULT = 'DEFAULT'
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)).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)).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_TABLE_STATS = {
DEFAULT: """
SELECT
((sum(relpages) * 8) * 1024) AS table_size,
count(1) AS table_count
FROM pg_class
WHERE relkind IN ('r', 't');
""",
}
QUERY_INDEX_STATS = {
DEFAULT: """
SELECT
((sum(relpages) * 8) * 1024) AS index_size,
count(1)