| 1 | // SPDX-License-Identifier: GPL-3.0-or-later |
| 2 | |
| 3 | package postgres |
| 4 | |
| 5 | func queryServerVersion() string { |
| 6 | return "SHOW server_version_num;" |
| 7 | } |
| 8 | |
| 9 | func queryIsSuperUser() string { |
| 10 | return "SELECT current_setting('is_superuser') = 'on' AS is_superuser;" |
| 11 | } |
| 12 | |
| 13 | func queryCanExecutePgLsDir() string { |
| 14 | return "SELECT has_function_privilege(current_user, 'pg_catalog.pg_ls_dir(text)', 'EXECUTE');" |
| 15 | } |
| 16 | |
| 17 | func queryPGIsInRecovery() string { |
| 18 | return "SELECT pg_is_in_recovery();" |
| 19 | } |
| 20 | |
| 21 | func querySettingsMaxConnections() string { |
| 22 | return "SELECT current_setting('max_connections')::INT - current_setting('superuser_reserved_connections')::INT;" |
| 23 | } |
| 24 | |
| 25 | func querySettingsMaxLocksHeld() string { |
| 26 | return ` |
| 27 | SELECT current_setting('max_locks_per_transaction')::INT * |
| 28 | (current_setting('max_connections')::INT + current_setting('max_prepared_transactions')::INT); |
| 29 | ` |
| 30 | } |
| 31 | |
| 32 | // TODO: this is not correct and we should use pg_stat_activity. |
| 33 | // But we need to check what connections (backend_type) count towards 'max_connections'. |
| 34 | // I think python version query doesn't count it correctly. |
| 35 | // https://github.com/netdata/netdata/blob/1782e2d002bc5203128e5a5d2b801010e2822d2d/collectors/python.d.plugin/postgres/postgres.chart.py#L266 |
| 36 | func queryServerCurrentConnectionsUsed() string { |
| 37 | return "SELECT sum(numbackends) FROM pg_stat_database;" |
| 38 | } |
| 39 | |
| 40 | func queryServerConnectionsState() string { |
| 41 | return ` |
| 42 | SELECT state, |
| 43 | COUNT(*) |
| 44 | FROM pg_stat_activity |
| 45 | WHERE state IN |
| 46 | ( |
| 47 | 'active', |
| 48 | 'idle', |
| 49 | 'idle in transaction', |
| 50 | 'idle in transaction (aborted)', |
| 51 | 'fastpath function call', |
| 52 | 'disabled' |
| 53 | ) |
| 54 | GROUP BY state; |
| 55 | ` |
| 56 | } |
| 57 | |
| 58 | func queryCheckpoints(version int) string { |
| 59 | // definition by version: https://pgpedia.info/p/pg_stat_bgwriter.html |
| 60 | // docs: https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-BGWRITER-VIEW |
| 61 | // code: https://github.com/postgres/postgres/blob/366283961ac0ed6d89014444c6090f3fd02fce0a/src/backend/catalog/system_views.sql#L1104 |
| 62 | |
| 63 | if version < pgVersion17 { |
| 64 | |
| 65 | return ` |
| 66 | SELECT checkpoints_timed, |
| 67 | checkpoints_req, |
| 68 | checkpoint_write_time, |
| 69 | checkpoint_sync_time, |
| 70 | buffers_checkpoint * current_setting('block_size')::numeric AS buffers_checkpoint_bytes, |
| 71 | buffers_clean * current_setting('block_size')::numeric AS buffers_clean_bytes, |
| 72 | maxwritten_clean, |
| 73 | buffers_backend * current_setting('block_size')::numeric AS buffers_backend_bytes, |
| 74 | buffers_backend_fsync, |
| 75 | buffers_alloc * current_setting('block_size')::numeric AS buffers_alloc_bytes |
| 76 | FROM pg_stat_bgwriter; |
| 77 | ` |
| 78 | } |
| 79 | return ` |
| 80 | SELECT |
| 81 | chkpt.num_timed AS checkpoints_timed, |
| 82 | chkpt.num_requested AS checkpoints_req, |
| 83 | chkpt.write_time AS checkpoint_write_time, |
| 84 | chkpt.sync_time AS checkpoint_sync_time, |
| 85 | chkpt.buffers_written * current_setting('block_size')::numeric AS buffers_checkpoint_bytes, |
| 86 | bgwrtr.buffers_clean * current_setting('block_size')::numeric AS buffers_clean_bytes, |
| 87 | bgwrtr.maxwritten_clean, |
| 88 | bgwrtr.buffers_alloc * current_setting('block_size')::numeric AS buffers_alloc_bytes |
| 89 | FROM |
| 90 | pg_stat_bgwriter AS bgwrtr, |
| 91 | pg_stat_checkpointer AS chkpt; |
| 92 | ` |
| 93 | } |
| 94 | |
| 95 | func queryServerUptime() string { |
| 96 | return `SELECT EXTRACT(epoch FROM CURRENT_TIMESTAMP - pg_postmaster_start_time());` |
| 97 | } |
| 98 | |
| 99 | func queryTXIDWraparound() string { |
| 100 | // https://www.crunchydata.com/blog/managing-transaction-id-wraparound-in-postgresql |
| 101 | return ` |
| 102 | WITH max_age AS ( SELECT |
| 103 | 2000000000 as max_old_xid, |
| 104 | setting AS autovacuum_freeze_max_age |
| 105 | FROM |
| 106 | pg_catalog.pg_settings |
| 107 | WHERE |
| 108 | name = 'autovacuum_freeze_max_age'), per_database_stats AS ( SELECT |
| 109 | datname , |
| 110 | m.max_old_xid::int , |
| 111 | m.autovacuum_freeze_max_age::int , |
| 112 | age(d.datfrozenxid) AS oldest_current_xid |
| 113 | FROM |
| 114 | pg_catalog.pg_database d |
| 115 | JOIN |
| 116 | max_age m |
| 117 | ON (true) |
| 118 | WHERE |
| 119 | d.datallowconn) SELECT |
| 120 | max(oldest_current_xid) AS oldest_current_xid , |
| 121 | max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound , |
| 122 | max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_emergency_autovacuum |
| 123 | FROM |
| 124 | per_database_stats; |
| 125 | ` |
| 126 | } |
| 127 | |
| 128 | func queryWALWrites(version int) string { |
| 129 | if version < pgVersion10 { |
| 130 | return ` |
| 131 | SELECT |
| 132 | pg_xlog_location_diff( |
| 133 | CASE |
| 134 | pg_is_in_recovery() |
| 135 | WHEN |
| 136 | TRUE |
| 137 | THEN |
| 138 | pg_last_xlog_receive_location() |
| 139 | ELSE |
| 140 | pg_current_xlog_location() |
| 141 | END |
| 142 | , '0/0') AS wal_writes ; |
| 143 | ` |
| 144 | } |
| 145 | return ` |
| 146 | SELECT |
| 147 | pg_wal_lsn_diff( |
| 148 | CASE |
| 149 | pg_is_in_recovery() |
| 150 | WHEN |
| 151 | TRUE |
| 152 | THEN |
| 153 | pg_last_wal_receive_lsn() |
| 154 | ELSE |
| 155 | pg_current_wal_lsn() |
| 156 | END |
| 157 | , '0/0') AS wal_writes ; |
| 158 | ` |
| 159 | } |
| 160 | |
| 161 | func queryWALFiles(version int) string { |
| 162 | if version < pgVersion10 { |
| 163 | return ` |
| 164 | SELECT count(*) FILTER (WHERE type = 'recycled') AS wal_recycled_files, |
| 165 | count(*) FILTER (WHERE type = 'written') AS wal_written_files |
| 166 | FROM (SELECT wal.name, |
| 167 | pg_xlogfile_name( |
| 168 | CASE pg_is_in_recovery() |
| 169 | WHEN true THEN NULL |
| 170 | ELSE pg_current_xlog_location() |
| 171 | END), |
| 172 | CASE |
| 173 | WHEN wal.name > pg_xlogfile_name( |
| 174 | CASE pg_is_in_recovery() |
| 175 | WHEN true THEN NULL |
| 176 | ELSE pg_current_xlog_location() |
| 177 | END) THEN 'recycled' |
| 178 | ELSE 'written' |
| 179 | END AS type |
| 180 | FROM pg_catalog.pg_ls_dir('pg_xlog') AS wal(name) |
| 181 | WHERE name ~ '^[0-9A-F]{24}$' |
| 182 | ORDER BY (pg_stat_file('pg_xlog/' || name, true)).modification, |
| 183 | wal.name DESC) sub; |
| 184 | ` |
| 185 | } |
| 186 | return ` |
| 187 | SELECT count(*) FILTER (WHERE type = 'recycled') AS wal_recycled_files, |
| 188 | count(*) FILTER (WHERE type = 'written') AS wal_written_files |
| 189 | FROM (SELECT wal.name, |
| 190 | pg_walfile_name( |
| 191 | CASE pg_is_in_recovery() |
| 192 | WHEN true THEN NULL |
| 193 | ELSE pg_current_wal_lsn() |
| 194 | END), |
| 195 | CASE |
| 196 | WHEN wal.name > pg_walfile_name( |
| 197 | CASE pg_is_in_recovery() |
| 198 | WHEN true THEN NULL |
| 199 | ELSE pg_current_wal_lsn() |
| 200 | END) THEN 'recycled' |
| 201 | ELSE 'written' |
| 202 | END AS type |
| 203 | FROM pg_catalog.pg_ls_dir('pg_wal') AS wal(name) |
| 204 | WHERE name ~ '^[0-9A-F]{24}$' |
| 205 | ORDER BY (pg_stat_file('pg_wal/' || name, true)).modification, |
| 206 | wal.name DESC) sub; |
| 207 | ` |
| 208 | } |
| 209 | |
| 210 | func queryWALArchiveFiles(version int) string { |
| 211 | if version < pgVersion10 { |
| 212 | return ` |
| 213 | SELECT |
| 214 | CAST(COALESCE(SUM(CAST(archive_file ~ $r$\.ready$$r$ as INT)), |
| 215 | 0) AS INT) AS wal_archive_files_ready_count, |
| 216 | CAST(COALESCE(SUM(CAST(archive_file ~ $r$\.done$$r$ AS INT)), |
| 217 | 0) AS INT) AS wal_archive_files_done_count |
| 218 | FROM |
| 219 | pg_catalog.pg_ls_dir('pg_xlog/archive_status') AS archive_files (archive_file); |
| 220 | ` |
| 221 | } |
| 222 | return ` |
| 223 | SELECT |
| 224 | CAST(COALESCE(SUM(CAST(archive_file ~ $r$\.ready$$r$ as INT)), |
| 225 | 0) AS INT) AS wal_archive_files_ready_count, |
| 226 | CAST(COALESCE(SUM(CAST(archive_file ~ $r$\.done$$r$ AS INT)), |
| 227 | 0) AS INT) AS wal_archive_files_done_count |
| 228 | FROM |
| 229 | pg_catalog.pg_ls_dir('pg_wal/archive_status') AS archive_files (archive_file); |
| 230 | ` |
| 231 | } |
| 232 | |
| 233 | func queryCatalogRelations() string { |
| 234 | // kind of same as |
| 235 | // https://github.com/netdata/netdata/blob/750810e1798e09cc6210e83594eb9ed4905f8f12/collectors/python.d.plugin/postgres/postgres.chart.py#L336-L354 |
| 236 | // TODO: do we need that? It is optional and disabled by default in py version. |
| 237 | return ` |
| 238 | SELECT relkind, |
| 239 | COUNT(1), |
| 240 | SUM(relpages) * current_setting('block_size')::NUMERIC AS size |
| 241 | FROM pg_class |
| 242 | GROUP BY relkind; |
| 243 | ` |
| 244 | } |
| 245 | |
| 246 | func queryAutovacuumWorkers() string { |
| 247 | // https://github.com/postgres/postgres/blob/9e4f914b5eba3f49ab99bdecdc4f96fac099571f/src/backend/postmaster/autovacuum.c#L3168-L3183 |
| 248 | return ` |
| 249 | SELECT count(*) FILTER ( |
| 250 | WHERE |
| 251 | query LIKE 'autovacuum: ANALYZE%%' |
| 252 | AND query NOT LIKE '%%to prevent wraparound%%' |
| 253 | ) AS autovacuum_analyze, |
| 254 | count(*) FILTER ( |
| 255 | WHERE |
| 256 | query LIKE 'autovacuum: VACUUM ANALYZE%%' |
| 257 | AND query NOT LIKE '%%to prevent wraparound%%' |
| 258 | ) AS autovacuum_vacuum_analyze, |
| 259 | count(*) FILTER ( |
| 260 | WHERE |
| 261 | query LIKE 'autovacuum: VACUUM %.%%' |
| 262 | AND query NOT LIKE '%%to prevent wraparound%%' |
| 263 | ) AS autovacuum_vacuum, |
| 264 | count(*) FILTER ( |
| 265 | WHERE |
| 266 | query LIKE '%%to prevent wraparound%%' |
| 267 | ) AS autovacuum_vacuum_freeze, |
| 268 | count(*) FILTER ( |
| 269 | WHERE |
| 270 | query LIKE 'autovacuum: BRIN summarize%%' |
| 271 | ) AS autovacuum_brin_summarize |
| 272 | FROM pg_stat_activity |
| 273 | WHERE query NOT LIKE '%%pg_stat_activity%%'; |
| 274 | ` |
| 275 | } |
| 276 | |
| 277 | func queryXactQueryRunningTime() string { |
| 278 | return ` |
| 279 | SELECT datname, |
| 280 | state, |
| 281 | EXTRACT(epoch from now() - xact_start) as xact_running_time, |
| 282 | EXTRACT(epoch from now() - query_start) as query_running_time |
| 283 | FROM pg_stat_activity |
| 284 | WHERE datname IS NOT NULL |
| 285 | AND state IN |
| 286 | ( |
| 287 | 'active', |
| 288 | 'idle in transaction', |
| 289 | 'idle in transaction (aborted)' |
| 290 | ) |
| 291 | AND backend_type = 'client backend'; |
| 292 | ` |
| 293 | } |
| 294 | |
| 295 | func queryReplicationStandbyAppDelta(version int) string { |
| 296 | if version < pgVersion10 { |
| 297 | return ` |
| 298 | SELECT application_name, |
| 299 | pg_xlog_location_diff( |
| 300 | CASE pg_is_in_recovery() |
| 301 | WHEN true THEN pg_last_xlog_receive_location() |
| 302 | ELSE pg_current_xlog_location() |
| 303 | END, |
| 304 | sent_location) AS sent_delta, |
| 305 | pg_xlog_location_diff( |
| 306 | sent_location, write_location) AS write_delta, |
| 307 | pg_xlog_location_diff( |
| 308 | write_location, flush_location) AS flush_delta, |
| 309 | pg_xlog_location_diff( |
| 310 | flush_location, replay_location) AS replay_delta |
| 311 | FROM pg_stat_replication psr |
| 312 | WHERE application_name IS NOT NULL; |
| 313 | ` |
| 314 | } |
| 315 | return ` |
| 316 | SELECT application_name, |
| 317 | pg_wal_lsn_diff( |
| 318 | CASE pg_is_in_recovery() |
| 319 | WHEN true THEN pg_last_wal_receive_lsn() |
| 320 | ELSE pg_current_wal_lsn() |
| 321 | END, |
| 322 | sent_lsn) AS sent_delta, |
| 323 | pg_wal_lsn_diff( |
| 324 | sent_lsn, write_lsn) AS write_delta, |
| 325 | pg_wal_lsn_diff( |
| 326 | write_lsn, flush_lsn) AS flush_delta, |
| 327 | pg_wal_lsn_diff( |
| 328 | flush_lsn, replay_lsn) AS replay_delta |
| 329 | FROM pg_stat_replication |
| 330 | WHERE application_name IS NOT NULL; |
| 331 | ` |
| 332 | } |
| 333 | |
| 334 | func queryReplicationStandbyAppLag() string { |
| 335 | return ` |
| 336 | SELECT application_name, |
| 337 | COALESCE(EXTRACT(EPOCH FROM write_lag)::bigint, 0) AS write_lag, |
| 338 | COALESCE(EXTRACT(EPOCH FROM flush_lag)::bigint, 0) AS flush_lag, |
| 339 | COALESCE(EXTRACT(EPOCH FROM replay_lag)::bigint, 0) AS replay_lag |
| 340 | FROM pg_stat_replication psr |
| 341 | WHERE application_name IS NOT NULL; |
| 342 | ` |
| 343 | } |
| 344 | |
| 345 | func queryReplicationSlotFiles(version int) string { |
| 346 | if version < pgVersion11 { |
| 347 | return ` |
| 348 | WITH wal_size AS ( |
| 349 | SELECT |
| 350 | current_setting('wal_block_size')::INT * setting::INT AS val |
| 351 | FROM pg_settings |
| 352 | WHERE name = 'wal_segment_size' |
| 353 | ) |
| 354 | SELECT |
| 355 | slot_name, |
| 356 | slot_type, |
| 357 | replslot_wal_keep, |
| 358 | count(slot_file) AS replslot_files |
| 359 | FROM |
| 360 | (SELECT |
| 361 | slot.slot_name, |
| 362 | CASE |
| 363 | WHEN slot_file <> 'state' THEN 1 |
| 364 | END AS slot_file , |
| 365 | slot_type, |
| 366 | COALESCE ( |
| 367 | floor( |
| 368 | CASE WHEN pg_is_in_recovery() |
| 369 | THEN ( |
| 370 | pg_wal_lsn_diff(pg_last_wal_receive_lsn(), slot.restart_lsn) |
| 371 | -- this is needed to account for whole WAL retention and |
| 372 | -- not only size retention |
| 373 | + (pg_wal_lsn_diff(restart_lsn, '0/0') % s.val) |
| 374 | ) / s.val |
| 375 | ELSE ( |
| 376 | pg_wal_lsn_diff(pg_current_wal_lsn(), slot.restart_lsn) |
| 377 | -- this is needed to account for whole WAL retention and |
| 378 | -- not only size retention |
| 379 | + (pg_walfile_name_offset(restart_lsn)).file_offset |
| 380 | ) / s.val |
| 381 | END |
| 382 | ),0) AS replslot_wal_keep |
| 383 | FROM pg_replication_slots slot |
| 384 | LEFT JOIN ( |
| 385 | SELECT |
| 386 | slot2.slot_name, |
| 387 | pg_ls_dir('pg_replslot/' || slot2.slot_name) AS slot_file |
| 388 | FROM pg_replication_slots slot2 |
| 389 | ) files (slot_name, slot_file) |
| 390 | ON slot.slot_name = files.slot_name |
| 391 | CROSS JOIN wal_size s |
| 392 | ) AS d |
| 393 | GROUP BY |
| 394 | slot_name, |
| 395 | slot_type, |
| 396 | replslot_wal_keep; |
| 397 | ` |
| 398 | } |
| 399 | |
| 400 | return ` |
| 401 | WITH wal_size AS ( |
| 402 | SELECT |
| 403 | setting::int AS val |
| 404 | FROM pg_settings |
| 405 | WHERE name = 'wal_segment_size' |
| 406 | ) |
| 407 | SELECT |
| 408 | slot_name, |
| 409 | slot_type, |
| 410 | replslot_wal_keep, |
| 411 | count(slot_file) AS replslot_files |
| 412 | FROM |
| 413 | (SELECT |
| 414 | slot.slot_name, |
| 415 | CASE |
| 416 | WHEN slot_file <> 'state' THEN 1 |
| 417 | END AS slot_file , |
| 418 | slot_type, |
| 419 | COALESCE ( |
| 420 | floor( |
| 421 | CASE WHEN pg_is_in_recovery() |
| 422 | THEN ( |
| 423 | pg_wal_lsn_diff(pg_last_wal_receive_lsn(), slot.restart_lsn) |
| 424 | -- this is needed to account for whole WAL retention and |
| 425 | -- not only size retention |
| 426 | + (pg_wal_lsn_diff(restart_lsn, '0/0') % s.val) |
| 427 | ) / s.val |
| 428 | ELSE ( |
| 429 | pg_wal_lsn_diff(pg_current_wal_lsn(), slot.restart_lsn) |
| 430 | -- this is needed to account for whole WAL retention and |
| 431 | -- not only size retention |
| 432 | + (pg_walfile_name_offset(restart_lsn)).file_offset |
| 433 | ) / s.val |
| 434 | END |
| 435 | ),0) AS replslot_wal_keep |
| 436 | FROM pg_replication_slots slot |
| 437 | LEFT JOIN ( |
| 438 | SELECT |
| 439 | slot2.slot_name, |
| 440 | pg_ls_dir('pg_replslot/' || slot2.slot_name) AS slot_file |
| 441 | FROM pg_replication_slots slot2 |
| 442 | ) files (slot_name, slot_file) |
| 443 | ON slot.slot_name = files.slot_name |
| 444 | CROSS JOIN wal_size s |
| 445 | ) AS d |
| 446 | GROUP BY |
| 447 | slot_name, |
| 448 | slot_type, |
| 449 | replslot_wal_keep; |
| 450 | ` |
| 451 | } |
| 452 | |
| 453 | func queryQueryableDatabaseList() string { |
| 454 | return ` |
| 455 | SELECT datname |
| 456 | FROM pg_database |
| 457 | WHERE datallowconn = true |
| 458 | AND datistemplate = false |
| 459 | AND datname != current_database() |
| 460 | AND has_database_privilege((SELECT CURRENT_USER), datname, 'connect'); |
| 461 | ` |
| 462 | } |
| 463 | |
| 464 | func queryDatabaseStats() string { |
| 465 | // definition by version: https://pgpedia.info/p/pg_stat_database.html |
| 466 | // docs: https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW |
| 467 | // code: https://github.com/postgres/postgres/blob/366283961ac0ed6d89014444c6090f3fd02fce0a/src/backend/catalog/system_views.sql#L1018 |
| 468 | |
| 469 | return ` |
| 470 | SELECT stat.datname, |
| 471 | numbackends, |
| 472 | pg_database.datconnlimit, |
| 473 | xact_commit, |
| 474 | xact_rollback, |
| 475 | blks_read * current_setting('block_size')::numeric AS blks_read_bytes, |
| 476 | blks_hit * current_setting('block_size')::numeric AS blks_hit_bytes, |
| 477 | tup_returned, |
| 478 | tup_fetched, |
| 479 | tup_inserted, |
| 480 | tup_updated, |
| 481 | tup_deleted, |
| 482 | conflicts, |
| 483 | temp_files, |
| 484 | temp_bytes, |
| 485 | deadlocks |
| 486 | FROM pg_stat_database stat |
| 487 | INNER JOIN |
| 488 | pg_database |
| 489 | ON pg_database.datname = stat.datname |
| 490 | WHERE pg_database.datistemplate = false; |
| 491 | ` |
| 492 | } |
| 493 | |
| 494 | func queryDatabaseSize(version int) string { |
| 495 | if version < pgVersion10 { |
| 496 | return ` |
| 497 | SELECT datname, |
| 498 | pg_database_size(datname) AS size |
| 499 | FROM pg_database |
| 500 | WHERE pg_database.datistemplate = false |
| 501 | AND has_database_privilege((SELECT CURRENT_USER), pg_database.datname, 'connect'); |
| 502 | ` |
| 503 | } |
| 504 | return ` |
| 505 | SELECT datname, |
| 506 | pg_database_size(datname) AS size |
| 507 | FROM pg_database |
| 508 | WHERE pg_database.datistemplate = false |
| 509 | AND (has_database_privilege((SELECT CURRENT_USER), datname, 'connect') |
| 510 | OR pg_has_role((SELECT CURRENT_USER), 'pg_read_all_stats', 'MEMBER')); |
| 511 | ` |
| 512 | } |
| 513 | |
| 514 | func queryDatabaseConflicts() string { |
| 515 | // definition by version: https://pgpedia.info/p/pg_stat_database_conflicts.html |
| 516 | // docs: https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-CONFLICTS-VIEW |
| 517 | // code: https://github.com/postgres/postgres/blob/366283961ac0ed6d89014444c6090f3fd02fce0a/src/backend/catalog/system_views.sql#L1058 |
| 518 | |
| 519 | return ` |
| 520 | SELECT stat.datname, |
| 521 | confl_tablespace, |
| 522 | confl_lock, |
| 523 | confl_snapshot, |
| 524 | confl_bufferpin, |
| 525 | confl_deadlock |
| 526 | FROM pg_stat_database_conflicts stat |
| 527 | INNER JOIN |
| 528 | pg_database |
| 529 | ON pg_database.datname = stat.datname |
| 530 | WHERE pg_database.datistemplate = false; |
| 531 | ` |
| 532 | } |
| 533 | |
| 534 | func queryDatabaseLocks() string { |
| 535 | // definition by version: https://pgpedia.info/p/pg_locks.html |
| 536 | // docs: https://www.postgresql.org/docs/current/view-pg-locks.html |
| 537 | |
| 538 | return ` |
| 539 | SELECT pg_database.datname, |
| 540 | mode, |
| 541 | granted, |
| 542 | count(mode) AS locks_count |
| 543 | FROM pg_locks |
| 544 | INNER JOIN |
| 545 | pg_database |
| 546 | ON pg_database.oid = pg_locks.database |
| 547 | WHERE pg_database.datistemplate = false |
| 548 | GROUP BY datname, |
| 549 | mode, |
| 550 | granted |
| 551 | ORDER BY datname, |
| 552 | mode; |
| 553 | ` |
| 554 | } |
| 555 | |
| 556 | func queryUserTablesCount() string { |
| 557 | return "SELECT count(*) from pg_stat_user_tables;" |
| 558 | } |
| 559 | |
| 560 | func queryStatUserTables() string { |
| 561 | return ` |
| 562 | SELECT current_database() as datname, |
| 563 | schemaname, |
| 564 | relname, |
| 565 | inh.parent_relname, |
| 566 | seq_scan, |
| 567 | seq_tup_read, |
| 568 | idx_scan, |
| 569 | idx_tup_fetch, |
| 570 | n_tup_ins, |
| 571 | n_tup_upd, |
| 572 | n_tup_del, |
| 573 | n_tup_hot_upd, |
| 574 | n_live_tup, |
| 575 | n_dead_tup, |
| 576 | EXTRACT(epoch from now() - last_vacuum) as last_vacuum, |
| 577 | EXTRACT(epoch from now() - last_autovacuum) as last_autovacuum, |
| 578 | EXTRACT(epoch from now() - last_analyze) as last_analyze, |
| 579 | EXTRACT(epoch from now() - last_autoanalyze) as last_autoanalyze, |
| 580 | vacuum_count, |
| 581 | autovacuum_count, |
| 582 | analyze_count, |
| 583 | autoanalyze_count, |
| 584 | pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(relname)) as total_relation_size |
| 585 | FROM pg_stat_user_tables |
| 586 | LEFT JOIN( |
| 587 | SELECT |
| 588 | c.oid AS child_oid, |
| 589 | p.relname AS parent_relname |
| 590 | FROM |
| 591 | pg_inherits |
| 592 | JOIN pg_class AS c ON (inhrelid = c.oid) |
| 593 | JOIN pg_class AS p ON (inhparent = p.oid) |
| 594 | ) AS inh ON inh.child_oid = relid |
| 595 | WHERE has_schema_privilege(schemaname, 'USAGE'); |
| 596 | ` |
| 597 | } |
| 598 | |
| 599 | func queryStatIOUserTables() string { |
| 600 | return ` |
| 601 | SELECT current_database() AS datname, |
| 602 | schemaname, |
| 603 | relname, |
| 604 | inh.parent_relname, |
| 605 | heap_blks_read * current_setting('block_size')::numeric AS heap_blks_read_bytes, |
| 606 | heap_blks_hit * current_setting('block_size')::numeric AS heap_blks_hit_bytes, |
| 607 | idx_blks_read * current_setting('block_size')::numeric AS idx_blks_read_bytes, |
| 608 | idx_blks_hit * current_setting('block_size')::numeric AS idx_blks_hit_bytes, |
| 609 | toast_blks_read * current_setting('block_size')::numeric AS toast_blks_read_bytes, |
| 610 | toast_blks_hit * current_setting('block_size')::numeric AS toast_blks_hit_bytes, |
| 611 | tidx_blks_read * current_setting('block_size')::numeric AS tidx_blks_read_bytes, |
| 612 | tidx_blks_hit * current_setting('block_size')::numeric AS tidx_blks_hit_bytes |
| 613 | FROM pg_statio_user_tables |
| 614 | LEFT JOIN( |
| 615 | SELECT |
| 616 | c.oid AS child_oid, |
| 617 | p.relname AS parent_relname |
| 618 | FROM |
| 619 | pg_inherits |
| 620 | JOIN pg_class AS c ON (inhrelid = c.oid) |
| 621 | JOIN pg_class AS p ON (inhparent = p.oid) |
| 622 | ) AS inh ON inh.child_oid = relid |
| 623 | WHERE has_schema_privilege(schemaname, 'USAGE'); |
| 624 | ` |
| 625 | } |
| 626 | |
| 627 | func queryUserIndexesCount() string { |
| 628 | return "SELECT count(*) from pg_stat_user_indexes;" |
| 629 | } |
| 630 | |
| 631 | func queryStatUserIndexes() string { |
| 632 | return ` |
| 633 | SELECT current_database() as datname, |
| 634 | schemaname, |
| 635 | relname, |
| 636 | indexrelname, |
| 637 | inh.parent_relname, |
| 638 | idx_scan, |
| 639 | idx_tup_read, |
| 640 | idx_tup_fetch, |
| 641 | pg_relation_size(quote_ident(schemaname) || '.' || quote_ident(indexrelname)::text) as size |
| 642 | FROM pg_stat_user_indexes |
| 643 | LEFT JOIN( |
| 644 | SELECT |
| 645 | c.oid AS child_oid, |
| 646 | p.relname AS parent_relname |
| 647 | FROM |
| 648 | pg_inherits |
| 649 | JOIN pg_class AS c ON (inhrelid = c.oid) |
| 650 | JOIN pg_class AS p ON (inhparent = p.oid) |
| 651 | ) AS inh ON inh.child_oid = relid |
| 652 | WHERE has_schema_privilege(schemaname, 'USAGE'); |
| 653 | ` |
| 654 | } |
| 655 | |
| 656 | // The following query for bloat was taken from the venerable check_postgres |
| 657 | // script (https://bucardo.org/check_postgres/), which is: |
| 658 | // |
| 659 | // Copyright (c) 2007-2017 Greg Sabino Mullane |
| 660 | //------------------------------------------------------------------------------ |
| 661 | |
| 662 | func queryBloat() string { |
| 663 | return ` |
| 664 | SELECT |
| 665 | current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta, |
| 666 | ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat, |
| 667 | CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages, |
| 668 | CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes, |
| 669 | CASE WHEN relpages < otta THEN '0 bytes'::text ELSE (bs*(relpages-otta))::bigint::text || ' bytes' END AS wastedsize, |
| 670 | iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta, |
| 671 | ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat, |
| 672 | CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages, |
| 673 | CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes, |
| 674 | CASE WHEN ipages < iotta THEN '0 bytes' ELSE (bs*(ipages-iotta))::bigint::text || ' bytes' END AS wastedisize, |
| 675 | CASE WHEN relpages < otta THEN |
| 676 | CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END |
| 677 | ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint) |
| 678 | ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END |
| 679 | END AS totalwastedbytes |
| 680 | FROM ( |
| 681 | SELECT |
| 682 | nn.nspname AS schemaname, |
| 683 | cc.relname AS tablename, |
| 684 | COALESCE(cc.reltuples,0) AS reltuples, |
| 685 | COALESCE(cc.relpages,0) AS relpages, |
| 686 | COALESCE(bs,0) AS bs, |
| 687 | COALESCE(CEIL((cc.reltuples*((datahdr+ma- |
| 688 | (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta, |
| 689 | COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages, |
| 690 | COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols |
| 691 | FROM |
| 692 | pg_class cc |
| 693 | JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> 'information_schema' |
| 694 | LEFT JOIN |
| 695 | ( |
| 696 | SELECT |
| 697 | ma,bs,foo.nspname,foo.relname, |
| 698 | (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr, |
| 699 | (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2 |
| 700 | FROM ( |
| 701 | SELECT |
| 702 | ns.nspname, tbl.relname, hdr, ma, bs, |
| 703 | SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth, |
| 704 | MAX(coalesce(null_frac,0)) AS maxfracsum, |
| 705 | hdr+( |
| 706 | SELECT 1+count(*)/8 |
| 707 | FROM pg_stats s2 |
| 708 | WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname |
| 709 | ) AS nullhdr |
| 710 | FROM pg_attribute att |
| 711 | JOIN pg_class tbl ON att.attrelid = tbl.oid |
| 712 | JOIN pg_namespace ns ON ns.oid = tbl.relnamespace |
| 713 | LEFT JOIN pg_stats s ON s.schemaname=ns.nspname |
| 714 | AND s.tablename = tbl.relname |
| 715 | AND s.inherited=false |
| 716 | AND s.attname=att.attname, |
| 717 | ( |
| 718 | SELECT |
| 719 | (SELECT current_setting('block_size')::numeric) AS bs, |
| 720 | CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM '#"[0-9]+.[0-9]+#"%' for '#') |
| 721 | IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr, |
| 722 | CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS ma |
| 723 | FROM (SELECT version() AS v) AS foo |
| 724 | ) AS constants |
| 725 | WHERE att.attnum > 0 AND tbl.relkind='r' |
| 726 | GROUP BY 1,2,3,4,5 |
| 727 | ) AS foo |
| 728 | ) AS rs |
| 729 | ON cc.relname = rs.relname AND nn.nspname = rs.nspname |
| 730 | LEFT JOIN pg_index i ON indrelid = cc.oid |
| 731 | LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid |
| 732 | ) AS sml |
| 733 | WHERE sml.relpages - otta > 10 OR ipages - iotta > 10; |
| 734 | ` |
| 735 | } |
| 736 | |
| 737 | func queryColumnsStats() string { |
| 738 | return ` |
| 739 | SELECT current_database() AS datname, |
| 740 | nspname AS schemaname, |
| 741 | relname, |
| 742 | st.attname, |
| 743 | typname, |
| 744 | (st.null_frac * 100)::int AS null_percent, |
| 745 | case |
| 746 | when st.n_distinct >= 0 |
| 747 | then st.n_distinct |
| 748 | else |
| 749 | abs(st.n_distinct) * reltuples |
| 750 | end AS "distinct" |
| 751 | FROM pg_class c |
| 752 | JOIN |
| 753 | pg_namespace ns |
| 754 | ON |
| 755 | (ns.oid = relnamespace) |
| 756 | JOIN |
| 757 | pg_attribute at |
| 758 | ON |
| 759 | (c.oid = attrelid) |
| 760 | JOIN |
| 761 | pg_type t |
| 762 | ON |
| 763 | (t.oid = atttypid) |
| 764 | JOIN |
| 765 | pg_stats st |
| 766 | ON |
| 767 | (st.tablename = relname AND st.attname = at.attname) |
| 768 | WHERE relkind = 'r' |
| 769 | AND nspname NOT LIKE E'pg\\_%' |
| 770 | AND nspname != 'information_schema' |
| 771 | AND NOT attisdropped |
| 772 | AND attstattarget != 0 |
| 773 | AND reltuples >= 100 |
| 774 | ORDER BY nspname, |
| 775 | relname, |
| 776 | st.attname; |
| 777 | ` |
| 778 | } |