Monitoring Queries

Transactions Per Second

This will get tps for all your databases. The “datname” piece of the select matches all databases, and you’ll get a different data point for each that can be sent to graphite.

psql -c "SELECT datname, xact_commit + xact_rollback AS tps FROM pg_stat_database;"

Write Activity

This command will print out a summary of writes (inserts and updates).

psql -c "SELECT sum(n_tup_ins) AS inserts, sum(n_tup_upd) AS updates, sum(n_tup_del) AS deletes FROM pg_stat_user_tables;"

Replication Delay/Lag

This command will show you how far behind a slave (or slaves) are. Run this from the master server.

psql -c "SELECT coalesce(round(extract(epoch from (now() - pg_last_xact_replay_timestamp()))), 0) AS time_lag;"

Client Activity

This will show you the client activity currently going on, i use similarly to mysql's show processlist.

psql -c "SELECT count(1) AS total, sum(CASE WHEN state = 'idle' THEN 1 ELSE 0 END) AS idle, sum(CASE WHEN state LIKE 'idle in%' THEN 1 ELSE 0 END) AS trans_idle, sum(CASE WHEN state = 'active' THEN 1 ELSE 0 END) AS active, sum(waiting::INT) AS waiting, sum(CASE WHEN now()-query_start > INTERVAL '1s' AND state = 'active' THEN 1 ELSE 0 END) AS slow FROM public.pg_stat_activity();"

Checkpoint Count and Seconds Since Last

This will show you the number of checkpoints and how long it has been since the last one.

psql -c "SELECT total_checkpoints, seconds_since_start / total_checkpoints / 60 AS minutes_between_checkpoints FROM (SELECT EXTRACT(EPOCH FROM (now() - pg_postmaster_start_time())) AS seconds_since_start, (checkpoints_timed+checkpoints_req) AS total_checkpoints FROM pg_stat_bgwriter) AS sub;"