Pgwatch2 :: Metrics and preset configs

Preset configs

Name	Description	Config JSON	Active DBs using config	Last modified
aurora	AWS Aurora doesn't expose all Postgres functions and there's no WAL	{"locks": 60, "db_size": 300, "archiver": 60, "backends": 60, "bgwriter": 60, "settings": 7200, "locks_mode": 60, "index_stats": 900, "replication": 120, "sproc_stats": 180, "table_stats": 300, "wal_receiver": 120, "change_events": 300, "table_io_stats": 600, "db_stats_aurora": 60, "stat_statements": 180, "replication_slots": 120, "stat_statements_calls": 60, "table_bloat_approx_summary_sql": 7200}		2021-06-16 17:37:50+00
azure	similar to 'exhaustive' with stuff that's not accessible on Azure Database for PostgreSQL removed	{"kpi": 120, "wal": 60, "locks": 60, "db_size": 300, "archiver": 60, "backends": 60, "bgwriter": 60, "db_stats": 60, "settings": 7200, "stat_ssl": 60, "locks_mode": 60, "index_stats": 900, "replication": 60, "sproc_stats": 180, "wal_receiver": 60, "change_events": 300, "table_io_stats": 600, "stat_statements": 180, "replication_slots": 60, "logical_subscriptions": 60, "stat_statements_calls": 60, "server_log_event_counts": 60, "table_bloat_approx_summary_sql": 7200}		2021-06-16 17:37:50+00
basic	only the most important metrics - WAL, DB-level statistics (size, tx and backend counts)	{"wal": 60, "db_size": 300, "db_stats": 60}		2021-06-16 17:37:50+00
exhaustive	all important metrics for a deeper performance understanding	{"wal": 60, "locks": 60, "db_size": 300, "archiver": 60, "backends": 60, "bgwriter": 60, "cpu_load": 60, "db_stats": 60, "settings": 7200, "wal_size": 300, "locks_mode": 60, "index_stats": 900, "replication": 120, "sproc_stats": 180, "table_stats": 300, "wal_receiver": 120, "change_events": 300, "table_io_stats": 600, "stat_statements": 180, "replication_slots": 120, "stat_statements_calls": 60, "table_bloat_approx_summary_sql": 7200}		2021-06-16 17:37:50+00
full	almost all available metrics for a even deeper performance understanding	{"kpi": 120, "wal": 60, "locks": 60, "db_size": 300, "archiver": 60, "backends": 60, "bgwriter": 60, "cpu_load": 60, "db_stats": 60, "settings": 7200, "stat_ssl": 120, "wal_size": 120, "locks_mode": 60, "psutil_cpu": 120, "psutil_mem": 120, "index_stats": 900, "psutil_disk": 120, "replication": 120, "sproc_stats": 180, "table_stats": 300, "wal_receiver": 120, "change_events": 300, "table_io_stats": 600, "recommendations": 43200, "stat_statements": 180, "replication_slots": 120, "psutil_disk_io_total": 120, "logical_subscriptions": 120, "stat_statements_calls": 60, "server_log_event_counts": 60, "table_bloat_approx_summary_sql": 7200}		2021-06-16 17:37:50+00
full_influx	almost all available metrics for a even deeper performance understanding	{"kpi": 120, "wal": 60, "locks": 60, "db_size": 300, "archiver": 60, "backends": 60, "bgwriter": 60, "cpu_load": 60, "db_stats": 60, "settings": 7200, "stat_ssl": 120, "wal_size": 120, "locks_mode": 60, "psutil_cpu": 120, "psutil_mem": 120, "index_stats": 900, "psutil_disk": 120, "replication": 120, "sproc_stats": 180, "table_stats": 300, "wal_receiver": 120, "change_events": 300, "table_io_stats": 600, "recommendations": 43200, "stat_statements": 180, "replication_slots": 120, "psutil_disk_io_total": 120, "logical_subscriptions": 120, "stat_statements_calls": 60, "server_log_event_counts": 60, "table_bloat_approx_summary_sql": 7200}		2021-06-16 17:37:50+00
minimal	single "Key Performance Indicators" query for fast cluster/db overview	{"kpi": 60}		2021-06-16 17:37:50+00
pgbouncer	per DB stats	{"pgbouncer_stats": 60}		2021-06-16 17:37:50+00
pgpool	pool global stats, 1 row per node ID	{"pgpool_stats": 60}		2021-06-16 17:37:50+00
prometheus	similar to "exhaustive" but without some possibly longer-running metrics and those keeping state	{"wal": 1, "db_size": 1, "archiver": 1, "backends": 1, "bgwriter": 1, "cpu_load": 1, "db_stats": 1, "locks_mode": 1, "replication": 1, "sproc_stats": 1, "table_stats": 1, "wal_receiver": 1, "replication_slots": 1, "stat_statements_calls": 1}		2021-06-16 17:37:50+00
rds	similar to 'exhaustive' with stuff not accessible on AWS RDS removed	{"wal": 60, "locks": 60, "db_size": 300, "archiver": 60, "backends": 60, "bgwriter": 60, "db_stats": 60, "settings": 7200, "locks_mode": 60, "index_stats": 900, "replication": 120, "sproc_stats": 180, "table_stats": 300, "wal_receiver": 120, "change_events": 300, "table_io_stats": 600, "stat_statements": 180, "replication_slots": 120, "stat_statements_calls": 60, "table_bloat_approx_summary_sql": 7200}		2021-06-16 17:37:50+00
standard	"basic" level + table, index, stat_statements stats	{"wal": 60, "db_size": 300, "cpu_load": 60, "db_stats": 60, "index_stats": 900, "sproc_stats": 180, "table_stats": 300, "stat_statements": 180}		2021-06-16 17:37:50+00
superuser_no_python	like exhaustive, but no PL/Python helpers	{"wal": 60, "locks": 60, "db_size": 300, "archiver": 60, "backends": 60, "bgwriter": 60, "db_stats": 60, "settings": 7200, "locks_mode": 60, "index_stats": 900, "replication": 120, "sproc_stats": 180, "table_stats": 300, "wal_receiver": 120, "change_events": 300, "table_io_stats": 600, "stat_statements": 180, "replication_slots": 120, "stat_statements_calls": 60, "table_bloat_approx_summary_sql": 7200}	booker, defender, hangfire, profiler	2021-06-16 17:58:16+00
unprivileged	no wrappers + only pg_stat_statements extension expected (developer mode)	{"wal": 60, "locks": 60, "db_size": 300, "archiver": 60, "bgwriter": 60, "db_stats": 60, "settings": 7200, "locks_mode": 60, "index_stats": 900, "replication": 120, "sproc_stats": 180, "table_stats": 300, "change_events": 300, "table_io_stats": 600, "replication_slots": 120, "stat_statements_calls": 60}		2021-06-16 17:37:50+00
		{"metric": interval_seconds, ...}

Active metrics listing

archiver [ver: 9.4]
backends [ver: 9.0,9.2,9.4,9.6,10]
backup_age_pgbackrest [ver: 9.1]
backup_age_walg [ver: 9.1]
bgwriter [ver: 9.0,9.2]
blocking_locks [ver: 9.2]
buffercache_by_db [ver: 9.2]
buffercache_by_type [ver: 9.2]
change_events [ver: 9.0]
configuration_hashes [ver: 9.0]
cpu_load [ver: 9.0]
database_conflicts [ver: 9.2]
db_size [ver: 9.0]
db_stats [ver: 9.0,9.1,9.2,9.3,10,12]
db_stats_aurora [ver: 9.6,10]
get_backup_age_pgbackrest [ver: 9.1]
get_backup_age_walg [ver: 9.1]
get_load_average [ver: 9.1]
get_load_average_copy [ver: 9.0]
get_load_average_windows [ver: 9.0]
get_psutil_cpu [ver: 9.1]
get_psutil_disk [ver: 9.1]
get_psutil_disk_io_total [ver: 9.1]
get_psutil_mem [ver: 9.1]
get_smart_health_per_device [ver: 9.1]
get_stat_activity [ver: 9.0]
get_stat_replication [ver: 9.2]
get_stat_statements [ver: 9.2,9.4]
get_table_bloat_approx [ver: 9.5]
get_table_bloat_approx_sql [ver: 9.0,12]
get_vmstat [ver: 9.1]
get_wal_size [ver: 9.0,10]
index_hashes [ver: 9.0]
index_stats [ver: 9.0,9.1,10]
kpi [ver: 9.0,9.2,9.6,10]
locks [ver: 9.0]
locks_mode [ver: 9.0]
logical_subscriptions [ver: 10]
pgbouncer_stats [ver: 0]
pgpool_stats [ver: 3.0]
privilege_changes [ver: 9.0]
psutil_cpu [ver: 9.1]
psutil_disk [ver: 9.1]
psutil_disk_io_total [ver: 9.1]
psutil_mem [ver: 9.1]
reco_add_index [ver: 9.1]
reco_add_index_ext_qualstats_2.0 [ver: 9.1]
reco_default_public_schema [ver: 9.1]
reco_disabled_triggers [ver: 9.0]
reco_drop_index [ver: 9.0,9.4]
recommendations [ver: 9.0]
reco_nested_views [ver: 9.1]
reco_partial_index_candidates [ver: 9.0]
reco_sprocs_wo_search_path [ver: 9.1]
reco_superusers [ver: 9.1]
replication [ver: 9.2,10]
replication_slots [ver: 9.4,10]
server_log_event_counts [ver: 9.0]
settings [ver: 9.0]
show_plans_realtime [ver: 9.0,10]
smart_health_per_disk [ver: 9.1]
sproc_hashes [ver: 9.0]
sproc_stats [ver: 9.0]
stat_activity_realtime [ver: 9.0,9.2,9.6,10]
stat_ssl [ver: 9.5]
stat_statements [ver: 9.2,9.4,13]
stat_statements_calls [ver: 9.2,13]
stat_statements_no_query_text [ver: 9.2,9.4,13]
table_bloat_approx_stattuple [ver: 9.5]
table_bloat_approx_summary [ver: 9.5,10]
table_bloat_approx_summary_sql [ver: 9.0,12]
table_hashes [ver: 9.0,9.3]
table_io_stats [ver: 9.0,10]
table_stats [ver: 9.0,9.0,9.1,9.1,9.2,10]
vmstat [ver: 9.1]
wal [ver: 9.2,10]
wal_receiver [ver: 9.2,10]
wal_size [ver: 9.0,10]

Metric definitions

Metric	PG ver. from	SQL	Privileged SQL	Comment	Is active?	Is helper?	Master only?	Standby only?	Metric attributes	Column attributes	Last modified
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, archived_count, failed_count, case when coalesce(last_failed_time, '1970-01-01'::timestamptz) > coalesce(last_archived_time, '1970-01-01'::timestamptz) then 1 else 0 end as is_failing_int, extract(epoch from now() - last_failed_time)::int8 as seconds_since_last_failure from pg_stat_archiver where current_setting('archive_mode') in ('on', 'always');							{"is_instance_level": true}	{"prometheus_gauge_columns": ["is_failing_int", "seconds_since_last_failure"]}	2021-06-16 17:37:50
		with sa_snapshot as ( select * from get_stat_activity() where not current_query like 'autovacuum:%' ) select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, (select count() from sa_snapshot) as total, (select count() from pg_stat_activity where procpid != pg_backend_pid()) as instance_total, current_setting('max_connections')::int as max_connections, (select count() from sa_snapshot where current_query != '<IDLE>') as active, (select count() from sa_snapshot where current_query = '<IDLE>') as idle, (select count() from sa_snapshot where current_query = '<IDLE> in transaction') as idleintransaction, (select count() from sa_snapshot where waiting) as waiting, (select extract(epoch from max(now() - query_start))::int from sa_snapshot where waiting) as longest_waiting_seconds, (select extract(epoch from (now() - backend_start))::int from sa_snapshot order by backend_start limit 1) as longest_session_seconds, (select extract(epoch from (now() - xact_start))::int from sa_snapshot where xact_start is not null order by xact_start limit 1) as longest_tx_seconds, (select extract(epoch from (now() - xact_start))::int from get_stat_activity() where current_query like 'autovacuum:%' order by xact_start limit 1) as longest_autovacuum_seconds, (select extract(epoch from max(now() - query_start))::int from sa_snapshot where current_query != '<IDLE>') as longest_query_seconds, (select count(*) from get_stat_activity() where current_query like 'autovacuum:%') as av_workers;	with sa_snapshot as ( select * from pg_stat_activity where datname = current_database() and not current_query like 'autovacuum:%' and procpid != pg_backend_pid() ) select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, (select count() from sa_snapshot) as total, (select count() from pg_stat_activity where procpid != pg_backend_pid()) as instance_total, current_setting('max_connections')::int as max_connections, (select count() from sa_snapshot where current_query != '<IDLE>') as active, (select count() from sa_snapshot where current_query = '<IDLE>') as idle, (select count() from sa_snapshot where current_query = '<IDLE> in transaction') as idleintransaction, (select count() from sa_snapshot where waiting) as waiting, (select extract(epoch from max(now() - query_start))::int from sa_snapshot where waiting) as longest_waiting_seconds, (select extract(epoch from (now() - backend_start))::int from sa_snapshot order by backend_start limit 1) as longest_session_seconds, (select extract(epoch from (now() - xact_start))::int from sa_snapshot where xact_start is not null order by xact_start limit 1) as longest_tx_seconds, (select extract(epoch from (now() - xact_start))::int from pg_stat_activity where current_query like 'autovacuum:%' order by xact_start limit 1) as longest_autovacuum_seconds, (select extract(epoch from max(now() - query_start))::int from sa_snapshot where current_query != '<IDLE>') as longest_query_seconds, (select count(*) from pg_stat_activity where datname = current_database() and current_query like 'autovacuum:%') as av_workers;							{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		with sa_snapshot as ( select * from get_stat_activity() where not query like 'autovacuum:%' ) select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, (select count() from sa_snapshot) as total, (select count() from pg_stat_activity where pid != pg_backend_pid()) as instance_total, current_setting('max_connections')::int as max_connections, (select count() from sa_snapshot where state = 'active') as active, (select count() from sa_snapshot where state = 'idle') as idle, (select count() from sa_snapshot where state = 'idle in transaction') as idleintransaction, (select count() from sa_snapshot where waiting) as waiting, (select extract(epoch from max(now() - query_start))::int from sa_snapshot where waiting) as longest_waiting_seconds, (select extract(epoch from (now() - backend_start))::int from sa_snapshot order by backend_start limit 1) as longest_session_seconds, (select extract(epoch from (now() - xact_start))::int from sa_snapshot where xact_start is not null order by xact_start limit 1) as longest_tx_seconds, (select extract(epoch from (now() - xact_start))::int from get_stat_activity() where query like 'autovacuum:%' order by xact_start limit 1) as longest_autovacuum_seconds, (select extract(epoch from max(now() - query_start))::int from sa_snapshot where state = 'active') as longest_query_seconds, (select count(*) from get_stat_activity() where query like 'autovacuum:%') as av_workers;	with sa_snapshot as ( select * from pg_stat_activity where datname = current_database() and not query like 'autovacuum:%' and pid != pg_backend_pid() ) select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, (select count() from sa_snapshot) as total, (select count() from pg_stat_activity where pid != pg_backend_pid()) as instance_total, current_setting('max_connections')::int as max_connections, (select count() from sa_snapshot where state = 'active') as active, (select count() from sa_snapshot where state = 'idle') as idle, (select count() from sa_snapshot where state = 'idle in transaction') as idleintransaction, (select count() from sa_snapshot where waiting) as waiting, (select extract(epoch from max(now() - query_start))::int from sa_snapshot where waiting) as longest_waiting_seconds, (select extract(epoch from (now() - backend_start))::int from sa_snapshot order by backend_start limit 1) as longest_session_seconds, (select extract(epoch from (now() - xact_start))::int from sa_snapshot where xact_start is not null order by xact_start limit 1) as longest_tx_seconds, (select extract(epoch from (now() - xact_start))::int from pg_stat_activity where query like 'autovacuum:%' order by xact_start limit 1) as longest_autovacuum_seconds, (select extract(epoch from max(now() - query_start))::int from sa_snapshot where state = 'active') as longest_query_seconds, (select count(*) from pg_stat_activity where datname = current_database() and query like 'autovacuum:%') as av_workers;							{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		with sa_snapshot as ( select * from get_stat_activity() where not query like 'autovacuum:%' ) select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, (select count() from sa_snapshot) as total, (select count() from pg_stat_activity where pid != pg_backend_pid()) as instance_total, current_setting('max_connections')::int as max_connections, (select count() from sa_snapshot where state = 'active') as active, (select count() from sa_snapshot where state = 'idle') as idle, (select count() from sa_snapshot where state = 'idle in transaction') as idleintransaction, (select count() from sa_snapshot where waiting) as waiting, (select extract(epoch from max(now() - query_start))::int from sa_snapshot where waiting) as longest_waiting_seconds, (select extract(epoch from (now() - backend_start))::int from sa_snapshot order by backend_start limit 1) as longest_session_seconds, (select extract(epoch from (now() - xact_start))::int from sa_snapshot where xact_start is not null order by xact_start limit 1) as longest_tx_seconds, (select extract(epoch from (now() - xact_start))::int from get_stat_activity() where query like 'autovacuum:%' order by xact_start limit 1) as longest_autovacuum_seconds, (select extract(epoch from max(now() - query_start))::int from sa_snapshot where state = 'active') as longest_query_seconds, (select max(age(backend_xmin))::int8 from sa_snapshot) as max_xmin_age_tx, (select count(*) from get_stat_activity() where query like 'autovacuum:%') as av_workers;	with sa_snapshot as ( select * from pg_stat_activity where datname = current_database() and not query like 'autovacuum:%' and pid != pg_backend_pid() ) select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, (select count() from sa_snapshot) as total, (select count() from pg_stat_activity where pid != pg_backend_pid()) as instance_total, current_setting('max_connections')::int as max_connections, (select count() from sa_snapshot where state = 'active') as active, (select count() from sa_snapshot where state = 'idle') as idle, (select count() from sa_snapshot where state = 'idle in transaction') as idleintransaction, (select count() from sa_snapshot where waiting) as waiting, (select extract(epoch from max(now() - query_start))::int from sa_snapshot where waiting) as longest_waiting_seconds, (select extract(epoch from (now() - backend_start))::int from sa_snapshot order by backend_start limit 1) as longest_session_seconds, (select extract(epoch from (now() - xact_start))::int from sa_snapshot where xact_start is not null order by xact_start limit 1) as longest_tx_seconds, (select extract(epoch from (now() - xact_start))::int from pg_stat_activity where query like 'autovacuum:%' order by xact_start limit 1) as longest_autovacuum_seconds, (select extract(epoch from max(now() - query_start))::int from sa_snapshot where state = 'active') as longest_query_seconds, (select max(age(backend_xmin))::int8 from sa_snapshot) as max_xmin_age_tx, (select count(*) from pg_stat_activity where datname = current_database() and query like 'autovacuum:%') as av_workers;							{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		with sa_snapshot as ( select * from get_stat_activity() where not query like 'autovacuum:%' ) select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, (select count() from sa_snapshot) as total, (select count() from pg_stat_activity where pid != pg_backend_pid()) as instance_total, current_setting('max_connections')::int as max_connections, (select count() from sa_snapshot where state = 'active') as active, (select count() from sa_snapshot where state = 'idle') as idle, (select count() from sa_snapshot where state = 'idle in transaction') as idleintransaction, (select count() from sa_snapshot where wait_event_type in ('LWLockNamed', 'Lock', 'BufferPin')) as waiting, (select extract(epoch from max(now() - query_start))::int from sa_snapshot where wait_event_type in ('LWLockNamed', 'Lock', 'BufferPin')) as longest_waiting_seconds, (select extract(epoch from (now() - backend_start))::int from sa_snapshot order by backend_start limit 1) as longest_session_seconds, (select extract(epoch from (now() - xact_start))::int from sa_snapshot where xact_start is not null order by xact_start limit 1) as longest_tx_seconds, (select extract(epoch from (now() - xact_start))::int from get_stat_activity() where query like 'autovacuum:%' order by xact_start limit 1) as longest_autovacuum_seconds, (select extract(epoch from max(now() - query_start))::int from sa_snapshot where state = 'active') as longest_query_seconds, (select max(age(backend_xmin))::int8 from sa_snapshot) as max_xmin_age_tx, (select count(*) from get_stat_activity() where query like 'autovacuum:%') as av_workers	with sa_snapshot as ( select * from pg_stat_activity where datname = current_database() and not query like 'autovacuum:%' and pid != pg_backend_pid() ) select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, (select count() from sa_snapshot) as total, (select count() from pg_stat_activity where pid != pg_backend_pid()) as instance_total, current_setting('max_connections')::int as max_connections, (select count() from sa_snapshot where state = 'active') as active, (select count() from sa_snapshot where state = 'idle') as idle, (select count() from sa_snapshot where state = 'idle in transaction') as idleintransaction, (select count() from sa_snapshot where wait_event_type in ('LWLockNamed', 'Lock', 'BufferPin')) as waiting, (select extract(epoch from max(now() - query_start))::int from sa_snapshot where wait_event_type in ('LWLockNamed', 'Lock', 'BufferPin')) as longest_waiting_seconds, (select extract(epoch from (now() - backend_start))::int from sa_snapshot order by backend_start limit 1) as longest_session_seconds, (select extract(epoch from (now() - xact_start))::int from sa_snapshot where xact_start is not null order by xact_start limit 1) as longest_tx_seconds, (select extract(epoch from (now() - xact_start))::int from pg_stat_activity where query like 'autovacuum:%' order by xact_start limit 1) as longest_autovacuum_seconds, (select extract(epoch from max(now() - query_start))::int from sa_snapshot where state = 'active') as longest_query_seconds, (select max(age(backend_xmin))::int8 from sa_snapshot) as max_xmin_age_tx, (select count(*) from pg_stat_activity where datname = current_database() and query like 'autovacuum:%') as av_workers							{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		with sa_snapshot as ( select * from get_stat_activity() ) select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, (select count() from sa_snapshot where backend_type = 'client backend') as total, (select count() from pg_stat_activity where pid != pg_backend_pid()) as instance_total, current_setting('max_connections')::int as max_connections, (select count() from sa_snapshot where backend_type = 'background worker') as background_workers, (select count() from sa_snapshot where state = 'active' and backend_type = 'client backend') as active, (select count() from sa_snapshot where state = 'idle' and backend_type = 'client backend') as idle, (select count() from sa_snapshot where state = 'idle in transaction' and backend_type = 'client backend') as idleintransaction, (select count() from sa_snapshot where wait_event_type in ('LWLock', 'Lock', 'BufferPin') and backend_type = 'client backend') as waiting, (select extract(epoch from max(now() - query_start))::int from sa_snapshot where wait_event_type in ('LWLock', 'Lock', 'BufferPin') and backend_type = 'client backend') as longest_waiting_seconds, (select extract(epoch from (now() - backend_start))::int from sa_snapshot where backend_type = 'client backend' order by backend_start limit 1) as longest_session_seconds, (select extract(epoch from (now() - xact_start))::int from sa_snapshot where xact_start is not null and backend_type = 'client backend' order by xact_start limit 1) as longest_tx_seconds, (select extract(epoch from (now() - xact_start))::int from get_stat_activity() where backend_type = 'autovacuum worker' order by xact_start limit 1) as longest_autovacuum_seconds, (select extract(epoch from max(now() - query_start))::int from sa_snapshot where state = 'active' and backend_type = 'client backend') as longest_query_seconds, (select max(age(backend_xmin))::int8 from sa_snapshot) as max_xmin_age_tx, (select count() from sa_snapshot where state = 'active' and backend_type = 'autovacuum worker') as av_workers	with sa_snapshot as ( select * from pg_stat_activity where pid != pg_backend_pid() and datname = current_database() ) select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, (select count() from sa_snapshot where backend_type = 'client backend') as total, (select count() from pg_stat_activity where pid != pg_backend_pid()) as instance_total, current_setting('max_connections')::int as max_connections, (select count() from sa_snapshot where backend_type = 'background worker') as background_workers, (select count() from sa_snapshot where state = 'active' and backend_type = 'client backend') as active, (select count() from sa_snapshot where state = 'idle' and backend_type = 'client backend') as idle, (select count() from sa_snapshot where state = 'idle in transaction' and backend_type = 'client backend') as idleintransaction, (select count() from sa_snapshot where wait_event_type in ('LWLock', 'Lock', 'BufferPin') and backend_type = 'client backend') as waiting, (select extract(epoch from max(now() - query_start))::int from sa_snapshot where wait_event_type in ('LWLock', 'Lock', 'BufferPin') and backend_type = 'client backend') as longest_waiting_seconds, (select extract(epoch from (now() - backend_start))::int from sa_snapshot where backend_type = 'client backend' order by backend_start limit 1) as longest_session_seconds, (select extract(epoch from (now() - xact_start))::int from sa_snapshot where xact_start is not null and backend_type = 'client backend' order by xact_start limit 1) as longest_tx_seconds, (select extract(epoch from (now() - xact_start))::int from pg_stat_activity where backend_type = 'autovacuum worker' order by xact_start limit 1) as longest_autovacuum_seconds, (select extract(epoch from max(now() - query_start))::int from sa_snapshot where state = 'active' and backend_type = 'client backend') as longest_query_seconds, (select max(age(backend_xmin))::int8 from sa_snapshot) as max_xmin_age_tx, (select count() from sa_snapshot where state = 'active' and backend_type = 'autovacuum worker') as av_workers;							{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, retcode, backup_age_seconds, message from get_backup_age_pgbackrest() ;							{"is_instance_level": true}	{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, retcode, backup_age_seconds, message from get_backup_age_walg() ;							{"is_instance_level": true}	{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, checkpoints_timed, checkpoints_req, buffers_checkpoint, buffers_clean, maxwritten_clean, buffers_backend, buffers_alloc from pg_stat_bgwriter;							{"is_instance_level": true}		2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint, buffers_clean, maxwritten_clean, buffers_backend, buffers_backend_fsync, buffers_alloc from pg_stat_bgwriter;							{"is_instance_level": true}		2021-06-16 17:37:50
		SELECT (extract(epoch from now()) * 1e9)::int8 AS epoch_ns, waiting.locktype AS tag_waiting_locktype, waiting_stm.usename::text AS tag_waiting_user, coalesce(waiting.mode, 'null'::text) AS tag_waiting_mode, coalesce(waiting.relation::regclass::text, 'null') AS tag_waiting_table, waiting_stm.query AS waiting_query, waiting.pid AS waiting_pid, other.locktype AS other_locktype, other.relation::regclass::text AS other_table, other_stm.query AS other_query, other.mode AS other_mode, other.pid AS other_pid, other_stm.usename::text AS other_user FROM pg_catalog.pg_locks AS waiting JOIN get_stat_activity() AS waiting_stm ON ( waiting_stm.pid = waiting.pid ) JOIN pg_catalog.pg_locks AS other ON ( ( waiting."database" = other."database" AND waiting.relation = other.relation ) OR waiting.transactionid = other.transactionid ) JOIN get_stat_activity() AS other_stm ON ( other_stm.pid = other.pid ) WHERE NOT waiting.GRANTED AND waiting.pid <> other.pid AND other.GRANTED AND waiting_stm.datname = current_database();	WITH sa_snapshot AS ( select * from pg_stat_activity where datname = current_database() and not query like 'autovacuum:%' and pid != pg_backend_pid() ) SELECT (extract(epoch from now()) * 1e9)::int8 AS epoch_ns, waiting.locktype AS tag_waiting_locktype, waiting_stm.usename::text AS tag_waiting_user, coalesce(waiting.mode, 'null'::text) AS tag_waiting_mode, coalesce(waiting.relation::regclass::text, 'null') AS tag_waiting_table, waiting_stm.query AS waiting_query, waiting.pid AS waiting_pid, other.locktype AS other_locktype, other.relation::regclass::text AS other_table, other_stm.query AS other_query, other.mode AS other_mode, other.pid AS other_pid, other_stm.usename::text AS other_user FROM pg_catalog.pg_locks AS waiting JOIN sa_snapshot AS waiting_stm ON ( waiting_stm.pid = waiting.pid ) JOIN pg_catalog.pg_locks AS other ON ( ( waiting."database" = other."database" AND waiting.relation = other.relation ) OR waiting.transactionid = other.transactionid ) JOIN sa_snapshot AS other_stm ON ( other_stm.pid = other.pid ) WHERE NOT waiting.GRANTED AND waiting.pid <> other.pid AND other.GRANTED AND waiting_stm.datname = current_database();								2021-06-16 17:37:50
		SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, datname as tag_database, count() (current_setting('block_size')::int8) as size_b FROM pg_buffercache AS b, pg_database AS d WHERE d.oid = b.reldatabase GROUP BY datname;							{"is_instance_level": true}	{"prometheus_gauge_columns": ["size_b"]}	2021-06-16 17:37:50
		SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, CASE WHEN relkind = 'r' THEN 'Table' -- TODO all relkinds covered? WHEN relkind = 'i' THEN 'Index' WHEN relkind = 't' THEN 'Toast' WHEN relkind = 'm' THEN 'Materialized view' ELSE 'Other' END as tag_relkind, count() (current_setting('block_size')::int8) as size_b FROM pg_buffercache AS b, pg_class AS d WHERE d.oid = b.relfilenode GROUP BY relkind;							{"is_instance_level": true}	{"prometheus_gauge_columns": ["size_b"]}	2021-06-16 17:37:50
											2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, name as tag_setting, coalesce(reset_val, '') as value from pg_settings;									2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, round(load_1min::numeric, 2)::float as load_1min, round(load_5min::numeric, 2)::float as load_5min, round(load_15min::numeric, 2)::float as load_15min from get_load_average(); -- needs the plpythonu proc from "metric_fetching_helpers" folder							{"is_instance_level": true}	{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, confl_tablespace, confl_lock, confl_snapshot, confl_bufferpin, confl_deadlock FROM pg_stat_database_conflicts WHERE datname = current_database();									2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, pg_database_size(current_database()) as size_b;								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, extract(epoch from (now() - pg_postmaster_start_time()))::int8 as postmaster_uptime_s, case when pg_is_in_recovery() then 1 else 0 end as in_recovery_int from pg_stat_database where datname = current_database();								{"prometheus_gauge_columns": ["numbackends", "postmaster_uptime_s"]}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, conflicts, extract(epoch from (now() - pg_postmaster_start_time()))::int8 as postmaster_uptime_s, case when pg_is_in_recovery() then 1 else 0 end as in_recovery_int from pg_stat_database where datname = current_database();								{"prometheus_gauge_columns": ["numbackends", "postmaster_uptime_s"]}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, conflicts, temp_files, temp_bytes, deadlocks, blk_read_time, blk_write_time, extract(epoch from (now() - pg_postmaster_start_time()))::int8 as postmaster_uptime_s, case when pg_is_in_recovery() then 1 else 0 end as in_recovery_int from pg_stat_database where datname = current_database();								{"prometheus_gauge_columns": ["numbackends", "postmaster_uptime_s"]}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, conflicts, temp_files, temp_bytes, deadlocks, blk_read_time, blk_write_time, extract(epoch from (now() - pg_postmaster_start_time()))::int8 as postmaster_uptime_s, extract(epoch from (now() - pg_backup_start_time()))::int8 as backup_duration_s, case when pg_is_in_recovery() then 1 else 0 end as in_recovery_int from pg_stat_database where datname = current_database();								{"prometheus_gauge_columns": ["numbackends", "postmaster_uptime_s", "backup_duration_s"]}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, conflicts, temp_files, temp_bytes, deadlocks, blk_read_time, blk_write_time, extract(epoch from (now() - pg_postmaster_start_time()))::int8 as postmaster_uptime_s, extract(epoch from (now() - pg_backup_start_time()))::int8 as backup_duration_s, case when pg_is_in_recovery() then 1 else 0 end as in_recovery_int, system_identifier::text as tag_sys_id from pg_stat_database, pg_control_system() where datname = current_database();	select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, conflicts, temp_files, temp_bytes, deadlocks, blk_read_time, blk_write_time, extract(epoch from (now() - coalesce((pg_stat_file('postmaster.pid', true)).modification, pg_postmaster_start_time())))::int8 as postmaster_uptime_s, extract(epoch from (now() - pg_backup_start_time()))::int8 as backup_duration_s, case when pg_is_in_recovery() then 1 else 0 end as in_recovery_int, system_identifier::text as tag_sys_id from pg_stat_database, pg_control_system() where datname = current_database();							{"prometheus_gauge_columns": ["numbackends", "postmaster_uptime_s", "backup_duration_s"]}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, conflicts, temp_files, temp_bytes, deadlocks, blk_read_time, blk_write_time, extract(epoch from (now() - pg_postmaster_start_time()))::int8 as postmaster_uptime_s, extract(epoch from (now() - pg_backup_start_time()))::int8 as backup_duration_s, checksum_failures, extract(epoch from (now() - checksum_last_failure))::int8 as checksum_last_failure_s, case when pg_is_in_recovery() then 1 else 0 end as in_recovery_int, system_identifier::text as tag_sys_id from pg_stat_database, pg_control_system() where datname = current_database();	select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, conflicts, temp_files, temp_bytes, deadlocks, blk_read_time, blk_write_time, extract(epoch from (now() - coalesce((pg_stat_file('postmaster.pid', true)).modification, pg_postmaster_start_time())))::int8 as postmaster_uptime_s, extract(epoch from (now() - pg_backup_start_time()))::int8 as backup_duration_s, checksum_failures, extract(epoch from (now() - checksum_last_failure))::int8 as checksum_last_failure_s, case when pg_is_in_recovery() then 1 else 0 end as in_recovery_int, system_identifier::text as tag_sys_id from pg_stat_database, pg_control_system() where datname = current_database();							{"prometheus_gauge_columns": ["numbackends", "postmaster_uptime_s", "backup_duration_s", "checksum_last_failure_s"]}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, conflicts, temp_files, temp_bytes, deadlocks, blk_read_time, blk_write_time, extract(epoch from (now() - pg_postmaster_start_time()))::int8 as postmaster_uptime_s, case when pg_is_in_recovery() then 1 else 0 end as in_recovery_int from pg_stat_database where datname = current_database();							{"metric_storage_name": "db_stats"}	{"prometheus_gauge_columns": ["numbackends", "postmaster_uptime_s"]}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, conflicts, temp_files, temp_bytes, deadlocks, blk_read_time, blk_write_time, extract(epoch from (now() - pg_postmaster_start_time()))::int8 as postmaster_uptime_s, case when pg_is_in_recovery() then 1 else 0 end as in_recovery_int, system_identifier::text as tag_sys_id from pg_stat_database, pg_control_system() where datname = current_database();							{"metric_storage_name": "db_stats"}	{"prometheus_gauge_columns": ["numbackends", "postmaster_uptime_s"]}	2021-06-16 17:37:50
		CREATE EXTENSION IF NOT EXISTS plpython3u; /* Gets age of last successful pgBackRest backup via "pgbackrest --output=json info" unix timestamp. Returns 0 retcode on success. Expects pgBackRest is correctly configured on monitored DB and "jq" tool is installed on the DB server. / CREATE OR REPLACE FUNCTION get_backup_age_pgbackrest(OUT retcode int, OUT backup_age_seconds int, OUT message text) AS $$ import subprocess retcode=1 backup_age_seconds=1000000 message='' # get latest wal-g backup timestamp walg_last_backup_cmd="""pgbackrest --output=json info \| jq '.[0] \| .backup[-1] \| .timestamp.stop'""" p = subprocess.run(walg_last_backup_cmd, stdout=subprocess.PIPE, encoding='utf-8', shell=True) if p.returncode != 0: # plpy.notice("p.stdout: " + str(p.stderr) + str(p.stderr)) return p.returncode, backup_age_seconds, 'Not OK. Failed on "pgbackrest info" call' last_backup_stop_epoch=p.stdout.rstrip('\n\r') try: plan = plpy.prepare("SELECT (extract(epoch from now()) - $1)::int8 AS backup_age_seconds;", ["int8"]) rv = plpy.execute(plan, [last_backup_stop_epoch]) except Exception as e: return retcode, backup_age_seconds, 'Not OK. Failed to extract seconds difference via Postgres' else: backup_age_seconds = rv[0]["backup_age_seconds"] return 0, backup_age_seconds, 'OK. Last backup age in seconds: %s' % backup_age_seconds $$ LANGUAGE plpython3u VOLATILE; / contacting S3 could be laggy depending on location */ ALTER FUNCTION get_backup_age_pgbackrest() SET statement_timeout TO '30s'; GRANT EXECUTE ON FUNCTION get_backup_age_pgbackrest() TO pgwatch2; COMMENT ON FUNCTION get_backup_age_pgbackrest() is 'created for pgwatch2';									2021-06-16 17:37:50
		CREATE EXTENSION IF NOT EXISTS plpython3u; /* Gets age of last successful WAL-G backup via "wal-g backup-list" timestamp. Returns 0 retcode on success. Expects .wal-g.json is correctly configured with all necessary credentials and "jq" tool is installed on the DB server. / CREATE OR REPLACE FUNCTION get_backup_age_walg(OUT retcode int, OUT backup_age_seconds int, OUT message text) AS $$ import subprocess retcode=1 backup_age_seconds=1000000 message='' # get latest wal-g backup timestamp walg_last_backup_cmd="""wal-g backup-list --json \| jq -r '.[0].time'""" p = subprocess.run(walg_last_backup_cmd, stdout=subprocess.PIPE, encoding='utf-8', shell=True) if p.returncode != 0: # plpy.notice("p.stdout: " + str(p.stderr) + str(p.stderr)) return p.returncode, backup_age_seconds, 'Not OK. Failed on wal-g backup-list call' # plpy.notice("last_tz: " + last_tz) last_tz=p.stdout.rstrip('\n\r') # get seconds since last backup from WAL-G timestamp in format '2020-01-22T17:50:51Z' try: plan = plpy.prepare("SELECT extract(epoch from now() - $1::timestamptz)::int AS backup_age_seconds;", ["text"]) rv = plpy.execute(plan, [last_tz]) except Exception as e: return retcode, backup_age_seconds, 'Not OK. Failed to convert WAL-G backup timestamp to seconds' else: backup_age_seconds = rv[0]["backup_age_seconds"] return 0, backup_age_seconds, 'OK. Last backup age in seconds: %s' % backup_age_seconds $$ LANGUAGE plpython3u VOLATILE; / contacting S3 could be laggy depending on location */ ALTER FUNCTION get_backup_age_walg() SET statement_timeout TO '30s'; GRANT EXECUTE ON FUNCTION get_backup_age_walg() TO pgwatch2; COMMENT ON FUNCTION get_backup_age_walg() is 'created for pgwatch2';									2021-06-16 17:37:50
		BEGIN; CREATE EXTENSION IF NOT EXISTS plpython3u; CREATE OR REPLACE FUNCTION get_load_average(OUT load_1min float, OUT load_5min float, OUT load_15min float) AS $$ from os import getloadavg la = getloadavg() return [la[0], la[1], la[2]] $$ LANGUAGE plpython3u VOLATILE; GRANT EXECUTE ON FUNCTION get_load_average() TO pgwatch2; COMMENT ON FUNCTION get_load_average() is 'created for pgwatch2'; COMMIT;									2021-06-16 17:37:50
		BEGIN; CREATE UNLOGGED TABLE IF NOT EXISTS get_load_average_copy /* remove the UNLOGGED and IF NOT EXISTS part for < v9.1 / ( load_1min float, load_5min float, load_15min float, proc_count text, last_procid int, created_on timestamptz not null default now() ); CREATE OR REPLACE FUNCTION get_load_average_copy(OUT load_1min float, OUT load_5min float, OUT load_15min float) AS $$ begin if random() < 0.02 then / clear the table on ca every 50th call not to be bigger than a couple of pages */ truncate get_load_average_copy; end if; copy get_load_average_copy (load_1min, load_5min, load_15min, proc_count, last_procid) from '/proc/loadavg' with (format csv, delimiter ' '); select t.load_1min, t.load_5min, t.load_15min into load_1min, load_5min, load_15min from get_load_average_copy t order by created_on desc nulls last limit 1; return; end; $$ LANGUAGE plpgsql SECURITY DEFINER; GRANT EXECUTE ON FUNCTION get_load_average_copy() TO pgwatch2; COMMENT ON FUNCTION get_load_average_copy() is 'created for pgwatch2'; -- below routine fixes function search_path to only include "more secure" schemas with no "public" CREATE privileges DO $_$ DECLARE l_secure_schemas_from_search_path text; BEGIN SELECT string_agg(safe_sp, ', ' ORDER BY rank) INTO l_secure_schemas_from_search_path FROM ( SELECT quote_ident(nspname) AS safe_sp, rank FROM unnest(regexp_split_to_array(current_setting('search_path'), ',')) WITH ORDINALITY AS csp(schema_name, rank) JOIN pg_namespace n ON quote_ident(n.nspname) = CASE WHEN schema_name = '"$user"' THEN quote_ident(user) ELSE trim(schema_name) END WHERE NOT has_schema_privilege('public', n.oid, 'CREATE') ) x; IF coalesce(l_secure_schemas_from_search_path, '') = '' THEN RAISE NOTICE 'search_path = %', current_setting('search_path'); RAISE EXCEPTION $$get_load_average_copy() SECURITY DEFINER helper will not be created as all schemas on search_path are unsecured where all users can create objects - execute 'REVOKE CREATE ON SCHEMA $my_schema FROM public' to tighten security or comment out the DO block to disable the check$$; ELSE RAISE NOTICE '%', format($$ALTER FUNCTION get_load_average_copy() SET search_path TO %s$$, l_secure_schemas_from_search_path); EXECUTE format($$ALTER FUNCTION get_load_average_copy() SET search_path TO %s$$, l_secure_schemas_from_search_path); END IF; END; $_$; COMMIT;									2021-06-16 17:37:50
		/* Python function for Windows that is used to extract CPU load from machine via SQL. Since os.getloadavg() function is unavailable for Windows, ctypes and kernel32.GetSystemTimes() used / --DROP TYPE load_average; --DROP FUNCTION get_load_average(); --DROP FUNCTION cpu(); BEGIN; DROP TYPE IF EXISTS load_average CASCADE; CREATE TYPE load_average AS ( load_1min real, load_5min real, load_15min real ); CREATE OR REPLACE FUNCTION cpu() RETURNS real AS $$ from ctypes import windll, Structure, sizeof, byref from ctypes.wintypes import DWORD import time class FILETIME(Structure): _fields_ = [("dwLowDateTime", DWORD), ("dwHighDateTime", DWORD)] def GetSystemTimes(): __GetSystemTimes = windll.kernel32.GetSystemTimes idleTime, kernelTime, userTime = FILETIME(), FILETIME(), FILETIME() success = __GetSystemTimes(byref(idleTime), byref(kernelTime), byref(userTime)) assert success, ctypes.WinError(ctypes.GetLastError())[1] return { "idleTime": idleTime.dwLowDateTime, "kernelTime": kernelTime.dwLowDateTime, "userTime": userTime.dwLowDateTime } FirstSystemTimes = GetSystemTimes() time.sleep(0.2) SecSystemTimes = GetSystemTimes() usr = SecSystemTimes['userTime'] - FirstSystemTimes['userTime'] ker = SecSystemTimes['kernelTime'] - FirstSystemTimes['kernelTime'] idl = SecSystemTimes['idleTime'] - FirstSystemTimes['idleTime'] sys = ker + usr return min((sys - idl) 100 / sys, 100) $$ LANGUAGE plpython3u; CREATE OR REPLACE FUNCTION get_load_average_windows() RETURNS load_average AS $$ SELECT val, val, val FROM cpu() AS cpu_now(val); $$ LANGUAGE sql; GRANT EXECUTE ON FUNCTION get_load_average_windows() TO pgwatch2; COMMENT ON FUNCTION get_load_average_windows() is 'created for pgwatch2'; COMMIT;									2021-06-16 17:37:50
		/* Pre-requisites: PL/Pythonu and "psutil" Python package (e.g. pip install psutil) NB! "psutil" is known to behave differently depending on the used version and operating system, so if getting errors please adjust to your needs. "psutil" documentation here: https://psutil.readthedocs.io/en/latest/ / CREATE EXTENSION IF NOT EXISTS plpython3u; / NB! "plpython3u" might need changing to "plpythonu" (Python 2) everywhere for older OS-es */ CREATE OR REPLACE FUNCTION get_psutil_cpu( OUT cpu_utilization float8, OUT load_1m_norm float8, OUT load_1m float8, OUT load_5m_norm float8, OUT load_5m float8, OUT "user" float8, OUT system float8, OUT idle float8, OUT iowait float8, OUT irqs float8, OUT other float8 ) LANGUAGE plpython3u AS $FUNCTION$ from os import getloadavg from psutil import cpu_times_percent, cpu_percent, cpu_count from threading import Thread class GetCpuPercentThread(Thread): def __init__(self, interval_seconds): self.interval_seconds = interval_seconds self.cpu_utilization_info = None super(GetCpuPercentThread, self).__init__() def run(self): self.cpu_utilization_info = cpu_percent(self.interval_seconds) t = GetCpuPercentThread(0.5) t.start() ct = cpu_times_percent(0.5) la = getloadavg() t.join() return 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 $FUNCTION$; GRANT EXECUTE ON FUNCTION get_psutil_cpu() TO pgwatch2; COMMENT ON FUNCTION get_psutil_cpu() IS 'created for pgwatch2';								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		/* Pre-requisites: PL/Pythonu and "psutil" Python package (e.g. pip install psutil) / CREATE EXTENSION IF NOT EXISTS plpython3u; / NB! "plpython3u" might need changing to "plpythonu" (Python 2) everywhere for older OS-es */ CREATE OR REPLACE FUNCTION get_psutil_disk( OUT dir_or_tablespace text, OUT path text, OUT total float8, OUT used float8, OUT free float8, OUT percent float8 ) RETURNS SETOF record LANGUAGE plpython3u SECURITY DEFINER AS $FUNCTION$ from os import stat from os.path import join, exists from psutil import disk_usage ret_list = [] # data_directory r = plpy.execute("select current_setting('data_directory') as dd, current_setting('log_directory') as ld, current_setting('server_version_num')::int as pgver") dd = r[0]['dd'] ld = r[0]['ld'] du_dd = disk_usage(dd) ret_list.append(['data_directory', dd, du_dd.total, du_dd.used, du_dd.free, du_dd.percent]) dd_stat = stat(dd) # log_directory if ld: if not ld.startswith('/'): ld_path = join(dd, ld) else: ld_path = ld if exists(ld_path): log_stat = stat(ld_path) if log_stat.st_dev == dd_stat.st_dev: pass # no new info, same device else: du = disk_usage(ld_path) ret_list.append(['log_directory', ld_path, du.total, du.used, du.free, du.percent]) # WAL / XLOG directory # plpy.notice('pg_wal' if r[0]['pgver'] >= 100000 else 'pg_xlog', r[0]['pgver']) joined_path_wal = join(r[0]['dd'], 'pg_wal' if r[0]['pgver'] >= 100000 else 'pg_xlog') wal_stat = stat(joined_path_wal) if wal_stat.st_dev == dd_stat.st_dev: pass # no new info, same device else: du = disk_usage(joined_path_wal) ret_list.append(['pg_wal', joined_path_wal, du.total, du.used, du.free, du.percent]) # add user created tablespaces if any sql_tablespaces = """ select spcname as name, pg_catalog.pg_tablespace_location(oid) as location from pg_catalog.pg_tablespace where not spcname like any(array[E'pg\\_%'])""" for row in plpy.cursor(sql_tablespaces): du = disk_usage(row['location']) ret_list.append([row['name'], row['location'], du.total, du.used, du.free, du.percent]) return ret_list $FUNCTION$; GRANT EXECUTE ON FUNCTION get_psutil_disk() TO pgwatch2; COMMENT ON FUNCTION get_psutil_disk() IS 'created for pgwatch2';								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		/* Pre-requisites: PL/Pythonu and "psutil" Python package (e.g. pip install psutil) / CREATE EXTENSION IF NOT EXISTS plpython3u; / NB! "plpython3u" might need changing to "plpythonu" (Python 2) everywhere for older OS-es */ CREATE OR REPLACE FUNCTION get_psutil_disk_io_total( OUT read_count float8, OUT write_count float8, OUT read_bytes float8, OUT write_bytes float8 ) LANGUAGE plpython3u AS $FUNCTION$ from psutil import disk_io_counters dc = disk_io_counters(perdisk=False) if dc: return dc.read_count, dc.write_count, dc.read_bytes, dc.write_bytes else: return None, None, None, None $FUNCTION$; GRANT EXECUTE ON FUNCTION get_psutil_disk_io_total() TO pgwatch2; COMMENT ON FUNCTION get_psutil_disk_io_total() IS 'created for pgwatch2';								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		/* Pre-requisites: PL/Pythonu and "psutil" Python package (e.g. pip install psutil) */ CREATE EXTENSION IF NOT EXISTS plpython3u; -- NB! "plpython3u" might need changing to "plpythonu" (Python 2) everywhere for older OS-es CREATE OR REPLACE FUNCTION get_psutil_mem( OUT total float8, OUT used float8, OUT free float8, OUT buff_cache float8, OUT available float8, OUT percent float8, OUT swap_total float8, OUT swap_used float8, OUT swap_free float8, OUT swap_percent float8 ) LANGUAGE plpython3u AS $FUNCTION$ from psutil import virtual_memory, swap_memory vm = virtual_memory() sw = swap_memory() return vm.total, vm.used, vm.free, vm.buffers + vm.cached, vm.available, vm.percent, sw.total, sw.used, sw.free, sw.percent $FUNCTION$; GRANT EXECUTE ON FUNCTION get_psutil_mem() TO pgwatch2; COMMENT ON FUNCTION get_psutil_mem() IS 'created for pgwatch2';								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		CREATE EXTENSION IF NOT EXISTS plpython3u; /* A wrapper around smartmontools to verify disk SMART health for all disk devices. 0 = SMART check PASSED. NB! This helper is always meant to be tested / adjusted to make sure all disk are detected etc. Most likely smartctl privileges must be escalated to give postgres access: sudo chmod u+s /usr/local/sbin/smartctl */ CREATE OR REPLACE FUNCTION get_smart_health_per_device(OUT device text, OUT retcode int) RETURNS SETOF record AS $$ import subprocess ret_list = [] #disk_detect_cmd='smartctl --scan \| cut -d " " -f3 \| grep mega' # for Lenovo ServerRAID M1210 disk_detect_cmd='lsblk -io KNAME,TYPE \| grep '' disk'' \| cut -d " " -f1 \| sort' p = subprocess.run(disk_detect_cmd, stdout=subprocess.PIPE, encoding='utf-8', shell=True) if p.returncode != 0: return ret_list disks = p.stdout.splitlines() for disk in disks: # health_cmd = 'smartctl -d $disk -a -q silent /dev/sda' % disk # for Lenovo ServerRAID M1210 members health_cmd = 'smartctl -a -q silent /dev/%s' % disk p = subprocess.run(health_cmd, stdout=subprocess.PIPE, encoding='utf-8', shell=True) ret_list.append((disk, p.returncode)) return ret_list $$ LANGUAGE plpython3u VOLATILE; GRANT EXECUTE ON FUNCTION get_smart_health_per_device() TO pgwatch2; COMMENT ON FUNCTION get_smart_health_per_device() is 'created for pgwatch2';								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		DO $OUTER$ DECLARE l_pgver double precision; l_sproc_text_pre92 text := $SQL$ CREATE OR REPLACE FUNCTION get_stat_activity() RETURNS SETOF pg_stat_activity AS $$ select * from pg_stat_activity where datname = current_database() and procpid != pg_backend_pid() $$ LANGUAGE sql VOLATILE SECURITY DEFINER; $SQL$; l_sproc_text_92_plus text := $SQL$ CREATE OR REPLACE FUNCTION get_stat_activity() RETURNS SETOF pg_stat_activity AS $$ select * from pg_stat_activity where datname = current_database() and pid != pg_backend_pid() $$ LANGUAGE sql VOLATILE SECURITY DEFINER; $SQL$; BEGIN SELECT ((regexp_matches( regexp_replace(current_setting('server_version'), '(beta\|devel).*', '', 'g'), E'\\d+\\.?\\d+?'))[1])::double precision INTO l_pgver; EXECUTE format(CASE WHEN l_pgver > 9.1 THEN l_sproc_text_92_plus ELSE l_sproc_text_pre92 END); END; $OUTER$; GRANT EXECUTE ON FUNCTION get_stat_activity() TO pgwatch2; COMMENT ON FUNCTION get_stat_activity() IS 'created for pgwatch2';									2021-06-16 17:37:50
		CREATE OR REPLACE FUNCTION get_stat_replication() RETURNS SETOF pg_stat_replication AS $$ select * from pg_stat_replication $$ LANGUAGE sql VOLATILE SECURITY DEFINER; GRANT EXECUTE ON FUNCTION get_stat_replication() TO pgwatch2; COMMENT ON FUNCTION get_stat_replication() IS 'created for pgwatch2';									2021-06-16 17:37:50
		BEGIN; CREATE EXTENSION IF NOT EXISTS pg_stat_statements; CREATE OR REPLACE FUNCTION get_stat_statements() RETURNS TABLE ( queryid int8, query text, calls int8, total_time float8, rows int8, shared_blks_hit int8, shared_blks_read int8, shared_blks_dirtied int8, shared_blks_written int8, local_blks_hit int8, local_blks_read int8, local_blks_dirtied int8, local_blks_written int8, temp_blks_read int8, temp_blks_written int8, blk_read_time float8, blk_write_time float8, userid int8, dbid int8 ) AS $$ select /* for versions <9.4 we need to spoof the queryid column to make data usable /linkable in Grafana */ (regexp_replace(md5(s.query), E'\\D', '', 'g'))::varchar(10)::int8 as queryid, s.query, s.calls, s.total_time, s.rows, s.shared_blks_hit, s.shared_blks_read, s.shared_blks_dirtied, s.shared_blks_written, s.local_blks_hit, s.local_blks_read, s.local_blks_dirtied, s.local_blks_written, s.temp_blks_read, s.temp_blks_written, s.blk_read_time, s.blk_write_time, s.userid::int8, s.dbid::int8 from pg_stat_statements s join pg_database d on d.oid = s.dbid and d.datname = current_database() $$ LANGUAGE sql VOLATILE SECURITY DEFINER; GRANT EXECUTE ON FUNCTION get_stat_statements() TO pgwatch2; COMMENT ON FUNCTION get_stat_statements() IS 'created for pgwatch2'; -- below routine fixes function search_path to only include "more secure" schemas with no "public" CREATE privileges DO $_$ DECLARE l_secure_schemas_from_search_path text; BEGIN SELECT string_agg(safe_sp, ', ' ORDER BY rank) INTO l_secure_schemas_from_search_path FROM ( SELECT quote_ident(nspname) AS safe_sp, rank FROM unnest(regexp_split_to_array(current_setting('search_path'), ',')) WITH ORDINALITY AS csp(schema_name, rank) JOIN pg_namespace n ON quote_ident(n.nspname) = CASE WHEN schema_name = '"$user"' THEN quote_ident(user) ELSE trim(schema_name) END WHERE NOT has_schema_privilege('public', n.oid, 'CREATE') ) x; IF coalesce(l_secure_schemas_from_search_path, '') = '' THEN RAISE NOTICE 'search_path = %', current_setting('search_path'); RAISE EXCEPTION $$get_stat_statements() SECURITY DEFINER helper will not be created as all schemas on search_path are unsecured where all users can create objects - execute 'REVOKE CREATE ON SCHEMA $my_schema FROM public' to tighten security or comment out the DO block to disable the check$$; ELSE RAISE NOTICE '%', format($$ALTER FUNCTION get_stat_statements() SET search_path TO %s$$, l_secure_schemas_from_search_path); EXECUTE format($$ALTER FUNCTION get_stat_statements() SET search_path TO %s$$, l_secure_schemas_from_search_path); END IF; END; $_$; COMMIT;									2021-06-16 17:37:50
		BEGIN; CREATE EXTENSION IF NOT EXISTS pg_stat_statements; CREATE OR REPLACE FUNCTION get_stat_statements() RETURNS SETOF pg_stat_statements AS $$ select s.* from pg_stat_statements s join pg_database d on d.oid = s.dbid and d.datname = current_database() $$ LANGUAGE sql VOLATILE SECURITY DEFINER; GRANT EXECUTE ON FUNCTION get_stat_statements() TO pgwatch2; COMMENT ON FUNCTION get_stat_statements() IS 'created for pgwatch2'; -- below routine fixes function search_path to only include "more secure" schemas with no "public" CREATE privileges DO $_$ DECLARE l_secure_schemas_from_search_path text; BEGIN SELECT string_agg(safe_sp, ', ' ORDER BY rank) INTO l_secure_schemas_from_search_path FROM ( SELECT quote_ident(nspname) AS safe_sp, rank FROM unnest(regexp_split_to_array(current_setting('search_path'), ',')) WITH ORDINALITY AS csp(schema_name, rank) JOIN pg_namespace n ON quote_ident(n.nspname) = CASE WHEN schema_name = '"$user"' THEN quote_ident(user) ELSE trim(schema_name) END WHERE NOT has_schema_privilege('public', n.oid, 'CREATE') ) x; IF coalesce(l_secure_schemas_from_search_path, '') = '' THEN RAISE NOTICE 'search_path = %', current_setting('search_path'); RAISE EXCEPTION $$get_stat_statements() SECURITY DEFINER helper will not be created as all schemas on search_path are unsecured where all users can create objects - execute 'REVOKE CREATE ON SCHEMA $my_schema FROM public' to tighten security or comment out the DO block to disable the check$$; ELSE RAISE NOTICE '%', format($$ALTER FUNCTION get_stat_statements() SET search_path TO %s$$, l_secure_schemas_from_search_path); EXECUTE format($$ALTER FUNCTION get_stat_statements() SET search_path TO %s$$, l_secure_schemas_from_search_path); END IF; END; $_$; COMMIT;									2021-06-16 17:37:50
		BEGIN; CREATE EXTENSION IF NOT EXISTS pgstattuple; CREATE OR REPLACE FUNCTION get_table_bloat_approx( OUT approx_free_percent double precision, OUT approx_free_space double precision, OUT dead_tuple_percent double precision, OUT dead_tuple_len double precision ) AS $$ select avg(approx_free_percent)::double precision as approx_free_percent, sum(approx_free_space)::double precision as approx_free_space, avg(dead_tuple_percent)::double precision as dead_tuple_percent, sum(dead_tuple_len)::double precision as dead_tuple_len from pg_class c join pg_namespace n on n.oid = c.relnamespace join lateral pgstattuple_approx(c.oid) on (c.oid not in (select relation from pg_locks where mode = 'AccessExclusiveLock')) -- skip locked tables where relkind in ('r', 'm') and c.relpages >= 128 -- tables >1mb and not n.nspname like any (array[E'pg\\_%', 'information_schema']) $$ LANGUAGE sql SECURITY DEFINER; GRANT EXECUTE ON FUNCTION get_table_bloat_approx() TO pgwatch2; COMMENT ON FUNCTION get_table_bloat_approx() is 'created for pgwatch2'; -- below routine fixes function search_path to only include "more secure" schemas with no "public" CREATE privileges DO $_$ DECLARE l_secure_schemas_from_search_path text; BEGIN SELECT string_agg(safe_sp, ', ' ORDER BY rank) INTO l_secure_schemas_from_search_path FROM ( SELECT quote_ident(nspname) AS safe_sp, rank FROM unnest(regexp_split_to_array(current_setting('search_path'), ',')) WITH ORDINALITY AS csp(schema_name, rank) JOIN pg_namespace n ON quote_ident(n.nspname) = CASE WHEN schema_name = '"$user"' THEN quote_ident(user) ELSE trim(schema_name) END WHERE NOT has_schema_privilege('public', n.oid, 'CREATE') ) x; IF coalesce(l_secure_schemas_from_search_path, '') = '' THEN RAISE NOTICE 'search_path = %', current_setting('search_path'); RAISE EXCEPTION $$get_table_bloat_approx() SECURITY DEFINER helper will not be created as all schemas on search_path are unsecured where all users can create objects - execute 'REVOKE CREATE ON SCHEMA $my_schema FROM public' to tighten security or comment out the DO block to disable the check$$; ELSE RAISE NOTICE '%', format($$ALTER FUNCTION get_table_bloat_approx() SET search_path TO %s$$, l_secure_schemas_from_search_path); EXECUTE format($$ALTER FUNCTION get_table_bloat_approx() SET search_path TO %s$$, l_secure_schemas_from_search_path); END IF; END; $_$; COMMIT;									2021-06-16 17:37:50
		-- small modifications to SQL from https://github.com/ioguix/pgsql-bloat-estimation -- NB! monitoring user needs SELECT grant on all tables or a SECURITY DEFINER wrapper around that SQL BEGIN; CREATE OR REPLACE FUNCTION get_table_bloat_approx_sql( OUT full_table_name text, OUT approx_bloat_percent double precision, OUT approx_bloat_bytes double precision, OUT fillfactor integer ) RETURNS SETOF RECORD LANGUAGE sql SECURITY DEFINER AS $$ SELECT quote_ident(schemaname)\|\|'.'\|\|quote_ident(tblname) as full_table_name, bloat_ratio, bloat_size, fillfactor FROM ( /* WARNING: executed with a non-superuser role, the query inspect only tables you are granted to read. * This query is compatible with PostgreSQL 9.0 and more / SELECT current_database(), schemaname, tblname, bs tblpages AS real_size, (tblpages - est_tblpages) * bs AS extra_size, CASE WHEN tblpages - est_tblpages > 0 THEN 100 * (tblpages - est_tblpages) / tblpages::float ELSE 0 END AS extra_ratio, fillfactor, CASE WHEN tblpages - est_tblpages_ff > 0 THEN (tblpages - est_tblpages_ff) * bs ELSE 0 END AS bloat_size, CASE WHEN tblpages - est_tblpages_ff > 0 THEN 100 * (tblpages - est_tblpages_ff) / tblpages::float ELSE 0 END AS bloat_ratio, is_na -- , (pst).free_percent + (pst).dead_tuple_percent AS real_frag FROM ( SELECT ceil(reltuples / ((bs - page_hdr) / tpl_size)) + ceil(toasttuples / 4) AS est_tblpages, ceil(reltuples / ((bs - page_hdr) * fillfactor / (tpl_size * 100))) + ceil(toasttuples / 4) AS est_tblpages_ff, tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na -- , stattuple.pgstattuple(tblid) AS pst FROM ( SELECT (4 + tpl_hdr_size + tpl_data_size + (2 * ma) - CASE WHEN tpl_hdr_size % ma = 0 THEN ma ELSE tpl_hdr_size % ma END - CASE WHEN ceil(tpl_data_size)::int % ma = 0 THEN ma ELSE ceil(tpl_data_size)::int % ma END ) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages, toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na FROM ( SELECT tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples, tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages, coalesce(toast.reltuples, 0) AS toasttuples, coalesce(substring( array_to_string(tbl.reloptions, ' ') FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor, current_setting('block_size')::numeric AS bs, CASE WHEN version() ~ 'mingw32' OR version() ~ '64-bit\|x86_64\|ppc64\|ia64\|amd64' THEN 8 ELSE 4 END AS ma, 24 AS page_hdr, 23 + CASE WHEN MAX(coalesce(null_frac, 0)) > 0 THEN (7 + count()) / 8 ELSE 0::int END + CASE WHEN tbl.relhasoids THEN 4 ELSE 0 END AS tpl_hdr_size, sum((1 - coalesce(s.null_frac, 0)) coalesce(s.avg_width, 1024)) AS tpl_data_size, bool_or(att.atttypid = 'pg_catalog.name'::regtype) OR count(att.attname) <> count(s.attname) AS is_na FROM pg_attribute AS att JOIN pg_class AS tbl ON att.attrelid = tbl.oid JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace LEFT JOIN pg_stats AS s ON s.schemaname = ns.nspname AND s.tablename = tbl.relname AND s.inherited = false AND s.attname = att.attname LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid WHERE att.attnum > 0 AND NOT att.attisdropped AND tbl.relkind IN ('r', 'm') AND ns.nspname != 'information_schema' GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, tbl.relhasoids ORDER BY 2, 3 ) AS s ) AS s2 ) AS s3 WHERE NOT is_na ) s4 $$; GRANT EXECUTE ON FUNCTION get_table_bloat_approx_sql() TO pgwatch2; COMMENT ON FUNCTION get_table_bloat_approx_sql() is 'created for pgwatch2'; -- below routine fixes function search_path to only include "more secure" schemas with no "public" CREATE privileges DO $_$ DECLARE l_secure_schemas_from_search_path text; BEGIN SELECT string_agg(safe_sp, ', ' ORDER BY rank) INTO l_secure_schemas_from_search_path FROM ( SELECT quote_ident(nspname) AS safe_sp, rank FROM unnest(regexp_split_to_array(current_setting('search_path'), ',')) WITH ORDINALITY AS csp(schema_name, rank) JOIN pg_namespace n ON quote_ident(n.nspname) = CASE WHEN schema_name = '"$user"' THEN quote_ident(user) ELSE trim(schema_name) END WHERE NOT has_schema_privilege('public', n.oid, 'CREATE') ) x; IF coalesce(l_secure_schemas_from_search_path, '') = '' THEN RAISE NOTICE 'search_path = %', current_setting('search_path'); RAISE EXCEPTION $$get_table_bloat_approx_sql() SECURITY DEFINER helper will not be created as all schemas on search_path are unsecured where all users can create objects - execute 'REVOKE CREATE ON SCHEMA $my_schema FROM public' to tighten security or comment out the DO block to disable the check$$; ELSE RAISE NOTICE '%', format($$ALTER FUNCTION get_table_bloat_approx_sql() SET search_path TO %s$$, l_secure_schemas_from_search_path); EXECUTE format($$ALTER FUNCTION get_table_bloat_approx_sql() SET search_path TO %s$$, l_secure_schemas_from_search_path); END IF; END; $_$; COMMIT;									2021-06-16 17:37:50
		-- small modifications to SQL from https://github.com/ioguix/pgsql-bloat-estimation -- NB! monitoring user needs SELECT grant on all tables or a SECURITY DEFINER wrapper around that SQL BEGIN; CREATE OR REPLACE FUNCTION get_table_bloat_approx_sql( OUT full_table_name text, OUT approx_bloat_percent double precision, OUT approx_bloat_bytes double precision, OUT fillfactor integer ) RETURNS SETOF RECORD LANGUAGE sql SECURITY DEFINER AS $$ SELECT quote_ident(schemaname)\|\|'.'\|\|quote_ident(tblname) as full_table_name, bloat_ratio, bloat_size, fillfactor FROM ( /* WARNING: executed with a non-superuser role, the query inspect only tables you are granted to read. * This query is compatible with PostgreSQL 9.0 and more / SELECT current_database(), schemaname, tblname, bs tblpages AS real_size, (tblpages - est_tblpages) * bs AS extra_size, CASE WHEN tblpages - est_tblpages > 0 THEN 100 * (tblpages - est_tblpages) / tblpages::float ELSE 0 END AS extra_ratio, fillfactor, CASE WHEN tblpages - est_tblpages_ff > 0 THEN (tblpages - est_tblpages_ff) * bs ELSE 0 END AS bloat_size, CASE WHEN tblpages - est_tblpages_ff > 0 THEN 100 * (tblpages - est_tblpages_ff) / tblpages::float ELSE 0 END AS bloat_ratio, is_na -- , (pst).free_percent + (pst).dead_tuple_percent AS real_frag FROM ( SELECT ceil(reltuples / ((bs - page_hdr) / tpl_size)) + ceil(toasttuples / 4) AS est_tblpages, ceil(reltuples / ((bs - page_hdr) * fillfactor / (tpl_size * 100))) + ceil(toasttuples / 4) AS est_tblpages_ff, tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na -- , stattuple.pgstattuple(tblid) AS pst FROM ( SELECT (4 + tpl_hdr_size + tpl_data_size + (2 * ma) - CASE WHEN tpl_hdr_size % ma = 0 THEN ma ELSE tpl_hdr_size % ma END - CASE WHEN ceil(tpl_data_size)::int % ma = 0 THEN ma ELSE ceil(tpl_data_size)::int % ma END ) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages, toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na FROM ( SELECT tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples, tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages, coalesce(toast.reltuples, 0) AS toasttuples, coalesce(substring( array_to_string(tbl.reloptions, ' ') FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor, current_setting('block_size')::numeric AS bs, CASE WHEN version() ~ 'mingw32' OR version() ~ '64-bit\|x86_64\|ppc64\|ia64\|amd64' THEN 8 ELSE 4 END AS ma, 24 AS page_hdr, 23 + CASE WHEN MAX(coalesce(null_frac, 0)) > 0 THEN (7 + count()) / 8 ELSE 0::int END + 0 AS tpl_hdr_size, sum((1 - coalesce(s.null_frac, 0)) coalesce(s.avg_width, 1024)) AS tpl_data_size, bool_or(att.atttypid = 'pg_catalog.name'::regtype) OR count(att.attname) <> count(s.attname) AS is_na FROM pg_attribute AS att JOIN pg_class AS tbl ON att.attrelid = tbl.oid JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace LEFT JOIN pg_stats AS s ON s.schemaname = ns.nspname AND s.tablename = tbl.relname AND s.inherited = false AND s.attname = att.attname LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid WHERE att.attnum > 0 AND NOT att.attisdropped AND tbl.relkind IN ('r', 'm') AND ns.nspname != 'information_schema' GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 ORDER BY 2, 3 ) AS s ) AS s2 ) AS s3 WHERE NOT is_na ) s4 $$; GRANT EXECUTE ON FUNCTION get_table_bloat_approx_sql() TO pgwatch2; COMMENT ON FUNCTION get_table_bloat_approx_sql() is 'created for pgwatch2'; -- below routine fixes function search_path to only include "more secure" schemas with no "public" CREATE privileges DO $_$ DECLARE l_secure_schemas_from_search_path text; BEGIN SELECT string_agg(safe_sp, ', ' ORDER BY rank) INTO l_secure_schemas_from_search_path FROM ( SELECT quote_ident(nspname) AS safe_sp, rank FROM unnest(regexp_split_to_array(current_setting('search_path'), ',')) WITH ORDINALITY AS csp(schema_name, rank) JOIN pg_namespace n ON quote_ident(n.nspname) = CASE WHEN schema_name = '"$user"' THEN quote_ident(user) ELSE trim(schema_name) END WHERE NOT has_schema_privilege('public', n.oid, 'CREATE') ) x; IF coalesce(l_secure_schemas_from_search_path, '') = '' THEN RAISE NOTICE 'search_path = %', current_setting('search_path'); RAISE EXCEPTION $$get_table_bloat_approx_sql() SECURITY DEFINER helper will not be created as all schemas on search_path are unsecured where all users can create objects - execute 'REVOKE CREATE ON SCHEMA $my_schema FROM public' to tighten security or comment out the DO block to disable the check$$; ELSE RAISE NOTICE '%', format($$ALTER FUNCTION get_table_bloat_approx_sql() SET search_path TO %s$$, l_secure_schemas_from_search_path); EXECUTE format($$ALTER FUNCTION get_table_bloat_approx_sql() SET search_path TO %s$$, l_secure_schemas_from_search_path); END IF; END; $_$; COMMIT;									2021-06-16 17:37:50
		/* vmstat + some extra infos like CPU count, 1m/5m/15m load avg. and total memory NB! Memory and disk info returned in bytes! / CREATE EXTENSION IF NOT EXISTS plpython3u; / NB! "plpython3u" might need changing to "plpythonu" (Python 2) everywhere for older OS-es / -- DROP FUNCTION get_vmstat(int); CREATE OR REPLACE FUNCTION get_vmstat( IN delay int default 1, OUT r int, OUT b int, OUT swpd int8, OUT free int8, OUT buff int8, OUT cache int8, OUT si int8, OUT so int8, OUT bi int8, OUT bo int8, OUT "in" int, OUT cs int, OUT us int, OUT sy int, OUT id int, OUT wa int, OUT st int, OUT cpu_count int, OUT load_1m float4, OUT load_5m float4, OUT load_15m float4, OUT total_memory int8 ) LANGUAGE plpython3u AS $FUNCTION$ from os import cpu_count, popen unit = 1024 # 'vmstat' default block byte size cpu_count = cpu_count() vmstat_lines = popen('vmstat {} 2'.format(delay)).readlines() vm = [int(x) for x in vmstat_lines[-1].split()] # plpy.notice(vm) load_1m, load_5m, load_15m = None, None, None with open('/proc/loadavg', 'r') as f: la_line = f.readline() if la_line: splits = la_line.split() if len(splits) == 5: load_1m, load_5m, load_15m = splits[0], splits[1], splits[2] total_memory = None with open('/proc/meminfo', 'r') as f: mi_line = f.readline() splits = mi_line.split() # plpy.notice(splits) if len(splits) == 3: total_memory = int(splits[1]) 1024 return vm[0], vm[1], vm[2] * unit, vm[3] * unit, vm[4] * unit, vm[5] * unit, vm[6] * unit, vm[7] * unit, vm[8] * unit, \ vm[9] * unit, vm[10], vm[11], vm[12], vm[13], vm[14], vm[15], vm[16], cpu_count, load_1m, load_5m, load_15m, total_memory $FUNCTION$; GRANT EXECUTE ON FUNCTION get_vmstat(int) TO pgwatch2; COMMENT ON FUNCTION get_vmstat(int) IS 'created for pgwatch2';									2021-06-16 17:37:50
		CREATE OR REPLACE FUNCTION get_wal_size() RETURNS int8 AS $$ select sum((pg_stat_file('pg_xlog/'\|\|f)).size)::int8 from (select pg_ls_dir('pg_xlog') f) ls $$ LANGUAGE sql VOLATILE SECURITY DEFINER; GRANT EXECUTE ON FUNCTION get_wal_size() TO pgwatch2; COMMENT ON FUNCTION get_wal_size() IS 'created for pgwatch2';									2021-06-16 17:37:50
		CREATE OR REPLACE FUNCTION get_wal_size() RETURNS int8 AS $$ select (sum((pg_stat_file('pg_wal/' \|\| name)).size))::int8 from pg_ls_waldir() $$ LANGUAGE sql VOLATILE SECURITY DEFINER; GRANT EXECUTE ON FUNCTION get_wal_size() TO pgwatch2; COMMENT ON FUNCTION get_wal_size() IS 'created for pgwatch2';									2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, quote_ident(nspname)\|\|'.'\|\|quote_ident(c.relname) as tag_index, quote_ident(nspname)\|\|'.'\|\|quote_ident(r.relname) as "table", i.indisvalid::text as is_valid, coalesce(md5(pg_get_indexdef(i.indexrelid)), random()::text) as md5 from pg_index i join pg_class c on c.oid = i.indexrelid join pg_class r on r.oid = i.indrelid join pg_namespace n on n.oid = c.relnamespace where c.relnamespace not in (select oid from pg_namespace where nspname like any(array[E'pg\\_%', 'information_schema']));									2021-06-16 17:37:50
		WITH q_locked_rels AS ( select relation from pg_locks where mode = 'AccessExclusiveLock' and granted ) SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, schemaname::text as tag_schema, indexrelname::text as tag_index_name, quote_ident(schemaname)\|\|'.'\|\|quote_ident(indexrelname) as tag_index_full_name, relname::text as tag_table_name, quote_ident(schemaname)\|\|'.'\|\|quote_ident(relname) as tag_table_full_name, coalesce(idx_scan, 0) as idx_scan, coalesce(idx_tup_read, 0) as idx_tup_read, coalesce(idx_tup_fetch, 0) as idx_tup_fetch, coalesce(pg_relation_size(indexrelid), 0) as index_size_b, quote_ident(schemaname)\|\|'.'\|\|quote_ident(sui.indexrelname) as index_full_name_val, md5(regexp_replace(regexp_replace(pg_get_indexdef(sui.indexrelid),indexrelname,'X'), '^CREATE UNIQUE','CREATE')) as tag_index_def_hash, regexp_replace(regexp_replace(pg_get_indexdef(sui.indexrelid),indexrelname,'X'), '^CREATE UNIQUE','CREATE') as index_def, case when not i.indisvalid then 1 else 0 end as is_invalid_int, case when i.indisprimary then 1 else 0 end as is_pk_int, case when i.indisunique then 1 else 0 end as is_uq_or_exc FROM pg_stat_user_indexes sui JOIN pg_index i USING (indexrelid) WHERE NOT schemaname like E'pg\\_temp%' AND i.indrelid not in (select relation from q_locked_rels) AND i.indexrelid not in (select relation from q_locked_rels) ORDER BY schemaname, relname, indexrelname;								{"prometheus_gauge_columns": ["index_size_b", "is_invalid_int", "is_pk_int"]}	2021-06-16 17:37:50
		WITH q_locked_rels AS ( select relation from pg_locks where mode = 'AccessExclusiveLock' and granted ) SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, schemaname::text as tag_schema, indexrelname::text as tag_index_name, quote_ident(schemaname)\|\|'.'\|\|quote_ident(indexrelname) as tag_index_full_name, relname::text as tag_table_name, quote_ident(schemaname)\|\|'.'\|\|quote_ident(relname) as tag_table_full_name, coalesce(idx_scan, 0) as idx_scan, coalesce(idx_tup_read, 0) as idx_tup_read, coalesce(idx_tup_fetch, 0) as idx_tup_fetch, coalesce(pg_relation_size(indexrelid), 0) as index_size_b, quote_ident(schemaname)\|\|'.'\|\|quote_ident(sui.indexrelname) as index_full_name_val, md5(regexp_replace(regexp_replace(pg_get_indexdef(sui.indexrelid),indexrelname,'X'), '^CREATE UNIQUE','CREATE')) as tag_index_def_hash, regexp_replace(regexp_replace(pg_get_indexdef(sui.indexrelid),indexrelname,'X'), '^CREATE UNIQUE','CREATE') as index_def, case when not i.indisvalid then 1 else 0 end as is_invalid_int, case when i.indisprimary then 1 else 0 end as is_pk_int, case when i.indisunique or indisexclusion then 1 else 0 end as is_uq_or_exc FROM pg_stat_user_indexes sui JOIN pg_index i USING (indexrelid) WHERE NOT schemaname like E'pg\\_temp%' AND i.indrelid not in (select relation from q_locked_rels) AND i.indexrelid not in (select relation from q_locked_rels) ORDER BY schemaname, relname, indexrelname;								{"prometheus_gauge_columns": ["index_size_b", "is_invalid_int", "is_pk_int"]}	2021-06-16 17:37:50
		WITH q_locked_rels AS ( select relation from pg_locks where mode = 'AccessExclusiveLock' and granted ) SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, schemaname::text as tag_schema, indexrelname::text as tag_index_name, quote_ident(schemaname)\|\|'.'\|\|quote_ident(indexrelname) as tag_index_full_name, relname::text as tag_table_name, quote_ident(schemaname)\|\|'.'\|\|quote_ident(relname) as tag_table_full_name, coalesce(idx_scan, 0) as idx_scan, coalesce(idx_tup_read, 0) as idx_tup_read, coalesce(idx_tup_fetch, 0) as idx_tup_fetch, coalesce(pg_relation_size(indexrelid), 0) as index_size_b, quote_ident(schemaname)\|\|'.'\|\|quote_ident(sui.indexrelname) as index_full_name_val, md5(regexp_replace(regexp_replace(pg_get_indexdef(sui.indexrelid),indexrelname,'X'), '^CREATE UNIQUE','CREATE')) as tag_index_def_hash, regexp_replace(regexp_replace(pg_get_indexdef(sui.indexrelid),indexrelname,'X'), '^CREATE UNIQUE','CREATE') as index_def, case when not i.indisvalid then 1 else 0 end as is_invalid_int, case when i.indisprimary then 1 else 0 end as is_pk_int, case when i.indisunique or indisexclusion then 1 else 0 end as is_uq_or_exc, system_identifier::text as tag_sys_id FROM pg_stat_user_indexes sui JOIN pg_index i USING (indexrelid) JOIN pg_control_system() ON true WHERE NOT schemaname like E'pg\\_temp%' AND i.indrelid not in (select relation from q_locked_rels) AND i.indexrelid not in (select relation from q_locked_rels) ORDER BY schemaname, relname, indexrelname;								{"prometheus_gauge_columns": ["index_size_b", "is_invalid_int", "is_pk_int"]}	2021-06-16 17:37:50
		WITH q_stat_tables AS ( SELECT * FROM pg_stat_user_tables t JOIN pg_class c ON c.oid = t.relid WHERE NOT schemaname LIKE E'pg\\_temp%' AND c.relpages > (1e7 / 8) -- >10MB ), q_stat_activity AS ( SELECT * FROM get_stat_activity() ) SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, numbackends - 1 as numbackends, (select count() from q_stat_activity where not current_query in ('<IDLE>', '<IDLE> in transaction')) AS active_backends, (select count() from q_stat_activity where waiting) AS blocked_backends, (select round(extract(epoch from now()) - extract(epoch from (select xact_start from q_stat_activity where datid = d.datid and not current_query like 'autovacuum:%' order by xact_start limit 1))))::int AS kpi_oldest_tx_s, xact_commit + xact_rollback AS tps, xact_commit, xact_rollback, blks_read, blks_hit, (select sum(seq_scan) from q_stat_tables)::int8 AS seq_scans_on_tbls_gt_10mb, tup_inserted, tup_updated, tup_deleted, (select sum(calls) from pg_stat_user_functions where not schemaname like any(array[E'pg\\_%', 'information_schema']))::int8 AS sproc_calls, extract(epoch from (now() - pg_postmaster_start_time()))::int8 as postmaster_uptime_s FROM pg_stat_database d WHERE datname = current_database();	WITH q_stat_tables AS ( SELECT * FROM pg_stat_user_tables t JOIN pg_class c ON c.oid = t.relid WHERE NOT schemaname LIKE E'pg\\_temp%' AND c.relpages > (1e7 / 8) -- >10MB ), q_stat_activity AS ( SELECT * FROM pg_stat_activity WHERE procpid != pg_backend_pid() AND datname = current_database() ) SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, numbackends - 1 as numbackends, (select count() from q_stat_activity where not current_query in ('<IDLE>', '<IDLE> in transaction')) AS active_backends, (select count() from q_stat_activity where waiting) AS blocked_backends, (select round(extract(epoch from now()) - extract(epoch from (select xact_start from q_stat_activity where datid = d.datid and not current_query like 'autovacuum:%' order by xact_start limit 1))))::int AS kpi_oldest_tx_s, xact_commit + xact_rollback AS tps, xact_commit, xact_rollback, blks_read, blks_hit, (select sum(seq_scan) from q_stat_tables)::int8 AS seq_scans_on_tbls_gt_10mb, tup_inserted, tup_updated, tup_deleted, (select sum(calls) from pg_stat_user_functions where not schemaname like any(array[E'pg\\_%', 'information_schema']))::int8 AS sproc_calls, extract(epoch from (now() - pg_postmaster_start_time()))::int8 as postmaster_uptime_s FROM pg_stat_database d WHERE datname = current_database();							{"prometheus_gauge_columns": ["numbackends", "active_backends", "blocked_backends", "kpi_oldest_tx_s"]}	2021-06-16 17:37:50
		WITH q_stat_tables AS ( SELECT * FROM pg_stat_user_tables t JOIN pg_class c ON c.oid = t.relid WHERE NOT schemaname LIKE E'pg\\_temp%' AND c.relpages > (1e7 / 8) -- >10MB ), q_stat_activity AS ( SELECT * FROM get_stat_activity() ) SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, case when pg_is_in_recovery() = false then pg_xlog_location_diff(pg_current_xlog_location(), '0/0')::int8 else pg_xlog_location_diff(pg_last_xlog_replay_location(), '0/0')::int8 end as wal_location_b, numbackends - 1 as numbackends, (select count(1) from q_stat_activity where state = 'active') AS active_backends, (select count(1) from q_stat_activity where waiting) AS blocked_backends, (select round(extract(epoch from now()) - extract(epoch from (select xact_start from q_stat_activity where datid = d.datid and not query like 'autovacuum:%' order by xact_start limit 1))))::int AS kpi_oldest_tx_s, xact_commit + xact_rollback AS tps, xact_commit, xact_rollback, blks_read, blks_hit, temp_bytes, (select sum(seq_scan) from q_stat_tables)::int8 AS seq_scans_on_tbls_gt_10mb, tup_inserted, tup_updated, tup_deleted, (select sum(calls) from pg_stat_user_functions where not schemaname like any(array[E'pg\\_%', 'information_schema']))::int8 AS sproc_calls, blk_read_time, blk_write_time, deadlocks, case when pg_is_in_recovery() then 1 else 0 end as in_recovery_int, extract(epoch from (now() - pg_postmaster_start_time()))::int8 as postmaster_uptime_s FROM pg_stat_database d WHERE datname = current_database();	WITH q_stat_tables AS ( SELECT * FROM pg_stat_user_tables t JOIN pg_class c ON c.oid = t.relid WHERE NOT schemaname LIKE E'pg\\_temp%' AND c.relpages > (1e7 / 8) -- >10MB ), q_stat_activity AS ( SELECT * FROM pg_stat_activity WHERE datname = current_database() AND pid != pg_backend_pid() ) SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, case when pg_is_in_recovery() = false then pg_xlog_location_diff(pg_current_xlog_location(), '0/0')::int8 else pg_xlog_location_diff(pg_last_xlog_replay_location(), '0/0')::int8 end as wal_location_b, numbackends - 1 as numbackends, (select count(1) from q_stat_activity where state = 'active') AS active_backends, (select count(1) from q_stat_activity where waiting) AS blocked_backends, (select round(extract(epoch from now()) - extract(epoch from (select xact_start from q_stat_activity where datid = d.datid and not query like 'autovacuum:%' order by xact_start limit 1))))::int AS kpi_oldest_tx_s, xact_commit + xact_rollback AS tps, xact_commit, xact_rollback, blks_read, blks_hit, temp_bytes, (select sum(seq_scan) from q_stat_tables)::int8 AS seq_scans_on_tbls_gt_10mb, tup_inserted, tup_updated, tup_deleted, (select sum(calls) from pg_stat_user_functions where not schemaname like any(array[E'pg\\_%', 'information_schema']))::int8 AS sproc_calls, blk_read_time, blk_write_time, deadlocks, case when pg_is_in_recovery() then 1 else 0 end as in_recovery_int, extract(epoch from (now() - pg_postmaster_start_time()))::int8 as postmaster_uptime_s FROM pg_stat_database d WHERE datname = current_database();							{"prometheus_gauge_columns": ["numbackends", "active_backends", "blocked_backends", "kpi_oldest_tx_s"]}	2021-06-16 17:37:50
		WITH q_stat_tables AS ( SELECT * FROM pg_stat_user_tables t JOIN pg_class c ON c.oid = t.relid WHERE NOT schemaname LIKE E'pg\\_temp%' AND c.relpages > (1e7 / 8) -- >10MB ), q_stat_activity AS ( SELECT * FROM get_stat_activity() ) SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, case when pg_is_in_recovery() = false then pg_xlog_location_diff(pg_current_xlog_location(), '0/0')::int8 else pg_xlog_location_diff(pg_last_xlog_replay_location(), '0/0')::int8 end as wal_location_b, numbackends - 1 as numbackends, (select count(1) from q_stat_activity where state = 'active') AS active_backends, (select count(1) from q_stat_activity where wait_event_type is not null) AS blocked_backends, (select round(extract(epoch from now()) - extract(epoch from (select xact_start from q_stat_activity where datid = d.datid and not query like 'autovacuum:%' order by xact_start limit 1))))::int AS kpi_oldest_tx_s, xact_commit + xact_rollback AS tps, xact_commit, xact_rollback, blks_read, blks_hit, temp_bytes, (select sum(seq_scan) from q_stat_tables)::int8 AS seq_scans_on_tbls_gt_10mb, tup_inserted, tup_updated, tup_deleted, (select sum(calls) from pg_stat_user_functions where not schemaname like any(array[E'pg\\_%', 'information_schema']))::int8 AS sproc_calls, blk_read_time, blk_write_time, deadlocks, case when pg_is_in_recovery() then 1 else 0 end as in_recovery_int, extract(epoch from (now() - pg_postmaster_start_time()))::int8 as postmaster_uptime_s FROM pg_stat_database d WHERE datname = current_database();	WITH q_stat_tables AS ( SELECT * FROM pg_stat_user_tables t JOIN pg_class c ON c.oid = t.relid WHERE NOT schemaname LIKE E'pg\\_temp%' AND c.relpages > (1e7 / 8) -- >10MB ), q_stat_activity AS ( SELECT * FROM pg_stat_activity WHERE datname = current_database() AND pid != pg_backend_pid() ) SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, case when pg_is_in_recovery() = false then pg_xlog_location_diff(pg_current_xlog_location(), '0/0')::int8 else pg_xlog_location_diff(pg_last_xlog_replay_location(), '0/0')::int8 end as xlog_location_b, numbackends - 1 as numbackends, (select count() from q_stat_activity where state in ('active', 'idle in transaction')) AS active_backends, (select count() from q_stat_activity where wait_event_type in ('LWLockNamed', 'Lock', 'BufferPin')) AS blocked_backends, (select round(extract(epoch from now()) - extract(epoch from (select xact_start from q_stat_activity where datid = d.datid and not query like 'autovacuum:%' order by xact_start limit 1))))::int AS kpi_oldest_tx_s, xact_commit + xact_rollback AS tps, xact_commit, xact_rollback, blks_read, blks_hit, temp_bytes, (select sum(seq_scan) from q_stat_tables)::int8 AS seq_scans_on_tbls_gt_10mb, tup_inserted, tup_updated, tup_deleted, (select sum(calls) from pg_stat_user_functions where not schemaname like any(array[E'pg\\_%', 'information_schema']))::int8 AS sproc_calls, blk_read_time, blk_write_time, deadlocks, case when pg_is_in_recovery() then 1 else 0 end as in_recovery_int, extract(epoch from (now() - pg_postmaster_start_time()))::int8 as postmaster_uptime_s FROM pg_stat_database d WHERE datname = current_database();							{"prometheus_gauge_columns": ["numbackends", "active_backends", "blocked_backends", "kpi_oldest_tx_s"]}	2021-06-16 17:37:50
		WITH q_stat_tables AS ( SELECT * FROM pg_stat_user_tables t JOIN pg_class c ON c.oid = t.relid WHERE NOT schemaname LIKE E'pg\\_temp%' AND c.relpages > (1e7 / 8) -- >10MB ), q_stat_activity AS ( SELECT * FROM get_stat_activity() ) SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, case when pg_is_in_recovery() = false then pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')::int8 else pg_wal_lsn_diff(pg_last_wal_replay_lsn(), '0/0')::int8 end as wal_location_b, numbackends - 1 as numbackends, (select count(1) from q_stat_activity where state = 'active') AS active_backends, (select count(1) from q_stat_activity where wait_event_type is not null) AS blocked_backends, (select round(extract(epoch from now()) - extract(epoch from (select xact_start from q_stat_activity where datid = d.datid and not query like 'autovacuum:%' order by xact_start limit 1))))::int AS kpi_oldest_tx_s, xact_commit + xact_rollback AS tps, xact_commit, xact_rollback, blks_read, blks_hit, temp_bytes, (select sum(seq_scan) from q_stat_tables)::int8 AS seq_scans_on_tbls_gt_10mb, tup_inserted, tup_updated, tup_deleted, (select sum(calls) from pg_stat_user_functions where not schemaname like any(array[E'pg\\_%', 'information_schema']))::int8 AS sproc_calls, blk_read_time, blk_write_time, deadlocks, case when pg_is_in_recovery() then 1 else 0 end as in_recovery_int, extract(epoch from (now() - pg_postmaster_start_time()))::int8 as postmaster_uptime_s FROM pg_stat_database d WHERE datname = current_database();	WITH q_stat_tables AS ( SELECT * FROM pg_stat_user_tables t JOIN pg_class c ON c.oid = t.relid WHERE NOT schemaname LIKE E'pg\\_temp%' AND c.relpages > (1e7 / 8) -- >10MB ), q_stat_activity AS ( SELECT * FROM pg_stat_activity WHERE datname = current_database() AND pid != pg_backend_pid() ) SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, case when pg_is_in_recovery() = false then pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')::int8 else pg_wal_lsn_diff(pg_last_wal_replay_lsn(), '0/0')::int8 end as wal_location_b, numbackends - 1 as numbackends, (select count() from q_stat_activity where state in ('active', 'idle in transaction')) AS active_backends, (select count() from q_stat_activity where wait_event_type in ('LWLock', 'Lock', 'BufferPin')) AS blocked_backends, (select round(extract(epoch from now()) - extract(epoch from (select xact_start from q_stat_activity where datid = d.datid and not query like 'autovacuum:%' order by xact_start limit 1))))::int AS kpi_oldest_tx_s, xact_commit + xact_rollback AS tps, xact_commit, xact_rollback, blks_read, blks_hit, temp_bytes, (select sum(seq_scan) from q_stat_tables)::int8 AS seq_scans_on_tbls_gt_10mb, tup_inserted, tup_updated, tup_deleted, (select sum(calls) from pg_stat_user_functions where not schemaname like any(array[E'pg\\_%', 'information_schema']))::int8 AS sproc_calls, blk_read_time, blk_write_time, deadlocks, case when pg_is_in_recovery() then 1 else 0 end as in_recovery_int, extract(epoch from (now() - pg_postmaster_start_time()))::int8 as postmaster_uptime_s FROM pg_stat_database d WHERE datname = current_database();							{"prometheus_gauge_columns": ["numbackends", "active_backends", "blocked_backends", "kpi_oldest_tx_s"]}	2021-06-16 17:37:50
		WITH q_locks AS ( select * from pg_locks where pid != pg_backend_pid() and database = (select oid from pg_database where datname = current_database()) ) SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, locktypes AS tag_locktype, coalesce((select count(*) FROM q_locks WHERE locktype = locktypes), 0) AS count FROM unnest('{relation, extend, page, tuple, transactionid, virtualxid, object, userlock, advisory}'::text[]) locktypes;								{"prometheus_gauge_columns": ["count"]}	2021-06-16 17:37:50
		WITH q_locks AS ( select * from pg_locks where pid != pg_backend_pid() and database = (select oid from pg_database where datname = current_database()) ) SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, lockmodes AS tag_lockmode, coalesce((select count(*) FROM q_locks WHERE mode = lockmodes), 0) AS count FROM unnest('{AccessShareLock, ExclusiveLock, RowShareLock, RowExclusiveLock, ShareLock, ShareRowExclusiveLock, AccessExclusiveLock, ShareUpdateExclusiveLock}'::text[]) lockmodes;								{"prometheus_gauge_columns": ["count"]}	2021-06-16 17:37:50
		with q_sr as ( select * from pg_subscription_rel ) select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, subname::text as tag_subname, subenabled, (select count() from q_sr where srsubid = oid) as relcount, (select count() from q_sr where srsubid = oid and srsubstate = 'i') as state_i, (select count() from q_sr where srsubid = oid and srsubstate = 'd') as state_d, (select count() from q_sr where srsubid = oid and srsubstate = 's') as state_s, (select count(*) from q_sr where srsubid = oid and srsubstate = 'r') as state_r from pg_subscription where subdbid = (select oid from pg_database where datname = current_database()) ;								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		show stats									2021-06-16 17:37:50
		/* SHOW POOL_NODES expected to be 1st "command" / SHOW POOL_NODES; / special handling in code - when below SHOW POOL_PROCESSES line is defined pgpool_stats will have additional summary columns: processes_total, processes_active */ SHOW POOL_PROCESSES;									2021-06-16 17:37:50
		SELECT (extract(epoch FROM now()) * 1e9)::int8 AS epoch_ns, * FROM ( SELECT 'table'::text AS object_type, grantee::text AS tag_role, quote_ident(table_schema) \|\| '.' \|\| quote_ident(table_name) AS tag_object, privilege_type FROM information_schema.table_privileges /* includes also VIEW-s actually / WHERE NOT grantee = ANY ( SELECT rolname FROM pg_roles WHERE rolsuper OR oid < 16384) AND NOT table_schema IN ('information_schema', 'pg_catalog') / union all select -- quite a heavy query currently, maybe faster directly via pg_attribute + has_column_privilege? 'column' AS object_type, grantee::text AS tag_role, quote_ident(table_schema) \|\| '.' \|\| quote_ident(table_name) AS tag_object, privilege_type FROM information_schema.column_privileges cp WHERE NOT table_schema IN ('pg_catalog', 'information_schema') AND NOT grantee = ANY ( SELECT rolname FROM pg_roles WHERE rolsuper OR oid < 16384) AND NOT EXISTS ( SELECT * FROM information_schema.table_privileges WHERE table_schema = cp.table_schema AND table_name = cp.table_name AND grantee = cp.grantee AND privilege_type = cp.privilege_type) */ UNION ALL SELECT 'function' AS object_type, grantee::text AS tag_role, quote_ident(routine_schema) \|\| '.' \|\| quote_ident(routine_name) AS tag_object, privilege_type FROM information_schema.routine_privileges WHERE NOT routine_schema IN ('information_schema', 'pg_catalog') AND NOT grantee = ANY ( SELECT rolname FROM pg_roles WHERE rolsuper OR oid < 16384) UNION ALL SELECT 'schema' AS object_type, r.rolname::text AS tag_role, quote_ident(n.nspname) AS tag_object, p.perm AS privilege_type FROM pg_catalog.pg_namespace AS n CROSS JOIN pg_catalog.pg_roles AS r CROSS JOIN ( VALUES ('USAGE'), ('CREATE')) AS p (perm) WHERE NOT n.nspname IN ('information_schema', 'pg_catalog') AND n.nspname NOT LIKE 'pg_%' AND NOT r.rolsuper AND r.oid >= 16384 AND has_schema_privilege(r.oid, n.oid, p.perm) UNION ALL SELECT 'database' AS object_type, r.rolname::text AS role_name, quote_ident(datname) AS tag_object, p.perm AS permission FROM pg_catalog.pg_database AS d CROSS JOIN pg_catalog.pg_roles AS r CROSS JOIN ( VALUES ('CREATE'), ('CONNECT'), ('TEMPORARY')) AS p (perm) WHERE d.datname = current_database() AND NOT r.rolsuper AND r.oid >= 16384 AND has_database_privilege(r.oid, d.oid, p.perm) UNION ALL SELECT 'superusers' AS object_type, rolname::text AS role_name, rolname::text AS tag_object, 'SUPERUSER' AS permission FROM pg_catalog.pg_roles WHERE rolsuper UNION ALL SELECT 'login_users' AS object_type, rolname::text AS role_name, rolname::text AS tag_object, 'LOGIN' AS permission FROM pg_catalog.pg_roles WHERE rolcanlogin) y;									2021-06-16 17:37:50
		SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, round(cpu_utilization::numeric, 2)::float as cpu_utilization, round(load_1m_norm::numeric, 2)::float as load_1m_norm, round(load_1m::numeric, 2)::float as load_1m, round(load_5m_norm::numeric, 2)::float as load_5m_norm, round(load_5m::numeric, 2)::float as load_5m, round("user"::numeric, 2)::float as "user", round(system::numeric, 2)::float as system, round(idle::numeric, 2)::float as idle, round(iowait::numeric, 2)::float as iowait, round(irqs::numeric, 2)::float as irqs, round(other::numeric, 2)::float as other from get_psutil_cpu();							{"is_instance_level": true}	{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, dir_or_tablespace as tag_dir_or_tablespace, path as tag_path, total, used, free, percent from get_psutil_disk();							{"is_instance_level": true}	{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, read_count, write_count, read_bytes, write_bytes from get_psutil_disk_io_total();							{"is_instance_level": true}	{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, total, used, free, buff_cache, available, percent, swap_total, swap_used, swap_free, swap_percent from get_psutil_mem();							{"is_instance_level": true}	{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		/* assumes the pg_qualstats extension and superuser or select grants on pg_qualstats_indexes_ddl view / select (extract(epoch from now()) 1e9)::int8 as epoch_ns, 'create_index'::text as tag_reco_topic, quote_ident(nspname::text)\|\|'.'\|\|quote_ident(relid::text) as tag_object_name, ddl as recommendation, ('qual execution count: '\|\| execution_count)::text as extra_info from pg_qualstats_indexes_ddl order by execution_count desc limit 25;							{"extension_version_based_overrides": [{"target_metric": "reco_add_index_ext_qualstats_2.0", "expected_extension_versions": [{"ext_name": "pg_qualstats", "ext_min_version": "2.0"}]}]}	{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		/* assumes the pg_qualstats extension and superuser or select grant on pg_qualstats_index_advisor() function / SELECT epoch_ns, tag_reco_topic, tag_object_name, recommendation, case when exists (select from pg_inherits where inhrelid = regclass(tag_object_name) ) then 'NB! Partitioned table, create the index on parent' else extra_info end as extra_info FROM ( SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, 'create_index'::text as tag_reco_topic, (regexp_matches(v::text, E'ON (.*?) '))[1] as tag_object_name, v::text as recommendation, '' as extra_info FROM json_array_elements( pg_qualstats_index_advisor() -> 'indexes') v ) x ORDER BY tag_object_name LIMIT 25;								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, 'default_public_schema_privs'::text as tag_reco_topic, nspname::text as tag_object_name, 'REVOKE CREATE ON SCHEMA public FROM PUBLIC;'::text as recommendation, 'only authorized users should be allowed to create new objects'::text as extra_info from pg_namespace where nspname = 'public' and nspacl::text ~ E'[,\\{]+=U?C/' ;								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		/* "temporarily" disabled triggers might be forgotten about... / select (extract(epoch from now()) 1e9)::int8 as epoch_ns, 'disabled_triggers'::text as tag_reco_topic, quote_ident(nspname)\|\|'.'\|\|quote_ident(relname) as tag_object_name, 'review usage of trigger and consider dropping it if not needed anymore'::text as recommendation, ''::text as extra_info from pg_trigger t join pg_class c on c.oid = t.tgrelid join pg_namespace n on n.oid = c.relnamespace where tgenabled = 'D' ;								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, 'drop_index'::text as tag_reco_topic, quote_ident(schemaname)\|\|'.'\|\|quote_ident(indexrelname) as tag_object_name, ('DROP INDEX ' \|\| quote_ident(schemaname)\|\|'.'\|\|quote_ident(indexrelname) \|\| ';')::text as recommendation, 'NB! Before dropping make sure to also check replica pg_stat_user_indexes.idx_scan count if using them for queries'::text as extra_info from pg_stat_user_indexes join pg_index using (indexrelid) where idx_scan = 0 and ((pg_relation_size(indexrelid)::numeric / (pg_database_size(current_database()))) > 0.005 /* 0.5% DB size threshold */ or indisvalid) and not indisprimary ;								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, 'drop_index'::text as tag_reco_topic, quote_ident(schemaname)\|\|'.'\|\|quote_ident(indexrelname) as tag_object_name, ('DROP INDEX ' \|\| quote_ident(schemaname)\|\|'.'\|\|quote_ident(indexrelname) \|\| ';')::text as recommendation, 'NB! Make sure to also check replica pg_stat_user_indexes.idx_scan count if using them for queries'::text as extra_info from pg_stat_user_indexes join pg_index using (indexrelid) where idx_scan = 0 and ((pg_relation_size(indexrelid)::numeric / (pg_database_size(current_database()))) > 0.005 /* 0.5% DB size threshold */ or indisvalid) and not indisprimary and not indisreplident ;								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		/* dummy placeholder - special handling in code to collect other metrics named reco_* */									2021-06-16 17:37:50
		WITH RECURSIVE views AS ( -- get the directly depending views SELECT v.oid::regclass AS view, format('%s.%s', quote_ident(n.nspname), quote_ident(v.relname)) as full_name, 1 AS level FROM pg_depend AS d JOIN pg_rewrite AS r ON r.oid = d.objid JOIN pg_class AS v ON v.oid = r.ev_class JOIN pg_namespace AS n ON n.oid = v.relnamespace WHERE v.relkind = 'v' AND NOT n.nspname = ANY(array['information_schema', E'pg\\_%']) AND NOT v.relname LIKE E'pg\\_%' AND d.classid = 'pg_rewrite'::regclass AND d.refclassid = 'pg_class'::regclass AND d.deptype = 'n' UNION ALL -- add the views that depend on these SELECT v.oid::regclass, format('%s.%s', quote_ident(n.nspname), quote_ident(v.relname)) as full_name, views.level + 1 FROM views JOIN pg_depend AS d ON d.refobjid = views.view JOIN pg_rewrite AS r ON r.oid = d.objid JOIN pg_class AS v ON v.oid = r.ev_class JOIN pg_namespace AS n ON n.oid = v.relnamespace WHERE v.relkind = 'v' AND NOT n.nspname = ANY(array['information_schema', E'pg\\_%']) AND d.classid = 'pg_rewrite'::regclass AND d.refclassid = 'pg_class'::regclass AND d.deptype = 'n' AND v.oid <> views.view -- avoid loop ) SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, 'overly_nested_views'::text AS tag_reco_topic, full_name::text as tag_object_name, 'overly nested views can affect performance'::text recommendation, 'nesting_depth: ' \|\| coalesce (max(level)::text, '-') AS extra_info FROM views GROUP BY 1, 2, 3 HAVING max(level) > 3 ORDER BY max(level) DESC, full_name::text;								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		select distinct (extract(epoch from now()) * 1e9)::int8 as epoch_ns, 'partial_index_candidates'::text as tag_reco_topic, quote_ident(ni.nspname)\|\|'.'\|\|quote_ident(ci.relname) as tag_object_name, ('index ' \|\| quote_ident(ni.nspname)\|\|'.'\|\|quote_ident(ci.relname) \|\| ' on ' \|\| quote_ident(s.schemaname) \|\| '.' \|\| quote_ident(s.tablename) \|\| ' column ' \|\| quote_ident(s.attname) \|\| ' could possibly be declared partial leaving out NULL-s')::text as recommendation, 'NULL fraction: ' \|\| round((null_frac * 100)::numeric, 1) \|\| '%, rowcount estimate: ' \|\| (c.reltuples)::int8 \|\| ', current definition: ' \|\| pg_get_indexdef(i.indexrelid) as extra_info from pg_stats s join pg_attribute a using (attname) join pg_index i on i.indkey[0] = a.attnum and i.indrelid = a.attrelid join pg_class c on c.oid = i.indrelid join pg_class ci on ci.oid = i.indexrelid join pg_namespace ni on ni.oid = ci.relnamespace where not indisprimary and not indisunique and indisready and indisvalid and i.indnatts = 1 /* simple 1 column indexes / and null_frac > 0.5 / 50% empty / and not pg_get_indexdef(i.indexrelid) like '% WHERE %' and c.reltuples >= 1e5 / ignore smaller tables / and not exists ( / leave out sub-partitions / select from pg_inherits where inhrelid = c.oid ) ;								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		with q_sprocs as ( select format('%s.%s', quote_ident(nspname), quote_ident(proname)) as sproc_name, 'alter function ' \|\| proname \|\| '(' \|\| pg_get_function_arguments(p.oid) \|\| ') set search_path = X;' as fix_sql from pg_proc p join pg_namespace n on n.oid = p.pronamespace where prosecdef and not 'search_path' = ANY(coalesce(proconfig, '{}'::text[])) and not pg_catalog.obj_description(p.oid, 'pg_proc') ~ 'pgwatch2' ) select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, 'sprocs_wo_search_path'::text as tag_reco_topic, sproc_name::text as tag_object_name, fix_sql::text as recommendation, 'functions without fixed search_path can be potentially abused by malicious users if used objects are not fully qualified'::text as extra_info from q_sprocs order by tag_object_name, extra_info;								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		/* reco_* metrics have special handling - all results are stored actually under one 'recommendations' metric and following text columns are expected: reco_topic, object_name, recommendation, extra_info. / with q_su as ( select count() from pg_roles where rolcanlogin and rolsuper ), q_total as ( select count() from pg_roles where rolcanlogin ) select (extract(epoch from now()) 1e9)::int8 as epoch_ns, 'superuser_count'::text as tag_reco_topic, '-'::text as tag_object_name, 'too many superusers detected - review recommended'::text as recommendation, format('%s active superusers, %s total active users', q_su.count, q_total.count) as extra_info from q_su, q_total where q_su.count >= 10 ;								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, application_name as tag_application_name, concat(coalesce(client_addr::text, client_hostname), '_', client_port::text) as tag_client_info, coalesce(pg_xlog_location_diff(pg_current_xlog_location(), write_location)::int8, 0) as write_lag_b, coalesce(pg_xlog_location_diff(pg_current_xlog_location(), flush_location)::int8, 0) as flush_lag_b, coalesce(pg_xlog_location_diff(pg_current_xlog_location(), replay_location)::int8, 0) as replay_lag_b, state, sync_state, case when sync_state in ('sync', 'quorum') then 1 else 0 end as is_sync_int from get_stat_replication();	SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, application_name as tag_application_name, concat(coalesce(client_addr::text, client_hostname), '_', client_port::text) as tag_client_info, coalesce(pg_xlog_location_diff(pg_current_xlog_location(), write_location)::int8, 0) as write_lag_b, coalesce(pg_xlog_location_diff(pg_current_xlog_location(), flush_location)::int8, 0) as flush_lag_b, coalesce(pg_xlog_location_diff(pg_current_xlog_location(), replay_location)::int8, 0) as replay_lag_b, state, sync_state, case when sync_state in ('sync', 'quorum') then 1 else 0 end as is_sync_int from pg_stat_replication						{"is_instance_level": true}	{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, application_name as tag_application_name, concat(coalesce(client_addr::text, client_hostname), '_', client_port::text) as tag_client_info, coalesce(pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn)::int8, 0) as write_lag_b, coalesce(pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)::int8, 0) as flush_lag_b, coalesce(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)::int8, 0) as replay_lag_b, state, sync_state, case when sync_state in ('sync', 'quorum') then 1 else 0 end as is_sync_int from /* NB! when the query fails, grant "pg_monitor" system role (exposing all stats) to the monitoring user or create specifically the "get_stat_replication" helper and use that instead of pg_stat_replication */ pg_stat_replication;							{"is_instance_level": true}	{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, slot_name::text as tag_slot_name, coalesce(plugin, 'physical')::text as tag_plugin, active, case when active then 0 else 1 end as non_active_int, pg_xlog_location_diff(pg_current_xlog_location(), restart_lsn)::int8 as restart_lsn_lag_b, greatest(age(xmin), age(catalog_xmin))::int8 as xmin_age_tx from pg_replication_slots;							{"is_instance_level": true}	{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, slot_name::text as tag_slot_name, coalesce(plugin, 'physical')::text as plugin, active, case when active then 0 else 1 end as non_active_int, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)::int8 as restart_lsn_lag_b, greatest(age(xmin), age(catalog_xmin))::int8 as xmin_age_tx from pg_replication_slots;							{"is_instance_level": true}	{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		/* dummy placeholder - special handling in code */								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		with qs as ( select name, setting from pg_settings ) select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, current_setting('server_version') as server_version, current_setting('server_version_num')::int8 as server_version_num, current_setting('block_size')::int as block_size, current_setting('max_connections')::int as max_connections, current_setting('hot_standby') as hot_standby, (select setting from qs where name = 'hot_standby_feedback') as hot_standby_feedback, current_setting('fsync') as fsync, current_setting('full_page_writes') as full_page_writes, current_setting('synchronous_commit') as synchronous_commit, (select setting from qs where name = 'wal_compression') as wal_compression, (select setting from qs where name = 'wal_log_hints') as wal_log_hints, (select setting from qs where name = 'synchronous_standby_names') as synchronous_standby_names, current_setting('shared_buffers') as shared_buffers, current_setting('work_mem') as work_mem, current_setting('maintenance_work_mem') as maintenance_work_mem, current_setting('effective_cache_size') as effective_cache_size, (select setting::int8 from qs where name = 'default_statistics_target') as default_statistics_target, (select setting::float8 from qs where name = 'random_page_cost') as random_page_cost, pg_size_pretty(((select setting::int8 from qs where name = 'min_wal_size') * 1024^2)::int8) as min_wal_size, pg_size_pretty(((select setting::int8 from qs where name = 'max_wal_size') * 1024^2)::int8) as max_wal_size, (select setting from qs where name = 'checkpoint_segments') as checkpoint_segments, current_setting('checkpoint_timeout') as checkpoint_timeout, current_setting('checkpoint_completion_target') as checkpoint_completion_target, (select setting::int8 from qs where name = 'max_worker_processes') as max_worker_processes, (select setting::int8 from qs where name = 'max_parallel_workers') as max_parallel_workers, (select setting::int8 from qs where name = 'max_parallel_workers_per_gather') as max_parallel_workers_per_gather, (select case when setting = 'on' then 1 else 0 end from qs where name = 'jit') as jit, (select case when setting = 'on' then 1 else 0 end from qs where name = 'ssl') as ssl, current_setting('statement_timeout') as statement_timeout, current_setting('deadlock_timeout') as deadlock_timeout, (select setting from qs where name = 'data_checksums') as data_checksums, (select setting::int8 from qs where name = 'max_connections') as max_connections, (select setting::int8 from qs where name = 'max_wal_senders') as max_wal_senders, (select setting::int8 from qs where name = 'max_replication_slots') as max_replication_slots, (select setting::int8 from qs where name = 'max_prepared_transactions') as max_prepared_transactions, (select setting::int8 from qs where name = 'lock_timeout') \|\| ' (ms)' as lock_timeout, (select setting from qs where name = 'archive_mode') as archive_mode, (select setting from qs where name = 'archive_command') as archive_command, current_setting('archive_timeout') as archive_timeout, (select setting from qs where name = 'shared_preload_libraries') as shared_preload_libraries, (select setting from qs where name = 'listen_addresses') as listen_addresses, (select setting from qs where name = 'ssl') as ssl, (select setting from qs where name = 'autovacuum') as autovacuum, (select setting::int8 from qs where name = 'autovacuum_max_workers') as autovacuum_max_workers, (select setting::float8 from qs where name = 'autovacuum_vacuum_scale_factor') as autovacuum_vacuum_scale_factor, (select setting::float8 from qs where name = 'autovacuum_vacuum_threshold') as autovacuum_vacuum_threshold, (select setting::float8 from qs where name = 'autovacuum_analyze_scale_factor') as autovacuum_analyze_scale_factor, (select setting::float8 from qs where name = 'autovacuum_analyze_threshold') as autovacuum_analyze_scale_factor ;								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		/* assumes pg_show_plans extension / select max((extract(epoch from now()) 1e9)::int8) as epoch_ns, max(extract(epoch from now() - query_start))::int as max_s, avg(extract(epoch from now() - query_start))::int as avg_s, count(), array_to_string(array_agg(distinct usename order by usename), ',') as "users", max(md5(plan)) as tag_hash, / needed for influx */ plan, max(query) as query from pg_show_plans p join pg_stat_activity a using (pid) where p.pid != pg_backend_pid() and datname = current_database() and now() - query_start > '1s'::interval group by plan order by max_s desc limit 10 ;								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		/* assumes pg_show_plans extension / select max((extract(epoch from now()) 1e9)::int8) as epoch_ns, max(extract(epoch from now() - query_start))::int as max_s, avg(extract(epoch from now() - query_start))::int as avg_s, count(), array_to_string(array_agg(distinct usename order by usename), ',') as "users", max(md5(plan)) as tag_hash, / needed for influx */ plan, max(query) as query from pg_show_plans p join pg_stat_activity a using (pid) where p.pid != pg_backend_pid() and datname = current_database() and now() - query_start > '1s'::interval and backend_type = 'client backend' group by plan order by max_s desc limit 10 ;								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, device as tag_device, retcode from get_smart_health_per_device();							{"is_instance_level": true}	{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, p.oid::text as tag_oid, quote_ident(nspname)\|\|'.'\|\|quote_ident(proname) as tag_sproc, md5(prosrc) from pg_proc p join pg_namespace n on n.oid = pronamespace where not nspname like any(array[E'pg\\_%', 'information_schema']);									2021-06-16 17:37:50
		SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, schemaname::text AS tag_schema, funcname::text AS tag_function_name, quote_ident(schemaname)\|\|'.'\|\|quote_ident(funcname) as tag_function_full_name, p.oid::text as tag_oid, -- for overloaded funcs calls as sp_calls, self_time, total_time FROM pg_stat_user_functions f JOIN pg_proc p ON p.oid = f.funcid;									2021-06-16 17:37:50
		SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, pid as tag_pid, usename::text AS user, application_name AS appname, coalesce(client_addr::text, 'local') AS ip, extract(epoch FROM (now() - query_start))::int AS duration_s, waiting::int, case when sa.waiting then (select array_to_string((select array_agg(distinct b.pid order by b.pid) from pg_locks b join pg_locks l on l.database = b.database and l.relation = b.relation where l.pid = sa.procpid and b.pid != l.pid and b.granted and not l.granted), ',')) else null end as blocking_pids, ltrim(regexp_replace(current_query, E'[ \\t\\n\\r]+' , ' ', 'g'))::varchar(300) AS query FROM pg_stat_activity sa WHERE current_query <> '<IDLE>' AND procpid != pg_backend_pid() AND datname = current_database() AND NOW() - query_start > '500ms'::interval ORDER BY NOW() - query_start DESC LIMIT 25;								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, pid as tag_pid, usename::text AS user, application_name AS appname, coalesce(client_addr::text, 'local') AS ip, extract(epoch FROM (now() - query_start))::int AS duration_s, waiting::int, case when sa.waiting then (select array_to_string((select array_agg(distinct b.pid order by b.pid) from pg_locks b join pg_locks l on l.database = b.database and l.relation = b.relation where l.pid = sa.pid and b.pid != l.pid and b.granted and not l.granted), ',')) else null end as blocking_pids, ltrim(regexp_replace(query, E'[ \\t\\n\\r]+' , ' ', 'g'))::varchar(300) AS query FROM pg_stat_activity sa WHERE state != 'idle' AND pid != pg_backend_pid() AND datname = current_database() AND now() - query_start > '500ms'::interval ORDER BY now() - query_start DESC LIMIT 25;								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, pid as tag_pid, usename::text AS user, application_name AS appname, coalesce(client_addr::text, 'local') AS ip, extract(epoch FROM (now() - query_start))::int AS duration_s, (wait_event_type IS NOT NULL)::int AS waiting, array_to_string(pg_blocking_pids(pid), ',') as blocking_pids, ltrim(regexp_replace(query, E'[ \\t\\n\\r]+' , ' ', 'g'))::varchar(300) AS query FROM pg_stat_activity WHERE state != 'idle' AND pid != pg_backend_pid() AND datname = current_database() AND now() - query_start > '500ms'::interval ORDER BY now() - query_start DESC LIMIT 25;								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, pid as tag_pid, usename::text AS user, application_name AS appname, coalesce(client_addr::text, 'local') AS ip, extract(epoch FROM (now() - query_start))::int AS duration_s, (coalesce(wait_event_type, '') IN ('LWLockNamed', 'Lock', 'BufferPin'))::int AS waiting, array_to_string(pg_blocking_pids(pid), ',') as blocking_pids, ltrim(regexp_replace(query, E'[ \\t\\n\\r]+' , ' ', 'g'))::varchar(300) AS query FROM pg_stat_activity WHERE state != 'idle' AND backend_type IN ('client backend', 'autovacuum worker') AND pid != pg_backend_pid() AND datname = current_database() AND now() - query_start > '500ms'::interval ORDER BY now() - query_start DESC LIMIT 25;								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, ssl, count(*) FROM pg_stat_ssl AS s, get_stat_activity() AS a WHERE a.pid = s.pid AND a.datname = current_database() GROUP BY 1, 2	SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, ssl, count(*) FROM pg_stat_ssl AS s, pg_stat_activity AS a WHERE a.pid = s.pid AND a.datname = current_database() GROUP BY 1, 2;								2021-06-16 17:37:50
		with q_data as ( select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, (regexp_replace(md5(query), E'\\D', '', 'g'))::varchar(10)::int8 as tag_queryid, max(ltrim(regexp_replace(query, E'[ \\t\\n\\r]+' , ' ', 'g')))::varchar(16000) as tag_query, array_to_string(array_agg(distinct quote_ident(pg_get_userbyid(userid))), ',') as users, sum(s.calls)::int8 as calls, round(sum(s.total_time)::numeric, 3)::double precision as total_time, sum(shared_blks_hit)::int8 as shared_blks_hit, sum(shared_blks_read)::int8 as shared_blks_read, sum(shared_blks_written)::int8 as shared_blks_written, sum(shared_blks_dirtied)::int8 as shared_blks_dirtied, sum(temp_blks_read)::int8 as temp_blks_read, sum(temp_blks_written)::int8 as temp_blks_written, round(sum(blk_read_time)::numeric, 3)::double precision as blk_read_time, round(sum(blk_write_time)::numeric, 3)::double precision as blk_write_time from get_stat_statements() s where calls > 5 and total_time > 0 and dbid = (select oid from pg_database where datname = current_database()) and not upper(s.query) like any (array['DEALLOCATE%', 'SET %', 'RESET %', 'BEGIN%', 'BEGIN;', 'COMMIT%', 'END%', 'ROLLBACK%', 'SHOW%']) group by tag_queryid ) select * from ( select * from q_data where total_time > 0 order by total_time desc limit 100 ) a union select * from ( select * from q_data order by calls desc limit 100 ) a union select * from ( select * from q_data where shared_blks_read > 0 order by shared_blks_read desc limit 100 ) a union select * from ( select * from q_data where shared_blks_written > 0 order by shared_blks_written desc limit 100 ) a union select * from ( select * from q_data where temp_blks_read > 0 order by temp_blks_read desc limit 100 ) a union select * from ( select * from q_data where temp_blks_written > 0 order by temp_blks_written desc limit 100 ) a;	with q_data as ( select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, (regexp_replace(md5(query), E'\\D', '', 'g'))::varchar(10)::int8 as tag_queryid, max(ltrim(regexp_replace(query, E'[ \\t\\n\\r]+' , ' ', 'g')))::varchar(16000) as tag_query, array_to_string(array_agg(distinct quote_ident(pg_get_userbyid(userid))), ',') as users, sum(s.calls)::int8 as calls, round(sum(s.total_time)::numeric, 3)::double precision as total_time, sum(shared_blks_hit)::int8 as shared_blks_hit, sum(shared_blks_read)::int8 as shared_blks_read, sum(shared_blks_written)::int8 as shared_blks_written, sum(shared_blks_dirtied)::int8 as shared_blks_dirtied, sum(temp_blks_read)::int8 as temp_blks_read, sum(temp_blks_written)::int8 as temp_blks_written, round(sum(blk_read_time)::numeric, 3)::double precision as blk_read_time, round(sum(blk_write_time)::numeric, 3)::double precision as blk_write_time from pg_stat_statements s where calls > 5 and total_time > 0 and dbid = (select oid from pg_database where datname = current_database()) and not upper(s.query) like any (array['DEALLOCATE%', 'SET %', 'RESET %', 'BEGIN%', 'BEGIN;', 'COMMIT%', 'END%', 'ROLLBACK%', 'SHOW%']) group by tag_queryid ) select * from ( select * from q_data where total_time > 0 order by total_time desc limit 100 ) a union select * from ( select * from q_data order by calls desc limit 100 ) a union select * from ( select * from q_data where shared_blks_read > 0 order by shared_blks_read desc limit 100 ) a union select * from ( select * from q_data where shared_blks_written > 0 order by shared_blks_written desc limit 100 ) a union select * from ( select * from q_data where temp_blks_read > 0 order by temp_blks_read desc limit 100 ) a union select * from ( select * from q_data where temp_blks_written > 0 order by temp_blks_written desc limit 100 ) a;								2021-06-16 17:37:50
		with q_data as ( select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, queryid::text as tag_queryid, max(ltrim(regexp_replace(query, E'[ \\t\\n\\r]+' , ' ', 'g')))::varchar(16000) as tag_query, array_to_string(array_agg(distinct quote_ident(pg_get_userbyid(userid))), ',') as users, sum(s.calls)::int8 as calls, round(sum(s.total_time)::numeric, 3)::double precision as total_time, sum(shared_blks_hit)::int8 as shared_blks_hit, sum(shared_blks_read)::int8 as shared_blks_read, sum(shared_blks_written)::int8 as shared_blks_written, sum(shared_blks_dirtied)::int8 as shared_blks_dirtied, sum(temp_blks_read)::int8 as temp_blks_read, sum(temp_blks_written)::int8 as temp_blks_written, round(sum(blk_read_time)::numeric, 3)::double precision as blk_read_time, round(sum(blk_write_time)::numeric, 3)::double precision as blk_write_time from get_stat_statements() s where calls > 5 and total_time > 0 and dbid = (select oid from pg_database where datname = current_database()) and not upper(s.query) like any (array['DEALLOCATE%', 'SET %', 'RESET %', 'BEGIN%', 'BEGIN;', 'COMMIT%', 'END%', 'ROLLBACK%', 'SHOW%']) group by queryid ) select * from ( select * from q_data where total_time > 0 order by total_time desc limit 100 ) a union select * from ( select * from q_data order by calls desc limit 100 ) a union select * from ( select * from q_data where shared_blks_read > 0 order by shared_blks_read desc limit 100 ) a union select * from ( select * from q_data where shared_blks_written > 0 order by shared_blks_written desc limit 100 ) a union select * from ( select * from q_data where temp_blks_read > 0 order by temp_blks_read desc limit 100 ) a union select * from ( select * from q_data where temp_blks_written > 0 order by temp_blks_written desc limit 100 ) a;	with q_data as ( select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, queryid::text as tag_queryid, max(ltrim(regexp_replace(query, E'[ \\t\\n\\r]+' , ' ', 'g')))::varchar(16000) as tag_query, array_to_string(array_agg(distinct quote_ident(pg_get_userbyid(userid))), ',') as users, sum(s.calls)::int8 as calls, round(sum(s.total_time)::numeric, 3)::double precision as total_time, sum(shared_blks_hit)::int8 as shared_blks_hit, sum(shared_blks_read)::int8 as shared_blks_read, sum(shared_blks_written)::int8 as shared_blks_written, sum(shared_blks_dirtied)::int8 as shared_blks_dirtied, sum(temp_blks_read)::int8 as temp_blks_read, sum(temp_blks_written)::int8 as temp_blks_written, round(sum(blk_read_time)::numeric, 3)::double precision as blk_read_time, round(sum(blk_write_time)::numeric, 3)::double precision as blk_write_time from pg_stat_statements s where calls > 5 and total_time > 0 and dbid = (select oid from pg_database where datname = current_database()) and not upper(s.query) like any (array['DEALLOCATE%', 'SET %', 'RESET %', 'BEGIN%', 'BEGIN;', 'COMMIT%', 'END%', 'ROLLBACK%', 'SHOW%']) group by queryid ) select * from ( select * from q_data where total_time > 0 order by total_time desc limit 100 ) a union select * from ( select * from q_data order by calls desc limit 100 ) a union select * from ( select * from q_data where shared_blks_read > 0 order by shared_blks_read desc limit 100 ) a union select * from ( select * from q_data where shared_blks_written > 0 order by shared_blks_written desc limit 100 ) a union select * from ( select * from q_data where temp_blks_read > 0 order by temp_blks_read desc limit 100 ) a union select * from ( select * from q_data where temp_blks_written > 0 order by temp_blks_written desc limit 100 ) a;								2021-06-16 17:37:50
		with q_data as ( select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, queryid::text as tag_queryid, max(ltrim(regexp_replace(query, E'[ \\t\\n\\r]+' , ' ', 'g')))::varchar(16000) as tag_query, array_to_string(array_agg(distinct quote_ident(pg_get_userbyid(userid))), ',') as users, sum(s.calls)::int8 as calls, round(sum(s.total_exec_time)::numeric, 3)::double precision as total_time, sum(shared_blks_hit)::int8 as shared_blks_hit, sum(shared_blks_read)::int8 as shared_blks_read, sum(shared_blks_written)::int8 as shared_blks_written, sum(shared_blks_dirtied)::int8 as shared_blks_dirtied, sum(temp_blks_read)::int8 as temp_blks_read, sum(temp_blks_written)::int8 as temp_blks_written, round(sum(blk_read_time)::numeric, 3)::double precision as blk_read_time, round(sum(blk_write_time)::numeric, 3)::double precision as blk_write_time, sum(wal_fpi)::int8 as wal_fpi, sum(wal_bytes)::int8 as wal_bytes, round(sum(s.total_plan_time)::numeric, 3)::double precision as total_plan_time from get_stat_statements() s where calls > 5 and total_exec_time > 0 and dbid = (select oid from pg_database where datname = current_database()) and not upper(s.query) like any (array['DEALLOCATE%', 'SET %', 'RESET %', 'BEGIN%', 'BEGIN;', 'COMMIT%', 'END%', 'ROLLBACK%', 'SHOW%']) group by queryid ) select * from ( select * from q_data where total_time > 0 order by total_time desc limit 100 ) a union select * from ( select * from q_data order by calls desc limit 100 ) a union select * from ( select * from q_data where shared_blks_read > 0 order by shared_blks_read desc limit 100 ) a union select * from ( select * from q_data where shared_blks_written > 0 order by shared_blks_written desc limit 100 ) a union select * from ( select * from q_data where temp_blks_read > 0 order by temp_blks_read desc limit 100 ) a union select * from ( select * from q_data where temp_blks_written > 0 order by temp_blks_written desc limit 100 ) a;	with q_data as ( select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, queryid::text as tag_queryid, max(ltrim(regexp_replace(query, E'[ \\t\\n\\r]+' , ' ', 'g')))::varchar(16000) as tag_query, array_to_string(array_agg(distinct quote_ident(pg_get_userbyid(userid))), ',') as users, sum(s.calls)::int8 as calls, round(sum(s.total_exec_time)::numeric, 3)::double precision as total_time, sum(shared_blks_hit)::int8 as shared_blks_hit, sum(shared_blks_read)::int8 as shared_blks_read, sum(shared_blks_written)::int8 as shared_blks_written, sum(shared_blks_dirtied)::int8 as shared_blks_dirtied, sum(temp_blks_read)::int8 as temp_blks_read, sum(temp_blks_written)::int8 as temp_blks_written, round(sum(blk_read_time)::numeric, 3)::double precision as blk_read_time, round(sum(blk_write_time)::numeric, 3)::double precision as blk_write_time, sum(wal_fpi)::int8 as wal_fpi, sum(wal_bytes)::int8 as wal_bytes, round(sum(s.total_plan_time)::numeric, 3)::double precision as total_plan_time from pg_stat_statements s where calls > 5 and total_exec_time > 0 and dbid = (select oid from pg_database where datname = current_database()) and not upper(s.query) like any (array['DEALLOCATE%', 'SET %', 'RESET %', 'BEGIN%', 'BEGIN;', 'COMMIT%', 'END%', 'ROLLBACK%', 'SHOW%']) group by queryid ) select * from ( select * from q_data where total_time > 0 order by total_time desc limit 100 ) a union select * from ( select * from q_data order by calls desc limit 100 ) a union select * from ( select * from q_data where shared_blks_read > 0 order by shared_blks_read desc limit 100 ) a union select * from ( select * from q_data where shared_blks_written > 0 order by shared_blks_written desc limit 100 ) a union select * from ( select * from q_data where temp_blks_read > 0 order by temp_blks_read desc limit 100 ) a union select * from ( select * from q_data where temp_blks_written > 0 order by temp_blks_written desc limit 100 ) a;								2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, coalesce(sum(calls), 0)::int8 as calls, coalesce(round(sum(total_time)::numeric, 3), 0)::float8 as total_time from pg_stat_statements where dbid = (select oid from pg_database where datname = current_database()) ;									2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, coalesce(sum(calls), 0)::int8 as calls, coalesce(round(sum(total_exec_time)::numeric, 3), 0)::float8 as total_time, round(sum(total_plan_time)::numeric, 3)::double precision as total_plan_time from pg_stat_statements where dbid = (select oid from pg_database where datname = current_database()) ;									2021-06-16 17:37:50
		with q_data as ( select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, (regexp_replace(md5(query), E'\\D', '', 'g'))::varchar(10)::int8 as tag_queryid, '-' as tag_query, array_to_string(array_agg(distinct quote_ident(pg_get_userbyid(userid))), ',') as users, sum(s.calls)::int8 as calls, round(sum(s.total_time)::numeric, 3)::double precision as total_time, sum(shared_blks_hit)::int8 as shared_blks_hit, sum(shared_blks_read)::int8 as shared_blks_read, sum(shared_blks_written)::int8 as shared_blks_written, sum(shared_blks_dirtied)::int8 as shared_blks_dirtied, sum(temp_blks_read)::int8 as temp_blks_read, sum(temp_blks_written)::int8 as temp_blks_written, round(sum(blk_read_time)::numeric, 3)::double precision as blk_read_time, round(sum(blk_write_time)::numeric, 3)::double precision as blk_write_time from get_stat_statements() s where calls > 5 and total_time > 0 and dbid = (select oid from pg_database where datname = current_database()) and not upper(s.query) like any (array['DEALLOCATE%', 'SET %', 'RESET %', 'BEGIN%', 'BEGIN;', 'COMMIT%', 'END%', 'ROLLBACK%', 'SHOW%']) group by tag_queryid ) select * from ( select * from q_data where total_time > 0 order by total_time desc limit 100 ) a union select * from ( select * from q_data order by calls desc limit 100 ) a union select * from ( select * from q_data where shared_blks_read > 0 order by shared_blks_read desc limit 100 ) a union select * from ( select * from q_data where shared_blks_written > 0 order by shared_blks_written desc limit 100 ) a union select * from ( select * from q_data where temp_blks_read > 0 order by temp_blks_read desc limit 100 ) a union select * from ( select * from q_data where temp_blks_written > 0 order by temp_blks_written desc limit 100 ) a;	with q_data as ( select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, (regexp_replace(md5(query), E'\\D', '', 'g'))::varchar(10)::int8 as tag_queryid, '-' as tag_query, array_to_string(array_agg(distinct quote_ident(pg_get_userbyid(userid))), ',') as users, sum(s.calls)::int8 as calls, round(sum(s.total_time)::numeric, 3)::double precision as total_time, sum(shared_blks_hit)::int8 as shared_blks_hit, sum(shared_blks_read)::int8 as shared_blks_read, sum(shared_blks_written)::int8 as shared_blks_written, sum(shared_blks_dirtied)::int8 as shared_blks_dirtied, sum(temp_blks_read)::int8 as temp_blks_read, sum(temp_blks_written)::int8 as temp_blks_written, round(sum(blk_read_time)::numeric, 3)::double precision as blk_read_time, round(sum(blk_write_time)::numeric, 3)::double precision as blk_write_time from pg_stat_statements s where calls > 5 and total_time > 0 and dbid = (select oid from pg_database where datname = current_database()) and not upper(s.query) like any (array['DEALLOCATE%', 'SET %', 'RESET %', 'BEGIN%', 'BEGIN;', 'COMMIT%', 'END%', 'ROLLBACK%', 'SHOW%']) group by tag_queryid ) select * from ( select * from q_data where total_time > 0 order by total_time desc limit 100 ) a union select * from ( select * from q_data order by calls desc limit 100 ) a union select * from ( select * from q_data where shared_blks_read > 0 order by shared_blks_read desc limit 100 ) a union select * from ( select * from q_data where shared_blks_written > 0 order by shared_blks_written desc limit 100 ) a union select * from ( select * from q_data where temp_blks_read > 0 order by temp_blks_read desc limit 100 ) a union select * from ( select * from q_data where temp_blks_written > 0 order by temp_blks_written desc limit 100 ) a;						{"metric_storage_name": "stat_statements"}		2021-06-16 17:37:50
		with q_data as ( select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, queryid::text as tag_queryid, '-' as tag_query, array_to_string(array_agg(distinct quote_ident(pg_get_userbyid(userid))), ',') as users, sum(s.calls)::int8 as calls, round(sum(s.total_time)::numeric, 3)::double precision as total_time, sum(shared_blks_hit)::int8 as shared_blks_hit, sum(shared_blks_read)::int8 as shared_blks_read, sum(shared_blks_written)::int8 as shared_blks_written, sum(shared_blks_dirtied)::int8 as shared_blks_dirtied, sum(temp_blks_read)::int8 as temp_blks_read, sum(temp_blks_written)::int8 as temp_blks_written, round(sum(blk_read_time)::numeric, 3)::double precision as blk_read_time, round(sum(blk_write_time)::numeric, 3)::double precision as blk_write_time from get_stat_statements() s where calls > 5 and total_time > 0 and dbid = (select oid from pg_database where datname = current_database()) and not upper(s.query) like any (array['DEALLOCATE%', 'SET %', 'RESET %', 'BEGIN%', 'BEGIN;', 'COMMIT%', 'END%', 'ROLLBACK%', 'SHOW%']) group by queryid ) select * from ( select * from q_data where total_time > 0 order by total_time desc limit 100 ) a union select * from ( select * from q_data order by calls desc limit 100 ) a union select * from ( select * from q_data where shared_blks_read > 0 order by shared_blks_read desc limit 100 ) a union select * from ( select * from q_data where shared_blks_written > 0 order by shared_blks_written desc limit 100 ) a union select * from ( select * from q_data where temp_blks_read > 0 order by temp_blks_read desc limit 100 ) a union select * from ( select * from q_data where temp_blks_written > 0 order by temp_blks_written desc limit 100 ) a;	with q_data as ( select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, queryid::text as tag_queryid, '-' as tag_query, array_to_string(array_agg(distinct quote_ident(pg_get_userbyid(userid))), ',') as users, sum(s.calls)::int8 as calls, round(sum(s.total_time)::numeric, 3)::double precision as total_time, sum(shared_blks_hit)::int8 as shared_blks_hit, sum(shared_blks_read)::int8 as shared_blks_read, sum(shared_blks_written)::int8 as shared_blks_written, sum(shared_blks_dirtied)::int8 as shared_blks_dirtied, sum(temp_blks_read)::int8 as temp_blks_read, sum(temp_blks_written)::int8 as temp_blks_written, round(sum(blk_read_time)::numeric, 3)::double precision as blk_read_time, round(sum(blk_write_time)::numeric, 3)::double precision as blk_write_time from pg_stat_statements s where calls > 5 and total_time > 0 and dbid = (select oid from pg_database where datname = current_database()) and not upper(s.query) like any (array['DEALLOCATE%', 'SET %', 'RESET %', 'BEGIN%', 'BEGIN;', 'COMMIT%', 'END%', 'ROLLBACK%', 'SHOW%']) group by queryid ) select * from ( select * from q_data where total_time > 0 order by total_time desc limit 100 ) a union select * from ( select * from q_data order by calls desc limit 100 ) a union select * from ( select * from q_data where shared_blks_read > 0 order by shared_blks_read desc limit 100 ) a union select * from ( select * from q_data where shared_blks_written > 0 order by shared_blks_written desc limit 100 ) a union select * from ( select * from q_data where temp_blks_read > 0 order by temp_blks_read desc limit 100 ) a union select * from ( select * from q_data where temp_blks_written > 0 order by temp_blks_written desc limit 100 ) a;						{"metric_storage_name": "stat_statements"}		2021-06-16 17:37:50
		with q_data as ( select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, queryid::text as tag_queryid, '-' as tag_query, array_to_string(array_agg(distinct quote_ident(pg_get_userbyid(userid))), ',') as users, sum(s.calls)::int8 as calls, round(sum(s.total_exec_time)::numeric, 3)::double precision as total_time, sum(shared_blks_hit)::int8 as shared_blks_hit, sum(shared_blks_read)::int8 as shared_blks_read, sum(shared_blks_written)::int8 as shared_blks_written, sum(shared_blks_dirtied)::int8 as shared_blks_dirtied, sum(temp_blks_read)::int8 as temp_blks_read, sum(temp_blks_written)::int8 as temp_blks_written, round(sum(blk_read_time)::numeric, 3)::double precision as blk_read_time, round(sum(blk_write_time)::numeric, 3)::double precision as blk_write_time, sum(wal_fpi)::int8 as wal_fpi, sum(wal_bytes)::int8 as wal_bytes, round(sum(s.total_plan_time)::numeric, 3)::double precision as total_plan_time from get_stat_statements() s where calls > 5 and total_exec_time > 0 and dbid = (select oid from pg_database where datname = current_database()) and not upper(s.query) like any (array['DEALLOCATE%', 'SET %', 'RESET %', 'BEGIN%', 'BEGIN;', 'COMMIT%', 'END%', 'ROLLBACK%', 'SHOW%']) group by queryid ) select * from ( select * from q_data where total_time > 0 order by total_time desc limit 100 ) a union select * from ( select * from q_data order by calls desc limit 100 ) a union select * from ( select * from q_data where shared_blks_read > 0 order by shared_blks_read desc limit 100 ) a union select * from ( select * from q_data where shared_blks_written > 0 order by shared_blks_written desc limit 100 ) a union select * from ( select * from q_data where temp_blks_read > 0 order by temp_blks_read desc limit 100 ) a union select * from ( select * from q_data where temp_blks_written > 0 order by temp_blks_written desc limit 100 ) a;	with q_data as ( select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, queryid::text as tag_queryid, '-' as tag_query, array_to_string(array_agg(distinct quote_ident(pg_get_userbyid(userid))), ',') as users, sum(s.calls)::int8 as calls, round(sum(s.total_exec_time)::numeric, 3)::double precision as total_time, sum(shared_blks_hit)::int8 as shared_blks_hit, sum(shared_blks_read)::int8 as shared_blks_read, sum(shared_blks_written)::int8 as shared_blks_written, sum(shared_blks_dirtied)::int8 as shared_blks_dirtied, sum(temp_blks_read)::int8 as temp_blks_read, sum(temp_blks_written)::int8 as temp_blks_written, round(sum(blk_read_time)::numeric, 3)::double precision as blk_read_time, round(sum(blk_write_time)::numeric, 3)::double precision as blk_write_time, sum(wal_fpi)::int8 as wal_fpi, sum(wal_bytes)::int8 as wal_bytes, round(sum(s.total_plan_time)::numeric, 3)::double precision as total_plan_time from pg_stat_statements s where calls > 5 and total_exec_time > 0 and dbid = (select oid from pg_database where datname = current_database()) and not upper(s.query) like any (array['DEALLOCATE%', 'SET %', 'RESET %', 'BEGIN%', 'BEGIN;', 'COMMIT%', 'END%', 'ROLLBACK%', 'SHOW%']) group by queryid ) select * from ( select * from q_data where total_time > 0 order by total_time desc limit 100 ) a union select * from ( select * from q_data order by calls desc limit 100 ) a union select * from ( select * from q_data where shared_blks_read > 0 order by shared_blks_read desc limit 100 ) a union select * from ( select * from q_data where shared_blks_written > 0 order by shared_blks_written desc limit 100 ) a union select * from ( select * from q_data where temp_blks_read > 0 order by temp_blks_read desc limit 100 ) a union select * from ( select * from q_data where temp_blks_written > 0 order by temp_blks_written desc limit 100 ) a;						{"metric_storage_name": "stat_statements"}		2021-06-16 17:37:50
		/* NB! accessing pgstattuple_approx directly requires superuser or pg_stat_scan_tables/pg_monitor builtin roles / select (extract(epoch from now()) 1e9)::int8 AS epoch_ns, quote_ident(n.nspname)\|\|'.'\|\|quote_ident(c.relname) as tag_full_table_name, approx_free_percent, approx_free_space as approx_free_space_b, approx_tuple_count, dead_tuple_percent, dead_tuple_len as dead_tuple_len_b from pg_class c join lateral pgstattuple_approx(c.oid) st on (c.oid not in (select relation from pg_locks where mode = 'AccessExclusiveLock')) -- skip locked tables, join pg_namespace n on n.oid = c.relnamespace where relkind in ('r', 'm') and c.relpages >= 128 -- tables > 1mb and not n.nspname like any (array[E'pg\\_%', 'information_schema']);								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, approx_free_percent, approx_free_space as approx_free_space_b, dead_tuple_percent, dead_tuple_len as dead_tuple_len_b from get_table_bloat_approx() where approx_free_space > 0	with table_bloat_approx as ( select avg(approx_free_percent)::double precision as approx_free_percent, sum(approx_free_space)::double precision as approx_free_space, avg(dead_tuple_percent)::double precision as dead_tuple_percent, sum(dead_tuple_len)::double precision as dead_tuple_len from pg_class c join pg_namespace n on n.oid = c.relnamespace join lateral pgstattuple_approx(c.oid) on (c.oid not in (select relation from pg_locks where mode = 'AccessExclusiveLock')) -- skip locked tables where relkind in ('r', 'm') and c.relpages >= 128 -- tables >1mb and not n.nspname like any (array[E'pg\\_%', 'information_schema']) ) select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, approx_free_percent, approx_free_space as approx_free_space_b, dead_tuple_percent, dead_tuple_len as dead_tuple_len_b from table_bloat_approx where approx_free_space > 0;							{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		/* NB! accessing pgstattuple_approx directly requires superuser or pg_stat_scan_tables/pg_monitor builtin roles or execute grant on pgstattuple_approx(regclass) / with table_bloat_approx as ( select avg(approx_free_percent)::double precision as approx_free_percent, sum(approx_free_space)::double precision as approx_free_space, avg(dead_tuple_percent)::double precision as dead_tuple_percent, sum(dead_tuple_len)::double precision as dead_tuple_len from pg_class c join pg_namespace n on n.oid = c.relnamespace join lateral pgstattuple_approx(c.oid) on (c.oid not in (select relation from pg_locks where mode = 'AccessExclusiveLock')) -- skip locked tables where relkind in ('r', 'm') and c.relpages >= 128 -- tables >1mb and not n.nspname != 'information_schema' ) select (extract(epoch from now()) 1e9)::int8 as epoch_ns, approx_free_percent, approx_free_space as approx_free_space_b, dead_tuple_percent, dead_tuple_len as dead_tuple_len_b from table_bloat_approx where approx_free_space > 0;								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		WITH q_bloat AS ( select * from get_table_bloat_approx_sql() ) SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, (select sum(approx_bloat_bytes) from q_bloat) as approx_table_bloat_b, ((select sum(approx_bloat_bytes) from q_bloat) * 100 / pg_database_size(current_database()))::int8 as approx_bloat_percentage ;	WITH q_bloat AS ( SELECT quote_ident(schemaname)\|\|'.'\|\|quote_ident(tblname) as full_table_name, bloat_ratio as approx_bloat_percent, bloat_size as approx_bloat_bytes, fillfactor FROM ( /* WARNING: executed with a non-superuser role, the query inspect only tables you are granted to read. * This query is compatible with PostgreSQL 9.0 and more / SELECT current_database(), schemaname, tblname, bs tblpages AS real_size, (tblpages - est_tblpages) * bs AS extra_size, CASE WHEN tblpages - est_tblpages > 0 THEN 100 * (tblpages - est_tblpages) / tblpages::float ELSE 0 END AS extra_ratio, fillfactor, CASE WHEN tblpages - est_tblpages_ff > 0 THEN (tblpages - est_tblpages_ff) * bs ELSE 0 END AS bloat_size, CASE WHEN tblpages - est_tblpages_ff > 0 THEN 100 * (tblpages - est_tblpages_ff) / tblpages::float ELSE 0 END AS bloat_ratio, is_na -- , (pst).free_percent + (pst).dead_tuple_percent AS real_frag FROM ( SELECT ceil(reltuples / ((bs - page_hdr) / tpl_size)) + ceil(toasttuples / 4) AS est_tblpages, ceil(reltuples / ((bs - page_hdr) * fillfactor / (tpl_size * 100))) + ceil(toasttuples / 4) AS est_tblpages_ff, tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na -- , stattuple.pgstattuple(tblid) AS pst FROM ( SELECT (4 + tpl_hdr_size + tpl_data_size + (2 * ma) - CASE WHEN tpl_hdr_size % ma = 0 THEN ma ELSE tpl_hdr_size % ma END - CASE WHEN ceil(tpl_data_size)::int % ma = 0 THEN ma ELSE ceil(tpl_data_size)::int % ma END ) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages, toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na FROM ( SELECT tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples, tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages, coalesce(toast.reltuples, 0) AS toasttuples, coalesce(substring( array_to_string(tbl.reloptions, ' ') FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor, current_setting('block_size')::numeric AS bs, CASE WHEN version() ~ 'mingw32' OR version() ~ '64-bit\|x86_64\|ppc64\|ia64\|amd64' THEN 8 ELSE 4 END AS ma, 24 AS page_hdr, 23 + CASE WHEN MAX(coalesce(null_frac, 0)) > 0 THEN (7 + count()) / 8 ELSE 0::int END + CASE WHEN tbl.relhasoids THEN 4 ELSE 0 END AS tpl_hdr_size, sum((1 - coalesce(s.null_frac, 0)) coalesce(s.avg_width, 1024)) AS tpl_data_size, bool_or(att.atttypid = 'pg_catalog.name'::regtype) OR count(att.attname) <> count(s.attname) AS is_na FROM pg_attribute AS att JOIN pg_class AS tbl ON att.attrelid = tbl.oid JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace LEFT JOIN pg_stats AS s ON s.schemaname = ns.nspname AND s.tablename = tbl.relname AND s.inherited = false AND s.attname = att.attname LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid WHERE att.attnum > 0 AND NOT att.attisdropped AND tbl.relkind IN ('r', 'm') AND ns.nspname != 'information_schema' GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, tbl.relhasoids ORDER BY 2, 3 ) AS s ) AS s2 ) AS s3 -- WHERE NOT is_na ) s4 ) SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, (select sum(approx_bloat_bytes) from q_bloat) as approx_table_bloat_b, ((select sum(approx_bloat_bytes) from q_bloat) * 100 / pg_database_size(current_database()))::int8 as approx_bloat_percentage;							{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		WITH q_bloat AS ( select * from get_table_bloat_approx_sql() ) SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, (select sum(approx_bloat_bytes) from q_bloat) as approx_table_bloat_b, ((select sum(approx_bloat_bytes) from q_bloat) * 100 / pg_database_size(current_database()))::int8 as approx_bloat_percentage ;	WITH q_bloat AS ( SELECT quote_ident(schemaname) \|\| '.' \|\| quote_ident(tblname) as full_table_name, bloat_ratio as approx_bloat_percent, bloat_size as approx_bloat_bytes, fillfactor FROM ( /* WARNING: executed with a non-superuser role, the query inspect only tables you are granted to read. * This query is compatible with PostgreSQL 9.0 and more / SELECT current_database(), schemaname, tblname, bs tblpages AS real_size, (tblpages - est_tblpages) * bs AS extra_size, CASE WHEN tblpages - est_tblpages > 0 THEN 100 * (tblpages - est_tblpages) / tblpages::float ELSE 0 END AS extra_ratio, fillfactor, CASE WHEN tblpages - est_tblpages_ff > 0 THEN (tblpages - est_tblpages_ff) * bs ELSE 0 END AS bloat_size, CASE WHEN tblpages - est_tblpages_ff > 0 THEN 100 * (tblpages - est_tblpages_ff) / tblpages::float ELSE 0 END AS bloat_ratio, is_na -- , (pst).free_percent + (pst).dead_tuple_percent AS real_frag FROM ( SELECT ceil(reltuples / ((bs - page_hdr) / tpl_size)) + ceil(toasttuples / 4) AS est_tblpages, ceil(reltuples / ((bs - page_hdr) * fillfactor / (tpl_size * 100))) + ceil(toasttuples / 4) AS est_tblpages_ff, tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na -- , stattuple.pgstattuple(tblid) AS pst FROM ( SELECT (4 + tpl_hdr_size + tpl_data_size + (2 * ma) - CASE WHEN tpl_hdr_size % ma = 0 THEN ma ELSE tpl_hdr_size % ma END - CASE WHEN ceil(tpl_data_size)::int % ma = 0 THEN ma ELSE ceil(tpl_data_size)::int % ma END ) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages, toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na FROM ( SELECT tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples, tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages, coalesce(toast.reltuples, 0) AS toasttuples, coalesce(substring( array_to_string(tbl.reloptions, ' ') FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor, current_setting('block_size')::numeric AS bs, CASE WHEN version() ~ 'mingw32' OR version() ~ '64-bit\|x86_64\|ppc64\|ia64\|amd64' THEN 8 ELSE 4 END AS ma, 24 AS page_hdr, 23 + CASE WHEN MAX(coalesce(null_frac, 0)) > 0 THEN (7 + count()) / 8 ELSE 0::int END + 0 AS tpl_hdr_size, sum((1 - coalesce(s.null_frac, 0)) coalesce(s.avg_width, 1024)) AS tpl_data_size, bool_or(att.atttypid = 'pg_catalog.name'::regtype) OR count(att.attname) <> count(s.attname) AS is_na FROM pg_attribute AS att JOIN pg_class AS tbl ON att.attrelid = tbl.oid JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace LEFT JOIN pg_stats AS s ON s.schemaname = ns.nspname AND s.tablename = tbl.relname AND s.inherited = false AND s.attname = att.attname LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid WHERE att.attnum > 0 AND NOT att.attisdropped AND tbl.relkind IN ('r', 'm') AND ns.nspname != 'information_schema' GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 ORDER BY 2, 3 ) AS s ) AS s2 ) AS s3 -- WHERE NOT is_na ) s4 ) SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, (select sum(approx_bloat_bytes) from q_bloat) as approx_table_bloat_b, ((select sum(approx_bloat_bytes) from q_bloat) * 100 / pg_database_size(current_database()))::int8 as approx_bloat_percentage;							{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, quote_ident(table_schema)\|\|'.'\|\|quote_ident(table_name) as tag_table, md5((array_agg((c.)::text order by ordinal_position))::text) from ( SELECT current_database()::information_schema.sql_identifier AS table_catalog, nc.nspname::information_schema.sql_identifier AS table_schema, c.relname::information_schema.sql_identifier AS table_name, a.attname::information_schema.sql_identifier AS column_name, a.attnum::information_schema.cardinal_number AS ordinal_position, pg_get_expr(ad.adbin, ad.adrelid)::information_schema.character_data AS column_default, CASE WHEN a.attnotnull OR t.typtype = 'd'::"char" AND t.typnotnull THEN 'NO'::text ELSE 'YES'::text END::information_schema.yes_or_no AS is_nullable, CASE WHEN t.typtype = 'd'::"char" THEN CASE WHEN bt.typelem <> 0::oid AND bt.typlen = (-1) THEN 'ARRAY'::text WHEN nbt.nspname = 'pg_catalog'::name THEN format_type(t.typbasetype, NULL::integer) ELSE 'USER-DEFINED'::text END ELSE CASE WHEN t.typelem <> 0::oid AND t.typlen = (-1) THEN 'ARRAY'::text WHEN nt.nspname = 'pg_catalog'::name THEN format_type(a.atttypid, NULL::integer) ELSE 'USER-DEFINED'::text END END::information_schema.character_data AS data_type, information_schema._pg_char_max_length(information_schema._pg_truetypid(a., t.), information_schema._pg_truetypmod(a., t.))::information_schema.cardinal_number AS character_maximum_length, information_schema._pg_char_octet_length(information_schema._pg_truetypid(a., t.), information_schema._pg_truetypmod(a., t.))::information_schema.cardinal_number AS character_octet_length, information_schema._pg_numeric_precision(information_schema._pg_truetypid(a., t.), information_schema._pg_truetypmod(a., t.))::information_schema.cardinal_number AS numeric_precision, information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a., t.), information_schema._pg_truetypmod(a., t.))::information_schema.cardinal_number AS numeric_precision_radix, information_schema._pg_numeric_scale(information_schema._pg_truetypid(a., t.), information_schema._pg_truetypmod(a., t.))::information_schema.cardinal_number AS numeric_scale, information_schema._pg_datetime_precision(information_schema._pg_truetypid(a., t.), information_schema._pg_truetypmod(a., t.*))::information_schema.cardinal_number AS datetime_precision, NULL::character varying::information_schema.character_data AS interval_type, NULL::character varying::information_schema.character_data AS interval_precision, NULL::character varying::information_schema.sql_identifier AS character_set_catalog, NULL::character varying::information_schema.sql_identifier AS character_set_schema, NULL::character varying::information_schema.sql_identifier AS character_set_name, NULL::character varying::information_schema.sql_identifier AS collation_catalog, NULL::character varying::information_schema.sql_identifier AS collation_schema, NULL::character varying::information_schema.sql_identifier AS collation_name, CASE WHEN t.typtype = 'd'::"char" THEN current_database() ELSE NULL::name END::information_schema.sql_identifier AS domain_catalog, CASE WHEN t.typtype = 'd'::"char" THEN nt.nspname ELSE NULL::name END::information_schema.sql_identifier AS domain_schema, CASE WHEN t.typtype = 'd'::"char" THEN t.typname ELSE NULL::name END::information_schema.sql_identifier AS domain_name, current_database()::information_schema.sql_identifier AS udt_catalog, COALESCE(nbt.nspname, nt.nspname)::information_schema.sql_identifier AS udt_schema, COALESCE(bt.typname, t.typname)::information_schema.sql_identifier AS udt_name, NULL::character varying::information_schema.sql_identifier AS scope_catalog, NULL::character varying::information_schema.sql_identifier AS scope_schema, NULL::character varying::information_schema.sql_identifier AS scope_name, NULL::integer::information_schema.cardinal_number AS maximum_cardinality, a.attnum::information_schema.sql_identifier AS dtd_identifier, 'NO'::character varying::information_schema.yes_or_no AS is_self_referencing, 'NO'::character varying::information_schema.yes_or_no AS is_identity, NULL::character varying::information_schema.character_data AS identity_generation, NULL::character varying::information_schema.character_data AS identity_start, NULL::character varying::information_schema.character_data AS identity_increment, NULL::character varying::information_schema.character_data AS identity_maximum, NULL::character varying::information_schema.character_data AS identity_minimum, NULL::character varying::information_schema.yes_or_no AS identity_cycle, 'NEVER'::character varying::information_schema.character_data AS is_generated, NULL::character varying::information_schema.character_data AS generation_expression, CASE WHEN c.relkind = 'r'::"char" OR c.relkind = 'v'::"char" AND (EXISTS ( SELECT 1 FROM pg_rewrite WHERE pg_rewrite.ev_class = c.oid AND pg_rewrite.ev_type = '2'::"char" AND pg_rewrite.is_instead)) AND (EXISTS ( SELECT 1 FROM pg_rewrite WHERE pg_rewrite.ev_class = c.oid AND pg_rewrite.ev_type = '4'::"char" AND pg_rewrite.is_instead)) THEN 'YES'::text ELSE 'NO'::text END::information_schema.yes_or_no AS is_updatable FROM pg_attribute a LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum, pg_class c, pg_namespace nc, pg_type t JOIN pg_namespace nt ON t.typnamespace = nt.oid LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid) ON t.typtype = 'd'::"char" AND t.typbasetype = bt.oid WHERE a.attrelid = c.oid AND a.atttypid = t.oid AND nc.oid = c.relnamespace AND NOT pg_is_other_temp_schema(nc.oid) AND a.attnum > 0 AND NOT a.attisdropped AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char"])) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)) ) c where not table_schema like any (array[E'pg\\_%', 'information_schema']) group by table_schema, table_name order by table_schema, table_name;									2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, quote_ident(table_schema)\|\|'.'\|\|quote_ident(table_name) as tag_table, md5((array_agg((c.)::text order by ordinal_position))::text) from ( SELECT current_database()::information_schema.sql_identifier AS table_catalog, nc.nspname::information_schema.sql_identifier AS table_schema, c.relname::information_schema.sql_identifier AS table_name, a.attname::information_schema.sql_identifier AS column_name, a.attnum::information_schema.cardinal_number AS ordinal_position, pg_get_expr(ad.adbin, ad.adrelid)::information_schema.character_data AS column_default, CASE WHEN a.attnotnull OR t.typtype = 'd'::"char" AND t.typnotnull THEN 'NO'::text ELSE 'YES'::text END::information_schema.yes_or_no AS is_nullable, CASE WHEN t.typtype = 'd'::"char" THEN CASE WHEN bt.typelem <> 0::oid AND bt.typlen = '-1'::integer THEN 'ARRAY'::text WHEN nbt.nspname = 'pg_catalog'::name THEN format_type(t.typbasetype, NULL::integer) ELSE 'USER-DEFINED'::text END ELSE CASE WHEN t.typelem <> 0::oid AND t.typlen = '-1'::integer THEN 'ARRAY'::text WHEN nt.nspname = 'pg_catalog'::name THEN format_type(a.atttypid, NULL::integer) ELSE 'USER-DEFINED'::text END END::information_schema.character_data AS data_type, information_schema._pg_char_max_length(information_schema._pg_truetypid(a., t.), information_schema._pg_truetypmod(a., t.))::information_schema.cardinal_number AS character_maximum_length, information_schema._pg_char_octet_length(information_schema._pg_truetypid(a., t.), information_schema._pg_truetypmod(a., t.))::information_schema.cardinal_number AS character_octet_length, information_schema._pg_numeric_precision(information_schema._pg_truetypid(a., t.), information_schema._pg_truetypmod(a., t.))::information_schema.cardinal_number AS numeric_precision, information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a., t.), information_schema._pg_truetypmod(a., t.))::information_schema.cardinal_number AS numeric_precision_radix, information_schema._pg_numeric_scale(information_schema._pg_truetypid(a., t.), information_schema._pg_truetypmod(a., t.))::information_schema.cardinal_number AS numeric_scale, information_schema._pg_datetime_precision(information_schema._pg_truetypid(a., t.), information_schema._pg_truetypmod(a., t.))::information_schema.cardinal_number AS datetime_precision, information_schema._pg_interval_type(information_schema._pg_truetypid(a., t.), information_schema._pg_truetypmod(a., t.*))::information_schema.character_data AS interval_type, NULL::integer::information_schema.cardinal_number AS interval_precision, NULL::character varying::information_schema.sql_identifier AS character_set_catalog, NULL::character varying::information_schema.sql_identifier AS character_set_schema, NULL::character varying::information_schema.sql_identifier AS character_set_name, CASE WHEN nco.nspname IS NOT NULL THEN current_database() ELSE NULL::name END::information_schema.sql_identifier AS collation_catalog, nco.nspname::information_schema.sql_identifier AS collation_schema, co.collname::information_schema.sql_identifier AS collation_name, CASE WHEN t.typtype = 'd'::"char" THEN current_database() ELSE NULL::name END::information_schema.sql_identifier AS domain_catalog, CASE WHEN t.typtype = 'd'::"char" THEN nt.nspname ELSE NULL::name END::information_schema.sql_identifier AS domain_schema, CASE WHEN t.typtype = 'd'::"char" THEN t.typname ELSE NULL::name END::information_schema.sql_identifier AS domain_name, current_database()::information_schema.sql_identifier AS udt_catalog, COALESCE(nbt.nspname, nt.nspname)::information_schema.sql_identifier AS udt_schema, COALESCE(bt.typname, t.typname)::information_schema.sql_identifier AS udt_name, NULL::character varying::information_schema.sql_identifier AS scope_catalog, NULL::character varying::information_schema.sql_identifier AS scope_schema, NULL::character varying::information_schema.sql_identifier AS scope_name, NULL::integer::information_schema.cardinal_number AS maximum_cardinality, a.attnum::information_schema.sql_identifier AS dtd_identifier, 'NO'::character varying::information_schema.yes_or_no AS is_self_referencing, 'NO'::character varying::information_schema.yes_or_no AS is_identity, NULL::character varying::information_schema.character_data AS identity_generation, NULL::character varying::information_schema.character_data AS identity_start, NULL::character varying::information_schema.character_data AS identity_increment, NULL::character varying::information_schema.character_data AS identity_maximum, NULL::character varying::information_schema.character_data AS identity_minimum, NULL::character varying::information_schema.yes_or_no AS identity_cycle, 'NEVER'::character varying::information_schema.character_data AS is_generated, NULL::character varying::information_schema.character_data AS generation_expression, CASE WHEN c.relkind = 'r'::"char" OR (c.relkind = ANY (ARRAY['v'::"char", 'f'::"char"])) AND pg_column_is_updatable(c.oid::regclass, a.attnum, false) THEN 'YES'::text ELSE 'NO'::text END::information_schema.yes_or_no AS is_updatable FROM pg_attribute a LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum JOIN (pg_class c JOIN pg_namespace nc ON c.relnamespace = nc.oid) ON a.attrelid = c.oid JOIN (pg_type t JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON a.atttypid = t.oid LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid) ON t.typtype = 'd'::"char" AND t.typbasetype = bt.oid LEFT JOIN (pg_collation co JOIN pg_namespace nco ON co.collnamespace = nco.oid) ON a.attcollation = co.oid AND (nco.nspname <> 'pg_catalog'::name OR co.collname <> 'default'::name) WHERE NOT pg_is_other_temp_schema(nc.oid) AND a.attnum > 0 AND NOT a.attisdropped AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char"])) ) c where not table_schema like any (array[E'pg\\_%', 'information_schema']) group by table_schema, table_name order by table_schema, table_name;									2021-06-16 17:37:50
		SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, schemaname::text as tag_schema, relname::text as tag_table_name, quote_ident(schemaname)\|\|'.'\|\|quote_ident(relname) as tag_table_full_name, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit, toast_blks_read, toast_blks_hit, tidx_blks_read, tidx_blks_hit FROM pg_statio_user_tables WHERE NOT schemaname LIKE E'pg\\_temp%' AND (heap_blks_read > 0 OR heap_blks_hit > 0 OR idx_blks_read > 0 OR idx_blks_hit > 0 OR tidx_blks_read > 0 OR tidx_blks_hit > 0);									2021-06-16 17:37:50
		with recursive q_root_part as ( select c.oid, c.relkind, n.nspname root_schema, c.relname root_relname from pg_class c join pg_namespace n on n.oid = c.relnamespace where relkind = 'p' and relpersistence != 't' and not exists(select * from pg_inherits where inhrelid = c.oid) ), q_parts (relid, relkind, level, root) as ( select oid, relkind, 1, oid from q_root_part union all select inhrelid, c.relkind, level + 1, q.root from pg_inherits i join q_parts q on inhparent = q.relid join pg_class c on c.oid = i.inhrelid ), q_tstats as ( SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, relid, schemaname::text as tag_schema, relname::text as tag_table_name, quote_ident(schemaname) \|\| '.' \|\| quote_ident(relname) as tag_table_full_name, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit, toast_blks_read, toast_blks_hit, tidx_blks_read, tidx_blks_hit FROM pg_statio_user_tables WHERE NOT schemaname LIKE E'pg\\_temp%' AND (heap_blks_read > 0 OR heap_blks_hit > 0 OR idx_blks_read > 0 OR idx_blks_hit > 0 OR tidx_blks_read > 0 OR tidx_blks_hit > 0) ) select epoch_ns, tag_schema, tag_table_name, tag_table_full_name, 0 as is_part_root, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit, toast_blks_read, toast_blks_hit, tidx_blks_read, tidx_blks_hit from q_tstats union all select * from ( select epoch_ns, quote_ident(qr.root_schema) as tag_schema, quote_ident(qr.root_relname) as tag_table_name, quote_ident(qr.root_schema) \|\| '.' \|\| quote_ident(qr.root_relname) as tag_table_full_name, 1 as is_part_root, sum(heap_blks_read)::int8, sum(heap_blks_hit)::int8, sum(idx_blks_read)::int8, sum(idx_blks_hit)::int8, sum(toast_blks_read)::int8, sum(toast_blks_hit)::int8, sum(tidx_blks_read)::int8, sum(tidx_blks_hit)::int8 from q_tstats ts join q_parts qp on qp.relid = ts.relid join q_root_part qr on qr.oid = qp.root where qp.relkind = 'r' group by 1, 2, 3, 4 ) x;									2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, quote_ident(schemaname) as tag_schema, quote_ident(ut.relname) as tag_table_name, quote_ident(schemaname)\|\|'.'\|\|quote_ident(ut.relname) as tag_table_full_name, pg_table_size(relid) as table_size_b, abs(greatest(ceil(log((pg_table_size(relid)+1) / 10^6)), 0))::text as tag_table_size_cardinality_mb, -- i.e. 0=<1MB, 1=<10MB, 2=<100MB,.. pg_total_relation_size(relid) as total_relation_size_b, case when reltoastrelid != 0 then pg_total_relation_size(reltoastrelid) else 0::int8 end as toast_size_b, (extract(epoch from now() - greatest(last_vacuum, last_autovacuum)))::int8 as seconds_since_last_vacuum, (extract(epoch from now() - greatest(last_analyze, last_autoanalyze)))::int8 as seconds_since_last_analyze, case when 'autovacuum_enabled=off' = ANY(c.reloptions) then 1 else 0 end as no_autovacuum, seq_scan, seq_tup_read, coalesce(idx_scan, 0) as idx_scan, coalesce(idx_tup_fetch, 0) as idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup, age(relfrozenxid) as tx_freeze_age from pg_stat_user_tables ut join pg_class c on c.oid = ut.relid where -- leaving out fully locked tables as pg_relation_size also wants a lock and would wait not exists (select 1 from pg_locks where relation = relid and mode = 'AccessExclusiveLock' and granted) and not relistemp; -- and temp tables								{"prometheus_gauge_columns": ["table_size_b", "total_relation_size_b", "toast_size_b", "seconds_since_last_vacuum", "seconds_since_last_analyze", "n_live_tup", "n_dead_tup"]}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, quote_ident(schemaname) as tag_schema, quote_ident(ut.relname) as tag_table_name, quote_ident(schemaname)\|\|'.'\|\|quote_ident(ut.relname) as tag_table_full_name, pg_table_size(relid) as table_size_b, abs(greatest(ceil(log((pg_table_size(relid)+1) / 10^6)), 0))::text as tag_table_size_cardinality_mb, -- i.e. 0=<1MB, 1=<10MB, 2=<100MB,.. pg_total_relation_size(relid) as total_relation_size_b, case when reltoastrelid != 0 then pg_total_relation_size(reltoastrelid) else 0::int8 end as toast_size_b, (extract(epoch from now() - greatest(last_vacuum, last_autovacuum)))::int8 as seconds_since_last_vacuum, (extract(epoch from now() - greatest(last_analyze, last_autoanalyze)))::int8 as seconds_since_last_analyze, case when 'autovacuum_enabled=off' = ANY(c.reloptions) then 1 else 0 end as no_autovacuum, seq_scan, seq_tup_read, coalesce(idx_scan, 0) as idx_scan, coalesce(idx_tup_fetch, 0) as idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup from pg_stat_user_tables ut join pg_class c on c.oid = ut.relid where -- leaving out fully locked tables as pg_relation_size also wants a lock and would wait not exists (select 1 from pg_locks where relation = relid and mode = 'AccessExclusiveLock' and granted) and not relistemp; -- and temp tables								{"prometheus_gauge_columns": ["table_size_b", "total_relation_size_b", "toast_size_b", "seconds_since_last_vacuum", "seconds_since_last_analyze", "n_live_tup", "n_dead_tup"]}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, quote_ident(schemaname) as tag_schema, quote_ident(ut.relname) as tag_table_name, quote_ident(schemaname)\|\|'.'\|\|quote_ident(ut.relname) as tag_table_full_name, pg_table_size(relid) as table_size_b, abs(greatest(ceil(log((pg_table_size(relid)+1) / 10^6)), 0))::text as tag_table_size_cardinality_mb, -- i.e. 0=<1MB, 1=<10MB, 2=<100MB,.. pg_total_relation_size(relid) as total_relation_size_b, case when reltoastrelid != 0 then pg_total_relation_size(reltoastrelid) else 0::int8 end as toast_size_b, (extract(epoch from now() - greatest(last_vacuum, last_autovacuum)))::int8 as seconds_since_last_vacuum, (extract(epoch from now() - greatest(last_analyze, last_autoanalyze)))::int8 as seconds_since_last_analyze, case when 'autovacuum_enabled=off' = ANY(c.reloptions) then 1 else 0 end as no_autovacuum, seq_scan, seq_tup_read, coalesce(idx_scan, 0) as idx_scan, coalesce(idx_tup_fetch, 0) as idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count, age(relfrozenxid) as tx_freeze_age from pg_stat_user_tables ut join pg_class c on c.oid = ut.relid where -- leaving out fully locked tables as pg_relation_size also wants a lock and would wait not exists (select 1 from pg_locks where relation = relid and mode = 'AccessExclusiveLock' and granted) and c.relpersistence != 't'; -- and temp tables								{"prometheus_gauge_columns": ["table_size_b", "total_relation_size_b", "toast_size_b", "seconds_since_last_vacuum", "seconds_since_last_analyze", "n_live_tup", "n_dead_tup"]}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, quote_ident(schemaname) as tag_schema, quote_ident(ut.relname) as tag_table_name, quote_ident(schemaname)\|\|'.'\|\|quote_ident(ut.relname) as tag_table_full_name, pg_table_size(relid) as table_size_b, abs(greatest(ceil(log((pg_table_size(relid)+1) / 10^6)), 0))::text as tag_table_size_cardinality_mb, -- i.e. 0=<1MB, 1=<10MB, 2=<100MB,.. pg_total_relation_size(relid) as total_relation_size_b, case when reltoastrelid != 0 then pg_total_relation_size(reltoastrelid) else 0::int8 end as toast_size_b, (extract(epoch from now() - greatest(last_vacuum, last_autovacuum)))::int8 as seconds_since_last_vacuum, (extract(epoch from now() - greatest(last_analyze, last_autoanalyze)))::int8 as seconds_since_last_analyze, case when 'autovacuum_enabled=off' = ANY(c.reloptions) then 1 else 0 end as no_autovacuum, seq_scan, seq_tup_read, coalesce(idx_scan, 0) as idx_scan, coalesce(idx_tup_fetch, 0) as idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count, age(relfrozenxid) as tx_freeze_age from pg_stat_user_tables ut join pg_class c on c.oid = ut.relid where -- leaving out fully locked tables as pg_relation_size also wants a lock and would wait not exists (select 1 from pg_locks where relation = relid and mode = 'AccessExclusiveLock' and granted) and c.relpersistence != 't'; -- and temp tables								{"prometheus_gauge_columns": ["table_size_b", "total_relation_size_b", "toast_size_b", "seconds_since_last_vacuum", "seconds_since_last_analyze", "n_live_tup", "n_dead_tup"]}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, quote_ident(schemaname) as tag_schema, quote_ident(ut.relname) as tag_table_name, quote_ident(schemaname)\|\|'.'\|\|quote_ident(ut.relname) as tag_table_full_name, pg_table_size(relid) as table_size_b, abs(greatest(ceil(log((pg_table_size(relid)+1) / 10^6)), 0))::text as tag_table_size_cardinality_mb, -- i.e. 0=<1MB, 1=<10MB, 2=<100MB,.. pg_total_relation_size(relid) as total_relation_size_b, case when reltoastrelid != 0 then pg_total_relation_size(reltoastrelid) else 0::int8 end as toast_size_b, (extract(epoch from now() - greatest(last_vacuum, last_autovacuum)))::int8 as seconds_since_last_vacuum, (extract(epoch from now() - greatest(last_analyze, last_autoanalyze)))::int8 as seconds_since_last_analyze, case when 'autovacuum_enabled=off' = ANY(c.reloptions) then 1 else 0 end as no_autovacuum, seq_scan, seq_tup_read, coalesce(idx_scan, 0) as idx_scan, coalesce(idx_tup_fetch, 0) as idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count, age(relfrozenxid) as tx_freeze_age from pg_stat_user_tables ut join pg_class c on c.oid = ut.relid where -- leaving out fully locked tables as pg_relation_size also wants a lock and would wait not exists (select 1 from pg_locks where relation = relid and mode = 'AccessExclusiveLock' and granted) and c.relpersistence != 't'; -- and temp tables								{"prometheus_gauge_columns": ["table_size_b", "total_relation_size_b", "toast_size_b", "seconds_since_last_vacuum", "seconds_since_last_analyze", "n_live_tup", "n_dead_tup"]}	2021-06-16 17:37:50
		with recursive q_root_part as ( select c.oid, c.relkind, n.nspname root_schema, c.relname root_relname from pg_class c join pg_namespace n on n.oid = c.relnamespace where relkind = 'p' and relpersistence != 't' and not exists(select * from pg_inherits where inhrelid = c.oid) ), q_parts (relid, relkind, level, root) as ( select oid, relkind, 1, oid from q_root_part union all select inhrelid, c.relkind, level + 1, q.root from pg_inherits i join q_parts q on inhparent = q.relid join pg_class c on c.oid = i.inhrelid ), q_tstats as ( select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, relid, -- not sent to final output quote_ident(schemaname) as tag_schema, quote_ident(ut.relname) as tag_table_name, quote_ident(schemaname) \|\| '.' \|\| quote_ident(ut.relname) as tag_table_full_name, pg_table_size(relid) as table_size_b, abs(greatest(ceil(log((pg_table_size(relid) + 1) / 10 ^ 6)), 0))::text as tag_table_size_cardinality_mb, -- i.e. 0=<1MB, 1=<10MB, 2=<100MB,.. pg_total_relation_size(relid) as total_relation_size_b, case when reltoastrelid != 0 then pg_total_relation_size(reltoastrelid) else 0::int8 end as toast_size_b, (extract(epoch from now() - greatest(last_vacuum, last_autovacuum)))::int8 as seconds_since_last_vacuum, (extract(epoch from now() - greatest(last_analyze, last_autoanalyze)))::int8 as seconds_since_last_analyze, case when 'autovacuum_enabled=off' = ANY (c.reloptions) then 1 else 0 end as no_autovacuum, seq_scan, seq_tup_read, coalesce(idx_scan, 0) as idx_scan, coalesce(idx_tup_fetch, 0) as idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count, age(relfrozenxid) as tx_freeze_age from pg_stat_user_tables ut join pg_class c on c.oid = ut.relid where -- leaving out fully locked tables as pg_relation_size also wants a lock and would wait not exists(select 1 from pg_locks where relation = relid and mode = 'AccessExclusiveLock' and granted) and c.relpersistence != 't' -- and temp tables ) select epoch_ns, tag_schema, tag_table_name, tag_table_full_name, 0 as is_part_root, table_size_b, tag_table_size_cardinality_mb, -- i.e. 0=<1MB, 1=<10MB, 2=<100MB,.. total_relation_size_b, toast_size_b, seconds_since_last_vacuum, seconds_since_last_analyze, no_autovacuum, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count, tx_freeze_age from q_tstats union all select * from ( select epoch_ns, quote_ident(qr.root_schema) as tag_schema, quote_ident(qr.root_relname) as tag_table_name, quote_ident(qr.root_schema) \|\| '.' \|\| quote_ident(qr.root_relname) as tag_table_full_name, 1 as is_part_root, sum(table_size_b)::int8 table_size_b, abs(greatest(ceil(log((sum(table_size_b) + 1) / 10 ^ 6)), 0))::text as tag_table_size_cardinality_mb, -- i.e. 0=<1MB, 1=<10MB, 2=<100MB,.. sum(total_relation_size_b)::int8 total_relation_size_b, sum(toast_size_b)::int8 toast_size_b, min(seconds_since_last_vacuum)::int8 seconds_since_last_vacuum, min(seconds_since_last_analyze)::int8 seconds_since_last_analyze, sum(no_autovacuum)::int8 no_autovacuum, sum(seq_scan)::int8 seq_scan, sum(seq_tup_read)::int8 seq_tup_read, sum(idx_scan)::int8 idx_scan, sum(idx_tup_fetch)::int8 idx_tup_fetch, sum(n_tup_ins)::int8 n_tup_ins, sum(n_tup_upd)::int8 n_tup_upd, sum(n_tup_del)::int8 n_tup_del, sum(n_tup_hot_upd)::int8 n_tup_hot_upd, sum(n_live_tup)::int8 n_live_tup, sum(n_dead_tup)::int8 n_dead_tup, sum(vacuum_count)::int8 vacuum_count, sum(autovacuum_count)::int8 autovacuum_count, sum(analyze_count)::int8 analyze_count, sum(autoanalyze_count)::int8 autoanalyze_count, max(tx_freeze_age)::int8 tx_freeze_age from q_tstats ts join q_parts qp on qp.relid = ts.relid join q_root_part qr on qr.oid = qp.root where qp.relkind = 'r' group by 1, 2, 3, 4 ) x --order by tag_table_full_name ;								{"prometheus_gauge_columns": ["table_size_b", "total_relation_size_b", "toast_size_b", "seconds_since_last_vacuum", "seconds_since_last_analyze", "n_live_tup", "n_dead_tup"]}	2021-06-16 17:37:50
		SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, r, b, swpd, free, buff, cache, si, so, bi, bo, "in", cs, us, sy, id, wa, st, cpu_count, load_1m, load_5m, load_15m, total_memory from get_vmstat();								{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, case when pg_is_in_recovery() = false then pg_xlog_location_diff(pg_current_xlog_location(), '0/0')::int8 else pg_xlog_location_diff(pg_last_xlog_replay_location(), '0/0')::int8 end as xlog_location_b, case when pg_is_in_recovery() then 1 else 0 end as in_recovery_int, extract(epoch from (now() - pg_postmaster_start_time()))::int8 as postmaster_uptime_s;							{"is_instance_level": true}	{"prometheus_gauge_columns": ["in_recovery_int", "postmaster_uptime_s"]}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, case when pg_is_in_recovery() = false then pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')::int8 else pg_wal_lsn_diff(pg_last_wal_replay_lsn(), '0/0')::int8 end as xlog_location_b, case when pg_is_in_recovery() then 1 else 0 end as in_recovery_int, extract(epoch from (now() - pg_postmaster_start_time()))::int8 as postmaster_uptime_s, system_identifier::text as tag_sys_id from pg_control_system();	select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, case when pg_is_in_recovery() = false then pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')::int8 else pg_wal_lsn_diff(pg_last_wal_replay_lsn(), '0/0')::int8 end as xlog_location_b, case when pg_is_in_recovery() then 1 else 0 end as in_recovery_int, extract(epoch from (now() - coalesce((pg_stat_file('postmaster.pid', true)).modification, pg_postmaster_start_time())))::int8 as postmaster_uptime_s, system_identifier::text as tag_sys_id from pg_control_system();						{"is_instance_level": true}	{"prometheus_gauge_columns": ["in_recovery_int", "postmaster_uptime_s"]}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, pg_xlog_location_diff(pg_last_xlog_receive_location(), pg_last_xlog_replay_location()) as replay_lag_b, extract(epoch from (now() - pg_last_xact_replay_timestamp()))::int8 as last_replay_s;							{"is_instance_level": true}	{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) as replay_lag_b, extract(epoch from (now() - pg_last_xact_replay_timestamp()))::int8 as last_replay_s;							{"is_instance_level": true}	{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, get_wal_size() as wal_size_b;	select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, sum((pg_stat_file('pg_xlog/'\|\|f)).size)::int8 as wal_size_b from (select pg_ls_dir('pg_xlog') f) ls;						{"is_instance_level": true}		2021-06-16 17:37:50
		select (extract(epoch from now()) * 1e9)::int8 as epoch_ns, get_wal_size() as wal_size_b;	/* NB! If using not a real superuser but a role with "pg_monitor" grant then below execute grant is needed: GRANT EXECUTE ON FUNCTION pg_stat_file(text) to pgwatch2; / select (extract(epoch from now()) 1e9)::int8 as epoch_ns, (sum((pg_stat_file('pg_wal/' \|\| name)).size))::int8 as wal_size_b from pg_ls_waldir();						{"is_instance_level": true}	{"prometheus_all_gauge_columns": true}	2021-06-16 17:37:50
		SELECT (extract(epoch from now()) * 1e9)::int8 as epoch_ns, ...							{"is_instance_level": false}	{"prometheus_all_gauge_columns": true}

NB! Metric fetching SQL-s should always have the "epoch_ns" column present (otherwise server time will be used). Additionally columns with "tag_" prefix will be turned into indexed tags in InfluxDB or stored in the indexed "$metric_name.tag_data" column for Postgres for fast access. Also "dbname" (DB unique name) column will always be added by the framework

Available metric attributes:

is_instance_level (default: false): Instance level metrics can be cached and shared between multiple DBs of a single instance to reduce query load on server. Default caching period is 30s, use the --instance-level-cache-max-seconds (PW2_INSTANCE_LEVEL_CACHE_MAX_SECONDS env.) gatherer param to change or set to 0 to disable. Caching will only be applied to 'continuous' DB types and only if metric interval is bigger than the --instance-level-cache-max-seconds value.
statement_timeout_seconds: Enables to override the default 'per monitored DB' statement timeouts on metric level
metric_storage_name: Enables dynamic "renaming" of metrics at storage level, i.e. declaring almost similar metrics with different names but the data will be stored under one metric. Currently used (for out-of-the box metrics) only for the 'stat_statements_no_query_text' metric, to not to store actual query texts from the "pg_stat_statements" extension for more security sensitive instances.
extension_version_based_overrides: Enables to "switch out" the query text from some other metric based on some specific extension version. See 'reco_add_index' for an example definition.
disabled_days: Enables to "pause" metric gathering on specified days. See metric_attrs.yaml for "wal" for an example.
disabled_times: Enables to "pause" metric gathering on specified time intervals. e.g. "09:00-17:00" for business hours. NB! disabled_days / disabled_times can also be defined both on metric and host (host_attrs) level.

Available column attributes:

prometheus_all_gauge_columns (default: false}: Only relevant for Prometheus output to correctly set the (suggested, not validated) metric type. GAUGE = value changing with every scrape, COUNTER = value is normally incremented (like most postgres statistics)
prometheus_gauge_columns (default: []): Only relevant for Prometheus output to correctly set the (suggested, not validated) metric type. List of GAUGE (value changing with every scrape) columns for the given metric. When not set all columns are considered to be of type COUNTER
prometheus_ignored_columns (default: []): Only relevant for Prometheus output. For cases where we don't want some columns to be exposed