jbc.lol
unoffical wafrn mirror
wafrn.net
atproto
social-network
activitypub
1-- cpu_load
2CREATE EXTENSION IF NOT EXISTS plpython3u;
3CREATE OR REPLACE FUNCTION get_load_average(OUT load_1min float, OUT load_5min float, OUT load_15min float) AS
4$$
5 from os import getloadavg
6 la = getloadavg()
7 return [la[0], la[1], la[2]]
8$$ LANGUAGE plpython3u VOLATILE;
9GRANT EXECUTE ON FUNCTION get_load_average() TO pgwatch;
10COMMENT ON FUNCTION get_load_average() is 'created for pgwatch';
11-- psutil_cpu
12/* Pre-requisites: PL/Pythonu and "psutil" Python package (e.g. pip install psutil)
13 "psutil" is known to behave differently depending on the used version and operating system, so if getting
14 errors please adjust to your needs. "psutil" documentation here: https://psutil.readthedocs.io/en/latest/
15*/
16CREATE EXTENSION IF NOT EXISTS plpython3u; /* "plpython3u" might need changing to "plpythonu" (Python 2) everywhere for older OS-es */
17
18CREATE OR REPLACE FUNCTION get_psutil_cpu(
19 OUT cpu_utilization float8, OUT load_1m_norm float8, OUT load_1m float8, OUT load_5m_norm float8, OUT load_5m float8,
20 OUT "user" float8, OUT system float8, OUT idle float8, OUT iowait float8, OUT irqs float8, OUT other float8
21)
22 LANGUAGE plpython3u
23AS $FUNCTION$
24
25from os import getloadavg
26from psutil import cpu_times_percent, cpu_percent, cpu_count
27from threading import Thread
28
29class GetCpuPercentThread(Thread):
30 def __init__(self, interval_seconds):
31 self.interval_seconds = interval_seconds
32 self.cpu_utilization_info = None
33 super(GetCpuPercentThread, self).__init__()
34
35 def run(self):
36 self.cpu_utilization_info = cpu_percent(self.interval_seconds)
37
38t = GetCpuPercentThread(0.5)
39t.start()
40
41ct = cpu_times_percent(0.5)
42la = getloadavg()
43
44t.join()
45
46return t.cpu_utilization_info, la[0] / cpu_count(), la[0], la[1] / cpu_count(), la[1], ct.user, ct.system, ct.idle, ct.iowait, ct.irq + ct.softirq, ct.steal + ct.guest + ct.guest_nice
47
48$FUNCTION$;
49
50GRANT EXECUTE ON FUNCTION get_psutil_cpu() TO pgwatch;
51COMMENT ON FUNCTION get_psutil_cpu() IS 'created for pgwatch';
52-- psutil_disk
53/* Pre-requisites: PL/Pythonu and "psutil" Python package (e.g. pip install psutil) */
54CREATE EXTENSION IF NOT EXISTS plpython3u; /* "plpython3u" might need changing to "plpythonu" (Python 2) everywhere for older OS-es */
55
56CREATE OR REPLACE FUNCTION get_psutil_disk(
57 OUT dir_or_tablespace text, OUT path text, OUT total float8, OUT used float8, OUT free float8, OUT percent float8
58)
59 RETURNS SETOF record
60 LANGUAGE plpython3u
61 SECURITY DEFINER
62AS $FUNCTION$
63
64from os import stat
65from os.path import join, exists
66from psutil import disk_usage
67ret_list = []
68
69# data_directory
70r = plpy.execute("select current_setting('data_directory') as dd, current_setting('log_directory') as ld, current_setting('server_version_num')::int as pgver")
71dd = r[0]['dd']
72ld = r[0]['ld']
73du_dd = disk_usage(dd)
74ret_list.append(['data_directory', dd, du_dd.total, du_dd.used, du_dd.free, du_dd.percent])
75
76dd_stat = stat(dd)
77# log_directory
78if ld:
79 if not ld.startswith('/'):
80 ld_path = join(dd, ld)
81 else:
82 ld_path = ld
83 if exists(ld_path):
84 log_stat = stat(ld_path)
85 if log_stat.st_dev == dd_stat.st_dev:
86 pass # no new info, same device
87 else:
88 du = disk_usage(ld_path)
89 ret_list.append(['log_directory', ld_path, du.total, du.used, du.free, du.percent])
90
91# WAL / XLOG directory
92# plpy.notice('pg_wal' if r[0]['pgver'] >= 100000 else 'pg_xlog', r[0]['pgver'])
93joined_path_wal = join(r[0]['dd'], 'pg_wal' if r[0]['pgver'] >= 100000 else 'pg_xlog')
94wal_stat = stat(joined_path_wal)
95if wal_stat.st_dev == dd_stat.st_dev:
96 pass # no new info, same device
97else:
98 du = disk_usage(joined_path_wal)
99 ret_list.append(['pg_wal', joined_path_wal, du.total, du.used, du.free, du.percent])
100
101# add user created tablespaces if any
102sql_tablespaces = """
103 select spcname as name, pg_catalog.pg_tablespace_location(oid) as location
104 from pg_catalog.pg_tablespace where not spcname like any(array[E'pg\\_%'])"""
105for row in plpy.cursor(sql_tablespaces):
106 du = disk_usage(row['location'])
107 ret_list.append([row['name'], row['location'], du.total, du.used, du.free, du.percent])
108return ret_list
109
110$FUNCTION$;
111
112GRANT EXECUTE ON FUNCTION get_psutil_disk() TO pgwatch;
113COMMENT ON FUNCTION get_psutil_disk() IS 'created for pgwatch';
114-- psutil_disk_io_total
115/* Pre-requisites: PL/Pythonu and "psutil" Python package (e.g. pip install psutil) */
116CREATE EXTENSION IF NOT EXISTS plpython3u; /* "plpython3u" might need changing to "plpythonu" (Python 2) everywhere for older OS-es */
117
118CREATE OR REPLACE FUNCTION get_psutil_disk_io_total(
119 OUT read_count float8, OUT write_count float8, OUT read_bytes float8, OUT write_bytes float8
120)
121 LANGUAGE plpython3u
122AS $FUNCTION$
123from psutil import disk_io_counters
124dc = disk_io_counters(perdisk=False)
125if dc:
126 return dc.read_count, dc.write_count, dc.read_bytes, dc.write_bytes
127else:
128 return None, None, None, None
129$FUNCTION$;
130
131GRANT EXECUTE ON FUNCTION get_psutil_disk_io_total() TO pgwatch;
132COMMENT ON FUNCTION get_psutil_disk_io_total() IS 'created for pgwatch';
133-- psutil_mem
134/* Pre-requisites: PL/Pythonu and "psutil" Python package (e.g. pip install psutil) */
135CREATE EXTENSION IF NOT EXISTS plpython3u; /* "plpython3u" might need changing to "plpythonu" (Python 2 everywhere for new OS-es */
136
137CREATE OR REPLACE FUNCTION get_psutil_mem(
138 OUT total float8, OUT used float8, OUT free float8, OUT buff_cache float8, OUT available float8, OUT percent float8,
139 OUT swap_total float8, OUT swap_used float8, OUT swap_free float8, OUT swap_percent float8
140)
141 LANGUAGE plpython3u
142AS $FUNCTION$
143from psutil import virtual_memory, swap_memory
144vm = virtual_memory()
145sw = swap_memory()
146return vm.total, vm.used, vm.free, vm.buffers + vm.cached, vm.available, vm.percent, sw.total, sw.used, sw.free, sw.percent
147$FUNCTION$;
148
149GRANT EXECUTE ON FUNCTION get_psutil_mem() TO pgwatch;
150COMMENT ON FUNCTION get_psutil_mem() IS 'created for pgwatch';
151-- stat_statements
152CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
153-- stat_statements_calls
154CREATE EXTENSION IF NOT EXISTS pg_stat_statements;