Postgres monitoring

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  usename,
  query,
  state,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE state = 'active' and (now() - pg_stat_activity.query_start) > interval '1 minute';

The wait_event_type and wait_event columns will show IO and DataFileRead if the query is reading from disk.

List Databases and Sizes

SELECT pg_database.datname, 
       pg_size_pretty(pg_database_size(pg_database.datname)) AS size 
FROM pg_database;

Show Running Queries in Postgres

SELECT * FROM pg_stat_activity;

Show Blocking Locks

SELECT bl.pid AS blocked_pid, 
       a.usename AS blocked_user, 
       kl.pid AS blocking_pid, 
       ka.usename AS blocking_user 
 FROM pg_catalog.pg_locks bl 
 JOIN pg_catalog.pg_stat_activity a ON bl.pid = a.pid 
 JOIN pg_catalog.pg_locks kl 
 JOIN pg_catalog.pg_stat_activity ka ON kl.pid = ka.pid ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid 
WHERE NOT bl.granted ;

Show Table Usage

If you want to know accesses or I/O per table or index you can use the pg_stat_tables and pg_statio_tables relations. For example:

SELECT * FROM pg_statio_user_tables;

to show the I/O caused by your relations. Or for the number of accesses and scan types and tuples fetched:

SELECT * FROM pg_stat_user_tables;

Show table size

monitor_table_size.sh:
 #!/bin/bash
 # Get table sizes in MB
 psql -U your_username -d your_database -c "SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS size FROM pg_tables ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;" >> /path/to/table_sizes.log

Monitor replication lag

monitor_replication_lag.sh:
#!/bin/bash
 # Get replication lag in bytes
 psql -U your_username -d your_database -c "SELECT
 pg_is_in_recovery() AS is_slave,
 pg_last_wal_receive_lsn() AS receive_lsn,
 pg_last_wal_replay_lsn() AS replay_lsn,
 pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() AS is_synced,
 EXTRACT(EPOCH FROM now()) - EXTRACT(EPOCH FROM pg_last_xact_replay_timestamp()) AS replication_lag_seconds,
 pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS replication_lag_bytes
 FROM
 pg_stat_replication;» >> /path/to/replication_lag.log

Long running query

monitor_long_running_queries.sh:
 #!/bin/bash
 # Set the threshold for long-running queries in seconds
 THRESHOLD=60
 # Log queries exceeding the threshold
 psql -U your_username -d your_database -c "SELECT * FROM pg_stat_activity WHERE state = 'active' AND now() - pg_stat_activity.query_start >= interval '$THRESHOLD seconds';" >> /path/to/long_running_queries.log