Create script to loop every db for a certain amount time for info

Collect data for a variety of metrics on every database (primary and replica, main, ci, etc), every 5 seconds in a loop, for 5 minutes or until cancelled:

  • Table sizes for all relations
  • Snapshots of pg_stat_activity
  • Snapshots of pg_locks, taken at the same time as pg_stat_activity so data can be correlated.
  • Snapshots of pg_stat_user_tables, pg_stat_user_indexes, pg_statio_user_tables, pg_statio_user_indexes
Edited by Irina Bronipolsky